CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_METADATA
Version 11.1
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmsmeta.sql
First Available 9.0.1

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

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

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

TYPE objddl IS TABLE OF offset INDEX BY BINARY_INTEGER;

TYPE multiobjects IS TABLE OF objddl INDEX BY BINARY_INTEGER;
 

Dependencies

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

XMLTYPE

DBMS_PLUGTS  

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

Object Types For OPEN (partial listing)
Type Name Meaning
CONSTRAINT constraints
DATABASE_EXPORT all metadata objects in a database
DB_LINK database links
FGA_POLICY fine-grained audit policies
INDEX_STATISTICS precomputed statistics on indexes
REF_CONSTRAINT referential constraint
RLS_CONTEXT driving contexts for enforcement of fine-grained access-control policies
RMGR_PLAN resource plans
SCHEMA_EXPORT all metadata objects in a schema
TABLE_DATA metadata describing row data for a table, nested table, or partition
TRANSPORTABLE_EXPORT metadata for objects in a transportable tablespace set
TYPE user-defined types
 
ADD_DOCUMENT (new 11g)
Specifies an (S)XML document (as XMLTYPE) to be compared

Overload 1
dbms_metadata.add_document(handle IN NUMBER, document IN sys.XMLType);
Undocumented
Overload 2 dbms_metadata.add_document(handle IN NUMBER, document IN CLOB);
Undocumented
 
ADD_TRANSFORM
When used to retrieve objects, it specifies a transform that FETCH_xxx applies to the XML representation of the retrieved objects. When used to submit objects, specifies a transform that CONVERT or PUT applies to the XML representation of the submitted objects. dbms_metadata.add_transform(
handle      IN NUMBER,
name        IN VARCHAR2,
encoding    IN VARCHAR2 DEFAULT NULL,
object_type IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;
See SET_REMAP_PARAM Demo
 
CHECK_MATCH_TEMPLATE
Check if sub-partitions were created via table's subpartition template clause dbms_metadata.check_match_template(
pobjno IN NUMBER,
spcnt  IN NUMBER)
RETURN NUMBER;
Undocumented
 
CHECK_MATCH_TEMPLATE_LOB
Check if sub-partitions lob were created via table's subpartition template clause dbms_metadata.check_match_template_lob(
pobjno IN NUMBER,
spcnt  IN NUMBER)
RETURN NUMBER;
Undocumented
 
CHECK_MATCH_TEMPLATE_PAR
Check if sub-partitions were created via table's subpartition template clause dbms_metadata.check_match_template_par(
pobjno IN NUMBER,
spcnt  IN NUMBER)
RETURN NUMBER;
Undocumented
 
CHECK_TYPE

For transportable import, check a type's definition and typeid
dbms_metadata.check_type(
schema    IN VARCHAR2,
type_name IN VARCHAR2,
version   IN VARCHAR2,
hashcode  IN VARCHAR2,
typeid    IN VARCHAR2);
Undocumented
 
CLOSE

Invalidates the handle returned by OPEN or OPENW and cleans up the associated state
dbms_metadata.close(handle IN NUMBER);
DECLARE
 h NUMBER;
BEGIN
  SELECT dbms_metadata.open('TABLE')
  INTO h
  FROM DUAL;

  dbms_metadata.close(h);
END;
/
 
COMPARE_ALTER (new 11g)

