Oracle DBMS_METADATA

Search the Reference Library pages:

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

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

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);

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

-----