Oracle DBMS_METADATA

Search the Reference Library pages:

Oracle DBMS_METADATA

Version 11.1

General Information

Source

{ORACLE_HOME}/rdbms/admin/dbmsmeta.sql

First Available

9.0.1

Constants

Name	Data Type	Value
MAX_PROCOBJ_RETLEN	BINARY_INTEGER	32767
NEWBLOCK_APPEND	NUMBER	-2
NEWBLOCK_BEGIN	NUMBER	1
NEWBLOCK_CONTINUE	NUMBER	0
SESSION_TRANSFORM	BINARY_INTEGER	-1

Object Type Constants

AQ_QUEUE	REF_CONSTRAINT
AQ_QUEUE_TABLE	REFRESH_GROUP
AQ_TRANSFORM	RESOURCE_COST
ASSOCIATION	RLS_CONTEXT
AUDIT	RLS_GROUP
AUDIT_OBJ	RLS_POLICY
CLUSTER	RMGR_CONSUMER_GROUP
COMMENT	RMGR_INTITIAL_CONSUMER_GROUP
CONSTRAINT	RMGR_PLAN
CONTEXT	RMGR_PLAN_DIRECTIVE
DATABASE_EXPORT	ROLE
DB_LINK	ROLE_GRANT
DEFAULT_ROLE	ROLLBACK_SEGMENT
DIMENSION	SCHEMA_EXPORT
DIRECTORY	SEQUENCE
FGA_POLICY	SYNONYM
FUNCTION	SYSTEM_GRANT
INDEX_STATISTICS	TABLE
INDEX	TABLE_DATA
INDEXTYPE	TABLE_EXPORT
JAVA_SOURCE	TABLE_STATISTICS
JOB	TABLESPACE
LIBRARY	TABLESPACE_QUOTA
MATERIALIZED_VIEW	TRANSPORTABLE_EXPORT
MATERIALIZED_VIEW_LOG	TRIGGER
OBJECT_GRANT	TRUSTED_DB_LINK
OPERATOR	TYPE
PACKAGE	TYPE_BODY
PACKAGE_SPEC	TYPE_SPEC
PACKAGE_BODY	USER
PROCEDURE	VIEW
PROFILE	XMLSCHEMA
PROXY

Defined Data Types

TYPE offset IS RECORD (
pos                PLS_INTEGER,
len                PLS_INTEGER,
grantor            VARCHAR2(30),
patchtablemetadata VARCHAR2(1));

TYPE objddl IS TABLE OF offset INDEX BY BINARY_INTEGER;

TYPE multiobjects IS TABLE OF objddl INDEX BY BINARY_INTEGER;

Dependencies

DBMSOBJG_DP	DBMS_REDEFINITION
DBMS_ASSERT	DBMS_SNAP_INTERNAL
DBMS_DATAPUMP	DBMS_SQL
DBMS_FILE_GROUP_EXP	DBMS_SYS_ERROR
DBMS_LOB	ODCI_EXTOPT_LIB
DBMS_LOGREP_EXP	PARTLOB$
DBMS_METADATA_BUILD	PLITBLM
DBMS_METADATA_INT	TABCOMPART$
DBMS_METADATA_UTIL	WWV_FLOW_GENERATE_DDL
DBMS_ODCI	XMLTYPE
DBMS_PLUGTS

Exceptions

Exception Name	Error Code	Reason
invalid_argval	31600	Invalid argument
invalid_operation	31601	The function was called after the first call to FETCH_xxx
inconsistent_args	31602	The parameter value is inconsistent with another value specified
object_not_found	31603	The specified object was not found in the database
invalid_object_param	31604	Specified parameter value is not valid for this object type
inconsistent_operation	31607	Either FETCH_XML was called when the DDL transform was specified, or FETCH_DDL was called when the DDL transform was omitted
object_not_found2	31608	The specified object was not found in the database
stylesheet_load_error	31609	Installation script initmeta.sql failed to load the named file from the file system directory into the database
sql_error	31642	Untrapped internal DBMS_METADATA error
dbmsjava_error	39128	Unexpected DBMS_JAVA error

Object Types For OPEN (partial listing)

Type Name	Meaning
CONSTRAINT	constraints
DATABASE_EXPORT	all metadata objects in a database
DB_LINK	database links
FGA_POLICY	fine-grained audit policies
INDEX_STATISTICS	precomputed statistics on indexes
REF_CONSTRAINT	referential constraint
RLS_CONTEXT	driving contexts for enforcement of fine-grained access-control policies
RMGR_PLAN	resource plans
SCHEMA_EXPORT	all metadata objects in a schema
TABLE_DATA	metadata describing row data for a table, nested table, or partition
TRANSPORTABLE_EXPORT	metadata for objects in a transportable tablespace set
TYPE	user-defined types

ADD_DOCUMENT (new 11g)

Specifies an (S)XML document (as XMLTYPE) to be compared

Overload 1

dbms_metadata.add_document(handle IN NUMBER, document IN sys.XMLType);

Undocumented

Overload 2

dbms_metadata.add_document(handle IN NUMBER, document IN CLOB);

Undocumented

ADD_TRANSFORM

When used to retrieve objects, it specifies a transform that FETCH_xxx applies to the XML representation of the retrieved objects. When used to submit objects, specifies a transform that CONVERT or PUT applies to the XML representation of the submitted objects.

dbms_metadata.add_transform(
handle      IN NUMBER,
name        IN VARCHAR2,
encoding    IN VARCHAR2 DEFAULT NULL,
object_type IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;

See SET_REMAP_PARAM Demo

CHECK_MATCH_TEMPLATE

Check if sub-partitions were created via table's subpartition template clause

dbms_metadata.check_match_template(
pobjno IN NUMBER,
spcnt IN NUMBER)
RETURN NUMBER;

Undocumented

CHECK_MATCH_TEMPLATE_LOB

Check if sub-partitions lob were created via table's subpartition template clause

dbms_metadata.check_match_template_lob(
pobjno IN NUMBER,
spcnt IN NUMBER)
RETURN NUMBER;

Undocumented

CHECK_MATCH_TEMPLATE_PAR

Check if sub-partitions were created via table's subpartition template clause

dbms_metadata.check_match_template_par(
pobjno IN NUMBER,
spcnt IN NUMBER)
RETURN NUMBER;

Undocumented

CHECK_TYPE

For transportable import, check a type's definition and typeid

dbms_metadata.check_type(
schema    IN VARCHAR2,
type_name IN VARCHAR2,
version   IN VARCHAR2,
hashcode IN VARCHAR2,
typeid    IN VARCHAR2);

Undocumented

CLOSE

Invalidates the handle returned by OPEN or OPENW and cleans up the associated state

dbms_metadata.close(handle IN NUMBER);

DECLARE
h NUMBER;
BEGIN
SELECT dbms_metadata.open('TABLE')
INTO h
FROM DUAL;

dbms_metadata.close(h);
END;
/

COMPARE_ALTER (new 11g)

This function compares the metadata for two objects and returns a set of ALTER statements for making object 1 like object2.

dbms_metadata.compare_alter (
object_type   IN VARCHAR2,
name1         IN VARCHAR2,
name2         IN VARCHAR2,
schema1       IN VARCHAR2 DEFAULT NULL,
schema2       IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;

conn uwclass/uwclass

set serveroutput on

DECLARE
c CLOB;
BEGIN
SELECT dbms_metadata.compare_alter('TABLE', 'SERVERS', 'SERV_INST', USER, USER)
INTO c
FROM DUAL;

dbms_output.put_line(c);
dbms_advisor.create_file(c, 'CTEMP', 'compalter.txt');
END;
/

COMPARE_ALTER_XML (new 11g)

Compares the metadata for two objects and returns an ALTER_XML document

dbms_metadata.compare_alter_xml (
object_type   IN VARCHAR2,
name1         IN VARCHAR2,
name2         IN VARCHAR2,
schema1       IN VARCHAR2 DEFAULT NULL,
schema2       IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;

conn / as sysdba

set serveroutput on

DECLARE
c CLOB;
BEGIN
SELECT dbms_metadata.compare_alter_xml('TABLE', 'EMP', 'EMPLOYEES', 'SCOTT', 'HR')
INTO c
FROM DUAL;

dbms_output.put_line(c);
END;
/

COMPARE_SXML (new 11g)

The functions compares the metadata for two objects and returns
an sxml difference document

dbms_metadata.compare_sxml (
object_type   IN VARCHAR2,
name1         IN VARCHAR2,
name2         IN VARCHAR2,
schema1       IN VARCHAR2 DEFAULT NULL,
schema2       IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;

conn / as sysdba

set serveroutput on

DECLARE
c CLOB;
BEGIN
SELECT dbms_metadata.compare_sxml('TABLE', 'EMP', 'EMPLOYEES', 'SCOTT', 'HR')
INTO c
FROM DUAL;

dbms_output.put_line(c);
END;
/

CONVERT (Function: Overload 3 new to 11g)

Transforms an input XML document into creation DDL

Overload 1

dbms_metadata.convert(handle IN NUMBER, document IN sys.XMLType)
RETURN sys.ku$_multi_ddls;

TBD

Overload 2

dbms_metadata.convert(handle IN NUMBER, document IN CLOB)
RETURN sys.ku$_multi_ddls;

TBD

This is an alternate higher-performing but less flexible form of CONVERT that returns only a single (but multi-object) CLOB with a collection providing offsets into this CLOB to locate each individual DDL. Parse items per DDL are NOT returned with this version.

Overload 3

DBMS_METADATA.CONVERT (
handle IN NUMBER,
document IN CLOB,
offsets OUT NOCOPY multiobjects)
RETURN CLOB;

TBD

Transforms an input XML document into creation DDL

Overload 4

DBMS_METADATA.CONVERT (
handle   IN     NUMBER,
document IN     sys.XMLType,
result   IN OUT NOCOPY CLOB);

TBD

Overload 5

DBMS_METADATA.CONVERT (
handle   IN     NUMBER,
document IN     CLOB,
result   IN OUT NOCOPY CLOB);

TBD

CONVERT_TO_CANONICAL (new 11g)

Convert string to canonical form
vv.vv.vv.vv.vv, e.g., '08.01.03.00.00'

dbms_metadata.convert_to_canonical(version IN VARCHAR2) RETURN VARCHAR2

SELECT dbms_metadata.convert_to_canonical('11.1.0.6.0')
FROM DUAL;

FETCH_CLOB (overload 3 new to 11g)

Returns the object, transformed or not, as a CLOB

Overload 1

dbms_metadata.fetch_clob(
handle IN NUMBER,
cache_lob IN BOOLEAN DEFAULT TRUE,
lob_duration IN PLS_INTEGER DEFAULT DBMS_LOB.SESSION)
RETURN CLOB;

TBD

Returns the object, transformed or not, as a CLOB

Overload 2

dbms_metadata.fetch_clob(
handle IN NUMBER,
xmldoc IN OUT NOCOPY CLOB);

TBD

Return metadata for object (transformed or not) as a CLOB

Overload 3

dbms_metadata.fetch_clob(
handle IN NUMBER,
xmldoc IN OUT NOCOPY CLOB,
diffs OUT BOOLEAN);

TBD

FETCH_DDL

Fetch selected DB objects as DDL

dbms_metadata.fetch_ddl(handle IN NUMBER) RETURN sys.ku$_ddls;

TBD

FETCH_DDL_TEXT

Fetch selected DB objects as DDL in a VARCHAR2

dbms_metadata.fetch_ddl_text(handle IN NUMBER, partial OUT NUMBER)
RETURN VARCHAR2;

TBD

FETCH_OBJNUMS

Table function to return object numbers. Used to speed up heterogeneous fetch

dbms_metadata.fetch_objnums(handle IN NUMBER)
RETURN sys.ku$_ObjNumSet pipelined;

TBD

FETCH_SORTED_OBJNUMS

Table function to return nested table of
obj#-order pairs

dbms_metadata.fetch_sorted_objnums(handle IN NUMBER)
RETURN sys.ku$_ObjNumPairList;

TBD

FETCH_XML

Fetch selected DB objects as XML docs

dbms_metadata.fetch_xml(handle IN NUMBER) RETURN sys.XMLType;

TBD

FETCH_XML_CLOB (overload 2 new 11g)

Returns the XML metadata for the objects as a CLOB in an IN OUT NOCOPY parameter

Overload 1

dbms_metadata.fetch_xml_clob(
handle           IN     NUMBER,
doc              IN OUT NOCOPY CLOB,
parsed_items        OUT sys.ku$_parsed_items,
object_type_path    OUT VARCHAR2);

TBD

Returns the XML metadata for the objects as a CLOB in an IN OUT NOCOPY parameter

Overload 2

dbms_metadata.fetch_xml_clob(
handle           IN     NUMBER,
doc              IN OUT NOCOPY CLOB,
parsed_items     IN OUT NOCOPY sys.ku$_parsed_items,
object_type_path    OUT VARCHAR2,
seqno               OUT NUMBER,
procobj_errors      OUT sys.ku$_vcnt);

TBD

FREE_CONTEXT_ENTRY

To be called *ONLY* by the definer's rights pkg. (dbms_metadata_int) error handling.

dbms_metadata.free_context_entry(ind IN NUMBER);

TBD

GET_ACTION_INSTANCE

Get the export string from call instance_info_exp and instance_extended_info_exp function of package in exppkgact$

dbms_metadata.get_action_instance(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2,
name       IN VARCHAR2,
schema     IN VARCHAR2,
namespace IN NUMBER,
objtype    IN NUMBER,
prepost    IN NUMBER,
isdba      IN NUMBER)
RETURN sys.ku$_procobj_lines;

TBD

GET_ACTION_SCHEMA

Get the export string from call schema_info_exp function of package in exppkgact$

dbms_metadata.get_action_schema(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2,
schema     IN VARCHAR2,
prepost    IN NUMBER,
isdba      IN NUMBER)
RETURN sys.ku$_procobj_lines;

TBD

GET_ACTION_SYS

Get the export string from call system_info_exp

dbms_metadata.get_action_sys(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2,
prepost    IN NUMBER)
RETURN sys.ku$_procobj_lines;

TBD

GET_CANONICAL_VSN

Convert user's VERSION param to canonical form. (APIs unique to the submit interface)

dbms_metadata.get_canonical_vsn(version IN VARCHAR2) RETURN VARCHAR2;

SELECT version, dbms_metadata.get_canonical_vsn(version)
FROM gv$instance;

GET_DDL

Fetch DDL for objects

dbms_metadata.get_ddl(
object_type IN VARCHAR2,
name        IN VARCHAR2,
schema      IN VARCHAR2 DEFAULT NULL,
version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
model     IN VARCHAR2 DEFAULT 'ORACLE',
transform   IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

Table
CREATE TABLE test
PCTFREE 0
TABLESPACE uwdata AS
SELECT table_name, tablespace_name
FROM user_tables;

SET LONG 10000

SELECT dbms_metadata.get_ddl('TABLE', 'TEST')
FROM DUAL;

View
CREATE OR REPLACE VIEW my_tables AS
select table_name, tablespace_name
FROM user_tables;

SELECT dbms_metadata.get_ddl('VIEW', 'MY_TABLES')
FROM DUAL;

Function
CREATE OR REPLACE FUNCTION whoami RETURN VARCHAR2 IS

BEGIN
RETURN user;
END whoami;
/

SELECT dbms_metadata.get_ddl('FUNCTION', 'WHOAMI')
FROM DUAL;

Tablespace
SELECT dbms_metadata.get_ddl('TABLESPACE', 'UWDATA')
FROM DUAL;

GET_DEPENDENT_DDL

Fetch DDL for dependent objects (audits, object grants)

dbms_metadata.get_dependent_ddl(
object_type        IN VARCHAR2,
base_object_name   IN VARCHAR2,
base_object_schema IN VARCHAR2 DEFAULT NULL,
version            IN VARCHAR2 DEFAULT 'COMPATIBLE',
model              IN VARCHAR2 DEFAULT 'ORACLE',
transform          IN VARCHAR2 DEFAULT 'DDL',
object_count       IN NUMBER   DEFAULT 10000)
RETURN CLOB;

GRANT select ON servers TO hr;
GRANT select ON servers TO scott;

set long 100000

SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT','SERVERS')
FROM DUAL;

GET_DEPENDENT_SXML (new 11g)

Return the metadata for objects dependent on a base object as XML

dbms_metadata.get_dependent_sxml(
object_type        IN VARCHAR2,
base_object_name   IN VARCHAR2,
base_object_schema IN VARCHAR2 DEFAULT NULL,
version            IN VARCHAR2 DEFAULT 'COMPATIBLE',
model              IN VARCHAR2 DEFAULT 'ORACLE',
transform          IN VARCHAR2 DEFAULT 'SXML',
object_count       IN NUMBER DEFAULT 10000)
RETURN CLOB;

TBD

GET_DEPENDENT_XML

Fetch XML for dependent objects (audits, object grants)

dbms_metadata.get_dependent_xml(
object_type        IN VARCHAR2,
base_object_name   IN VARCHAR2,
base_object_schema IN VARCHAR2 DEFAULT NULL,
version            IN VARCHAR2 DEFAULT 'COMPATIBLE',
model              IN VARCHAR2 DEFAULT 'ORACLE',
transform          IN VARCHAR2 DEFAULT NULL,
object_count       IN NUMBER DEFAULT 10000)
RETURN CLOB;

GRANT all ON servers TO system;

set long 1000000

SELECT dbms_metadata.get_dependent_xml('TABLE', 'UWCLASS.SERVERS')
FROM DUAL;

GET_DOMIDX_METADATA

Get PLSQL code from the ODCIIndexGetMetadata
method of a domain index's implementation type

dbms_metadata.get_domidx_metadata(
index_name   IN VARCHAR2,
index_schema IN VARCHAR2,
type_name    IN VARCHAR2,
type_schema IN VARCHAR2,
flags        IN NUMBER)
RETURN sys.ku$_procobj_lines;

TBD

GET_DPSTRM_MD

Get stream metadata for table (for use by DataPump data layer only this is an internal API)

dbms_metadata.get_dpstrm_md (
schema           IN     VARCHAR2,
name             IN     VARCHAR2,
mdversion        IN     VARCHAR2 DEFAULT 'COMPATIBLE',
dpapiversion     IN     NUMBER DEFAULT 3,
doc              IN OUT NOCOPY CLOB,
network_link     IN     VARCHAR2 DEFAULT NULL,
force_lob_be     IN     BOOLEAN DEFAULT FALSE,
force_no_encrypt IN     BOOLEAN DEFAULT FALSE);

TBD

GET_EDITION (new 11g)

Returns the edition of interest for the current MDAPI function. (this is either specified as the 'edition' filter, or the session current edition.

dbms_metadata.get_edition RETURN VARCHAR2;

SELECT dbms_metadata.get_edition FROM DUAL;

GET_EDITION_ID (new 11g)

Returns the edition ID of interest for the current MDAPI context.

dbms_metadata.get_edition_id RETURN NUMBER;

TBD

GET_GRANTED_DDL

Fetch granted objects (system grants, role grants) DDL

dbms_metadata.get_granted_ddl(
object_type IN VARCHAR2,
grantee      IN VARCHAR2 DEFAULT NULL,
version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
model        IN VARCHAR2 DEFAULT 'ORACLE',
transform    IN VARCHAR2 DEFAULT 'DDL',
object_count IN NUMBER   DEFAULT 10000)
RETURN CLOB;

SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'UWCLASS')
FROM DUAL;

GET_GRANTED_XML

Fetch granted objects (system grants, role grants) in XML format

dbms_metadata.get_granted_xml(
object_type IN VARCHAR2,
grantee      IN VARCHAR2 DEFAULT NULL,
version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
model        IN VARCHAR2 DEFAULT 'ORACLE',
transform    IN VARCHAR2 DEFAULT NULL,
object_count IN NUMBER   DEFAULT 10000)
RETURN CLOB;

set long 1000000

SELECT dbms_metadata.get_granted_xml('SYSTEM_GRANT', 'UWCLASS')
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.get_index_intcol(
obj_num IN NUMBER,
intcol_num IN NUMBER)
RETURN NUMBER;

TBD

GET_JAVA_METADATA

Return java info from DBMS_JAVA.EXPORT

dbms_metadata.get_java_metadata(
java_name IN VARCHAR2,
java_schema IN VARCHAR2,
type_num IN NUMBER)
RETURN sys.ku$_java_t;

TBD

GET_PLUGTS_BLK

Get the export string from dbms_plugts

dbms_metadata.get_plugts_blk(blockid IN NUMBER)
RETURN sys.ku$_procobj_lines;

TBD

GET_PREPOST_TABLE_ACT

Get the export string for pre-table action from call dbms_export_extension.pre_table

1 = pre

dbms_metadata.get_prepost_table_act(
prepost IN NUMBER,
schema IN VARCHAR2,
tname IN VARCHAR2)
RETURN sys.ku$_taction_list_t;

TBD

Get the export string for post-table action from call dbms_export_extension.post_table

2 = post

dbms_metadata.get_prepost_table_act(
prepost IN NUMBER,
schema IN VARCHAR2,
tname IN VARCHAR2)
RETURN sys.ku$_taction_list_t;

TBD

GET_PROCOBJ

Get the export string from create_exp or audit_exp function of package in exppkobj$

dbms_metadata.get_procobj(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2,
objid      IN NUMBER,
isdba      IN PLS_INTEGER)
RETURN sys.ku$_procobj_lines;

TBD

GET_PROCOBJ_GRANT

Get the export string from call grant_exp function of package in exppkobj$

dbms_metadata.get_procobj_grant(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2,
objid      IN NUMBER,
isdba      IN PLS_INTEGER)
RETURN sys.ku$_procobj_lines;

TBD

GET_QUERY

Returns the text of the queries that are used by FETCH_xxx. This function assists in debugging

dbms_metadata.get_query(handle IN NUMBER) RETURN VARCHAR2;

set long 1000000
set serveroutput on
set pagesize 0
set linesize 1000
set trim on
set trimspool on

spool c:\temp\demo.txt

DECLARE
n NUMBER;
s VARCHAR2(32767);
BEGIN
SELECT dbms_metadata.open('TABLE')
INTO n
FROM DUAL;

SELECT dbms_metadata.get_query(n)
INTO s
FROM DUAL;

dbms_output.put_line(s);

dbms_metadata.close(n);
END;
/

spool off

-- replace :SCHEMA1 with 'UWCLASS'

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), 0
FROM SYS.KU$_IOTABLE_VIEW KU$
WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192)
AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0
AND KU$.SCHEMA_OBJ.OWNER_NAME='UWCLASS';

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), 0
FROM SYS.KU$_PFHTABLE_VIEW KU$
WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192)
AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0
AND KU$.SCHEMA_OBJ.OWNER_NAME='UWCLASS';

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), 0
FROM SYS.KU$_PHTABLE_VIEW KU$
WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192)
AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0
AND KU$.SCHEMA_OBJ.OWNER_NAME='UWCLASS';

SELECT /*+rule*/ SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), 0
FROM SYS.KU$_FHTABLE_VIEW KU$
WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192)
AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0
AND KU$.SCHEMA_OBJ.OWNER_NAME='UWCLASS';

SELECT /*+rule*/
SYS_XMLGEN(VALUE(KU$), XMLFORMAT.createFormat2('TABLE_T', '7')), 0
FROM SYS.KU$_HTABLE_VIEW KU$
WHERE NOT (BITAND (KU$.PROPERTY,8192)=8192)
AND NOT BITAND(KU$.SCHEMA_OBJ.FLAGS,128)!=0
AND KU$.SCHEMA_OBJ.OWNER_NAME='UWCLASS';

GET_STAT_COLNAME (new in 11.1.0.7)

Returns a column name for restoring statistics

dbms_metadata.get_index_intcol(
owner_name   IN VARCHAR2,
table_name   IN VARCHAR2,
default_val IN LONG,     -- value or null from col$.default$
attr_colname IN VARCHAR2, -- value or null from attrcol$.name
nested_table IN NUMBER)   -- 1 if nested table, 0 otherwise
RETURN VARCHAR2;

TBD

GET_STAT_INDNAME (new in 11.1.0.7)

Returns an index_owner and index name for restoring statistics

dbms_metadata.get_index_intcol(
table_owner IN VARCHAR2,
table_name IN VARCHAR2,
col_names   IN sys.t_var_coll   -- varray of columns that index is on
col_count   IN NUMBER,          -- number of columns that index is on
ind_owner   OUT VARCHAR2,        -- index owner
ind_name    OUT VARCHAR2);       -- index name

TBD

GET_SXML (new 11.1.0.6)

Returns the metadata for a single object as SXML. This interface is meant for casual browsing (e.g., from SQLPlus) vs. the programmatic OPEN / FETCH / CLOSE interfaces

dbms_metadata.get_sxml(
object_type IN VARCHAR2,
name        IN VARCHAR2,
schema      IN VARCHAR2 DEFAULT NULL,
version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
model       IN VARCHAR2 DEFAULT 'ORACLE',
transform   IN VARCHAR2 DEFAULT 'SXML')
RETURN CLOB;

conn / as sysdba

set serveroutput on

DECLARE
c CLOB;
BEGIN
SELECT dbms_metadata.get_sxml('TABLE', 'SERVERS', 'UWCLASS')
INTO c
FROM DUAL;

dbms_output.put_line(c);
END;
/

GET_SYSPRIVS

Get the export string from call grant_sysprivs_exp and audit_sysprivs_exp function of a package in exppkgobj$

dbms_metadata.get_sysprivs(
package IN VARCHAR2,
pkg_schema IN VARCHAR2,
function IN VARCHAR2)
RETURN sys.ku$_procobj_lines;

TBD

GET_SYSPRIVS

Get the export string from call grant_sysprivs_exp and audit_sysprivs_exp function of a package in exppkgobj$

dbms_metadata.get_sysprivs(
package IN VARCHAR2,
pkg_schema IN VARCHAR2,
function IN VARCHAR2)
RETURN sys.ku$_procobj_lines;

TBD

GET_VERSION (new 11g)

Returns the version of interest for the current MDAPI context. Comes from the version parameter in open

dbms_metadata.get_version RETURN VARCHAR2;

SELECT dbms_metadata.get_version FROM DUAL;

GET_XML

Fetch XML for objects

dbms_metadata.get_xml(
object_type IN VARCHAR2,
name        IN VARCHAR2,
schema      IN VARCHAR2 DEFAULT NULL,
version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
model       IN VARCHAR2 DEFAULT 'ORACLE',
transform   IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;

Table
CREATE TABLE test
PCTFREE 0
TABLESPACE uwdata AS
SELECT table_name, tablespace_name
FROM user_tables;

SELECT dbms_metadata.get_xml('TABLE', 'TEST')
FROM DUAL;

View
CREATE OR REPLACE VIEW my_tables AS
SELECT table_name, tablespace_name
FROM user_tables;

SET LONG 4000

SELECT dbms_metadata.get_xml('VIEW', 'MY_TABLES')
FROM DUAL;

Function
CREATE OR REPLACE FUNCTION whoami RETURN VARCHAR2 IS
BEGIN
RETURN user;
END whoami;
/

SELECT dbms_metadata.get_xml('FUNCTION', 'WHOAMI')
FROM DUAL;

IS_OBJECT_XDB_GENERATED (new 11g)

Check for whether object is generated by an dbms_xmlschema.registerschema call. Used by export for triggers and RLS policies.

dbms_metadata.is_object_xdb_generated(
object_name IN VARCHAR2,
schema IN VARCHAR2,
base_obj_num IN NUMBER)

Undocumented

NETWORK_CALLOUTS

Execute callouts (used by network mode)

dbms_metadata.network_callouts(handle IN NUMBER);

TBD

NETWORK_FETCH_CLOB

Fetch selected DB objects in a VARCHAR2 (used by network mode)

dbms_metadata.network_fetch_clob(
handle       IN NUMBER,
do_xsl_parse IN NUMBER,
partial      OUT NUMBER,
parse_delim OUT VARCHAR2,
do_callout   OUT NUMBER,
have_errors OUT NUMBER)
RETURN VARCHAR2;

TBD

NETWORK_FETCH_ERRORS

Serializes a ku$_vcnt into a VARCHAR2 for network operations. Returns a delimited series of error string

dbms_metadata.network_fetch_errors(
handle IN NUMBER,
cnt     OUT NUMBER,
partial OUT NUMBER,
seqno   OUT NUMBER,
path    OUT VARCHAR2)
RETURN VARCHAR2;

TBD

NETWORK_FETCH_PARSE

Return serialized parse items in a VARCHAR2 used by network mode)

dbms_metadata.network_fetch_parse(
handle IN NUMBER,
cnt     OUT NUMBER,
partial OUT NUMBER,
seqno   OUT NUMBER,
path    OUT VARCHAR2)
RETURN VARCHAR2;

TBD

NETWORK_OPEN

Do OPEN over network, negotiate protocol version

dbms_metadata.network_open(
object_type      IN VARCHAR2,
version          IN VARCHAR2 DEFAULT 'COMPATIBLE',
model            IN VARCHAR2 DEFAULT 'ORACLE',
client_version   IN NUMBER,
protocol_version OUT NUMBER)
RETURN NUMBER;

TBD

NET_SET_DEBUG

Set the internal debug switch on remote node

dbms_metadata.net_set_debug(on_off IN BOOLEAN);

TBD

OKTOEXP_2NDARY_TABLE

Should a secondary object of a domain index be exported?

dbms_metadata.oktoexp_2ndary_table(tab_obj_num IN NUMBER)
RETURN PLS_INTEGER;

TBD

OPEN

Specifies the type of object to be retrieved, the version of its metadata, and the object model

dbms_metadata.open(
object_type IN VARCHAR2,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
network_link IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;

set serveroutput on

DECLARE
h NUMBER;
BEGIN
SELECT dbms_metadata.open('TABLE')
INTO h
FROM DUAL;

dbms_output.put_line(h);

dbms_metadata.close(h);
END;
/

OPENC (new 11g)

Establishes a 'compare' context and specifies the object type for comparing to (S)XML documents

dbms_metadata.openc(object_type IN VARCHAR2) RETURN NUMBER;

Undocumented

OPENW

Specifies the type of object to be submitted and the object model. The return value is an opaque context handle

dbms_metadata.openw(
object_type IN VARCHAR2,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE')
RETURN NUMBER;

set serveroutput on

DECLARE
h NUMBER;
BEGIN
SELECT dbms_metadata.openw('TABLE')
INTO h
FROM DUAL;

dbms_output.put_line(h);

dbms_metadata.close(h);
END;
/

PATCH_TYPEID

For transportable import, modify a type's typeid

dbms_metadata.patch_typeid(
schema   IN VARCHAR2,
name     IN VARCHAR2,
typeid   IN VARCHAR2,
hashcode IN VARCHAR2);

TBD

PUT

Submits an XML document containing object metadata to the database to create the objects

Overload 1

dbms_metadata.put(
handle   IN     NUMBER,
document IN     sys.XMLType,
flags    IN     NUMBER,
results IN OUT NOCOPY sys.ku$_SubmitResults)
RETURN BOOLEAN;

TBD

Overload 2

dbms_metadata.put(
handle   IN     NUMBER,
document IN     CLOB,
flags    IN     NUMBER,
results IN OUT NOCOPY sys.ku$_SubmitResults)
RETURN BOOLEAN;

TBD

SET_COUNT

The maximum number of objects to be retrieved in a single FETCH_xxx call

dbms_metadata.set_count(
handle IN NUMBER,
value IN NUMBER,
object_type_path IN VARCHAR2 DEFAULT NULL);

DECLARE
h NUMBER;
BEGIN
SELECT dbms_metadata.open('TABLE')
INTO h
FROM DUAL;

dbms_metadata.set_count(h, 20, 'TABLE');

dbms_metadata.close(h);
END;
/

SET_DEBUG

Set the internal debug switch and turn on jdev

dbms_metadata.set_debug(on_off IN BOOLEAN);

TBD

SET_FILTER (overload 2 and 3 new in 11.1.)

Specifies restrictions on the objects to be retrieved, for example, the object name or schema

Overload 1

dbms_metadata.set_filter(
handle           IN NUMBER,
name             IN VARCHAR2,
value            IN VARCHAR2,
object_type_path IN VARCHAR2 DEFAULT NULL);

DECLARE
h NUMBER;
BEGIN
SELECT dbms_metadata.open('TABLE')
INTO h
FROM DUAL;

dbms_metadata.set_filter(h, 'NAME', '<=''BOWIE_STUFF''');
dbms_metadata.set_filter(h, 'NAME', '<=''FPN''');

dbms_metadata.close(h);
END;
/

Overload 2

dbms_metadata.set_filter(
handle           IN NUMBER,
name             IN VARCHAR2,
value            IN BOOLEAN DEFAULT TRUE,
object_type_path IN VARCHAR2 DEFAULT NULL);

TBD

Overload 3

dbms_metadata.set_filter(
handle           IN NUMBER,
name             IN VARCHAR2,
value            IN NUMBER,
object_type_path IN VARCHAR2 DEFAULT NULL);

TBD

SET_PARSE_ITEM

Enables output parsing and specifies an object attribute to be parsed and returned

dbms_metadata.set_parse_item(
handle      IN NUMBER,
name        IN VARCHAR2,
object_type IN VARCHAR2 DEFAULT NULL);

The following syntax applies when SET_PARSE_ITEM is used for XML submission:

dbms_metadata.set_parse_item(
handle IN NUMBER,
name   IN VARCHAR2);

DECLARE
h NUMBER;
BEGIN
SELECT dbms_metadata.open('TABLE')
INTO h
FROM DUAL;

dbms_metadata.set_parse_item(h, 'T1');

dbms_metadata.close(h);
END;
/

SET_PARSING (new 11g)

Turn query parsing on/off

dbms_metadata.set_parsing(on_off IN BOOLEAN);

exec dbms_metadata.set_parsing(TRUE);

SET_REMAP_PARAM

Specify parameters to the XSLT stylesheet identified by transform_handle.Use them to modify or customize the output of the transform

dbms_metadata.set_remap_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
old_value        IN VARCHAR2,
new_value        IN VARCHAR2,
object_type      IN VARCHAR2 DEFAULT NULL);

set serveroutput on

DECLARE
h NUMBER;
th NUMBER;
BEGIN
SELECT dbms_metadata.openw('TABLE')
INTO h
FROM DUAL;

th := dbms_metadata.add_transform(h,'MODIFY');

-- prepare for a different schema
dbms_metadata.set_remap_param(th, 'REMAP_SCHEMA', 'UWCLASS', 'IDS');

dbms_metadata.close(h);
END;
/

SET_TRANSFORM_PARAM

Specify parameters to the XSLT stylesheet identified by transform_handle.Use them to modify or customize the output of the transform

Overload 1

dbms_metadata.set_transform_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
value            IN VARCHAR2),
object_type      IN VARCHAR2 DEFAULT NULL);

set long 2000000
set pagesize 0

SELECT dbms_metadata.get_ddl('TABLE', 'T1')
FROM DUAL;

-- omit the storage clause
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', FALSE);

SELECT dbms_metadata.get_ddl('TABLE', 'T1')
FROM DUAL;

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'DEFAULT');

Overload 2

dbms_metadata.set_transform_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
value            IN BOOLEAN DEFAULT TRUE,
object_type      IN VARCHAR2 DEFAULT NULL);

TBD

Overload 3

dbms_metadata.set_transform_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
value            IN NUMBER,),
object_type      IN VARCHAR2 DEFAULT NULL);

TBD

SET_XMLFORMAT

Specify formatting attributes for XML output

dbms_metadata.set_xmlformat(
handle IN NUMBER,
name IN VARCHAR2,
value IN BOOLEAN DEFAULT TRUE);

TBD

Demo (copied from http://www.orafaq.com/node/59)

CREATE TYPE tableddl_ty AS OBJECT (
table_name VARCHAR2(30),
orig_schema VARCHAR2(30),
orig_ddl CLOB,
comp_schema VARCHAR2(30),
comp_ddl CLOB);
/

CREATE TYPE tableddl_ty_tb AS TABLE OF tableddl_ty;
/

CREATE OR REPLACE FUNCTION tableddl_fc (input_values SYS_REFCURSOR)
RETURN tableddl_ty_tb PIPELINED IS

PRAGMA AUTONOMOUS_TRANSACTION;

-- variables to be passed in by sys_refcursor */
table_name VARCHAR2(30);
orig_schema VARCHAR2(30);
comp_schema VARCHAR2(30);

-- setup output record of TYPE tableddl_ty
out_rec tableddl_ty := tableddl_ty(NULL,NULL,NULL,NULL,NULL);

/* setup handles to be used for setup and fetching metadata information handles are used
to keep track of the different objects (DDL) we will be referencing in the PL/SQL code */
hOpenOrig0 NUMBER;
hOpenOrig   NUMBER;
hOpenComp   NUMBER;
hModifyOrig NUMBER;
hTransDDL   NUMBER;
dmsf        PLS_INTEGER;

/*
CLOBs to hold DDL
Orig_ddl0 will hold the baseline DDL for the object to be compared
Orig_ddl1 will also hold the baseline DDL for the object to be compared against
but will also go through some translations before being compared
against Comp_ddl2
Comp_ddl2 will contain the DDL to be compared against the baseline
*/
Orig_ddl0 CLOB;
Orig_ddl1 CLOB;
Comp_ddl2 CLOB;

ret        NUMBER;
BEGIN
/* Strip off Attributes not concerned with in DDL. If you are concerned with
     TABLESPACE, STORAGE, or SEGMENT information just comment out these few lines. */
dmsf := dbms_metadata.session_transform
dbms_metadata.set_transform_param(dmsf, 'TABLESPACE', FALSE);
dbms_metadata.set_transform_param(dmsf, 'STORAGE', FALSE);
dbms_metadata.set_transform_param(dmsf, 'SEGMENT_ATTRIBUTES', FALSE);

-- Loop through each of the rows passed in by the reference cursor
LOOP
    /* Fetch the input cursor into PL/SQL variables */
    FETCH input_values INTO table_name, orig_schema, comp_schema;
    EXIT WHEN input_values%NOTFOUND;

    /* Here is the first use of our handles for pointing to the original table DDL
       It names the object_type (TABLE), provides the name of the object (our PL/SQL
       variable table_name), and states the schema it is from */
    hOpenOrig0 := dbms_metadata.open('TABLE');
    dbms_metadata.set_filter(hOpenOrig0,'NAME',table_name);
    dbms_metadata.set_filter(hOpenOrig0,'SCHEMA',orig_schema);

    /* Setup handle again for the original table DDL that will undergo transformation
       We setup two handles for the original object DDL because we want to be able to
       Manipulate one set for comparison but output the original DDL to the user */
    hOpenOrig := dbms_metadata.open('TABLE');
    dbms_metadata.set_filter(hOpenOrig,'NAME',table_name);
    dbms_metadata.set_filter(hOpenOrig,'SCHEMA',orig_schema);

    -- Setup handle for table to compare original against
    hOpenComp := dbms_metadata.open('TABLE');
    dbms_metadata.set_filter(hOpenComp,'NAME',table_name);
    dbms_metadata.set_filter(hOpenComp,'SCHEMA',comp_schema);

    /* Modify the transformation of "orig_schema" to take on ownership of "comp_schema"
       If we didn't do this, when we compared the original to the comp objects there
       would always be a difference because the schema_owner is in the DDL generated */
    hModifyOrig := dbms_metadata.add_transform(hOpenOrig,'MODIFY');
    dbms_metadata.set_remap_param(hModifyOrig,'REMAP_SCHEMA',orig_schema,comp_schema);

    -- This states to created DDL instead of XML to be compared
    hTransDDL := dbms_metadata.add_transform(hOpenOrig0,'DDL');
    hTransDDL := dbms_metadata.add_transform(hOpenOrig ,'DDL');
    hTransDDL := dbms_metadata.add_transform(hOpenComp ,'DDL');

    -- Get the DDD and store into the CLOB PL/SQL variables
    Orig_ddl0 := dbms_metadata.fetch_clob(hOpenOrig0);
    Orig_ddl1 := dbms_metadata.fetch_clob(hOpenOrig);

    /* Here we are providing for those instances where the baseline object does not
       exist in the Comp_schema. */
    BEGIN
      Comp_ddl2 := dbms_metadata.fetch_clob(hOpenComp);
    EXCEPTION
      WHEN OTHERS THEN
        comp_ddl2 := 'DOES NOT EXIST';
    END;

    -- Now simply compare the two DDL statements and output row if not equal
    ret := dbms_lob.compare(Orig_ddl1, Comp_ddl2);
    IF ret != 0 THEN
      out_rec.table_name := table_name;
      out_rec.orig_schema := orig_schema;
      out_rec.orig_ddl := Orig_ddl0;
      out_rec.comp_schema := comp_schema;
      out_rec.comp_ddl := Comp_ddl2;
      PIPE ROW(out_rec);
    END IF;

    -- Cleanup and release the handles
    dbms_metadata.close(hOpenOrig0);
    dbms_metadata.close(hOpenOrig);
    dbms_metadata.close(hOpenComp);
END LOOP;
RETURN;
END TABLEDDL_FC;
/

SELECT *
FROM TABLE(tableddl_fc(CURSOR(SELECT table_name, owner, 'UWCLASS'
FROM dba_tables where owner = 'ABC')));

Related Topics

DBMS_PREPROCESSOR

-----