Oracle DBMS_METADATA Built-In Package
Versions 9.0 - 19.3

Security Advisory
There are a lot of ways to steal intellectual property. Oracle, in the interest of backward compatibility, unfortunately makes it too easy: Even for a user with no escalated privileges.

This package provides an API for fetching metadata from the database dictionary as XML or creation DDL and making possible to easily recreate the objects.

Oracle has granted EXECUTE on this package to PUBLIC and believe that is unnecessary and puts intellectual property at risk. We would strongly prefer that Oracle remove all abilities to obtain from a database fully functional DDL without an independent intentionally escalated privilege. What the ALL_SOURCE view does to compromise PL/SQL this package does for essentially anything else in a user's schema.
 
Recommended Security Rules

 NEVER
  • allow any unprivileged user, without specifically approved permission to gain access to DBMS_METADATA
 WITH GREAT CARE
  • Revoke EXECUTE on DBMS_METADATA from PUBLIC
 CAUTIONS
  • Be sure that you thoroughly test your Oracle Database with your application before rolling out a change in PUBLIC permissions into a production environment.
 
How Oracle Works
Dynamically resolves code enclosed in parentheses First step any time you are dealing with security issues related to an object created by Oracle Corp. is to determine the security environment in which it operates.

col privilege format a12
col grantable format a9

SELECT UNIQUE authid
FROM dba_procedures
WHERE object_name = 'DBMS_METADATA';

AUTHID
------------
CURRENT_USER

SELECT grantee, owner, grantor, type, privilege, grantable
FROM dba_tab_privs
WHERE table_name ='DBMS_METADATA'
ORDER BY 1;

GRANTEE OWNER GRANTOR TYPE    PRIVILEGE GRANTABLE
------- ----- ------- ------- --------- ---------
PUBLIC  SYS   SYS     PACKAGE EXECUTE    NO

What you will find with DBMS_METADATA is that in Oracle's version of security any user, even one with no privilege other than CREATE SESSION should be able to reverse engineer any object that they can access. Let's see what that means in the practical sense.

To do this we are going to create a new user "NOPRIVS" and, as the DBA, create a stored procedure in the users's schema.

SQL> CREATE USER noprivs IDENTIFIED BY noprivs;

User created.

SQL> GRANT create session TO noprivs;

Grant succeeded.

SQL> CREATE PROCEDURE noprivs.testproc AUTHID DEFINER IS
  2  BEGIN
  3    dbms_output.put_line('Stop Looking At My Intellectual Property');
  4* END testproc;
SQL> /

Procedure created.

Now we will connect as the user NOPRIVS, with the least possible privileges, and see what that user can do.

SQL> conn noprivs/noprivs
Connected.

SQL> SELECT dbms_metadata.get_ddl('PROCEDURE','TESTPROC') FROM dual;

DBMS_METADATA.GET_DDL('PROCEDURE','TESTPROC')
--------------------------------------------------------------------------------
CREATE OR REPLACE EDITIONABLE PROCEDURE "NOPRIVS"."TESTPROC" AUTHID DEFINER
IS
BEGIN
dbms_output.put_line('Stop Looking At My Intellectual Property');
END testproc;

Clearly, NOPRIVS can walk away with the original DDL statement.

So, we respectfully disagree with Oracle on this. Our user, NOPRIVS, shouldn't be able to walk away with DDL source code for anything. We understand the argument that this user "owns" the schema but while that is technically true it is equally true that a lot of applications are designed to give users access using the application schema. And, while that is absolutely a terrible practice, we have to be honest about the fact that it is also a common practice. For that reason, we recommend locking down the DBMS_METADATA package.

When you look at the number of dependencies below, you may be concerned that revoking EXECUTE from PUBLIC is going to break hundreds of objects (in 18.3 that would be 264 objects to be exact). But it turns out that only three of those objects are not owned by SYS so they are the only ones that need grants before the revoke takes place with the exception of any of your application code.

SQL> SELECT owner, name, type
  2  FROM dba_dependencies
  3  WHERE referenced_name = 'DBMS_METADATA'
  4  AND referenced_owner <> 'SYS'
  5  ORDER BY 1;

OWNER              NAME                   TYPE
------------------ ---------------------- -------------------
GSMADMIN_INTERNAL  EXCHANGE               PACKAGE BODY
MDSYS              SDO_RDF_INTERNAL       PACKAGE BODY
XDB                DBMS_XMLSTORAGE_MANAGE PACKAGE BODY

That doesn't look so hard.

SQL> GRANT execute ON dbms_metadata TO gsmadmin_internal;
SQL> GRANT execute ON dbms_metadata TO mdsys;
SQL> GRANT execute ON dbms_metadata TO xdb;

We have said it elsewhere but it bears repeating. Never make any change to the Oracle Database without first testing it thoroughly in a preproduction environment. You can also open SR at MyOracleSupport and specifically request guidance after you have tested in your preprod environment and before you roll your changes out to prod.
 
DBMS_METADATA Package Information
AUTHID CURRENT_USER
Constants
Name Data Type Value
Session
SESSION_TRANSFORM BINARY_INTEGER -1
SESSION_HANDLE BINARY_INTEGER -1
PUBLIC
MAX_PROCOBJ_RETLEN BINARY_INTEGER 32767
New Block
NEWBLOCK_CONTINUE NUMBER 0
NEWBLOCK_BEGIN NUMBER 1
NEWBLOCK_APPEND NUMBER -2
Marker
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
Use for special processing by the worker for those PL/SQL object types whose source moves in a table rather than XML
MARKER_PACKAGE_SPEC NUMBER 11
MARKER_PACKAGE_BODY NUMBER 12
MARKER_FUNCTION NUMBER 13
Constant for use with the OBJECT_SEQNUM filter
ROOT_OBJECT NUMBER 0
Flags definition for 'put'
PUT_RAISE_EXCEPTION NUMBER 1
Flags definnitions for SET_TRANSFORM_PARAM - now for DataPump only
TRANSFORM_REPLACE NUMBER 1
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 multiobjects IS TABLE OF objddl INDEX BY BINARY_INTEGER;

TYPE t_var_coll IS TABLE OF VARCHAR2(60) 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';

Returns 329 objects in version 18.3
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 RLS_GROUP
AQ_QUEUE_TABLE RLS_POLICY
AQ_TRANSFORM RMGR_CONSUMER_GROUP
ASSOCIATION RMGR_INTITIAL_CONSUMER_GROUP
AUDIT RMGR_PLAN
AUDIT_OBJ RMGR_PLAN_DIRECTIVE
CLUSTER ROLE
COMMENT ROLE_GRANT
CONSTRAINT (P & U only) ROLLBACK_SEGMENT
CONTEXT SCHEMA_EXPORT
DATABASE_EXPORT SEQUENCE
DB_LINK SYNONYM
DEFAULT_ROLE SYSTEM_GRANT
DIMENSION TABLE
DIRECTORY TABLESPACE
FGA_POLICY TABLESPACE_QUOTA
FUNCTION TABLE_DATA
INDEX_STATISTICS TABLE_EXPORT
INDEX TABLE_STATISTICS
INDEXTYPE TRANSPORTABLE_EXPORT
JAVA_SOURCE TRIGGER
JOB TRUSTED_DB_LINK
LIBRARY TYPE
MATERIALIZED_VIEW TYPE_BODY
MATERIALIZED_VIEW_LOG TYPE_SPEC
OBJECT_GRANT USER
OPERATOR VIEW
PACKAGE XMLSCHEMA
PACKAGE_BODY XS_ACL (Real Application Security: RAS)
PACKAGE_SPEC XS_ACL_PARAM (RAS)
PROCEDURE XS_DATA_SECURITY (RAS)
PROFILE XS_NAMESPACE (RAS)
PROXY XS_ROLE (RAS)
REFRESH_GROUP XS_ROLESET (RAS)
REF_CONSTRAINT XS_ROLE_GRANT (RAS)
RESOURCE_COST XS_SECURITY_CLASS
RLS_CONTEXT XS_USER (RAS)
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 PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsmeta.sql
Subprograms
ADD_TRANSFORM GET_DOMIDX_METADATA IS_ATTR_VALID_ON_10
CHECK_CONSTRAINT GET_DPSTRM_MD IS_XDB_TRANS
CHECK_MATCH_TEMPLATE GET_EDITION NETWORK_CALLOUTS
CHECK_MATCH_TEMPLATE_LOB GET_EDITION_ID NETWORK_FETCH_CLOB
CHECK_MATCH_TEMPLATE_PAR GET_FK_CONSTRAINT_NAME NETWORK_FETCH_ERRORS
CHECK_TYPE GET_GRANTED_DDL NETWORK_FETCH_PARSE
CLOSE GET_GRANTED_XML NETWORK_OPEN
CONVERT GET_HASHCODE NET_SET_DEBUG
CONVERT_TO_CANONICAL GET_INDEX_INTCOL OKTOEXP_2NDARY_TABLE
FETCH_CLOB GET_INDPART_TS OPEN
FETCH_DDL GET_JAVA_METADATA OPENW
FETCH_DDL_TEXT GET_PARTN OPEN_GET_FK_CONSTRAINT_NAME
FETCH_OBJNUMS GET_PLSQL_OPTIMIZE_LEVEL PARSE_CONDITION
FETCH_OBJNUMS_NAMES GET_PLUGTS_BLK PARSE_DEFAULT
FETCH_SORTED_OBJNUMS GET_PREPOST_TABLE_ACT PARSE_QUERY
FETCH_VAT_OBJNUMS GET_PROCOBJ PATCH_TYPEID
FETCH_XML GET_PROCOBJ_GRANT PUT
FETCH_XML_CLOB GET_QUERY SET_COUNT
FREE_CONTEXT_ENTRY GET_STAT_COLNAME SET_DEBUG
GET_ACTION_INSTANCE GET_STAT_INDNAME SET_FILTER
GET_ACTION_SCHEMA GET_SXML SET_FK_CONSTRAINT_COL_PAIR
GET_ACTION_SYS GET_SXML_DDL SET_PARAMETER
GET_CANONICAL_VSN GET_SYSPRIVS SET_PARSE_ITEM
GET_CHECK_CONSTRAINT_NAME GET_VERSION SET_REMAP_PARAM
GET_DDL GET_XML SET_TRANSFORM_PARAM
GET_DEPENDENT_DDL IN_TSNUM SET_XMLFORMAT
GET_DEPENDENT_SXML IN_TSNUM_2 TRANSFORM_STRM
GET_DEPENDENT_XML IS_ACTIVE_REGISTRATION Demo
 
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_CONSTRAINT
Returns 1 if the identified check constraint exists: Otherwise NULL

At least that's the theory. Here's what Oracle's file comments state:
"-- CHECK_CONSTRAINT - Check whether constraint exists or not
--
-- PARAMETER :
-- obj_num - obj# of index
--
-- Returns 1 if constraint exists"

The above statement is nonsensical ... because an index is not a constraint and a constraint doesn't have an object number as demonstrated, at right. What it appears it is looking for is constraints enforced by unique indexes.		 dbms_metadata.check_constraint(obj_num IN NUMBER)
RETURN NUMBER;
SELECT table_name, constraint_name
FROM dba_constraints
WHERE owner = 'SYS'
AND table_name LIKE '%$'
AND constraint_type = 'P'
AND rownum < 11
ORDER BY 1;

TABLE_NAME           CONSTRAINT_NAME
-------------------- --------------------
COL_GROUP_USAGE$     PK_COL_GROUP_USAGE$
DAM_CLEANUP_EVENTS$  SYS_C00776
DAM_CLEANUP_JOBS$    SYS_C00772
DAM_PARAM_TAB$       SYS_C00761
RADM_PE$             SYS_C00711
SQLLOG$              SQLLOG$_PKEY
SQLOBJ$              SQLOBJ$_PKEY
SVCOBJ$              IDX1_SVCOBJ$
SVCOBJ_ACCESS$ IDX1 _SVCOBJ_ACCESS$
TSDP_SENSITIVE_DATA$ TSDP_SENSITIVE_DATA$PK

SQL> SELECT con#
  2  FROM con$
  3  WHERE name = 'TSDP_SENSITIVE_DATA$PK';

      CON#
----------
       780

SELECT dbms_metadata.check_constraint(780)
FROM dual;

DBMS_METADATA.CHECK_CONSTRAINT(780)
-----------------------------------
                                  1
 
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 dbms_metadata.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;
SQL> SELECT dbms_metadata.convert_to_canonical('12.2.0.1.0')
  2  FROM dual;

DBMS_METADATA.CONVERT_TO_CANONICAL('12.2.0.1.0')
------------------------------------------------
12.02.00.01.00
 
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(600001)
FROM dual;

exec dbms_metadata.close(600001);
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
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(
tag        IN VARCHAR2,
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(
tag        IN VARCHAR2,
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_all_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_all_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_INDPART_TS
Returns the tablespace number for the tablespace of a partitioned index component from KU$_INDEX_VIEW dbms_metadata.get_indpart_ts(obj_num IN NUMBER)
RETURN NUMBER;
SQL> SELECT object_id
  2  FROM dba_objects
  3  WHERE object_name = (
  4  SELECT index_name
  5  FROM dba_ind_partitions
  6  WHERE rownum = 1);

OBJECT_ID
----------
      8610
     18124
     93318
     93632
     93631
     93456

SQL> SELECT dbms_metadata.get_indpart_ts(8610)
  2  FROM dual;

DBMS_METADATA.GET_INDPART_TS(8610)
----------------------------------
                                 1
SQL> SELECT name
   2 FROM ts$
   3 WHERE ts# = 1;

NAME
------------------------------
SYSAUX
 
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
Undocumented dbms_metadata.get_partn(
partype  IN NUMBER,
bobj_num IN NUMBER,
part_num IN NUMBER)
RETURN NUMBER;
TBD
 
GET_PLSQL_OPTIMIZE_LEVEL
GET_PLSQL_OPTIMIZE_LEVEL - get a version specific plsql optimize level pre 11.1 the max was 2, then 3 from 11.1 on dbms_metadata.get_plsql_optimize_level(level IN NUMBER)
RETURN NUMBER;
SELECT dbms_metadata.get_plsql_optimize_level(999)
FROM dual;

-- we suspect this function needs some QA
 
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(
tag        IN VARCHAR2,
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(
tag        IN VARCHAR2,
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  dbms_metadata.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(
tag        IN VARCHAR2,
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2)
RETURN sys.ku$_procobj_lines;
TBD
 
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_all_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_all_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
Returns 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_ACTIVE_REGISTRATION
Checks the current registration in impcalloutreg$ to see if it should be exported based on its beginning and ending RDBMSversions (if present) when compared to the job's target import version (1 if honored, 0 not honored) dbms_metadata.is_active_registration(
beginning_version IN VARCHAR2,
ending_version    IN VARCHAR2)
RETURN NUMBER;
desc impcalloutreg$

SELECT dbms_metadata.is_active_registration('6.0.12', '12.2.0.1')
FROM dual;

SELECT dbms_metadata.is_active_registration('11.2.0.4', '12.2.0.1')
FROM dual;

SELECT dbms_metadata.is_active_registration('0', '99.99.99.99')
FROM dual;

 -- we suspect this function needs some QA
 
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 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_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,
flags            IN NUMBER   DEFAULT 0);

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,
flags            IN NUMBER   DEFAULT 0);
TBD
Overload 3 dbms_metadata.set_transform_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
value            IN NUMBER,
object_type      IN VARCHAR2 DEFAULT NULL,
flags            IN NUMBER   DEFAULT 0);
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
 
TRANSFORM_STRM
Transform stream metata: Intended for use with the CLOB output from GET_DPSTRM_MD dbms_metadata.transform_strm(
indoc     IN            CLOB,
outdoc    IN OUT NOCOPY CLOB,
mdversion IN            VARCHAR2 DEFAULT 'COMPATIBLE');
TBD
 
Demo (copied from 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_all_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