CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_METADATA_UTIL
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsmetu.sql
First Available 9.0.1

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

x

Exception Name Error Code Reason
invalid_argval 31600 Invalid argument
invalid_operation 31601 invalid operation
inconsistent_args 31602 inconsistent_arguments
x 31603 z

x

31604 x
31607 x
x 31608 z
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
 
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;
/
 
BINARY2VARCHAR

Convert a column default blob value to a VARCHAR2
dbms_metadata_util.binary2varchar(tabobj IN NUMBER, incolnum IN NUMBER)
RETURN VARCHAR2;
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.binary2varchar(70374, 8)
FROM dual;

DECLARE
 v VARCHAR2(4000);
BEGIN
  v := dbms_metadata_util.binary2varchar(70374, 8);
  dbms_output.put_line(v);
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.1.0.6')
FROM dual;
 
GET_ANC
Get the object number of the base table to which a nested table belongs dbms_metadata_util.get_anc(nt IN NUMBER) RETURN NUMBER;
SELECT dbms_metadata_util.get_anc( HELP
 
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
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_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.1.0.6.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 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_HASHCODE (new in 11.1.0.7)
The upgrade from 8.1.7 corrupts the hashcode in type$, so it must be obtained  by calling kotgHashCode.
This function calls utl_cxml.getHashCode which calls into kux.c.
dbms_metadata_util.get_hashcode(
schema   IN VARCHAR2,
typename IN VARCHAR2)
RETURN RAW;
SELECT dbms_metadata_util.get_hashcode('SYSTEM', 'REPCAT$_OBJECT_NULL_VECTOR')
FROM dual;
 
GET_INDEX_INTCOL (new in 11.1.0.7)

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_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;
TBD
 
GET_VERS_DPAPI
Retrieve DPAPI version dbms_metadata_util.get_vers_dpapi RETURN NUMBER);
SELECT dbms_metadata_util.get_vers_dpapi
FROM dual;
 
GET_XMLTYPE_FMTS
Return formats of XMLType columns in a table dbms_metadata_util.get_xmltype_fmts(objnum IN NUMBER) RETURN NUMBER; 
TBD
 
IS_OBJECT_XDB_GENERATED

Check for whether object is generated by a registerschema call
dbms_metadata_util.is_object_xdb_generated(
object_name  IN VARCHAR2,
schema       IN VARCHAR2,
base_obj_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;
/
 
LOAD_STYLESHEETS
Load the XSL stylesheets into the database dbms_metadata_util.load_stylesheets(<no_name> IN <no_data_type>);
exec dbms_metadata_util.load_stylesheets( '/app/oracle/product/11.1.0/db_1/rdbms/xml/xsl');
 
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;
TBD
 
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;
TBD
 
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 with CHR(0) in varchar dbms_metadata_util.null2chr0(
value         IN VARCHAR2,
replace_quote IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2;
TBD
 
PARSE_CONDITION

Return a check condition 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

Return the default value of a virt col as XML
dbms_metadata_util.parse_default(
schema IN VARCHAR2,
tab    IN VARCHAR2,
length IN NUMBER,
row    IN ROWID)
RETURN SYS.XMLTYPE;
TBD
 
PARSE_QUERY

Return a query 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

R
eturn 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);
TBD
 
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);
TBD
 
SET_FORCE_LOB_BE
Save the 'force_lob_be' switch dbms_metadata_util.set_force_lob_be( value IN BOOLEAN);
TBD
 
SET_FORCE_NO_ENCRYPT
Save the 'force_no_encrypt' switch dbms_metadata_util.set_force_no_encrypt(value IN BOOLEAN);
TBD
 
SET_PARSING
Turn query parsing on/off dbms_metadata_util.set_parsing(on_off IN BOOLEAN);
exec dbms_metadata_util.set_parsing(TRUE);
 
SET_VERS_DPAPI
Save DPAPI version dbms_metadata_util.set_vers_dpapi( version IN NUMBER);
TBD
 
UPDATEFEATURETABLE

Updates the feature usage table to indicate that the given utility has been used.
dbms_metadata_util.updatefeaturetable(utility_name IN VARCHAR2);
col name format a40

SELECT name, detected_usages
FROM dba_feature_usage_statistics
WHERE detected_usages = 0
ORDER BY 1;

exec dbms_metadata_util.updatefeaturetable('Spatial');
 
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.1.0.6.0')
FROM dual;
 
WRITE_CLOB
Write a CLOB to the trace file dbms_metadata_util.write_clob(xml IN CLOB);
TBD
 
Related Topics
DBMS_FEATURE_USAGE
DBMS_METADATA
LONG to CLOB
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [194 users online]    © 2010 psoug.org