This function compares the metadata for two objects and returns a set of ALTER statements for making object 1 like object2.
dbms_metadata.compare_alter (
object_type   IN VARCHAR2,
name1         IN VARCHAR2,
name2         IN VARCHAR2,
schema1       IN VARCHAR2 DEFAULT NULL,
schema2       IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
conn uwclass/uwclass

set serveroutput on

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

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

Compares the metadata for two objects and returns an ALTER_XML document
dbms_metadata.compare_alter_xml (
object_type   IN VARCHAR2,
name1         IN VARCHAR2,
name2         IN VARCHAR2,
schema1       IN VARCHAR2 DEFAULT NULL,
schema2       IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
conn / as sysdba

set serveroutput on

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

  dbms_output.put_line(c);
END;
/
 
COMPARE_SXML (new 11g)

The functions compares the metadata for two objects and returns
an sxml difference document
dbms_metadata.compare_sxml (
object_type   IN VARCHAR2,
name1         IN VARCHAR2,
name2         IN VARCHAR2,
schema1       IN VARCHAR2 DEFAULT NULL,
schema2       IN VARCHAR2 DEFAULT NULL,
network_link1 IN VARCHAR2 DEFAULT NULL,
network_link2 IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
conn / as sysdba

set serveroutput on

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

  dbms_output.put_line(c);
END;
/
 
CONVERT (Function: Overload 3 new to 11g)
Transforms an input XML document into creation DDL

Overload 1
dbms_metadata.convert(handle IN NUMBER, document IN sys.XMLType)
RETURN sys.ku$_multi_ddls;
TBD
Overload 2 dbms_metadata.convert(handle IN NUMBER, document IN CLOB)
RETURN sys.ku$_multi_ddls;
TBD
This is an alternate higher-performing but less flexible form of CONVERT that returns only a single (but multi-object) CLOB with a collection providing offsets into this CLOB to locate each individual DDL. Parse items per DDL are NOT returned with this version.

Overload 3
DBMS_METADATA.CONVERT (
handle   IN  NUMBER,
document IN  CLOB,
offsets  OUT NOCOPY multiobjects)
RETURN CLOB;
TBD
Transforms an input XML document into creation DDL

Overload 4
DBMS_METADATA.CONVERT (
handle   IN     NUMBER,
document IN     sys.XMLType,
result   IN OUT NOCOPY CLOB);
TBD
Overload 5 DBMS_METADATA.CONVERT (
handle   IN     NUMBER,
document IN     CLOB,
result   IN OUT NOCOPY CLOB);
TBD
 
CONVERT_TO_CANONICAL (new 11g)
Convert string to canonical form
vv.vv.vv.vv.vv, e.g., '08.01.03.00.00'
dbms_metadata.convert_to_canonical(version IN VARCHAR2) RETURN VARCHAR2
SELECT dbms_metadata.convert_to_canonical('11.1.0.6.0')
FROM DUAL;
 
FETCH_CLOB (overload 3 new to 11g)
Returns the object, transformed or not, as a CLOB

Overload 1
dbms_metadata.fetch_clob(
handle       IN NUMBER,
cache_lob    IN BOOLEAN DEFAULT TRUE,
lob_duration IN PLS_INTEGER DEFAULT DBMS_LOB.SESSION)
RETURN CLOB;
TBD
Returns the object, transformed or not, as a CLOB

Overload 2
dbms_metadata.fetch_clob(
handle IN     NUMBER,
xmldoc IN OUT NOCOPY CLOB);
TBD
Return metadata for object (transformed or not) as a CLOB

Overload 3
dbms_metadata.fetch_clob(
handle IN     NUMBER,
xmldoc IN OUT NOCOPY CLOB,
diffs     OUT BOOLEAN);
TBD
 
FETCH_DDL
Fetch selected DB objects as DDL dbms_metadata.fetch_ddl(handle IN NUMBER) RETURN sys.ku$_ddls;
TBD
 
FETCH_DDL_TEXT
Fetch selected DB objects as DDL in a VARCHAR2 dbms_metadata.fetch_ddl_text(handle  IN  NUMBER, partial OUT NUMBER)
RETURN VARCHAR2;
TBD
 
FETCH_OBJNUMS
Table function to return object numbers. Used to speed up heterogeneous fetch dbms_metadata.fetch_objnums(handle IN NUMBER)
RETURN sys.ku$_ObjNumSet pipelined;
TBD
 
FETCH_SORTED_OBJNUMS
Table function to return nested table of
obj#-order pairs
dbms_metadata.fetch_sorted_objnums(handle IN NUMBER)
RETURN sys.ku$_ObjNumPairList;
TBD
 
FETCH_XML
Fetch selected DB objects as XML docs dbms_metadata.fetch_xml(handle IN NUMBER) RETURN sys.XMLType;
TBD
 
FETCH_XML_CLOB (overload 2 new 11g)
Returns the XML metadata for the objects as a CLOB in an IN OUT NOCOPY parameter

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

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

Overload 2
dbms_metadata.fetch_xml_clob(
handle           IN     NUMBER,
doc              IN OUT NOCOPY CLOB,
parsed_items     IN OUT NOCOPY sys.ku$_parsed_items,
object_type_path    OUT VARCHAR2,
seqno               OUT NUMBER,
procobj_errors      OUT sys.ku$_vcnt
);
TBD
 
FREE_CONTEXT_ENTRY
To be called *ONLY* by the definer's rights pkg. (dbms_metadata_int) error handling. dbms_metadata.free_context_entry(ind IN NUMBER);
TBD
 
GET_ACTION_INSTANCE

Get the export string from call instance_info_exp and instance_extended_info_exp function of package in exppkgact$
dbms_metadata.get_action_instance(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2,
name       IN VARCHAR2,
schema     IN VARCHAR2,
namespace  IN NUMBER,
objtype    IN NUMBER,
prepost    IN NUMBER,
isdba      IN NUMBER)
RETURN sys.ku$_procobj_lines;
TBD
 
GET_ACTION_SCHEMA

Get the export string from call schema_info_exp function of package in exppkgact$
dbms_metadata.get_action_schema(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2,
schema     IN VARCHAR2,
prepost    IN NUMBER,
isdba      IN NUMBER)
RETURN sys.ku$_procobj_lines;
TBD
 
GET_ACTION_SYS

Get the export string from call system_info_exp
dbms_metadata.get_action_sys(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2,
prepost    IN NUMBER)
RETURN sys.ku$_procobj_lines;
TBD
 
GET_CANONICAL_VSN
Convert user's VERSION param to canonical form. (APIs unique to the submit interface) dbms_metadata.get_canonical_vsn(version IN VARCHAR2) RETURN VARCHAR2;
SELECT version, dbms_metadata.get_canonical_vsn(version)
FROM gv$instance;
 
GET_DDL

Fetch DDL for objects
dbms_metadata.get_ddl(
object_type IN VARCHAR2,
name        IN VARCHAR2,
schema      IN VARCHAR2 DEFAULT NULL,
version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
model       IN VARCHAR2 DEFAULT 'ORACLE',
transform   IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
Table
CREATE TABLE test
PCTFREE 0
TABLESPACE uwdata AS
SELECT table_name, tablespace_name
FROM user_tables;

SET LONG 10000

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

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

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

Function
CREATE OR REPLACE FUNCTION whoami RETURN VARCHAR2 IS

BEGIN
   RETURN user;
END whoami;
/

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

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

Fetch DDL for dependent objects (audits, object grants)
dbms_metadata.get_dependent_ddl(
object_type        IN VARCHAR2,
base_object_name   IN VARCHAR2,
base_object_schema IN VARCHAR2 DEFAULT NULL,
version            IN VARCHAR2 DEFAULT 'COMPATIBLE',
model              IN VARCHAR2 DEFAULT 'ORACLE',
transform          IN VARCHAR2 DEFAULT 'DDL',
object_count       IN NUMBER   DEFAULT 10000)
RETURN CLOB;
GRANT select ON servers TO hr;
GRANT select ON servers TO scott;

set long 100000

SELECT dbms_metadata.get_dependent_ddl('OBJECT_GRANT','SERVERS')
FROM DUAL;
 
GET_DEPENDENT_SXML (new 11g)

Return the metadata for objects dependent on a base object as XML
dbms_metadata.get_dependent_sxml(
object_type        IN VARCHAR2,
base_object_name   IN VARCHAR2,
base_object_schema IN VARCHAR2 DEFAULT NULL,
version            IN VARCHAR2 DEFAULT 'COMPATIBLE',
model              IN VARCHAR2 DEFAULT 'ORACLE',
transform          IN VARCHAR2 DEFAULT 'SXML',
object_count       IN NUMBER DEFAULT 10000)
RETURN CLOB;
TBD
 
GET_DEPENDENT_XML

Fetch XML for dependent objects (audits, object grants)
dbms_metadata.get_dependent_xml(
object_type        IN VARCHAR2,
base_object_name   IN VARCHAR2,
base_object_schema IN VARCHAR2 DEFAULT NULL,
version            IN VARCHAR2 DEFAULT 'COMPATIBLE',
model              IN VARCHAR2 DEFAULT 'ORACLE',
transform          IN VARCHAR2 DEFAULT NULL,
object_count       IN NUMBER DEFAULT 10000)
RETURN CLOB;
GRANT all ON servers TO system;

set long 1000000

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

Get PLSQL code from the ODCIIndexGetMetadata
method of a domain index's implementation type
dbms_metadata.get_domidx_metadata(
index_name   IN VARCHAR2,
index_schema IN VARCHAR2,
type_name    IN VARCHAR2,
type_schema  IN VARCHAR2,
flags        IN NUMBER)
RETURN sys.ku$_procobj_lines;
TBD
 
GET_DPSTRM_MD

Get stream metadata for table (for use by DataPump data layer only this is an internal API)
dbms_metadata.get_dpstrm_md (
schema           IN     VARCHAR2,
name             IN     VARCHAR2,
mdversion        IN     VARCHAR2 DEFAULT 'COMPATIBLE',
dpapiversion     IN     NUMBER DEFAULT 3,
doc              IN OUT NOCOPY CLOB,
network_link     IN     VARCHAR2 DEFAULT NULL,
force_lob_be     IN     BOOLEAN DEFAULT FALSE,
force_no_encrypt IN     BOOLEAN DEFAULT FALSE);
TBD
 
GET_EDITION (new 11g)
Returns the edition of interest for the current MDAPI function. (this is either specified as the 'edition' filter, or the session current edition. dbms_metadata.get_edition RETURN VARCHAR2;
SELECT dbms_metadata.get_edition FROM DUAL;
 
GET_EDITION_ID (new 11g)
Returns the edition ID of interest for the current MDAPI context. dbms_metadata.get_edition_id RETURN NUMBER;
TBD
 
GET_GRANTED_DDL

Fetch granted objects (system grants, role grants) DDL
dbms_metadata.get_granted_ddl(
object_type  IN VARCHAR2,
grantee      IN VARCHAR2 DEFAULT NULL,
version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
model        IN VARCHAR2 DEFAULT 'ORACLE',
transform    IN VARCHAR2 DEFAULT 'DDL',
object_count IN NUMBER   DEFAULT 10000)
RETURN CLOB;
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT', 'UWCLASS')
FROM DUAL;
 
GET_GRANTED_XML

Fetch granted objects (system grants, role grants) in XML format
dbms_metadata.get_granted_xml(
object_type  IN VARCHAR2,
grantee      IN VARCHAR2 DEFAULT NULL,
version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
model        IN VARCHAR2 DEFAULT 'ORACLE',
transform    IN VARCHAR2 DEFAULT NULL,
object_count IN NUMBER   DEFAULT 10000)
RETURN CLOB;
set long 1000000

SELECT dbms_metadata.get_granted_xml('SYSTEM_GRANT', 'UWCLASS')
FROM DUAL;
 
GET_INDEX_INTCOL (new in 11.1.0.7)

Get intcol# in table of column on which an index is defined that needs special handling for xmltype cols
dbms_metadata.get_index_intcol(
obj_num    IN NUMBER,
intcol_num IN NUMBER)
RETURN NUMBER;
TBD
 
GET_JAVA_METADATA

Return java info from DBMS_JAVA.EXPORT
dbms_metadata.get_java_metadata(
java_name   IN VARCHAR2,
java_schema IN VARCHAR2,
type_num    IN NUMBER)
RETURN sys.ku$_java_t;
TBD
 
GET_PLUGTS_BLK
Get the export string from dbms_plugts dbms_metadata.get_plugts_blk(blockid IN NUMBER)
RETURN sys.ku$_procobj_lines;
TBD
 
GET_PREPOST_TABLE_ACT
Get the export string for pre-table action from call dbms_export_extension.pre_table

1 = pre
dbms_metadata.get_prepost_table_act(
prepost IN NUMBER,
schema  IN VARCHAR2,
tname   IN VARCHAR2)
RETURN sys.ku$_taction_list_t;
TBD
Get the export string for post-table action from call dbms_export_extension.post_table

2 = post
dbms_metadata.get_prepost_table_act(
prepost IN NUMBER,
schema  IN VARCHAR2,
tname   IN VARCHAR2)
RETURN sys.ku$_taction_list_t;
TBD
 
GET_PROCOBJ

Get the export string from create_exp or audit_exp function of package in exppkobj$
dbms_metadata.get_procobj(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2,
objid      IN NUMBER,
isdba      IN PLS_INTEGER)
RETURN sys.ku$_procobj_lines;
TBD
 
GET_PROCOBJ_GRANT

Get the export string from call grant_exp function of package in exppkobj$
dbms_metadata.get_procobj_grant(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2,
objid      IN NUMBER,
isdba      IN PLS_INTEGER)
RETURN sys.ku$_procobj_lines;
TBD
 
GET_QUERY

Returns the text of the queries that are used by FETCH_xxx. This function assists in debugging
dbms_metadata.get_query(handle IN NUMBER) RETURN VARCHAR2;
set long 1000000
set serveroutput on
set pagesize 0
set linesize 1000
set trim on
set trimspool on

spool c:\temp\demo.txt

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

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

  dbms_output.put_line(s);

  dbms_metadata.close(n);
END;
/

spool off

-- replace :SCHEMA1 with 'UWCLASS'

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

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

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

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

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

Returns a column name for restoring statistics
dbms_metadata.get_index_intcol(
owner_name   IN VARCHAR2,
table_name   IN VARCHAR2,
default_val  IN LONG,     -- value or null from col$.default$
attr_colname IN VARCHAR2, -- value or null from attrcol$.name
nested_table IN NUMBER)   -- 1 if nested table, 0 otherwise
RETURN VARCHAR2;
TBD
 
GET_STAT_INDNAME (new in 11.1.0.7)

Returns an index_owner and index name for restoring statistics
dbms_metadata.get_index_intcol(
table_owner IN  VARCHAR2,
table_name  IN  VARCHAR2,
col_names   IN  sys.t_var_coll   -- varray of columns that index is on
col_count   IN  NUMBER,          -- number of columns that index is on
ind_owner   OUT VARCHAR2,        -- index owner
ind_name    OUT VARCHAR2);       -- index name
TBD
 
GET_SXML (new 11.1.0.6)

Returns the metadata for a single object as SXML. This interface is meant for casual browsing (e.g., from SQLPlus) vs. the programmatic OPEN / FETCH / CLOSE interfaces
dbms_metadata.get_sxml(
object_type IN VARCHAR2,
name        IN VARCHAR2,
schema      IN VARCHAR2 DEFAULT NULL,
version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
model       IN VARCHAR2 DEFAULT 'ORACLE',
transform   IN VARCHAR2 DEFAULT 'SXML')
RETURN CLOB;
conn / as sysdba

set serveroutput on

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

  dbms_output.put_line(c);
END;
/
 
GET_SYSPRIVS

Get the export string from call grant_sysprivs_exp and audit_sysprivs_exp function of a package in exppkgobj$
dbms_metadata.get_sysprivs(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2)
RETURN sys.ku$_procobj_lines;
TBD
 
GET_SYSPRIVS

Get the export string from call grant_sysprivs_exp and audit_sysprivs_exp function of a package in exppkgobj$
dbms_metadata.get_sysprivs(
package    IN VARCHAR2,
pkg_schema IN VARCHAR2,
function   IN VARCHAR2)
RETURN sys.ku$_procobj_lines;
TBD
 
GET_VERSION (new 11g)
Returns the version of interest for the current MDAPI context. Comes from the version parameter in open dbms_metadata.get_version RETURN VARCHAR2;
SELECT dbms_metadata.get_version FROM DUAL;
 
GET_XML

Fetch XML for objects
dbms_metadata.get_xml(
object_type IN VARCHAR2,
name        IN VARCHAR2,
schema      IN VARCHAR2 DEFAULT NULL,
version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
model       IN VARCHAR2 DEFAULT 'ORACLE',
transform   IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;
Table
CREATE TABLE test
PCTFREE 0
TABLESPACE uwdata AS
SELECT table_name, tablespace_name
FROM user_tables;

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

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

SET LONG 4000

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

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

SELECT dbms_metadata.get_xml('FUNCTION', 'WHOAMI')
FROM DUAL;
IS_OBJECT_XDB_GENERATED (new 11g)
Check for whether object is generated by an dbms_xmlschema.registerschema call. Used by export for triggers and RLS policies. dbms_metadata.is_object_xdb_generated(
object_name  IN VARCHAR2,
schema       IN VARCHAR2,
base_obj_num IN NUMBER)
Undocumented
 
NETWORK_CALLOUTS
Execute callouts (used by network mode) dbms_metadata.network_callouts(handle IN NUMBER);
TBD
 
NETWORK_FETCH_CLOB

Fetch selected DB objects in a VARCHAR2 (used by network mode)
dbms_metadata.network_fetch_clob(
handle       IN  NUMBER,
do_xsl_parse IN  NUMBER,
partial      OUT NUMBER,
parse_delim  OUT VARCHAR2,
do_callout   OUT NUMBER,
have_errors  OUT NUMBER)
RETURN VARCHAR2;
TBD
 
NETWORK_FETCH_ERRORS

Serializes a ku$_vcnt into a VARCHAR2 for network operations. Returns a delimited series of error string
dbms_metadata.network_fetch_errors(
handle  IN  NUMBER,
cnt     OUT NUMBER,
partial OUT NUMBER,
seqno   OUT NUMBER,
path    OUT VARCHAR2)
RETURN VARCHAR2;
TBD
 
NETWORK_FETCH_PARSE

Return serialized parse items in a VARCHAR2 used by network mode)
dbms_metadata.network_fetch_parse(
handle  IN  NUMBER,
cnt     OUT NUMBER,
partial OUT NUMBER,
seqno   OUT NUMBER,
path    OUT VARCHAR2)
RETURN VARCHAR2;
TBD
 
NETWORK_OPEN

Do OPEN over network, negotiate protocol version
dbms_metadata.network_open(
object_type      IN  VARCHAR2,
version          IN  VARCHAR2 DEFAULT 'COMPATIBLE',
model            IN  VARCHAR2 DEFAULT 'ORACLE',
client_version   IN  NUMBER,
protocol_version OUT NUMBER)
RETURN NUMBER;
TBD
 
NET_SET_DEBUG
Set the internal debug switch on remote node dbms_metadata.net_set_debug(on_off  IN BOOLEAN);
TBD
 
OKTOEXP_2NDARY_TABLE
Should a secondary object of a domain index be exported? dbms_metadata.oktoexp_2ndary_table(tab_obj_num IN NUMBER)
RETURN PLS_INTEGER;
TBD
 
OPEN

Specifies the type of object to be retrieved, the version of its metadata, and the object model
dbms_metadata.open(
object_type  IN VARCHAR2,
version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
model        IN VARCHAR2 DEFAULT 'ORACLE',
network_link IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;
set serveroutput on

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

  dbms_output.put_line(h);

  dbms_metadata.close(h);
END;
/
 
OPENC (new 11g)
Establishes a 'compare' context and specifies the object type for comparing to (S)XML documents dbms_metadata.openc(object_type IN VARCHAR2) RETURN NUMBER;
Undocumented
 
OPENW

Specifies the type of object to be submitted and the object model. The return value is an opaque context handle
dbms_metadata.openw(
object_type IN VARCHAR2,
version     IN VARCHAR2 DEFAULT 'COMPATIBLE',
model       IN VARCHAR2 DEFAULT 'ORACLE')
RETURN NUMBER;
set serveroutput on

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

  dbms_output.put_line(h);

  dbms_metadata.close(h);
END;
/
 
PATCH_TYPEID

For transportable import, modify a type's typeid
dbms_metadata.patch_typeid(
schema   IN VARCHAR2,
name     IN VARCHAR2,
typeid   IN VARCHAR2,
hashcode IN VARCHAR2);
TBD
 
PUT

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

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

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

The maximum number of objects to be retrieved in a single FETCH_xxx call
dbms_metadata.set_count(
handle           IN NUMBER,
value            IN NUMBER,
object_type_path IN VARCHAR2 DEFAULT NULL);
DECLARE
 h NUMBER;
BEGIN
  SELECT dbms_metadata.open('TABLE')
  INTO h
  FROM DUAL;

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

  dbms_metadata.close(h);
END;
/
 
SET_DEBUG
Set the internal debug switch and turn on jdev dbms_metadata.set_debug(on_off IN BOOLEAN);
TBD
 
SET_FILTER (overload 2 and 3 new in 11.1.)

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

Overload 1
dbms_metadata.set_filter(
handle           IN NUMBER,
name             IN VARCHAR2,
value            IN VARCHAR2,
object_type_path IN VARCHAR2 DEFAULT NULL);
DECLARE
 h NUMBER;
BEGIN
  SELECT dbms_metadata.open('TABLE')
  INTO h
  FROM DUAL;

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

  dbms_metadata.close(h);
END;
/

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

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

Enables output parsing and specifies an object attribute to be parsed and returned
dbms_metadata.set_parse_item(
handle      IN NUMBER,
name        IN VARCHAR2,
object_type IN VARCHAR2 DEFAULT NULL);

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

dbms_metadata.set_parse_item(
handle IN NUMBER,
name   IN VARCHAR2);
DECLARE
 h NUMBER;
BEGIN
  SELECT dbms_metadata.open('TABLE')
  INTO h
  FROM DUAL;

  dbms_metadata.set_parse_item(h, 'T1');

  dbms_metadata.close(h);
END;
/
 
SET_PARSING (new 11g)
Turn query parsing on/off dbms_metadata.set_parsing(on_off IN BOOLEAN);
exec dbms_metadata.set_parsing(TRUE);
 
SET_REMAP_PARAM

Specify parameters to the XSLT stylesheet identified by transform_handle.Use them to modify or customize the output of the transform
dbms_metadata.set_remap_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
old_value        IN VARCHAR2,
new_value        IN VARCHAR2,
object_type      IN VARCHAR2 DEFAULT NULL);
set serveroutput on

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

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

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

  dbms_metadata.close(h);
END;
/
 
SET_TRANSFORM_PARAM

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

Overload 1
dbms_metadata.set_transform_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
value            IN VARCHAR2),
object_type      IN VARCHAR2 DEFAULT NULL);
set long 2000000
set pagesize 0

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

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

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

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

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

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

Specify formatting attributes for XML output
dbms_metadata.set_xmlformat(
handle IN NUMBER,
name   IN VARCHAR2,
value  IN BOOLEAN DEFAULT TRUE);
TBD
 
Demo (copied from http://www.orafaq.com/node/59)
CREATE TYPE tableddl_ty AS OBJECT (
table_name  VARCHAR2(30),
orig_schema VARCHAR2(30),
orig_ddl    CLOB,
comp_schema VARCHAR2(30),
comp_ddl    CLOB);
/

CREATE TYPE tableddl_ty_tb AS TABLE OF tableddl_ty;
/
CREATE OR REPLACE FUNCTION tableddl_fc (input_values SYS_REFCURSOR)
RETURN tableddl_ty_tb PIPELINED IS

PRAGMA AUTONOMOUS_TRANSACTION;

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

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

/* setup handles to be used for setup and fetching metadata information handles are used
to keep track of the different objects (DDL) we will be referencing in the PL/SQL code */

hOpenOrig0  NUMBER;
hOpenOrig   NUMBER;
hOpenComp   NUMBER;
hModifyOrig NUMBER;
hTransDDL   NUMBER;
dmsf        PLS_INTEGER;

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

Orig_ddl0  CLOB;
Orig_ddl1  CLOB;
Comp_ddl2  CLOB;

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

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

    /* Here is the first use of our handles for pointing to the original table DDL
       It names the object_type (TABLE), provides the name of the object (our PL/SQL
       variable table_name), and states the schema it is from */

    hOpenOrig0 := dbms_metadata.open('TABLE');
    dbms_metadata.set_filter(hOpenOrig0,'NAME',table_name);
    dbms_metadata.set_filter(hOpenOrig0,'SCHEMA',orig_schema);

    /* Setup handle again for the original table DDL that will undergo transformation
       We setup two handles for the original object DDL because we want to be able to
       Manipulate one set for comparison but output the original DDL to the user */

    hOpenOrig := dbms_metadata.open('TABLE');
    dbms_metadata.set_filter(hOpenOrig,'NAME',table_name);
    dbms_metadata.set_filter(hOpenOrig,'SCHEMA',orig_schema);

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

    /* Modify the transformation of "orig_schema" to take on ownership of "comp_schema"
       If we didn't do this, when we compared the original to the comp objects there
       would always be a difference because the schema_owner is in the DDL generated */

    hModifyOrig := dbms_metadata.add_transform(hOpenOrig,'MODIFY');
    dbms_metadata.set_remap_param(hModifyOrig,'REMAP_SCHEMA',orig_schema,comp_schema);

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

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

    /* Here we are providing for those instances where the baseline object does not
       exist in the Comp_schema. */

    BEGIN
      Comp_ddl2 := dbms_metadata.fetch_clob(hOpenComp);
    EXCEPTION
      WHEN OTHERS THEN
        comp_ddl2 := 'DOES NOT EXIST';
    END;

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

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

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

 
Related Topics
DBMS_PREPROCESSOR
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [113 users online]    © 2010 psoug.org