Oracle DBMS_METADATA_UTIL
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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';

-- 269 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
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 GET_FULLATTRNAME IS_OMF
BLOB2CLOB GET_INDEX_INTCOL IS_SCHEMANAME_EXISTS
BLOCK_ESTIMATE GET_INDEX_INTCOL_PARSE IS_SHARD_TBL_IN_SHARD_CATALOG
BYTES_ALLOC GET_INDPART_TS LOAD_STYLESHEETS
CHECK_CONSTRAINT GET_LATEST_VSN LOAD_TEMP_TABLE
CHECK_TYPE GET_LOB_PROPERTY LOAD_XSD
CONVERT_TO_CANONICAL GET_LOST_WRITE_PROTECTION LONG2CLOB
DELETE_XMLSCHEMA GET_MARKER LONG2VARCHAR
FETCH_STAT GET_OPEN_MODE LONG2VCMAX
FUNC_INDEX_DEFAULT GET_PART_LIST LONG2VCNT
FUNC_INDEX_DEFAULTC GET_PROCOBJ_ERRORS NULLTOCHR0
FUNC_VIEW_DEFAULT GET_QA_LRG_TYPE PARSE_TRIGGER_DEFINITION
FUNC_VIEW_DEFAULTC GET_REFRESH_ADD_DBA PATCH_TYPEID
GET_ANC GET_REFRESH_ADD_USER PUT_BOOL_
GET_ANYDATA_COLSET GET_REFRESH_MAKE_DBA PUT_LINE
GET_ATTRNAME GET_REFRESH_MAKE_USER REF_PAR_LEVEL
GET_ATTRNAME2 GET_SOURCE_LINES REF_PAR_PARENT
GET_AUDIT GET_STRM_MINVER SAVE_PROCOBJ_ERRORS
GET_AUDIT_DEFAULT GET_TABPART_TS SET_BLOCK_ESTIMATE
GET_BASE_COL_NAME GET_VERS_DPAPI SET_DEBUG
GET_BASE_COL_TYPE GET_XMLCOLSET SET_FORCE_LOB_BE
GET_BASE_INTCOL_NUM GET_XMLHIERARCHY SET_FORCE_NO_ENCRYPT
GET_CANONICAL_VSN GET_XMLTYPE_FMTS SET_MARKER
GET_COL_PROPERTY GLO SET_VERS_DPAPI
GET_COMPAT_VSN HAS_TSTZ_COLS TABLE_TSNUM
GET_DB_VSN HAS_TSTZ_ELEMENTS VSN2NUM
GET_EDITIONID ISXML WRITE_CLOB
GET_ENDIANNESS    
 
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
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
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_CONSTRAINT
According to Oracle checks to see if a constraint exists ... but that's total nonsense. See our comments for the function of the same name in DBMS_METADATA dbms_metadata_util.check_constraint(OBJ_NUM IN NUMBER)
RETURN NUMBER;
SELECT dbms_metadata_util.check_constraint(780)
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('21.1.0.1')
FROM dual;

DBMS_METADATA_UTIL.CONVERT_TO_CANONICAL('21.1.0.1')
----------------------------------------------------
21.01.00.01.00
 
DELETE_XMLSCHEMA
Deletes the named schema dbms_metadata_util.delete_xmlschema(name IN VARCHAR2);
TBD
 
FETCH_STAT
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
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
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
 
FUNC_VIEW_DEFAULT
Returns TEXT from view$ for a func view removing null bytes from view$.text dbms_metadata_util.func_view_default(
length IN NUMBER,
row    IN ROWID)
RETURN VARCHAR2;
TBD
 
FUNC_VIEW_DEFAULTC
Returns TEXT from view$ for a func view removing null bytes to from view$.text dbms_metadata_util.func_view_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
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
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('21.1.0.1.0')
FROM dual;

DBMS_METADATA_UTIL.GET_CANONICAL_VSN('21.1.0.1.0')
---------------------------------------------------
21.01.00.01.00
 
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
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;
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
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_LOST_WRITE_PROTECTION
Returns TRUE when LOST WRITE PROTECTION is enabled, else FALSE dbms_metadata_util.get_lost_write_protection RETURN BOOLEAN;
show parameter lost

NAME                   TYPE    VALUE
---------------------- ------- ------
db_lost_write_protect  string  NONE

BEGIN
  IF dbms_metadata_util.get_lost_write_protection THEN
    dbms_output.put_line('Lost Write Protection Enabled');
  ELSE
    dbms_output.put_line('Lost Write Protection Disabled');
  END IF;
END;
/
 
GET_MARKER
Returns the current marker number dbms_metadata_util.get_marker RETURN NUMBER;
SELECT dbms_metadata_util.get_marker
FROM dual;

GET_MARKER
----------
        42
 
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_OPEN_MODE
-------------
            1
 
GET_PART_LIST
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;

 OBJECT_ID OBJECT_TYPE
---------- -------------------------
     11130 TABLE
     11131 TABLE PARTITION
    132212 TABLE PARTITION
    132507 TABLE PARTITION
    132823 TABLE PARTITION
    133109 TABLE PARTITION
    133372 TABLE PARTITION
    133704 TABLE PARTITION
    133974 TABLE PARTITION
    134283 TABLE PARTITION
    134557 TABLE PARTITION
    134910 TABLE PARTITION

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

  FOR i IN 1..lCnt LOOP
    dbms_output.put_line(lRetVal(i));
  END LOOP;
END;
/
505
506
516
525
526
527
528
529
530
532
536

SELECT part#
FROM tabpart$
WHERE bo# = 11130;

 PART#
------
   505
   506
   516
   525
   526
   527
   528
   529
   530
   532
   536
 
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_QA_LRG_TYPE
Return the value of qa_lrg_type, which can be used to determine that the database is being used for testing dbms_metadata_util.get_qa_lrg_type RETURN NUMBER
SELECT dbms_metadata_util.get_qa_lrg_type
FROM dual;

GET_QA_LRG_TYPE
---------------
              0
 
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';

 OBJECT_ID OBJECT_TYPE
---------- -------------
     14036 PACKAGE
     18767 PACKAGE BODY
     14037 SYNONYM

SELECT obj#, dataobj#, name, type#
FROM obj$
WHERE obj# = 14036

      OBJ# DATAOBJ#   NAME                TYPE#
---------- ---------- ------------------ ------
     14036            UTL_COLL                9

DECLARE
 retVal sys.ku$_source_list_t;
BEGIN
  retVal := dbms_metadata_util.get_source_lines('UTL_COLL', 14036, 9);
  FOR i IN 1 .. retVal.COUNT LOOP
    dbms_output.put_line(retVal(i).source);
  END LOOP;
END;
/
PACKAGE utl_coll wrapped
a000000
1
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
9
b9
d6
1E6p1w9C4063bW1HqiGKlH0mplYwg3nwLZ4VZ3RAkPiOa16Z0f+UqO3oYwTHbJxqCEDiW9GS
+dShW6jPpQi1od906nHnh3KjM4A7S+g5j3Lkl25stKuV2QILZ22DAoW54c0xuJHFsAwF1vEB
lAQLkeaIzzJecUh9NWVM1pRjWIkUafy8
xkq4nD4QU609MSguhGU139H59Adq/WBJVt8=
 
GET_STRM_MINVER
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_STRM_MINVER
----------------
2
 
GET_TABPART_TS
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;

 OBJECT_ID OBJECT_TYPE
---------- -------------------------
        18 TABLE

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

      OBJ#        TS#
---------- ----------
        18          0


SELECT dbms_metadata_util.get_tabpart_ts(18)
FROM dual;

DBMS_METADATA_UTIL.GET_TABPART_TS(18)
-------------------------------------
                                    0
 
GET_VERS_DPAPI
Retrieve DPAPI version dbms_metadata_util.get_vers_dpapi RETURN NUMBER;
SELECT dbms_metadata_util.get_vers_dpapi
FROM dual;

GET_VERS_DPAPI
--------------
             0
 
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
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;

UTL_RAW.CAST_TO_RAW('MORGAN')
------------------------------
4D6F7267616E

SELECT dbms_metadata_util.glo(utl_raw.cast_to_raw('Morgan'))
FROM dual;

DBMS_METADATA_UTIL.GLO(UTL_RAW.CAST_TO_RAW('MORGAN'))
------------------------------------------------------
4D6F7267616E
 
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
 
IS_SHARD_TBL_IN_SHARD_CATALOG (new 21c)
Return 1 if table whose obj# passed in is a shard table in a shard catalog, else 0 dbms_metadata_util.is_shard_tbl_in_shard_catalog(obj_num IN NUMBER) RETURN NUMBER;
SELECT dbms_metadata_util.is_shard_tbl_in_shard_catalog(127367)
FROM dual;

DBMS_METADATA_UTIL.IS_SHARD_TBL_IN_SHARD_CATALOG(127367)
--------------------------------------------------------
                                                       0
 
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
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_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
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_MARKER
Sets the current marker number dbms_metadata_util.set_marker(new_marker IN NUMBER)
ACCESSIBLE BY (PACKAGE sys.dbms_metadata_int);
No direct demo possible due to ACCESSIBLE BY clause
 
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
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('21.1.0.1.0')
FROM dual;

DBMS_METADATA_UTIL.VSN2NUM('21.1.0.1.0')
----------------------------------------
                              2101000100
 
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;
/

-- runs successfully but I can not find any indication of where it was written,
-- definitely not to the alert log.

Related Topics
Built-in Functions
Built-in Packages
DBMS_FEATURE_USAGE
DBMS_METADATA
DBMS_METADATA_BUILD
DBMS_METADATA_DIFF
DBMS_METADATA_DPBUILD
DBMS_METADATA_HACK
DBMS_METADATA_INT
LONG to CLOB
What's New In 21c
What's New In 23c

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