Rem Rem $Header: rdbms/admin/dbmsmetu.sql /st_rdbms_11.2.0/5 2011/07/15 12:07:27 dgagne Exp $ Rem Rem dbmsmetu.sql Rem Rem Copyright (c) 2001, 2011, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem dbmsmetu.sql - Package header for DBMS_METADATA_UTIL. Rem NOTE - Package body is in: Rem /vobs/rdbms/src/server/datapump/ddl/prvtmetu.sql Rem DESCRIPTION Rem This file contains the package header for DBMS_METADATA_UTIL, Rem a definer's rights package that implements functions used by Rem both DBMS_METADATA and DBMS_METADATA_INT Rem Rem FUNCTIONS / PROCEDURES Rem PUT_LINE - Write debugging output. Rem PUT_BOOL - Write debugging output. Rem VSN2NUM - Convert version string to number. Rem GET_COMPAT_VSN - Return the compatibility version number as a number. Rem GET_DB_VSN - Return the database version number as a string Rem GET_CANONICAL_VSN - Convert user's VERSION param to canonical form. Rem CONVERT_TO_CANONICAL - Convert VERSION string to canonical form. Rem GET_LATEST_VSN - Return a number for the latest version number. Rem GET_OPEN_MODE - Return database open mode (read only, read write) Rem LONG2VARCHAR - Convert a table LONG value to a VARCHAR2. Rem LONG2VCMAX - Convert a table LONG value to a VARCHAR2 and each Rem line max length is 2000. Rem LONG2VCNT - Convert a table LONG value to a nested table of Rem VARCHAR2. Rem LONG2CLOB - Convert a table LONG value to a CLOB. Rem PARSE_CONDITION - Return a check condition as XML Rem PARSE_DEFAULT - Return the default value of a virt col as XML Rem PARSE_QUERY - Return a query as XML Rem NULLTOCHR0 - Replace \0 with CHR(0) in varchar Rem GET_SOURCE_LINES- Fetch/annotate lines from source$. Rem PARSE_TRIGGER_DEFINITION - Return annotated trigger definition. Rem GET_PROCOBJ_ERRORS - Get any errors raised by procedural object code Rem SAVE_PROCOBJ_ERRORS - Save errors raised by procedural object code Rem GET_AUDIT - Return audit information for a schema object. Rem GET_AUDIT_DEFAULT - Return default object audit information setting. Rem GET_ANC - Get the object number of the base table to which Rem a nested table belongs Rem GET_ENDIANNESS - Determine platform endianness. Rem SET_VERS_DPAPI - Save DPAPI version. Rem GET_VERS_DPAPI - Retrieve DPAPI version. Rem LOAD_STYLESHEETS- Load the XSL stylesheets into the database Rem ARE_STYLESHEETS_LOADED - Are the XSL stylesheets loaded? Rem SET_DEBUG - Set the internal debug switch. Rem PATCH_TYPEID - For transportable import, modify a type's typeid. Rem CHECK_TYPE - For transportable import, check a type's definition Rem and typeid. Rem WRITE_CLOB - Write a CLOB to the trace file Rem IS_OMF - determine if a name is a Oracle Managed File (OMF) Rem BLOB2CLOB - Convert a column default blob value to a clob. Rem GET_BASE_INTCOL_NUM - Return intcol# of base column, i.e., the Rem intcol# of the first column with this col# Rem GET_BASE_COL_TYPE - Return 1 if base column is udt, Rem 2 if base column is XMLType stored OR or CSX Rem 3 if base column is XMLType stored as CLOB Rem 0 if (a) intcol = base column or Rem (b) base column not udt or XMLType Rem REF_PAR_LEVEL - return level of ref partitioned child table Rem REF_PAR_PARENT - return object number of ref partitioned parent table Rem GET_EDITIONID - return ID for specified edition Rem GET_INDEX_INTCOL - Get intcol# in table of column on which index is Rem defined (need special handling for xmltype cols) Rem HAS_TSTZ_COLS - Determine whether a table has data of type DTYSTZ Rem DELETE_XMLSCHEMA Rem LOAD_XSD Rem GET_XMLHIERARCHY - return 'Y' if table is hierachy enabled, else null Rem GET_XMLCOLSET - OR storage columns for xmltype Rem GET_BASE_COL_NAME - Return name of base_col, if xmltype Rem IS_SCHEMANAME_EXISTS - Returns 1 if schema name exists in trigger Rem definition otherwise 0. Rem GET_MARKER - returns the current marker number Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem sdavidso 06/22/11 - parse item for Hierarchy Enabled Tables Rem lbarton 03/31/11 - Backport lbarton_bug-10363497 from main Rem sdavidso 12/23/10 - Extend full exp for options Rem sdavidso 11/16/10 - Backport sdavidso_bug-10107749 from main Rem tbhukya 10/27/10 - Backport tbhukya_bug-9841333 from main Rem sdavidso 03/22/10 - Bug 8847153: reduce resources for xmlschema Rem export Rem lbarton 03/23/09 - bug 8347514: parse read-only view query Rem sdavidso 02/27/09 - bug 7567327 - ORA-904 importing xmltype Rem sdavidso 03/06/09 - bug 8328108: add xml exclusion option to get_anc Rem lbarton 11/13/08 - TSTZ support Rem lbarton 12/30/08 - Bug 7354560: modify GET_SOURCE_LINES to trim Rem trailing newline Rem sdavidso 12/16/08 - bug 7620558: problems w/xmltype,ADT,nested tables Rem lbarton 06/10/08 - add xmlschema load/unload routines Rem lbarton 04/22/08 - bug 6730161: move get_hashcode to dbmsmeta Rem msakayed 04/17/08 - compression/encryption feature tracking for 11.2 Rem lbarton 02/05/08 - bug 6029076: query parsing; remove SET_PARSING Rem lbarton 01/31/08 - bug 5961283: get uncorrupted hashcode Rem lbarton 11/26/07 - bug 6454237: parse trigger definition Rem lbarton 10/31/07 - bug 6051635: domain index on xmltype col Rem htseng 04/23/07 - fix bug 5690152 - add post_keyw and pre_name_len Rem to source_t Rem msakayed 10/17/06 - Add UpdateFeatureTable for feature usage Rem sdavidso 09/22/06 - edition support Rem lbarton 07/18/06 - bug 5386908: get_xmltype_fmts Rem sdavidso 07/20/06 - modify ref_par_parent Rem lbarton 02/21/06 - PARSE_CONDITION, PARSE_DEFAULT, PARSE_QUERY Rem lbarton 10/05/05 - bug 4516042: xmlschemas and SB tables in Data Rem Pump Rem htseng 05/30/06 - add binary2varchar Rem sdavidso 05/12/06 - support for ref partitioning Rem rapayne 10/24/05 - Bug 4675928: add convert_to_canonical. Rem sdavidso 12/08/05 - add function for attrname Rem sdavidso 11/15/05 - Add routine to get fully qualified attrname Rem sdavidso 08/22/05 - add function to check for OMF files Rem rpfau 10/15/04 - bug 3599656 - Add check_type routine. Rem rapayne 10/15/04 - add prototypes for get_col_property Rem lbarton 09/01/04 - Bug 3827736: add NULLTOCHR0 function Rem lbarton 06/16/04 - Bug 3695154: add ARE_STYLESHEETS_LOADED Rem Modify LOAD_STYLESHEETS: dirpath not needed Rem lbarton 03/31/04 - Bug 3225530: use supplied version for domidx Rem lbarton 01/07/04 - Bug 3358912: force lob big endian Rem lbarton 11/04/03 - network debug Rem lbarton 10/02/03 - Bug 3167541: run domain index metadata code as Rem cur user Rem lbarton 09/16/03 - Bug 3121396: run procobj code as cur user Rem lbarton 08/12/03 - Bug 3082230: increase size of line_of_code Rem lbarton 07/31/03 - Bug 3056720: change long2clob interface Rem lbarton 07/17/03 - Bug 3045926: restructure ku$_procobj_lines Rem lbarton 07/03/03 - Bug 3016951: add patch_typeid Rem lbarton 06/06/03 - Bug 2849559: report errors from proc. actions Rem lbarton 05/01/03 - Bug 2925579: set transportable state Rem gclaborn 05/20/03 - Remove select_mode Rem lbarton 04/10/03 - bug 2893918: add PARSE_TRIGGER_DEFINITION Rem lbarton 04/04/03 - bug 2844111: add GET_SOURCE_LINES function Rem nmanappa 12/27/02 - Adding get_audit_default Rem lbarton 11/08/02 - new types for procedural objects Rem lbarton 10/23/02 - Test for READ_ONLY database Rem gclaborn 11/12/02 - add write_clob Rem htseng 12/11/02 - fix long2varchar each line >2499 Rem lbarton 08/02/02 - transportable export Rem htseng 06/25/02 - add post/pre table action support Rem lbarton 05/01/02 - domain index support Rem lbarton 04/25/02 - change CREATE SYNONYM to CREATE OR REPLACE Rem htseng 05/08/02 - add GET_PROCOBJ_GRANT. Rem htseng 05/02/02 - add procedural objects and actions API support. Rem lbarton 04/10/02 - add DPSTREAM_TABLE object Rem lbarton 03/21/02 - tweak select_mode Rem htseng 04/04/02 - add get_refresh_make and get_refresh_add function Rem lbarton 03/14/02 - add select_mode Rem htseng 12/07/01 - add java object support. Rem lbarton 11/27/01 - better error messages Rem lbarton 09/10/01 - Merged lbarton_mdapi_reorg Rem lbarton 09/05/01 - Split off from dbmsmeta.sql Rem -- Types used internally by mdAPI --------------------------------- -- Schema object audit settings are stored in tab$, etc. as a 38-byte -- field named audit$ where each byte corresponds to a different access -- type. This encoding is difficult for xsl to decode and process, -- so the function GET_AUDIT in this package unpacks the field into -- a nested table of ku$_audobj_t objects, each of which has the setting -- for one access type. CREATE TYPE sys.ku$_audobj_t AS OBJECT ( name VARCHAR2(31), -- operation to be audited, e.g., ALTER value CHAR(1), -- 'S' = by session -- 'A' = by access -- '-' = no auditing type CHAR(1) -- 'S' = when successful -- 'F' = when not successful ) / GRANT EXECUTE ON sys.ku$_audobj_t TO PUBLIC; CREATE OR REPLACE PUBLIC SYNONYM ku$_audobj_t FOR sys.ku$_audobj_t; CREATE TYPE sys.ku$_audit_list_t IS TABLE OF sys.ku$_audobj_t / GRANT EXECUTE ON sys.ku$_audit_list_t TO public; CREATE OR REPLACE PUBLIC SYNONYM ku$_audit_list_t FOR sys.ku$_audit_list_t; -- For storing default auditing options CREATE TYPE sys.ku$_auddef_t AS OBJECT ( name VARCHAR2(31), -- operation to be audited, e.g., ALTER value CHAR(1), -- 'S' = by session -- 'A' = by access -- '-' = no auditing type CHAR(1) -- 'S' = when successful -- 'F' = when not successful ) / GRANT EXECUTE ON sys.ku$_auddef_t TO PUBLIC; CREATE OR REPLACE PUBLIC SYNONYM ku$_auddef_t FOR sys.ku$_auddef_t; CREATE TYPE sys.ku$_audit_default_list_t IS TABLE OF sys.ku$_auddef_t / GRANT EXECUTE ON sys.ku$_audit_default_list_t TO public; CREATE OR REPLACE PUBLIC SYNONYM ku$_audit_default_list_t FOR sys.ku$_audit_default_list_t; -- ADT for list of columns for OR storage of xmltype CREATE TYPE ku$_XmlColSet_t IS TABLE OF NUMBER / GRANT EXECUTE ON sys.ku$_XmlColSet_t TO PUBLIC; CREATE OR REPLACE PUBLIC SYNONYM ku$_XmlColSet_t FOR sys.ku$_ObjNumSet; -- UDTs for lines of source CREATE TYPE sys.ku$_source_t AS OBJECT ( obj_num number, /* object number */ line number, /* line number */ -- -- The next 2 attributes are used by XSL scripts to edit the source line. -- E.g., in a type definition, the line might be 'type foobar as object' -- -- 'foobar' is the object name. Since the xsl script has already -- generated CREATE OR REPLACE TYPE FOOBAR, it uses 'post_name_off' -- to extract the useful part of the line. If the source were -- create type /* this is a comment -- that continues on the next line */ -- foobar -- which is rare but legal, the xsl script knows from 'pre_name' which -- lines are prior to the name and can safely be discarded. -- See bug 2844111 and rdbms/xml/xsl/kusource.xsl. pre_name number, /* 1 = this line is prior to line containing name */ post_name_off number, /* 1-based offset of 1st non-space char after name */ post_keyw number, /* the offset of post keyword */ pre_name_len number, /* length between keyword and name */ -- -- The next attribute is needed for appending the SQL terminator. -- If the last line ends in a newline, we simply add the "/"; -- otherwise, we must insert a newline before the "/". -- This attribute is NULL for all but the last line; for the last -- line it is "Y" if the line ends in a newline, "N" otherwise. trailing_nl char(1), source varchar2(4000) /* source line */ ) / GRANT EXECUTE ON sys.ku$_source_t TO PUBLIC / CREATE OR REPLACE PUBLIC SYNONYM ku$_source_t FOR sys.ku$_source_t; CREATE TYPE ku$_source_list_t AS TABLE OF sys.ku$_source_t; / GRANT EXECUTE ON ku$_source_list_t TO PUBLIC / CREATE OR REPLACE PUBLIC SYNONYM ku$_source_list_t FOR sys.ku$_source_list_t; CREATE OR REPLACE PACKAGE dbms_metadata_util AUTHID DEFINER AS ------------------------------------------------------------ -- Overview -- This pkg implements utility functions of the mdAPI. --------------------------------------------------------------------- -- SECURITY -- This package is owned by SYS. It runs with definers, not invokers rights -- because it needs to access dictionary tables. ------------- -- EXCEPTIONS -- invalid_argval EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_argval, -31600); invalid_argval_num NUMBER := -31600; -- "Invalid input value %s for parameter %s in function %s" -- *Cause: A NULL or invalid value was supplied for the parameter. -- *Action: Correct the input value and try the call again. invalid_operation EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_operation, -31601); invalid_operation_num NUMBER := -31601; -- "Function %s cannot be called now that fetch has begun" -- *Cause: The function was called after the first call to FETCH_xxx. -- *Action: Correct the program. inconsistent_args EXCEPTION; PRAGMA EXCEPTION_INIT(inconsistent_args, -31602); inconsistent_args_num NUMBER := -31602; -- "parameter %s value \"%s\" in function %s inconsistent with %s" -- "Value \"%s\" for parameter %s in function %s is inconsistent with %s" -- *Cause: The parameter value is inconsistent with another value specified -- by the program. It may be not valid for the the object type -- associated with the OPEN context, or it may be of the wrong -- datatype: a boolean rather than a text string or vice versa. -- *Action: Correct the program. object_not_found EXCEPTION; PRAGMA EXCEPTION_INIT(object_not_found, -31603); object_not_found_num NUMBER := -31603; -- "object \"%s\" of type %s not found in schema \"%s\"" -- *Cause: The specified object was not found in the database. -- *Action: Correct the object specification and try the call again. invalid_object_param EXCEPTION; PRAGMA EXCEPTION_INIT(invalid_object_param, -31604); invalid_object_param_num NUMBER := -31604; -- "invalid %s parameter \"%s\" for object type %s in function %s" -- *Cause: The specified parameter value is not valid for this object type. -- *Action: Correct the parameter and try the call again. inconsistent_operation EXCEPTION; PRAGMA EXCEPTION_INIT(inconsistent_operation, -31607); inconsistent_operation_num NUMBER := -31607; -- "Function %s is inconsistent with transform." -- *Cause: Either (1) FETCH_XML was called when the "DDL" transform -- was specified, or (2) FETCH_DDL was called when the -- "DDL" transform was omitted. -- *Action: Correct the program. object_not_found2 EXCEPTION; PRAGMA EXCEPTION_INIT(object_not_found2, -31608); object_not_found2_num NUMBER := -31608; -- "specified object of type %s not found" -- (Used by GET_DEPENDENT_xxx and GET_GRANTED_xxx.) -- *Cause: The specified object was not found in the database. -- *Action: Correct the object specification and try the call again. stylesheet_load_error EXCEPTION; PRAGMA EXCEPTION_INIT(stylesheet_load_error, -31609); stylesheet_load_error_num NUMBER := -31609; -- "error loading file %s from file system directory \'%s\'" -- *Cause: The installation script initmeta.sql failed to load -- the named file from the file system directory into the database. -- *Action: Examine the directory and see if the file is present -- and can be read. procobj_error EXCEPTION; PRAGMA EXCEPTION_INIT(procobj_error, -39127); procobj_error_num NUMBER := -39127; -- "Unexpected error from call to %s \n%s" -- *Cause: The exception was raised by the function invocation. -- *Action: Record the accompanying messages and report this as a Data Pump -- internal error to customer support. bad_hashcode EXCEPTION; PRAGMA EXCEPTION_INIT(bad_hashcode, -39132); bad_hashcode_num NUMBER := -39132; -- "Object type \"%s\".\"%s\" already exists with different hashcode" -- *Cause: An object type cannot be created because it already exists on the -- target system, but with a different hashcode. Tables in the -- transportable tablespace set which use this object type -- cannot be read. -- *Action: Drop the object type from the target system and retry the -- operation. type_in_use EXCEPTION; PRAGMA EXCEPTION_INIT(type_in_use, -39133); type_in_use_num NUMBER := -39133; -- "Object type \"%s\".\"%s\" already exists with different typeid" -- *Cause: An object type in a transportable tablespace set already exists -- on the target system, but with a different typeid. The typeid -- cannot be changed because the type is used by an existing table. -- Tables in the transportable tablespace set which use this object -- type cannot be read. -- *Action: Drop the object type from the target system and retry the -- operation. --------------------------- -- PACKAGE VARIABLES marker NUMBER := 42; -- marker number: used in worker/mcp to take actions -- at appropriate times, without depending on -- pathnames --------------------------- -- PROCEDURES AND FUNCTIONS -- -- PUT_LINE: Does a DBMS_OUTPUT.PUT_LINE regardless of string length; i.e, -- works with strings > 255. PROCEDURE put_line(stmt IN VARCHAR2); -- PUT_BOOL: Convenience function. PROCEDURE put_bool( stmt IN VARCHAR2, value IN BOOLEAN); -- VSN2NUM: Convert a dot-separated version string (e.g., '8.1.6.0.0') -- to a number (e.g., 8010600000). FUNCTION vsn2num ( vsn IN VARCHAR2) RETURN NUMBER; -- GET_COMPAT_VSN: return the compatibility version number as a number. -- E.g., if compatibility='8.1.6', return 801060000. FUNCTION get_compat_vsn RETURN NUMBER; -- GET_ATTRNAME: attrname for a table-column. FUNCTION get_attrname ( obj IN NUMBER, intcol IN NUMBER) RETURN VARCHAR2; -- GET_FULLATTRNAME: return fully qualified attrname, when attrname is a -- system generated name. FUNCTION get_fullattrname ( obj IN NUMBER, col IN NUMBER, intcol IN NUMBER, type IN NUMBER) RETURN VARCHAR2; -- GET_DB_VSN: return the database version number as a string -- in the format vv.vv.vv.vv.vv, e.g., '08.01.03.00.00' FUNCTION get_db_vsn RETURN VARCHAR2; -- GET_CANONICAL_VSN: convert the user's VERSION param to a string -- in the format vv.vv.vv.vv.vv, e.g., '08.01.03.00.00' -- PARAMETERS: -- version - The version from DBMS_METADATA.OPEN. -- Values can be 'COMPATIBLE' (default), 'LATEST' or a specific -- version number. FUNCTION get_canonical_vsn(version IN VARCHAR2) RETURN VARCHAR2; --------------------------------------------------------------------- -- CONVERT_TO_CANONICAL: Convert string to canonical form -- vv.vv.vv.vv.vv, e.g., '08.01.03.00.00' -- PARAMETERS: -- version - version string (e.g., 10.2.0.2) FUNCTION convert_to_canonical(version IN VARCHAR2) RETURN VARCHAR2; -- GET_LATEST_VSN: return a number that will serve as the latest version number FUNCTION get_latest_vsn RETURN NUMBER; -- GET_OPEN_MODE: return a number signifying the open mode of the database -- RETURNS: 0 = MOUNTED -- 1 = READ WRITE -- 2 = READ ONLY FUNCTION get_open_mode RETURN NUMBER; -- LONG2VARCHAR: Convert a LONG column value to a VARCHAR2 -- PARAMETERS: -- length - length of the LONG -- tab - table name -- col - column name -- row - rowid of the row -- RETURNS: LONG value converted to VARCHAR2 if length <= 4000 -- otherwise NULL FUNCTION long2varchar( length IN NUMBER, tab IN VARCHAR2, col IN VARCHAR2, row IN UROWID) RETURN VARCHAR2; -- LONG2VCMAX: Convert a LONG column value to a VARCHAR2 and each line -- max length is 2000 -- PARAMETERS: -- length - length of the LONG -- tab - table name -- col - column name -- row - rowid of the row -- RETURNS: LONG value converted to VARCHAR2 -- otherwise NULL FUNCTION long2vcmax( length IN NUMBER, tab IN VARCHAR2, col IN VARCHAR2, row IN UROWID) RETURN sys.ku$_vcnt; -- LONG2VCNT: Convert a LONG column value to an array of VARCHAR2 -- PARAMETERS: -- length - length of the LONG -- tab - table name -- col - column name -- row - rowid of the row -- RETURNS: LONG value converted to array of VARCHAR2 if length > 4000 -- otherwise NULL FUNCTION long2vcnt( length IN NUMBER, tab IN VARCHAR2, col IN VARCHAR2, row IN UROWID) RETURN sys.ku$_vcnt; -- LONG2CLOB: Convert a LONG column value to a CLOB -- PARAMETERS: -- length - length of the LONG -- tab - table name -- col - column name -- row - rowid of the row -- RETURNS: LONG value converted to temporary CLOB if length > 4000 -- otherwise NULL FUNCTION long2clob( length IN NUMBER, tab IN VARCHAR2, col IN VARCHAR2, row IN ROWID) RETURN CLOB; -- PARSE_CONDITION: Parse a check constraint condition on a table -- and return it as XML -- PARAMETERS: -- schema - schema -- tab - table name -- length - length of the constraint -- row - rowid of the row in CDEF$ -- RETURNS: XMLType containing parsed condition as XML -- if length is not NULL -- otherwise NULL FUNCTION parse_condition( schema IN VARCHAR2, tab IN VARCHAR2, length IN NUMBER, row IN ROWID) RETURN SYS.XMLTYPE; -- PARSE_DEFAULT: Parse the default value of a virtual column -- (which contains an arithmetic expression for a functional index) -- and return it as XML -- PARAMETERS: -- schema - schema -- tab - table name -- length - length of the default -- row - rowid of the row in COL$ -- RETURNS: XMLType containing parsed expression as XML -- if length is not NULL -- otherwise NULL FUNCTION parse_default( schema IN VARCHAR2, tab IN VARCHAR2, length IN NUMBER, row IN ROWID) RETURN SYS.XMLTYPE; -- PARSE_QUERY: Parse a query stored in a long column (e.g., view query). -- and return it as XML -- PARAMETERS: -- schema - schema -- length - length of the LONG -- tab - table name -- col - column name -- row - rowid of the row -- read_only - non-0 = query has 'with read only' -- check_option - non-0 = query has 'with check option' -- RETURNS: XMLType containing parsed query as XML -- if length is not NULL -- otherwise NULL FUNCTION parse_query( schema IN VARCHAR2, length IN NUMBER, tab IN VARCHAR2, col IN VARCHAR2, row IN ROWID, read_only IN NUMBER DEFAULT 0, check_option IN NUMBER DEFAULT 0) RETURN SYS.XMLTYPE; -- PARSE_QUERY: Parse a query stored in a clob -- and return it as XML -- PARAMETERS: -- schema - schema -- query - query text -- read_only - non-0 = query has 'with read only' -- check_option - non-0 = query has 'with check option' -- RETURNS: XMLType containing parsed query as XML -- if length is not NULL -- otherwise NULL FUNCTION parse_query( schema IN VARCHAR2, query IN CLOB, read_only IN NUMBER DEFAULT 0, check_option IN NUMBER DEFAULT 0) RETURN SYS.XMLTYPE; -- NULLTOCHR0 - Replace \0 with CHR(0) in varchar -- PARAMETERS: -- value - varchar value -- replace_quote - TRUE = replace ' with '' -- RETURNS: varchar value with substitutions made FUNCTION nulltochr0( value IN VARCHAR2, replace_quote IN BOOLEAN DEFAULT TRUE) RETURN VARCHAR2; -- GET_SOURCE_LINES: Get records from source$ for the object -- and annotate them to make xsl processing easier. -- PARAMETERS: -- obj_name - name of object -- obj_num - obj# of object -- type_num - type# of object -- RETURNS: Nested table containing the source lines FUNCTION get_source_lines( obj_name IN VARCHAR2, obj_num IN NUMBER, type_num IN NUMBER) RETURN sys.ku$_source_list_t; -- PARSE_TRIGGER_DEFINITION: Return "annotated" trigger definition -- to make xsl processing easier. -- PARAMETERS: -- owner - owner name -- obj_name - trigger name -- definition - the definition from trigger$ -- RETURNS: The annotated definition FUNCTION parse_trigger_definition( owner IN VARCHAR2, obj_name IN VARCHAR2, definition IN VARCHAR2) RETURN sys.ku$_source_t; -- SAVE_PROCOBJ_ERRORS: Construct a text string for a raised exception -- and store it in the package variable 'procobj_errors'. PROCEDURE save_procobj_errors( sql_stmt IN VARCHAR2 ); -- GET_PROCOBJ_ERRORS: Retrieve saved errors and reset state. -- PARAMETERS: -- err_list - the saved errors -- RETURN VALUE: -- error count PROCEDURE get_procobj_errors( err_list OUT sys.ku$_vcnt); -- GET_AUDIT: Return audit information for a schema object. -- PARAMETERS: -- obj_num - object number -- type_num - object type -- RETURNS: nested table of audit settings FUNCTION get_audit( obj_num IN NUMBER, type_num IN NUMBER ) RETURN sys.ku$_audit_list_t; -- GET_AUDIT_DEFAULT: Return default object audit information setting. -- PARAMETERS: -- obj_num - object number -- RETURNS: nested table of default audit settings FUNCTION get_audit_default( obj_num IN NUMBER) RETURN sys.ku$_audit_default_list_t; -- GET_ANC: Get the object number of the base table to which -- a nested table belongs -- PARAMETERS: -- nt - obj# of the nested table -- exclude_xml - 0 - return anc for all NTs -- 1 - exclude NTs for XMLtype OR storage -- 2 - include only NTs for XMLtype OR storage -- RETURNS: -- obj# of the base table FUNCTION get_anc( nt IN NUMBER, exclude_xml IN NUMBER := 1) RETURN NUMBER; -- GET_ENDIANNESS: function to determine endianness: -- RETURNS: -- 1 = big_endian -- 2 = little_endian FUNCTION get_endianness RETURN NUMBER; -- SET_VERS_DPAPI: Save Direct Path API version. -- PARAMETERS: -- version - version number PROCEDURE set_vers_dpapi ( version IN NUMBER); -- GET_VERS_DPAPI: Retrieve saved Direct Path API version. -- RETURNS: version number FUNCTION get_vers_dpapi RETURN NUMBER; -- SET_FORCE_LOB_BE: Save the 'force_lob_be' switch. -- PARAMETERS: -- value - switch value PROCEDURE set_force_lob_be ( value IN BOOLEAN); -- SET_FORCE_NO_ENCRYPT: Save the 'force_no_encrypt' switch. -- PARAMETERS: -- value - switch value PROCEDURE set_force_no_encrypt ( value IN BOOLEAN); -- GET_LOB_PROPERTY: Return lob$.property (but clear bit 0x0200 if -- force_lob_be is set; 0x0200 = LOB data in little endian format). -- PARAMETERS: -- objnum - obj# of table -- intcol_num - intcol# of column -- RETURNS: lob$.property, maybe with bit 0x0200 cleared FUNCTION get_lob_property ( objnum IN NUMBER, intcol_num IN NUMBER) RETURN NUMBER; -- GET_COL_PROPERTY: Return col$.property (but clear encryption bits if -- force_no_encrypt flag is set: -- 0x04000000 = 67108864 = Column is encrypted -- 0x20000000 = 536870912 = Column is encrypted without salt -- This is necessary when users do not specify an -- encryption_password and the data is written to the dumpfile -- in clear text although the col properity retains the -- encrypt property. -- PARAMETERS: -- objnum - obj# of table -- intcol_num - intcol# of column -- RETURNS: col$.property, maybe with encryption bits cleared FUNCTION get_col_property ( objnum IN NUMBER, intcol_num IN NUMBER) RETURN NUMBER; --------------------------------------------------------------------- -- GET_REFRESH_MAKE_USER: Return refresh group dbms_refresh.make execute string -- PARAMETERS: -- group_id - refresh group id -- RETURNS: executing string FUNCTION get_refresh_make_user ( group_id IN NUMBER) RETURN varchar2; --------------------------------------------------------------------- -- GET_REFRESH_ADD_USER: Return refresh group dbms_refresh.add execute string -- PARAMETERS: -- owner - snapshot owner -- child - snapshot name -- type - type name -- instsite - site id -- RETURNS: executing string FUNCTION get_refresh_add_user ( owner IN VARCHAR2, child IN VARCHAR2, type IN VARCHAR2, instsite IN VARCHAR2 ) RETURN varchar2; --------------------------------------------------------------------- -- GET_REFRESH_MAKE_DBA: Return refresh group dbms_irefresh.make execute string -- PARAMETERS: -- group_id - refresh group id -- RETURNS: executing string FUNCTION get_refresh_make_dba ( group_id IN NUMBER) RETURN varchar2; --------------------------------------------------------------------- -- GET_REFRESH_ADD_DBA: Return refresh group dbms_irefresh.add execute string -- PARAMETERS: -- owner - snapshot owner -- child - snapshot name -- type - type name -- instsite - site id -- RETURNS: executing string FUNCTION get_refresh_add_dba ( owner IN VARCHAR2, child IN VARCHAR2, type IN VARCHAR2, instsite IN VARCHAR2 ) RETURN varchar2; --------------------------------------------------------------------- -- LOAD_STYLESHEETS: Load the XSL stylesheets into the database PROCEDURE load_stylesheets; --------------------------------------------------------------------- -- ARE_STYLESHEETS_LOADED: Are the XSL stylesheets loaded? -- RETURNS: FALSE = definitely not FUNCTION are_stylesheets_loaded RETURN BOOLEAN; --------------------------------------------------------------------- -- SET_DEBUG: Set the internal debug switch. -- PARAMETERS: -- on_off - new switch state. PROCEDURE set_debug( on_off IN BOOLEAN, force_trace IN BOOLEAN DEFAULT FALSE); ----------------------------------------------------------------------- -- PATCH_TYPEID: For transportable import, modify a type's typeid. -- PARAMETERS: -- schema - the type's schema -- name - the type's name -- typeid - the type's typeid -- hashcode - the type's hashcode PROCEDURE patch_typeid ( schema IN VARCHAR2, name IN VARCHAR2, typeid IN VARCHAR2, hashcode IN VARCHAR2); ----------------------------------------------------------------------- -- CHECK_TYPE: For transportable import, check a type's definition (using the -- hashcode) and typeid for a match against the one from the -- export source database. This will catch differences in -- a pre-existing type with the same name which already exists on -- the import target database. This routine is called for each -- referenced type right before a create table call is made. -- If any of these calls raises an exception, then the table -- is not created. -- PARAMS: -- schema - schema of type -- type_name - type name -- version - internal stored verson of type -- hashcode - hashcode of the type defn -- typeid - subtype typeid ('' if no subtypes) -- RETURNS: Nothing, returns if the hashcode and version match. Raises an -- nnn exception if the type does not exist in the db or if the -- type exists but the hash code and/or the version number does -- not match. -- PROCEDURE check_type (schema IN VARCHAR2, type_name IN VARCHAR2, version IN VARCHAR2, hashcode IN VARCHAR2, typeid IN VARCHAR2); ----------------------------------------------------------------------- -- WRITE_CLOB : Write a CLOB to the trace file PROCEDURE write_clob(xml IN CLOB); --------------------------------------------------------------------- -- IS_OMF: return 1 if name is an OMF, 0 otherwise. -- PARAMETERS: -- name - a file name -- RETURNS: number 0 or 1 FUNCTION is_omf ( name IN VARCHAR2 ) RETURN number; --------------------------------------------------------------------- -- ref_par_level: returns the level number of a reference partitioned -- child table. -- PARAMETERS: -- objnum - object number of target table -- RETURNS: number 1 or greater FUNCTION ref_par_level ( objnum IN NUMBER ) RETURN number; FUNCTION ref_par_level ( objnum IN NUMBER, properties IN NUMBER ) RETURN number; FUNCTION ref_par_parent ( objnum IN NUMBER ) RETURN NUMBER; ---------------------------------------------------------------------------- -- GET_XMLTYPE_FMTS: Return formats of XMLType columns in a table -- PARAMETERS: -- objnum - table object number -- RETURNS: 0x01 = table has XMLType stored as CLOB -- 0x02 = table has XMLType stored OR or binary FUNCTION get_xmltype_fmts( objnum IN number) RETURN NUMBER; ---------------------------------------------------------------------------- -- BLOB2CLOB: Convert a column default replace null blob to a clob -- PARAMETERS: -- tabobj - tab object num -- incolnum - incolumn num -- RETURNS: clob ( this is hex value to pass to hextoraw as argument) FUNCTION blob2clob( tabobj IN NUMBER, incolnum IN NUMBER) RETURN CLOB; ----------------------------------------------------------------------- -- GET_BASE_INTCOL_NUM - Return intcol# of base column. -- For lobs associated with XMLType columns, -- this is the intcol# of the XMLType column; -- otherwise, intcol# of the first column with this col# -- PARAMETERS: -- objnum - table obj# -- colnum - col# of this column -- intcol - intcol# of this column -- typenum - type# of this column FUNCTION get_base_intcol_num ( objnum IN NUMBER, colnum IN NUMBER, intcol IN NUMBER, typenum IN NUMBER ) RETURN NUMBER; ----------------------------------------------------------------------- -- GET_BASE_COL_TYPE - Return 1 if base column is udt, -- 2 if base column is XMLType stored OR or CSX -- 3 if base column is XMLType stored as LOB -- 0 if intcol = base column or base column not -- udt or XMLType -- PARAMETERS: -- objnum - table obj# -- colnum - col# of this column -- intcol - intcol# of this column -- typenum - type# of this column FUNCTION get_base_col_type ( objnum IN NUMBER, colnum IN NUMBER, intcol IN NUMBER, typenum IN NUMBER ) RETURN NUMBER; ----------------------------------------------------------------------- -- GET_BASE_COL_NAME - return name of base xmltype column -- PARAMETERS: -- objnum - table obj# -- colnum - col# of this column -- intcol - intcol# of this column -- typenum - type# of this column FUNCTION get_base_col_name ( objnum IN NUMBER, colnum IN NUMBER, intcol IN NUMBER, typenum IN NUMBER ) RETURN VARCHAR2; ----------------------------------------------------------------------- -- GET_EDITIONID - Return the ID of a specified edition -- an exception will be raised on a non-existent editn -- PARAMETERS: -- edition - edition name FUNCTION get_editionid ( edition IN VARCHAR2 ) RETURN NUMBER; --------------------------------------------------------------------- -- GET_INDEX_INTCOL - Get intcol# in table of column on which index is -- defined (need special handling for xmltype cols) -- PARAMETERS: -- obj_num - base table object # -- intcol_num - intcol# from icol$ -- FUNCTION get_index_intcol(obj_num IN NUMBER, intcol_num in NUMBER) RETURN NUMBER; --------------------------------------------------------------------- -- HAS_TSTZ_COLS - Determine whether a table has data of type DTYSTZ -- (type# = 181): "TIMESTAMP WITH TIME ZONE" -- If so, data pump may have to convert the data. -- The data may be in a top-level column, an object column, -- or a varray. -- PARAMETERS: -- obj_num - table object # -- RETURNS: -- 'Y' - it does -- 'N' - it does not FUNCTION has_tstz_cols(obj_num IN NUMBER) RETURN CHAR; --------------------------------------------------------------------- -- HAS_TSTZ_ELEMENTS - Determine whether a varray type has TSTZ elements. -- This is a jacket function around utl_xml.haststz -- PARAMETERS: -- type_schema -- type_name -- RETURNS: -- 'Y' - it does -- 'N' - it does not FUNCTION has_tstz_elements(type_schema IN VARCHAR2, type_name IN VARCHAR2) RETURN CHAR; --------------------------------------------------------------------- -- DELETE_XMLSCHEMA: call dbms_xmlschema.deleteSchema -- to delete the named schema PROCEDURE DELETE_XMLSCHEMA(name varchar2); --------------------------------------------------------------------- -- LOAD_XSD: call dbms_xmlschema.registerSchema -- to register the named schema PROCEDURE LOAD_XSD(filename varchar2); --------------------------------------------------------------------- -- GET_XMLCOLSET - return nested table of intcol numbers for OR storage -- columns for xmltypes in table -- -- PARAMETERS: -- obj_num - object number of table FUNCTION get_xmlcolset (obj_num IN NUMBER) RETURN ku$_XmlColSet_t; --------------------------------------------------------------------- -- GET_XMLHIERARCHY - return 'Y' if table is hierachy enabled, else null -- -- PARAMETERS: -- schema - owner of table -- name - name of table FUNCTION get_xmlhierarchy ( schema IN VARCHAR2, name IN VARCHAR2) RETURN CHAR; --------------------------------------------------------------------- -- isXml - return number indicating if intcol is OR storage -- column of an xmltype column in table -- PARAMETERS: -- obj_num - object number of table -- intcol - column to be tested -- RETURNS -- 0 = not part of xmltype column -- 1 = is part of xmltype column FUNCTION isXml (obj_num IN NUMBER, intcol IN NUMBER) return NUMBER; --------------------------------------------------------------------- -- isXml - another variant, starting with nested table -- column of an xmltype column in table -- PARAMETERS: -- nt_num - nt number of nested table -- RETURNS -- 0 = not part of xmltype column -- 1 = is part of xmltype column FUNCTION isXml (nt_num IN NUMBER) return NUMBER; --------------------------------------------------------------------- -- IS_SCHEMANAME_EXISTS - Return 1 if schema name exists in trigger definition -- 0 other wise -- PARAMETERS: -- tdefinition - Trigger definition FUNCTION is_schemaname_exists(tdefinition varchar2) RETURN NUMBER; --------------------------------------------------------------------- -- GET_MARKER - Return the current marker number -- PARAMETERS: -- none FUNCTION get_marker RETURN NUMBER; END DBMS_METADATA_UTIL; / GRANT EXECUTE ON sys.dbms_metadata_util TO EXECUTE_CATALOG_ROLE;