Oracle DBMS_METADATA_UTIL
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
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';

-- 234 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@pdbdev

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@pdbdev 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;
/
 
FUNC_INDEX_DEFAULT (new 12.1)
Get default$ from col$ for a func index converting any null bytes to 'CHR(0)' dbms_metadata_util.func_index_default(length IN NUMBER, row IN ROWID) RETURN VARCHAR2;
TBD
 
FUNC_INDEX_DEFAULTC (new 12.1)
Get default$ from col$ for a func index converting any null bytes to 'CHR(0)' dbms_metadata_util.func_index_defaultc(length IN NUMBER, row IN ROWID) RETURN CLOB;
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_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_INDPART_TS (new 12.1)
Get a ts# for an index (sub)partition used by ku$_index_view dbms_metadata_util.get_indpart_ts(obj_num IN NUMBER) RETURN NUMBER;
See the GET_INDPART_TS demo under DBMS_METADATA linked below
 
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;
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 and clearly it should fail
EXECUTE SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS('C:\app2\oracle\product\12.2.0\dbhome_1\rdbms\xml\xsl');
SQL> EXECUTE SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS('C:\app2\oracle\product\12.2.0\dbhome_1\rdbms\xml\xsl');
BEGIN SYS.DBMS_METADATA_UTIL.LOAD_STYLESHEETS('C:\app2\oracle\product\12.2.0\dbhome_1\rdbms\xml\xsl'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'LOAD_STYLESHEETS'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

-- and it does as you see ... whoever at Oracle wrote the code that generates this ... didn't test it.
 
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@pdbdev

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
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved