Morgan's Library Oracle Database DBMS_METADATA Example Sample Code Demos Tutorials SQL PL/SQL PLSQL PL SQL Metadata Reverse Engineer XML

Oracle DBMS_METADATA
Version 11.2.0.3

General Information

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 (P & U only)	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

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

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

TBD

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

CONVERT

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

Convert string to canonical form
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

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 (new 11.2.0.3 overload)

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

Overload 1

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

set serveroutput on

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

dbms_output.put_line(h);
END;
/

SELECT dbms_metadata.fetch_objnums(100002) FROM dual;

exec dbms_metadata.close(100002);

Overload 2

dbms_metadata.fetch_objnums RETURN sys.ku$_ObjNumSet PIPELINED;

TBD

Overload 3

dbms_metadata.fetch_objnums(table_type IN VARCHAR2) RETURN sys.ku$_ObjNumSet PIPELINED;

TBD

FETCH_OBJNUMS_NAMES (new 11.2.0.2)

Return object numbers and names of the current handle

dbms_metadata.fetch_objnums_names RETURN sys.ku$_ObjNumNamSet 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;

DECLARE
h NUMBER;
BEGIN
h := dbms_metadata.open('TABLE');
dbms_output.put_line(h);
END;
/

SELECT dbms_metadata.fetch_sorted_objnums(200001) FROM dual;

exec dbms_metadata.close(200001);

FETCH_XML

Fetch selected DB objects as XML docs

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

DECLARE
h NUMBER;
BEGIN
h := dbms_metadata.open('TABLE');
dbms_output.put_line(h);
dbms_metadata.close(h);
END;
/

SELECT dbms_metadata.fetch_xml(300001) FROM dual;

exec dbms_metadata.close(300001);

FETCH_XML_CLOB

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 NOCOPY sys.ku$_parsed_items,
object_type_path    OUT VARCHAR2);

DECLARE
h NUMBER;
c CLOB;
x sys.ku$_parsed_items;
y VARCHAR2(100);
BEGIN
SELECT dbms_metadata.open('TABLE')
INTO h
FROM dual;

dbms_metadata.fetch_xml_clob(h, c, x, y);
dbms_output.put_line(c);
dbms_metadata.close(h);
END;
/

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_CHECK_CONSTRAINT_NAME

Return the constraint name given the
condition

dbms_metadata.get_check_constraint_name(
object_type      IN VARCHAR2,
schema           IN VARCHAR2,
name             IN VARCHAR2,
condition        IN CLOB,
parsed_condition IN BOOLEAN DEFAULT FALSE)
RETURN VARCHAR2;

CREATE TABLE t (
testcol NUMBER(3));

ALTER TABLE t
ADD CONSTRAINT cc_t_testcol_gt
CHECK (testcol > 0);

ALTER TABLE t
ADD CONSTRAINT cc_t_testcol_bt
CHECK (testcol BETWEEN 1 AND 10);

SELECT constraint_name, search_condition
FROM user_constraints
WHERE table_name = 'T';

set serveroutput on

DECLARE
otype VARCHAR2(30) := 'TABLE';
pc CLOB := 'testcol > 0';
retval VARCHAR2(100);
BEGIN
retval := dbms_metadata.get_check_constraint_name(otype, USER, 'T', pc);
dbms_output.put_line(retval);
END;
/

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

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

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

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

dbms_metadata.get_edition_id RETURN NUMBER;

TBD

GET_FK_CONSTRAINT_NAME

Returns the name of a foreign key constraint given its definition

dbms_metadata.get_fk_constraint_name(handle IN NUMBER) RETURN VARCHAR2;

See OPEN_GET_FK_CONSTRAINT_NAME Demo Above

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_HASHCODE

Returns the type hashcode

dbms_metadata.get_hashcode(schema IN VARCHAR2, typename IN VARCHAR2) RETURN RAW;

SQL> select dbms_metadata.get_ddl('VIEW', 'KU$_TYPE_VIEW') from dual;

DBMS_METADATA.GET_DDL('VIEW','KU$_TYPE_VIEW')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE VIEW " SYS"." KU$_TYPE_VIEW" OF "SYS"."KU$_TYPE_T"
WITH OBJECT IDENTIFIER (obj_num) AS
select '1','2', oo.obj#, value(o), oo.oid$, t.typeid, t.version#,
sys.dbms_metadata.get_hashcode(o.owner_name,o.name), t.typecode, t.properties,
t.attributes, t.methods, t.hiddenMethods, t.externtype, t.externname,
sys.dbms_metadata_util.get_source_lines(oo.name,oo.obj#,oo.type#),
(select value(c) from ku$_switch_compiler_view c where c.obj_num =oo.obj#),
(select value(stso) from ku$_schemaobj_view stso where stso.oid = t.supertoid),
(select value(c) from ku$_collection_view c where oo.oid$ = c.toid),
cast(multiset(select value(a) from sys.ku$_type_attr_view a where a.toid = oo.oid$)
as ku$_type_attr_list_t), cast(multiset(select value(m) from sys.ku$_method_view m where m.toid = oo.oid$ and m.xflags=0 and m.obj_num=oo.o bj#) as ku$_method_list_t)
from sys.obj$ oo, sys.ku$_edition_schemaobj_view o, type$ t
where oo.type# = 13
and oo.obj# = o.obj_num
and oo.subname is null /* latest type version */
and oo.oid$ = t.toid
/* type$ properties bits:
262144=0 - latest type version
other bits=0 - not system-generated type
*/
and bitand(t.properties,262144+2048+64+16)=0
AND (SYS_CONTEXT('USERENV','CURRENT_USERID') IN (o.owner_num, 0) OR
EXISTS ( SELECT * FROM session_roles
WHERE role='SELECT_CATALOG_ROLE' ));

GET_INDEX_INTCOL

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

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

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

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_SXML_DDL

Simple 1-step method for retrieving a single
DB object, converting to SXML, then to DDL

dbms_metadata.get_sxml_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 'SXMLDDL')
RETURN CLOB;

set serveroutput on

DECLARE
clobvar CLOB;
BEGIN
clobvar := dbms_metadata.get_sxml_ddl('TABLE', 'SERVERS');
dbms_output.put_line(clobvar);
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_VAT_TABLE_NAME (new 11.2.0.3)

Undocumented. From the demo you can see my best attempt compiles and runs but appears to accomplish nothing of value.

VAT is unlikely to mean Value Added Tax so I expect this is another stub for new 12.1 functionality we will see in 2012.

dbms_metadata.get_vat_table_name(
schema IN VARCHAR2,
name IN VARCHAR2,
network_link IN VARCHAR2 DEFAULT NULL )
RETURN VARCHAR2;

SELECT dbms_metadata.get_vat_table_name('UWCLASS', 'SERVERS')
FROM dual;

exec dbms_metadata.set_vat_table_name('UWCLASS', 'VNAME', 'SERVERS');

SELECT dbms_metadata.get_vat_table_name('UWCLASS', 'SERVERS')
FROM dual;

GET_VERSION

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;

IN_TSNUM (new 11.2.0.3)

Returns 1 if the tablespace number is in the selected set

dbms_metadata.in_snum(ts_set IN NUMBER, ts_num IN NUMBER) RETURN NUMBER DETERMINISTIC;

TBD

IS_ATTR_VALID_ON_10 (new 11.2.0.2)

This is a wrapper function around dbms_metadata_int.is_attr_valid_on_10

dbms_metadata.is_attr_valid_on_10(obj_num IN NUMBER, intcol_num in NUMBER) RETURN NUMBER;

TBD

IS_XDB_TRANS (new 11.2.0.3)

Tests for XDB/transportability

dbms_metadata.is_xdb_trans RETURN NUMBER;

SELECT dbms_metadata.is_xdb_trans
FROM dual;

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
h := dbms_metadata.open('TABLE');
dbms_output.put_line(h);
dbms_metadata.close(h);
END;
/

OPEN_GET_FK_CONSTRAINT_NAME

Returns a handle that can be used in subsequent calls

dbms_metadata.open_get_fk_constraint_name(
object_type IN VARCHAR2,
schema      IN VARCHAR2,
name        IN VARCHAR2,
ref_schema IN VARCHAR2,
ref_name    IN VARCHAR2)
RETURN NUMBER;

CREATE TABLE parent (
testcol NUMBER(3));

ALTER TABLE parent
ADD CONSTRAINT pk_parent
PRIMARY KEY (testcol);

CREATE TABLE child (
testcol NUMBER(3));

ALTER TABLE child
ADD CONSTRAINT fk_child_parent_testcol
FOREIGN KEY (testcol)
REFERENCES parent(testcol);

set serveroutput on

DECLARE
otype VARCHAR2(30) := 'TABLE';
hdl NUMBER;
retval VARCHAR2(100);
BEGIN
hdl := dbms_metadata.open_get_fk_constraint_name(otype, USER, 'CHILD', USER, 'PARENT');
dbms_output.put_line(hdl);
dbms_metadata.set_fk_constraint_col_pair(hdl, 'TESTCOL', 'TESTCOL');
retval := dbms_metadata.get_fk_constraint_name(hdl);
dbms_output.put_line(retval);
dbms_metadata.close(hdl);
END;
/

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
h := dbms_metadata.openw('TABLE');
dbms_output.put_line(h);
dbms_metadata.close(h);
END;
/

PARSE_CONDITION

Parses a check constraint condition on a table and return it as XML

dbms_metadata.parse_condition(
schema IN VARCHAR2,
tab IN VARCHAR2,
length IN NUMBER,
row IN ROWID)
RETURN SYS.XMLTYPE;

SELECT object_id, object_name
FROM user_objects
WHERE object_type = 'TABLE';

SELECT rowid
FROM sys.cdef$
WHERE obj# = 73778;

set serveroutput on

DECLARE
retxml XMLType;
BEGIN
retxml := dbms_metadata.parse_condition(USER, 'SERVERS', 60, 'AAAAAdAABAAAS6lAAM');
END;
/

PARSE_DEFAULT

Parses the default value of a virtual column
(which contains an arithmetic expression for a functional index) and return it as XML

dbms_metadata.parse_default(
schema IN VARCHAR2,
tab IN VARCHAR2,
length IN NUMBER,
row IN ROWID)
RETURN SYS.XMLTYPE;

CREATE TABLE virtual (
sal NUMBER(3),
bonus NUMBER(3),
total AS (sal+bonus));

SELECT object_id
FROM user_objects
WHERE object_name = 'VIRTUAL';

SELECT rowid, col#, name
FROM sys.col$
WHERE obj# = 74021;

set serveroutput on

DECLARE
retxml XMLType;
BEGIN
retxml := dbms_metadata.parse_default(USER, 'VIRTUAL', 60, 'AAAAACAABAAAV2dAAH');
END;
/

-- need to do an activity that creates a handle first

PARSE_QUERY

Parses a query stored in a long column (e.g., view query) and return it as XML

dbms_metadata.parse_query(
schema IN VARCHAR2,
length IN NUMBER,
tab    IN VARCHAR2,
col    IN VARCHAR2,
row    IN ROWID)
RETURN SYS.XMLTYPE;

TBD

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;

Overload 2

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

conn / as sysdba

SELECT COUNT(*)
FROM dba_objects_ae
WHERE object_name = 'SERVERS' and owner = 'SCOTT';

CREATE OR REPLACE PROCEDURE put_table(
tname IN VARCHAR2, fschema IN VARCHAR2, tschema IN VARCHAR2) AUTHID CURRENT_USER IS
dmoh   NUMBER; -- OPENW handle
th1    NUMBER; -- ADD_TRANSFORM for MODIFY handle
th2    NUMBER; -- ADD_TRANSFORM for DDL handle
tabxml CLOB;   -- table XML
errors sys.ku$_SubmitResults := sys.ku$_SubmitResults();
err_t sys.ku$_SubmitResult;
retval BOOLEAN;
BEGIN
tabxml := dbms_metadata.get_xml('TABLE', tname, 'UWCLASS');

-- specify the object type using OPENW
dmoh := dbms_metadata.openw('TABLE');

-- add the MODIFY transform
th1 := dbms_metadata.add_transform(dmoh, 'MODIFY');

-- specify a schema remap transform
dbms_metadata.set_remap_param(th1, 'REMAP_SCHEMA', fschema, tschema);

-- sanction the DDL transform
th2 := dbms_metadata.add_transform(dmoh, 'DDL');

-- recreates the table
retval := dbms_metadata.put(dmoh, tabxml, 0, errors);

dbms_metadata.close(dmoh);
IF NOT retval THEN
    -- display errors if any
    FOR i IN errors.FIRST..errors.LAST LOOP
      err_t := errors(i);
      FOR j IN err_t.errorLines.FIRST .. err_t.errorLines.LAST LOOP
        dbms_output.put_line(err_t.errorLines(j).errorText);
      END LOOP;
    END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Metadata XML Not Available');
END put_table;
/

set serveroutput on

exec put_table('SERVERS' ,'UWCLASS', 'SCOTT');

SELECT COUNT(*)
FROM dba_objects_ae
WHERE object_name = 'SERVERS'
AND owner = 'SCOTT';

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 (new 11.2.0.1 parameters and overload)

Set the internal debug switch. args2 is unused
Overload 1

dbms_metadata.set_debug(on_off IN BOOLEAN, args2 IN BOOLEAN DEFAULT TRUE);

TBD

Flag definitions in prvtkupc.sql
Overload 2

dbms_metadata.set_debug(debug_flags IN BINARY_INTEGER);

TBD

SET_FILTER

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
h := dbms_metadata.open('TABLE');
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_FK_CONSTRAINT_COL_PAIR

After calling OPEN_GET_FK_CONSTRAINT_NAME, the program calls this for each pair of corresponding columns. The order of calls defines the order of columns in the constraint

dbms_metadata.set_fk_constraint_col_pair(
handle IN NUMBER, -- handle returned by OPEN_GET_FK_CONSTRAINT_NAME
src_col IN VARCHAR2, -- name of column in base table
tgt_col IN VARCHAR2); -- name of corresponding column in ref table

See OPEN_GET_FK_CONSTRAINT_NAME Demo Above

SET_PARAMETER

Specifies a parameter values that affect the operation

Can not find a list of possible parameters.

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

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

Transform Name Parameters

Transform	Applies To	Description
BODY	PACKAGE TYPE	If TRUE, omit the package body. Defaults to TRUE.
CONSTRAINTS	TABLE	If TRUE, omit all non-referential table constraints. Defaults to TRUE.
CONSTRAINTS_AS_ALTER	TABLE	If TRUE, omit table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements. If FALSE, specify table constraints as part of the CREATE TABLE statement. Defaults to FALSE. Requires that CONSTRAINTS be TRUE.
DEFAULT	ALL	ICalling SET_TRANSFORM_PARAM with this parameter set to TRUE has the effect of resetting all parameters for the transform to their default values. Setting this FALSE has no effect. There is no default.
FORCE	VIEW	If TRUE, use the FORCE keyword in the CREATE VIEW statement. Defaults to TRUE.
INHERIT	ALL	If TRUE, inherits session-level parameters. Defaults to FALSE. If an application calls ADD_TRANSFORM to add the DDL transform, then by default the only transform parameters that apply are those explicitly set for that transform handle. This has no effect if the transform handle is the session transform handle.
INSERT	OUTLINE	If TRUE, emit the INSERT statements into the OL$ dictionary tables that will create the outline and its hints. If FALSE, emit a CREATE OUTLINE statement. Defaults to FALSE. Note: This object type is being deprecated.
OID	TABLE TYPE	If TRUE, emit the OID clause for object tables. Defaults to FALSE.
PARTITIONING	INDEX TABLE	Omit partitioning clause: Defaults to TRUE
PCTSPACE	CLUSTER INDEX TABLE TABLESPACE	A number representing the percentage by which space allocation for the object type is to be modified. The value is the number of one-hundreths of the current allocation. For example, 100 means 100%. If the object type is TABLESPACE, the following size values are affected: in file specifications, the value of SIZE MINIMUM EXTENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE For other object types, INITIAL and NEXT are affected.
PRETTY	ALL	Format the output with indentation and line feeds. Defaults to TRUE
REF_CONSTRAINTS	TABLE	If TRUE, emit all referential constraints (foreign keys). Defaults to TRUE.
REUSE	TABLESPACE	If TRUE, include the REUSE parameter for datafiles in a tablespace to indicate that existing files can be reused. Defaults to FALSE.
REVOKE_FROM	ROLE	The name of a user from whom the role must be revoked. If this is a non-null string and if the CREATE ROLE statement grants you the role, a REVOKE statement is emitted after the CREATE ROLE. Note: When you issue a CREATE ROLE statement, Oracle may grant you the role. You can use this transform parameter to undo the grant. Defaults to null string.
SEGMENT_ATTRIBUTES	CLUSTER CONSTRAINT INDEX TABLE TABLESPACE	If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.
SIZE_BYTE_KEYWORD	TABLE	If TRUE, emit the BYTE keyword as part of the size specification of CHAR and VARCHAR2 columns that use byte semantics. If FALSE, omit the keyword. Defaults to FALSE.
SPECIFICATION	PACKAGE TYPE	If TRUE, emit the package specification. Defaults to TRUE.
SQLTERMINATOR	ALL	Append a SQL terminator ";" or "/" to each DDL statement. Defaults to FALSE
STORAGE	CLUSTER CONSTRAINT INDEX TABLE	If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
TABLESPACE	CLUSTER CONSTRAINT INDEX TABLE	If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.

set long 2000000
set pagesize 0

SELECT dbms_metadata.get_ddl('TABLE', 'SERVERS')
FROM dual;

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

SELECT dbms_metadata.get_ddl('TABLE', 'SERVERS')
FROM dual;

-- omit the segment attributes clause
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);

SELECT dbms_metadata.get_ddl('TABLE', 'SERVERS')
FROM dual;

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

SELECT dbms_metadata.get_ddl('TABLE', 'SERVERS')
FROM dual;

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_VAT_TABLE_NAME (new 11.2.0.3)

Undocumented

dbms_metadata.set_vat_table_name(
schema IN VARCHAR2,
vname IN VARCHAR2,
tname IN VARCHAR2);

See GET_VAT_TABLE_NAME above

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

-- set up handles to be used for setup and fetching metadata information. handles are used to keep track of the different objects (DDL) referenced 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);

    -- created DDL instead of peforming an XML comparison
    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;

    -- compare the two DDL statements and output any 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_METADATA_BUILD
DBMS_METADATA_DIFF
DBMS_METADATA_UTIL
DBMS_PREPROCESSOR

Morgan's Library Page Footer

This site is maintained by Dan Morgan. Last Updated: