| General Information |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmsmetu.sql |
| First Available |
9.0.1 |
| Constants |
| Name |
Data Type |
Value |
| marker |
NUMBER |
42 |
|
|
| Data Types |
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
CREATE TYPE sys.ku$_audit_list_t IS TABLE OF sys.ku$_audobj_t
/
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
/
CREATE TYPE sys.ku$_audit_default_list_t IS TABLE OF sys.ku$_auddef_t;
/
CREATE TYPE sys.ku$_source_t AS OBJECT (
obj_num NUMBER, -- object number
line NUMBER, -- line number
pre_name NUMBER,
post_name_off NUMBER,
post_keyw NUMBER, -- the offset of post keyword
pre_name_len NUMBER, -- length between keyword and name
source VARCHAR2(4000)) -- source line
/
CREATE TYPE ku$_source_list_t AS TABLE OF sys.ku$_source_t;
/ |
| Dependencies |
SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_METADATA_UTIL'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_METADATA_UTIL'; |
| Exceptions |
| Exception Name |
Error Code |
Reason |
| invalid_argval |
31600 |
Invalid argument |
| invalid_operation |
31601 |
invalid operation |
| inconsistent_args |
31602 |
inconsistent arguments |
| object_not_found |
31603 |
object not found |
| invalid_objeject_param |
31604 |
invalid object parameter |
| inconsistent_operation |
31607 |
inconsistent operation |
| object_not_found2 |
31608 |
object not found |
| stylesheet_load_error |
31609 |
installation script initmeta.sql failed to load the named file |
| procobj_error |
39127 |
DataPump Internal Error |
| bad_hashcode |
39132 |
Object exists with different hash code on the target system |
| type_in_use |
39133 |
Object type already exists with different typeid |
|
| Security Model |
Execute is granted to the EXECUTE_CATALOG_ROLE role |
| Subprograms |
|
| |
| ARE_STYLESHEETS_LOADED |
| Are the XSL stylesheets loaded? |
dbms_metadata_util.are_stylesheets_loaded RETURN BOOLEAN; |
set serveroutput on
BEGIN
IF dbms_metadata_util.are_stylesheets_loaded THEN
dbms_output.put_line('TRUE');
ELSE
dbms_output.put_line('FALSE');
END IF;
END;
/ |
| |
BLOB2CLOB (renamed and modified in 11.2.0.3 from BINARY2VARCHAR)  |
| Converts a column default replace NULL BLOB into a CLOB |
dbms_metadata_util.blob2clob(tabobj IN NUMBER, incolnum IN NUMBER) RETURN CLOB; |
conn pm/pm
SELECT object_id
FROM user_objects
WHERE object_name = 'PRINT_MEDIA';
SELECT column_id, data_type
FROM user_tab_cols
WHERE table_name = 'PRINT_MEDIA';
conn / as sysdba
SELECT dbms_metadata_util.blob2clob(76328, 3)
FROM dual;
DECLARE
vClob CLOB;
BEGIN
vClob := dbms_metadata_util.blob2clob(76328, 3);
dbms_output.put_line(vClob);
END;
/ |
| |
| CHECK_TYPE |
| For transportable import, check a type's definition and typeid |
dbms_metadata_util.check_type(
schema IN VARCHAR2,
type_name IN VARCHAR2,
version IN VARCHAR2,
hashcode IN VARCHAR2,
typeid IN VARCHAR2); |
| TBD |
| |
| CONVERT_TO_CANONICAL |
| Convert version string to canonical form |
dbms_metadata_util.convert_to_canonical(version IN VARCHAR2) RETURN VARCHAR2; |
SELECT dbms_metadata_util.convert_to_canonical('11.2.0.2')
FROM dual; |
| |
| DELETE_XMLSCHEMA (new 11.2.0.1) |
| Deletes the named schema |
dbms_metadata_util.delete_xmlschema(name IN VARCHAR2); |
| TBD |
| |
| GET_ANC |
| Get the object number of the base table to which a nested table belongs |
dbms_metadata_util.get_anc(nt IN NUMBER, exclude_xml IN NUMBER := 1) RETURN NUMBER; |
| TBD |
| |
| GET_ATTRNAME |
| Return attribute name for a table-column |
dbms_metadata_util.get_attrname(obj IN NUMBER, intcol IN NUMBER) RETURN VARCHAR2; |
SELECT object_id
FROM dba_objects_ae
WHERE owner = 'UWCLASS'
AND object_name = 'SERVERS';
SELECT dbms_metadata_util.get_attrname(76292, 1)
FROM dual; |
| |
| GET_AUDIT |
| Return audit information for a schema object |
dbms_metadata_util.get_audit(
obj_num IN NUMBER,
type_num IN NUMBER )
RETURN sys.ku$_audit_list_t; |
| TBD |
| |
| GET_AUDIT_DEFAULT |
| Return default object audit information setting |
dbms_metadata_util.get_audit(obj_num IN NUMBER)
RETURN sys.ku$_audit_default_list_t; |
| TBD |
| |
| GET_BASE_COL_NAME (new 11.2.0.3) |
| Returns the name of a base XMLType column |
dbms_metadata_util.get_base_col_name(
objnum IN NUMBER,
colnum IN NUMBER,
intcol IN NUMBER,
typenum IN NUMBER)
RETURN VARCHAR2; |
| TBD |
| |
| 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 CLOB 0 if (a) intcol = base column or (b) base column not udt or XMLType |
dbms_metadata_util.get_base_col_type(
objnum IN NUMBER,
colnum IN NUMBER,
intcol IN NUMBER,
typenum IN NUMBER)
RETURN NUMBER; |
| TBD |
| |
| GET_BASE_INTCOL_NUM |
| Return intcol# of base column, i.e., the intcol# of the first column with this col# |
dbms_metadata_util.get_base_intcol_num(
objnum IN NUMBER,
colnum IN NUMBER,
intcol IN NUMBER,
typenum IN NUMBER)
RETURN NUMBER; |
| TBD |
| |
| GET_CANONICAL_VSN |
| Convert user's VERSION param to canonical form |
dbms_metadata_util.get_canonical_vsn(version IN VARCHAR2) RETURN VARCHAR2; |
SELECT dbms_metadata_util.get_canonical_vsn('11.2.0.2.0')
FROM dual; |
| |
| GET_COL_PROPERTY |
| Return col$.property (but clear encryption bits if force_no_encrypt flag is set |
dbms_metadata_util.get_col_property(
objnum IN NUMBER,
intcol_num IN NUMBER)
RETURN NUMBER; |
| TBD |
| |
| GET_COMPAT_VSN |
| Return the compatibility version number as a number |
dbms_metadata_util.get_compat_vsn RETURN NUMBER; |
SELECT dbms_metadata_util.get_compat_vsn
FROM dual; |
| |
| GET_DB_VSN |
| Return the database version number as a string |
dbms_metadata_util.get_db_vsn RETURN NUMBER; |
SELECT dbms_metadata_util.get_db_vsn
FROM dual; |
| |
| GET_EDITIONID |
| Return ID for specified edition |
dbms_metadata_util.get_editionid(edition IN VARCHAR2) RETURN NUMBER; |
SELECT * FROM dba_editions;
SELECT sys_context('USERENV', 'CURRENT_EDITION_ID')
FROM dual;
SELECT dbms_metadata_util.get_editionid('ORA$BASE')
FROM dual; |
| |
| GET_ENDIANNESS |
Returns platform endianness
big = 1, little = 2 |
dbms_metadata_util.get_endianness RETURN NUMBER; |
SELECT dbms_metadata_util.get_endianness
FROM dual; |
| |
| GET_FULLATTRNAME |
| Return fully qualified attrname, when attrname is a system generated name |
dbms_metadata_util.get_fullattrname(
obj IN NUMBER,
col IN NUMBER,
intcol IN NUMBER,
type IN NUMBER)
RETURN VARCHAR2; |
| TBD |
| |
| GET_INDEX_INTCOL |
| Get intcol# in table of column on which an index is defined that needs special handling for xmltype cols |
dbms_metadata_util.get_index_intcol(
obj_num IN NUMBER, -- base table object number
intcol_num IN NUMBER) -- intcol# from intcol$
RETURN NUMBER; |
| TBD |
| |
| GET_LATEST_VSN |
| Return a number for the latest version number |
dbms_metadata_util.get_latest_vsn
RETURN NUMBER; |
SELECT dbms_metadata_util.get_latest_vsn
FROM dual; |
| |
| GET_LOB_PROPERTY |
| Return lob$.property (but clear bit 0x0200 if force_lob_be is set; 0x0200 = LOB data in little endian format) |
dbms_metadata_util.get_lob_property(
objnum IN NUMBER,
intcol_num IN NUMBER)
RETURN NUMBER; |
| TBD |
| |
| GET_MARKER (new 11.2.0.3) |
| Returns the current marker number |
dbms_metadata_util.get_marker RETURN NUMBER; |
SELECT dbms_metadata_util.get_marker
FROM dual; |
| |
| GET_OPEN_MODE |
Return database open mode (read only, read write). Returns:
0 = MOUNTED
1 = READ WRITE
2 = READ ONLY |
dbms_metadata_util.get_open_mode RETURN NUMBER; |
SELECT dbms_metadata_util.get_open_mode
FROM dual; |
| |
| GET_PROCOBJ_ERRORS |
| Get any errors raised by procedural object code |
dbms_metadata_util.get_procobj_errors(err_list OUT sys.ku$_vcnt); |
| TBD |
| |
| GET_REFRESH_ADD_DBA |
| Return refresh group dbms_irefresh.add execute string |
dbms_metadata_util.get_get_refresh_add_dba(
owner IN VARCHAR2,
child IN VARCHAR2,
type IN VARCHAR2,
instsite IN VARCHAR2)
RETURN VARCHAR2; |
| TBD |
| |
| GET_REFRESH_ADD_USER |
| Return refresh group dbms_refresh.add execute string |
dbms_metadata_util.get_get_refresh_add_user(
owner IN VARCHAR2,
child IN VARCHAR2,
type IN VARCHAR2,
instsite IN VARCHAR2)
RETURN VARCHAR2; |
| TBD |
| |
| GET_REFRESH_MAKE_DBA |
| Return refresh group dbms_irefresh.make execute string |
dbms_metadata_util.get_get_refresh_make_dba(group_id IN NUMBER) RETURN VARCHAR2; |
| TBD |
| |
| GET_REFRESH_MAKE_USER |
| Return refresh group dbms_refresh.make execute string |
dbms_metadata_util.get_get_refresh_make_user(group_id IN NUMBER) RETURN VARCHAR2; |
| TBD |
| |
| GET_SOURCE_LINES |
| Fetch/annotate lines from source$ |
dbms_metadata_util.get_source_lines(
obj_name IN VARCHAR2,
obj_num IN NUMBER,
type_num IN NUMBER)
RETURN sys.ku$_source_list_t; |
SELECT object_id, object_type
FROM dba_objects
WHERE object_name = 'UTL_COLL';
SELECT obj#, dataobj#, name, type#
FROM obj$
WHERE obj# = 8123
DECLARE
retVal sys.ku$_source_list_t;
BEGIN
retVal := dbms_metadata_util.get_source_lines('UTL_COLL', 8123, 9);
FOR i IN 1 .. retVal.COUNT LOOP
dbms_output.put_line(retVal(i).source);
END LOOP;
END;
/ |
| |
| GET_VERS_DPAPI |
| Retrieve DPAPI version |
dbms_metadata_util.get_vers_dpapi RETURN NUMBER); |
SELECT dbms_metadata_util.get_vers_dpapi
FROM dual; |
| |
| GET_XMLCOLSET (new 11.2.0.3) |
| Return nested table of intcol numbers for Object Relational storage columns for xmltypes in table |
dbms_metadata_util.get_xmlcolset(obj_num IN NUMBER) RETURN ku$_XmlColSet_t; |
| TBD |
| |
| GET_XMLHIERARCHY (new 11.2.0.3) |
| Returns 'Y' if the table is hierachy enabled, otherwise NULL |
dbms_metadata_util.get_xmlcolset(schema IN VARCHAR2, name IN VARCHAR2) RETURN CHAR; |
| TBD |
| |
| GET_XMLTYPE_FMTS |
| Return formats of XMLType columns in a table |
dbms_metadata_util.get_xmltype_fmts(objnum IN NUMBER) RETURN NUMBER; |
| TBD |
| |
| HAS_TSTZ_COLS (new 11.2.0.3) |
| Determine whether a table has data of type DTYSTZ (type# = 181): "TIMESTAMP WITH TIME ZONE" column. |
dbms_metadata_util.has_tstz_cols(obj_num IN NUMBER) RETURN CHAR; |
| TBD |
| |
| HAS_TSTZ_ELEMENTS (new 11.2.0.3) |
| Determines whether a varray type has TSTZ elements. This is a wrapper around utl_xml.haststz |
dbms_metadata_util.has_tstz_elements(type_schema IN VARCHAR2, type_name IN VARCHAR2) RETURN CHAR; |
| TBD |
| |
| ISXML (new 11.2.0.3) |
Another variant, starting with nested table
column of an xmltype column in table
Overload 1 |
dbms_metadata_util.isXML(obj_num IN NUMBER, intcol IN NUMBER) return NUMBER; |
| TBD |
| Overload 2 |
dbms_metadata_util.isXML(nt_num IN NUMBER) return NUMBER; |
| TBD |
| |
| IS_OMF |
| Determine if a name is a Oracle Managed File (OMF) |
dbms_metadata_util.is_omf(name IN VARCHAR2) RETURN NUMBER; |
set serveroutput on
DECLARE
dfn dba_data_files.file_name%TYPE;
BEGIN
SELECT file_name
INTO dfn
FROM dba_data_files
WHERE rownum = 1;
dbms_output.put_line(dbms_metadata_util.is_omf(dfn));
END;
/ |
| |
| IS_SCHEMANAME_EXISTS (new 11.2.0.3) |
| Return 1 if schema name exists in trigger definition 0 other wise |
dbms_metadata_util.is_schemaname_exists(tdefinition IN VARCHAR2) RETURN NUMBER; |
| TBD |
| |
| LOAD_STYLESHEETS |
| Load the XSL stylesheets into the database |
dbms_metadata_util.load_stylesheets |
exec dbms_metadata_util.load_stylesheets;
-- the following is from $ORACLE_HOME/rdbms/admin/initmeta.sql
EXECUTE SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS('C:\oracle\product\11.2.0\db_2\rdbms\xml\xsl'); |
| |
| LOAD_XSD (new 11.2.0.1) |
Calls dbms_xmlschema.registerSchema
to register the named schema |
dbms_metadata_util.load_xsd(filename IN VARCHAR2); |
| TBD |
| |
| LONG2CLOB |
| Convert a table LONG value to a CLOB |
dbms_metadata_util.long2clob(
length IN NUMBER,
tab IN VARCHAR2,
col IN VARCHAR2,
row IN ROWID)
RETURN CLOB; |
-- This demo creates the exception shown when run as shown but, as can be seen,
it is just an API to a UTL_XML function_
set serveroutput on
DECLARE
retVal VARCHAR2(4000);
rid rowid;
BEGIN
SELECT rowid
INTO rid
FROM view$
WHERE obj# IN (SELECT obj# FROM obj$ WHERE name = 'DBA_VIEWS' AND type# = 4);
retVal := dbms_metadata_util.long2clob(120, 'DBA_VIEWS', 'TEXT', rid);
dbms_output.put_line(retVal);
END;
/
META:20:47:23.640: exception from 'SELECT TEXT FROM DBA_VIEWS WHERE ROWID = :1' for rowid value AAAA
ORA-31600: invalid input value DBA_VIEWS for parameter TAB in function UTL_XML.LONG2CLOB |
| |
| LONG2VARCHAR |
| Convert a table LONG value to a VARCHAR2 |
dbms_metadata_util.long2varchar(
length IN NUMBER,
tab IN VARCHAR2,
col IN VARCHAR2,
row IN UROWID)
RETURN VARCHAR2; |
-- I can not get the demo to return a string but it does compile and run
set serveroutput on
DECLARE
retVal VARCHAR2(4000);
urid urowid;
BEGIN
SELECT rowid
INTO urid
FROM view$
WHERE obj# IN (SELECT obj# FROM obj$ WHERE name = 'DBA_VIEWS' AND type# = 4);
retVal := dbms_metadata_util.long2varchar(120, 'DBA_VIEWS', 'TEXT', urid);
dbms_output.put_line(retVal);
END;
/ |
| |
| LONG2VCMAX |
| Convert a table LONG value to a VARCHAR2 and each line max length is 2000 |
dbms_metadata_util.long2vcmax(
length IN NUMBER,
tab IN VARCHAR2,
col IN VARCHAR2,
row IN UROWID)
RETURN sys.ku$_vcnt; |
| TBD |
| |
| LONG2VCNT |
| Convert a table LONG value to a nested table of VARCHAR2 |
dbms_metadata_util.long2vcnt(
length IN NUMBER,
tab IN VARCHAR2,
col IN VARCHAR2,
row IN UROWID)
RETURN sys.ku$_vcnt; |
| TBD |
| |
| NULL2CHR0 |
| Replace \0 with CHR(0) in a VARCHAR2 |
dbms_metadata_util.null2chr0(value IN VARCHAR2, replace_quote IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2; |
| TBD |
| |
| PARSE_CONDITION |
| Parses a check constraint condition on a table and returns it as XML |
dbms_metadata_util.parse_condition(
schema IN VARCHAR2,
tab IN VARCHAR2,
length IN NUMBER,
row IN ROWID)
RETURN SYS.XMLTYPE; |
| TBD |
| |
| PARSE_DEFAULT |
Parses the default value of a virtual column
(which contains an arithmetic expression for a functional index) and returns it as XML |
dbms_metadata_util.parse_default(
schema IN VARCHAR2,
tab IN VARCHAR2,
length IN NUMBER,
row IN ROWID)
RETURN SYS.XMLTYPE; |
-- This produces the exception shown which is instructive
DECLARE
retVal XMLTYPE;
rid rowid;
BEGIN
SELECT rowid
INTO rid
FROM oe.purchaseorder
WHERE rownum = 1;
retVal := dbms_metadata_util.parse_default('OE', 'PURCHASEORDER', 400, rid);
END;
/
META:21:09:39.609: exception from 'SELECT DEFAULT$ FROM SYS.COL$ WHERE ROWID = :1' for rowid value A
ORA-06502: PL/SQL: numeric or value error
ORA-01410: invalid ROWID
ø)Ê ð§n T©n ÿ *Ê 0 ®ºŒ`1
ø)Ê ÿÿÿÿ Ôªn £oÙa T©n ÿÿÿÿÿÿÿÿ «n ¸ ÿ= NI=æªn ˜ µ %Í_ ”·µ ·µ ¬¨n €à_ ·µ ”·µ ”·
P²> s¿ %Í_ Tw´ ¤v´ ܨn €à_ Ä·µ d·µ @©n àþ_ P²> ·µ 0 ·µ
¤v´ '4_ '_ T/Ê D Ê d T/Ê X©n (¢_ P²>T/Ê %Í_ ˜/Ê T/Ê |©n Fá_ T/Ê ¤/Ê ø/Ê Fá_ T/Ê . ´ ¦ ¼+Ê 1/
È(Ê À ªn %ÿ_ P²> |
| |
| PARSE_QUERY |
Parses a query stored in a long column (e.g., view query) and returns it as XML
Overload 1 |
dbms_metadata_util.parse_query(
schema IN VARCHAR2,
length IN NUMBER,
tab IN VARCHAR2,
col IN VARCHAR2,
row IN ROWID)
RETURN SYS.XMLTYPE; |
| TBD |
| Overload 2 |
dbms_metadata_util.parse_query(
schema IN VARCHAR2,
query IN CLOB)
RETURN SYS.XMLTYPE; |
| TBD |
| |
| PARSE_TRIGGER_DEFINITION |
| Return annotated trigger definition |
dbms_metadata_util.parse_trigger_definition(
obj_name IN VARCHAR2,
definition IN VARCHAR2)
RETURN sys.ku$_source_t; |
| TBD |
| |
| PATCH_TYPEID |
| For transportable import, modify a type's typeid |
dbms_metadata_util.patch_typeid(
schema IN VARCHAR2,
name IN VARCHAR2,
typeid IN VARCHAR2,
hashcode IN VARCHAR2); |
| TBD |
| |
| PUT_BOOL |
| Write debugging output |
dbms_metadata_util.put_bool(stmt IN VARCHAR2, value IN BOOLEAN); |
BEGIN
dbms_metadata_util.put_bool('This is ', TRUE);
END;
/ |
| |
| PUT_LINE |
| Does a DBMS_OUTPUT.PUT_LINE regardless of string length; i.e, works with strings > 255 |
dbms_metadata_util.put_line(stmt IN VARCHAR2); |
set serveroutput on
exec dbms_metadata_util.put_line('This works'); |
| |
| REF_PAR_LEVEL |
Return level of ref partitioned child table
Overload 1 |
dbms_metadata_util.ref_par_level(objnum IN NUMBER) RETURN NUMBER; |
| TBD |
| Overload 2 |
dbms_metadata_util.ref_par_level(objnum IN NUMBER, properties IN NUMBER)
RETURN NUMBER; |
| TBD |
| |
| REF_PAR_PARENT |
| Return object number of ref partitioned parent table |
dbms_metadata_util.ref_par_parent(objnum IN NUMBER) RETURN NUMBER; |
| TBD |
| |
| SAVE_PROCOBJ_ERRORS |
| Save errors raised by procedural object code |
dbms_metadata_util.save_procobj_errors(sql_stmt IN VARCHAR2); |
| TBD |
| |
| SET_DEBUG |
| Set the internal debug switch |
dbms_metadata_util.set_debug(
on_off IN BOOLEAN,
force_trace IN BOOLEAN DEFAULT FALSE); |
| exec dbms_metadata_util.set_debug(FALSE, FALSE); |
| |
| SET_FORCE_LOB_BE |
| Save the 'force_lob_be' switch |
dbms_metadata_util.set_force_lob_be(value IN BOOLEAN); |
| exec dbms_metadata_util.set_force_lob_be(FALSE); |
| |
| SET_FORCE_NO_ENCRYPT |
| Save the 'force_no_encrypt' switch |
dbms_metadata_util.set_force_no_encrypt(value IN BOOLEAN); |
| exec dbms_metadata_util.set_force_no_encrypt(TRUE); |
| |
| SET_VERS_DPAPI |
| Save DPAPI version |
dbms_metadata_util.set_vers_dpapi(version IN NUMBER); |
| TBD |
| |
| VSN2NUM |
| Convert a dot-separated version string (e.g., '8.1.6.0.0') to a number (e.g., 8010600000) |
dbms_metadata_util.vsn2num(vsn IN VARCHAR2) RETURN NUMBER; |
SELECT dbms_metadata_util.vsn2num('11.2.0.2.0')
FROM dual; |
| |
| WRITE_CLOB |
| Write a CLOB to the trace file |
dbms_metadata_util.write_clob(xml IN CLOB); |
DECLARE
clobvar CLOB := 'This is a test';
BEGIN
dbms_metadata_util.write_clob(clobvar);
END;
/
-- this runs successfully but I can not find a trace file to which it has been written |
