Oracle DBMS_METADATA
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose This package provides an API for fetching metadata from the database dictionary as XML or creation DDL and facilitiates submitting the XML to recreate the objects.
AUTHID CURRENT_USER
Constants
Name Data Type Value
SESSION_TRANSFORM BINARY_INTEGER -1
SESSION_HANDLE BINARY_INTEGER -1
 
MAX_PROCOBJ_RETLEN BINARY_INTEGER 32767
 
NEWBLOCK_CONTINUE NUMBER 0
NEWBLOCK_BEGIN NUMBER 1
NEWBLOCK_APPEND NUMBER -2
 
MARKER_PRE_SYSTEM NUMBER 1
MARKER_PRE_SCHEMA NUMBER 2
MARKER_PRE_INSTANCE NUMBER 3
MARKER_POST_SYSTEM NUMBER 4
MARKER_POST_SCHEMA NUMBER 5
MARKER_EARLY_POST_INSTANCE NUMBER 6
MARKER_POST_INSTANCE NUMBER 7
MARKER_FINAL_POST_INSTANCE NUMBER 7
MARKER_NORMAL_POST_INSTANCE NUMBER 8
MARKER_STATISTICS NUMBER 10
Data Types TYPE offset IS RECORD (
pos                 PLS_INTEGER,
len                 PLS_INTEGER,
grantor             VARCHAR2(30),
bind_pattern        VARCHAR2(30),
alt_connect_type    VARCHAR2(4),
has_tstz_cols       VARCHAR2(30),
has_virtual_columns VARCHAR2(30));

TYPE objddl IS TABLE OF offset INDEX BY BINARY_INTEGER;

TYPE T_VAR_COLL IS TABLE OF VARCHAR2(60) INDEX BY BINARY_INTEGER;

TYPE multiobjects IS TABLE OF objddl INDEX BY BINARY_INTEGER;
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_METADATA'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_METADATA';

-- this query returns 279 objects
Documented Yes
Exceptions
Error Code Reason
31600 invalid_argval: Invalid argument
31601 invalid_operation: The function was called after the first call to FETCH_xxx
31602 inconsistent_args: The parameter value is inconsistent with another value specified
31603 object_not_found: The specified object was not found in the database
31604 invalid_object_param: Specified parameter value is not valid for this object type
31607 inconsistent_operation: Either FETCH_XML was called when the DDL transform was specified, or FETCH_DDL was called when the DDL transform was omitted
31608 object_not_found2: The specified object was not found in the database
31609 stylesheet_load_error: Installation script initmeta.sql failed to load the named file from the file system directory into the database
31642 sql_error: Untrapped internal DBMS_METADATA error
39128 dbmsjava_error: Unexpected DBMS_JAVA error
First Available 9.0.1
Object Type Strings
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 XS_USER
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
Security Model Owned by SYS with EXECUTE granted to APEX_040200 and PUBLIC

Execute is granted to PUBLIC for reverse engineering objects not owned by the connected user's schema you must be what Oracle calls a "privileged user." Privileged user means that the user is either SYS or has been granted the SELECT_CATALOG_ROLE role.
Source {ORACLE_HOME}/rdbms/admin/dbmsmeta.sql
Subprograms
 
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;
TBD
 
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;
TBD
 
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;
TBD
 
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
 
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;
/
 
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.2.0.3.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
 
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

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
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_VAT_OBJNUMS (new 12.1)
Table function to return object numbers of generated template tables for views_as_tables dbms_metadata.fetch_vat_objnums RETURN sys.ku$_ObjNumSet PIPELINED;
DECLARE
 h NUMBER;
 x sys.ku$_ObjNumSet;
BEGIN
  h := dbms_metadata.open('TABLE');
  SELECT dbms_metadata.fetch_vat_objnums
  INTO x
  FROM dual;
  dbms_metadata.close(h);
END;
/
 
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
  h := dbms_metadata.open('TABLE')
  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 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;
SELECT obj#
FROM edition$;

SELECT dbms_metadata.get_edition_id
FROM dual;
 
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;
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_PARTN (new 12.1)
Undocumented dbms_metadata.get_partn(
partype  IN NUMBER,
bobj_num IN NUMBER,
part_num IN NUMBER)
RETURN NUMBER
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 sys@pdbdev 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;
conn sys@pdbdev as sysdba

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
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;
conn sys@pdbdev as sysdba

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;
conn sys@pdbdev as sysdba

-- 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
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
 
IN_TSNUM_2 (new 12.1)
Rreturns 1 if OBJ# is in the selected set dbms_metadata.in_tsnum_2(
TS_SET   IN NUMBER,
OBJ_NUM  IN NUMBER,
TS_NUM   IN NUMBER,
PROPERTY IN NUMBER)
RETURN NUMBER DETERMINISTIC;
TBD
 
IS_ATTR_VALID_ON_10
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
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;
/
 
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;
/
 
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;
conn sys@pdbdev as sysdba

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;
/
 
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;
conn uwclass/uwclass@pdbdev

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;
conn uwclass/uwclass@pdbdev

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;
TBD
Overload 2 dbms_metadata.put(
handle   IN     NUMBER,
document IN     CLOB,
flags    IN     NUMBER,
results  IN OUT NOCOPY sys.ku$_SubmitResults)
RETURN BOOLEAN;
conn sys@pdbdev 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
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 which is internal to Oracle not part of the distro
Overload 2
dbms_metadata.set_debug(debug_flags IN BINARY_INTEGER);
exec dbms_metadata.set_debug(0);
 
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);
conn sys@pdbdev as sysdba

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.
conn uwclass/uwclass@pdbdev

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
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_DPBUILD
DBMS_METADATA_HACK
DBMS_METADATA_INT
DBMS_METADATA_UTIL
DBMS_PREPROCESSOR
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved