Oracle DBMS_METADATA_UTIL
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Utilities supporting the DBMS_METADATA package
AUTHID DEFINER
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';

-- 227 objects returned
Documented No
Exceptions
Error Code Reason
31600 invalid_argval: Invalid Argument
31601 invalid_operation: Invalid Operation
31602 inconsistent_args: Inconsistent Arguments
31603 object_not_found: Object Not Found
31604 invalid_objeject_param: Invalid Object Parameter
31607 inconsistent_operation: Inconsistent Operation
31608 object_not_found2: Object Not Found
31609 stylesheet_load_error: installation script initmeta.sql failed to load the named file
39127 procobj_error: DataPump Internal Error
39132 bad_hashcode: Object exists with different hash code on the target system
39133 type_in_use: Object type already exists with different typeid
First Available 9.0.1
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsmetu.sql
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
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@pdborcl

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 sys@pdborcl 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;
/
 
BLOCK_ESTIMATE (new 12.1)
Calculates bytes allocated using the BLOCKS method dbms_metadata_util.block_estimate(
o_num    IN NUMBER,
view_num IN NUMBER)
RETURN NUMBER;

View# View Name
1 ku$_htable_bytes_alloc_view
2 ku$_htpart_bytes_alloc_view
3 ku$_htspart_bytes_alloc_view
4 ku$_iotable_bytes_alloc_view
5 ku$_iotpart_bytes_alloc_view
6 ku$_ntable_bytes_alloc_view
7 ku$_eqntable_bytes_alloc_view
SELECT object_id
FROM dba_objects
WHERE object_name = 'SOURCE$';

SELECT dbms_metadata_util.block_estimate(284, 1)
FROM dual;
 
BYTES_ALLOC (new 12.1)
Calculate bytes allocated from x$ktfbue using the temporary table if it has been initialized. dbms_metadata_util.bytes_alloc(
ts_num     IN NUMBER,
file_num   IN NUMBER,
block_num  IN NUMBER,
block_size IN NUMBER)
RETURN NUMBER;
SELECT ts#, name, blocksize
FROM ts$;

SELECT file_id
FROM dba_data_files
WHERE tablespace_name = 'SYSTEM';

SELECT block_id
FROM dba_extents
WHERE segment_name = 'SOURCE$'
ORDER BY 1;

-- choose one of the first block_id values from the query of dba_extents
SELECT dbms_metadata_util.bytes_alloc(0, 1, 2216, 8)
FROM dual;
 
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('12.1.0.2')
FROM dual;
 
DELETE_XMLSCHEMA
Deletes the named schema dbms_metadata_util.delete_xmlschema(name IN VARCHAR2);
TBD
 
FETCH_STAT (new 12.1)
In theory returns the XML from the metadata api for the SYS.IMPDP_STATS table dbms_metadata_util.fetch_stat(stat_clob IN OUT CLOB);
conn sys@pdbdev as sysdba

desc impdp_stats

SELECT COUNT(*) FROM impdp_stats;
-- don't expect a lot if the count is zero

DECLARE
 cVal CLOB := 'dflt';
BEGIN
  dbms_metadata_util.fetch_stat(cVal);
  dbms_output.put_line(cVal);
END;
/
 
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_ANYDATA_COLSET (new 12.1)
Return nested table of type names for unpacked ADT columns contained in an opaque column. Each element in the list contains one or more type names dbms_metadata_util.get_anydata_colset(
objnum   IN NUMBER,
colnum   IN NUMBER,
colcnt   IN NUMBER,
col_list IN RAW)
RETURN ku$_Unpacked_AnyData_t;
TBD
 
GET_ATTRNAME
Return attribute name for a table-column dbms_metadata_util.get_attrname(
obj    IN NUMBER,
intcol IN NUMBER)
RETURN VARCHAR2;
conn sys@pdbdev as sysdba

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_ATTRNAME2 (new 12.1)
For ANYDATA column sets removes the system generated part of the name and
replaces it with the type name and its owner.
dbms_metadata_util.get_attrname2(
objnum    IN NUMBER,
intcolnum IN NUMBER,
colnum    IN NUMBER)
RETURN VARCHAR2;
TBD
 
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
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 icol$
RETURN NUMBER;
TBD
 
GET_INDEX_INTCOL_PARSE (new 12.1)
Parse default_val_clob and get intcol# in table of column on which index is defined dbms_metadata_util.get_index_intcol_parse(
obj_num          IN NUMBER,  -- base table object number
intcol_num       IN NUMBER,  -- intcol# from icol$
default_val_clob IN CLOB)    -- default_val from col$
RETURN NUMBER;
TBD
SELECT do.object_id, di.index_name
FROM dba_objects do, dba_indexes di
WHERE do.object_name = di.index_name
AND di.table_name = 'OBJ$';

SELECT dbms_metadata_util.get_index_intcol_parse(36, 7, '')
FROM dual;
 
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
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_PART_LIST (new 12.1)
Get ordered list of partition numbers or partition base object numbers dbms_metadata_util.get_part_list(
PARTYPE  IN  NUMBER,
BOBJ_NUM IN  NUMBER,
CNT      OUT NUMBER)
RETURN dbms_metadata_int.t_num_coll;
Par Types Explanation
1 tabpart
2 tabcompart
3 tabsubpart
4 indpart
5 indcompart
6 indsubpart
7 lobfrag
8 lobcomppart
conn / as sysdba

SELECT object_id, object_type
FROM dba_objects
WHERE object_name = 'WRH$_SQLSTAT'
ORDER BY 1;

DECLARE
 lRetVal dbms_metadata_int.t_num_coll;
 lCnt    PLS_INTEGER;
BEGIN
  lRetVal := dbms_metadata_util.get_part_list(1, 7354, lCnt);

  FOR i IN 1..lCnt LOOP
    dbms_output.put_line(lRetVal(i));
  END LOOP;
END;
/

SELECT part#
FROM tabpart$
WHERE bo# = 7354;
 
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_STRM_MINVER (new 12.1)
Retrieve stream minor version based on job version dbms_metadata_util.get_strm_minver RETURN CHAR;
SELECT dbms_metadata_util.get_strm_minver
FROM dual;
 
GET_TABPART_TS (new 12.1)
Retrieve the TS# for a table (sub)partition (used by dbms_metadata.in_tsnum_2 dbms_metadata_util.get_tabpart_ts(OBJ_NUM IN NUMBER) RETURN NUMBER;
conn / as sysdba

SELECT object_id, object_type
FROM dba_objects
WHERE object_name = 'OBJ$'
ORDER BY 1;

SELECT obj#, ts#
FROM tab$
WHERE obj# = 18;

SELECT dbms_metadata_util.get_tabpart_ts(18)
FROM dual;
 
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
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
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
 
GLO (new 12.1)
Undocumented fix for bug 12866600 which, at least on my system, is an unreproducible bug dbms_metadata_util.glo(inval IN RAW) RETURN RAW;
SELECT utl_raw.cast_to_raw('Morgan')
FROM dual;

SELECT dbms_metadata_util.glo(utl_raw.cast_to_raw('Morgan'))
FROM dual;
 
HAS_TSTZ_COLS
Returns "Y" if a table contains a type DTYSTZ (type# = 181): "TIMESTAMP WITH TIME ZONE" column dbms_metadata_util.has_tstz_cols(obj_num IN NUMBER) RETURN CHAR;
TBD

SELECT column_name, data_type
FROM dba_tab_cols
WHERE table_name = 'TSM_SRC$';

SELECT object_id
FROM dba_objects
WHERE object_name = 'TSM_SRC$';

SELECT dbms_metadata_util.has_tstz_cols(366)
FROM dual;
 
HAS_TSTZ_ELEMENTS
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
Returns 1 if an XMLType column: Otherwise 0
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
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_TEMP_TABLE (new 12.1)
Copy sys.x$ktfbue into a temporary table for subsequent use in computing bytes allocated dbms_metadata_util.load_temp_table;
SELECT sql_id, last_load_time
FROM v$sqlarea
WHERE last_load_time > sysdate-1/24
AND LOWER(sql_fulltext) LIKE '%insert%'
ORDER BY last_load_time;

exec dbms_metadata_util.load_temp_table;

-- the statement being executed:
INSERT INTO SYS.KU$XKTFBUE
SELECT KTFBUESEGTSN, KTFBUESEGFNO, KTFBUESEGBNO, KTFBUEBLKS
FROM SYS.X$KTFBUE;
 
LOAD_XSD
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 table 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
 
NULLTOCHR0
Replace \0 with CHR(0) in a VARCHAR2 dbms_metadata_util.nulltochr0(
value         IN VARCHAR2,
replace_quote IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2;
DECLARE
 testVal VARCHAR2(30) := 'Daniel A\0''Morgan';
BEGIN
  dbms_output.put_line(testVal);
  testVal := dbms_metadata_util.nulltochr0(testVal, TRUE);
  dbms_output.put_line(testVal); 
END;
/
Daniel A\0'Morgan
Daniel A\0''Morgan
 
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

conn oe/oe@pdborcl

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;
DECLARE
 sVal VARCHAR2(30) := 'SYS';
 qVal CLOB := 'SELECT dummy FROM dual';
 xVal XMLType;
BEGIN
  xVal := dbms_metadata_util.parse_query(sVal, qVal);
  dbms_output.put_line(xVal.getStringVal);
END;
/
 
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_BLOCK_ESTIMATE (new 12.1)
Sets the state for the estimate phase dbms_metadata_util.set_block_estimate(value IN BOOLEAN);
exec dbms_metadata_util.set_block_estimate(TRUE);
 
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);
SELECT dbms_metadata_util.get_vers_dpapi
FROM dual;

exec dbms_metadata_util.set_vers_dpapi(0.99);

SELECT dbms_metadata_util.get_vers_dpapi
FROM dual;

exec dbms_metadata_util.set_vers_dpapi(0);

SELECT dbms_metadata_util.get_vers_dpapi
FROM dual;
 
TABLE_TSNUM (new 12.1)
Returns a table's canonical tablespace number dbms_metadata_util.table_tsnum(objnum IN NUMBER)  RETURN NUMBER;
conn / as sysdba

SELECT object_id
FROM dba_objects
WHERE object_name = 'TAB$';

SELECT dbms_metadata_util.table_tsnum(4)
FROM dual;
 
VSN2NUM
Convert a dot-separated version string dbms_metadata_util.vsn2num(vsn IN VARCHAR2) RETURN NUMBER;
SELECT dbms_metadata_util.vsn2num('12.1.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 any indication of where anyting was written

Related Topics
DBMS_FEATURE_USAGE
DBMS_METADATA
DBMS_METADATA_BUILD
DBMS_METADATA_DIFF
DBMS_METADATA_DPBUILD
DBMS_METADATA_HACK
DBMS_METADATA_INT
LONG to CLOB
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved