Oracle DBMS_METADATA_INT
Version 12.1.0.1

General Information
Purpose Implements the privileged functions of the mdAPI (Metadata API). The package is called by DBMS_METADATA and DBMS_METADATA_DIFF.
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Note Found in catmetx.sql

-- recompile dbms_metadata_int to enable the diffing code
alter package dbms_metadata_int compile plsql_ccflags = 'ku$xml_enabled:true';
AUTHID DEFINER
Dependencies
DBMS_ASSERT DBMS_UTILITY PLITBLM
DBMS_LOB DBMS_XMLDOM SUBCOLTYPE$
DBMS_METADATA DBMS_XMLPARSER UTL_CHARACTERINPUTSTREAM
DBMS_METADATA_DIFF KU$_SIMPLE_TYPE_VIEW UTL_CHARACTEROUTPUTSTREAM
DBMS_METADATA_UTIL KUPCC UTL_RAW
DBMS_SQL KUPU$UTILITIES UTL_XML
DBMS_SYS_ERROR PARTOBJ$  
Data Types -- type used in BULK COLLECT fetches and in SET_OBJECTS_FETCHED
TYPE t_num_coll IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
Documented No
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
38500 xdb_not_loaded: XDB not loaded
39212 stylesheets_not_loaded: The XSL stylesheets used by the Data Pump Metadata API were not loaded correctly into the Oracle dictionary table "sys.metastylesheet".
First Available 10.2.0.1
Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsmeti.sql
Subprograms
 
ADD_TRANSFORM
Specify a transform to be applied to the XML representation of objects returned by FETCH_xxx dbms_metadata_int.add_transform(
handle      IN NUMBER,
name        IN VARCHAR2,
encoding    IN VARCHAR2 DEFAULT NULL,
object_type IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;
TBD
 
CLOSE
Cleanup all context associated with handle dbms_metadata_int.close(handle IN NUMBER);
See OPEN, OPENC, and OPENW Demos Beow
 
COMPARE
Specifies the type of object whose metadata is to be compared dbms_metadata_int.compare (
handle IN            NUMBER,
doc1   IN            CLOB,
doc2   IN            CLOB,
difdoc IN OUT NOCOPY CLOB,
diffs     OUT        BOOLEAN);
CREATE TABLE c##abc.t1 AS SELECT * FROM tab$;
CREATE TABLE c##abc.t2 AS SELECT * FROM tab$;
ALTER TALBE c##abc.t2 DROP COLUMN SPARE6;

DECLARE
 h  NUMBER;
 c1 CLOB;
 c2 CLOB;
 dd CLOB;
 diffs BOOLEAN;
BEGIN
  c1 := dbms_metadata.get_ddl('TABLE', 'T1', 'C##ABC');
  c2 := dbms_metadata.get_ddl('TABLE', 'T2', 'C##ABC');

  h := dbms_metadata_int.openc('TABLE', 'LATEST', 'ORACLE', 'OPEN', USER);
  dc := dbms_metadata_int.compare(h, c1, c2, dd, diffs);
  IF diffs THEN
    dbms_output.put_line(dd);
  ELSE
    dbms_output.put_line('No Differences');
  END IF;
  dbms_metadata_int.close(h);
EXCEPTION
  WHEN OTHERS THEN
    dbms_metadata_int.close(h);
END;
/
 dc := dbms_metadata_int.compare(h, c1, c2, dc, diffs);
*
ERROR at line 12:
ORA-06550: line 12, column 9:
PLS-00222: no function with name 'COMPARE' exists in this scope
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored
 
DO_PARSE_TRANSFORM
Transform the XML doc with the parse transform (used by FETCH_XML) dbms_metadata_int.do_parse_transform(
handle IN            NUMBER,
xmldoc IN            CLOB,
doc    IN OUT NOCOPY CLOB);
TBD
 
DO_TRANSFORM
Transform the XML doc using all added transforms dbms_metadata_int.do_transform(
handle   IN            NUMBER,
xmldoc   IN            CLOB,
doc      IN OUT NOCOPY CLOB,
do_parse IN            BOOLEAN DEFAULT FALSE);
TBD
 
GET_OBJECT_TYPE_INFO
Returns TRUE if a heterogeneous type dbms_metadata_int.get_object_type_info(
handle        IN  NUMBER,
heterogeneous OUT BOOLEAN);
-- this is not a real-world demo but does show return of FALSE
DECLARE
 b BOOLEAN;
 h NUMBER;
BEGIN
  h := dbms_metadata_int.open('TABLE', 'LATEST', 'ORACLE', 'OPEN', USER);
  dbms_metadata_int.get_object_type_info(h, b);

  IF b THEN
    dbms_output.put_line('T');
  ELSE
    dbms_output.put_line('F');
  END IF;

  dbms_metadata_int.close(h);
EXCEPTION
  WHEN OTHERS THEN
    dbms_metadata_int.close(h);
END;
/
 
GET_PARSE_DELIM
Get the parse delimiter dbms_metadata_int.get_parse_delim(handle IN NUMBER) RETURN VARCHAR2;
-- while this compiles and runs it is obviously not what Oracle has in mind

DECLARE
 h NUMBER;
 v VARCHAR2(128);
BEGIN
  h := dbms_metadata_int.open('TABLE', 'LATEST', 'ORACLE', 'OPEN', USER);
  v := dbms_metadata_int.get_parse_delim(h);
  dbms_output.put_line('Delimiter Is: ' || v);
  dbms_metadata_int.close(h);
END;
/
 
GET_QUERY
Return the text of the query (or queries) that will be used by FETCH_xxx. This function is provided to aid in debugging dbms_metadata_int.get_query(handle IN NUMBER) RETURN VARCHAR2;
DECLARE
 h NUMBER;
 v VARCHAR2(4096);
BEGIN
  h := dbms_metadata_int.open('TABLE', 'LATEST', 'ORACLE', 'OPEN', USER);
  v := dbms_metadata_int.get_query(h);
  dbms_output.put_line('Query Is: ' || v);
  dbms_metadata_int.close(h);
END;
/
 
GET_VIEW_FILTER_INPUTS
Returns filters for the caller to issue the query For some object types (TABLE, MView, etc.) we define multiple views for fetching the objects (e.g., separate views for partitioned and non-partitioned tables). We can improve performance by avoiding querying views which don't match the the user filters: cheap queries against ku$_tabprop_view, etc. allow us to avoid expensive queries against the object views. To avoid SQL injection, the cheap queries must be issued from the invoker rights package rather than from this package.

dbms_metadata_int.get_view_filter_inputs(
handle                 IN  NUMBER,
obj_handle             OUT NUMBER,
object_type            OUT VARCHAR2,
schema_filter          OUT VARCHAR2,
name_filter            OUT VARCHAR2,
schema_expr_filter     OUT VARCHAR2,
name_expr_filter       OUT VARCHAR2,
primary_filter         OUT BOOLEAN,
secondary_filter       OUT BOOLEAN,
objnum_count           OUT NUMBER,
object_numbers         OUT t_num_coll,
objnum_filter_attrname OUT VARCHAR2,
object_type_path       OUT VARCHAR2);
TBD
 
GET_XML_INPUTS
Fetches the inputs needed to invoke the XML renderer dbms_metadata_int.get_xml_inputs(
handle              IN  NUMBER,
objnum_function     IN  VARCHAR2,
sortobjnum_function IN  VARCHAR2,
stmt                OUT VARCHAR2,
rowtag              OUT VARCHAR2,
xmltag              OUT VARCHAR2,
object_count        OUT NUMBER,
object_type_path    OUT VARCHAR2,
seqno               OUT NUMBER,
callout             OUT NUMBER,
parsed_items        OUT dbms_sql.varchar2_table,
bind_vars           OUT dbms_sql.varchar2_table,
objnum_count        OUT NUMBER,
object_numbers      OUT t_num_coll,
object_names        OUT dbms_sql.varchar2_table);
TBD
 
IS_ATTR_VALID_ON_10
Undocumented dbms_metadata_int.is_attr_valid_on_10(
obj_num    IN NUMBER,
intcol_num IN NUMBER)
RETURN NUMBER;
TBD
 
MODIFY_VAT
Do MODIFY/REMAP for VIEWS_AS_TABLES step dbms_metadata_int.modify_vat(
handle    IN NUMBER,
ho_type   IN VARCHAR2,
path      IN VARCHAR2,
transform IN VARCHAR2,
name1     IN VARCHAR2,
name2     IN VARCHAR2);
TBD
 
NEXT_OBJECT
Set the dbms_metadata_int state to point to the next object type to be fetched. (For homogeneous object types this is a no-op.) dbms_metadata_int.next_object(
handle       IN NUMBER,
skip_current IN BOOLEAN DEFAULT FALSE);
TBD
 
OPEN
Specifies the type of object whose metadata is to be retrieved dbms_metadata_int.open(
object_type  IN VARCHAR2,
version      IN VARCHAR2,
model        IN VARCHAR2,
public_func  IN VARCHAR2,
current_user IN VARCHAR2)
RETURN NUMBER;
DECLARE
 h NUMBER;
BEGIN
  h := dbms_metadata_int.open('TABLE', 'LATEST', 'ORACLE', 'OPEN', USER);
  dbms_output.put_line(TO_CHAR(h));
  dbms_metadata_int.close(h);
END;
/
 
OPENC
Specifies the type of object whose metadata is to be compared dbms_metadata_int.openc(
object_type  IN VARCHAR2,
version      IN VARCHAR2,
model        IN VARCHAR2,
public_func  IN VARCHAR2,
current_user IN VARCHAR2)
RETURN NUMBER;
DECLARE
 h NUMBER;
BEGIN
  h := dbms_metadata_int.openc('TABLE', 'LATEST', 'ORACLE', 'OPEN', USER);
  dbms_output.put_line(TO_CHAR(h));
  dbms_metadata_int.close(h);
END;
/
 
OPENW
Specifies the type of object whose metadata is to be submitted (written) dbms_metadata_int.openw(
object_type IN VARCHAR2,
version     IN VARCHAR2,
model       IN VARCHAR2,
public_func IN VARCHAR2)
RETURN NUMBER;
DECLARE
 h NUMBER;
BEGIN
  h := dbms_metadata_int.openw('TABLE', 'LATEST', 'ORACLE', 'OPEN');
  dbms_output.put_line(TO_CHAR(h));
  dbms_metadata_int.close(h);
END;
/
 
PRINT_CTXS
For debugging: Print all active contexts dbms_metadata_int.print_ctxs;
BEGIN
  dbms_metadata.set_debug(TRUE);
  dbms_metadata_int.print_ctxs;
END;
/
 
SET_COUNT
Specifies the number of objects to be returned in a single FETCH_xxx call dbms_metadata_int.set_count(
handle      IN NUMBER,
value       IN NUMBER,
object_type IN VARCHAR2 DEFAULT NULL);
DECLARE
 h NUMBER;
BEGIN
  h := dbms_metadata_int.openc('TABLE', 'LATEST', 'ORACLE', 'OPEN', USER);
  dbms_metadata_int.set_count(h, 5, 'TABLE');
  dbms_metadata_int.close(h);
END;
/
 
SET_DEBUG
Set the internal debug switch

Overload 1
dbms_metadata_int.set_debug(
on_off IN BOOLEAN,               -- new switch state
arg2   IN BOOLEAN DEFAULT TRUE); -- unused argument
See PRINT_CTXS Demo Above
Overload 2 dbms_metadata_int.set_debug(debug_flags IN BINARY_INTEGER);
TBD
 
SET_FILTER
Undocumented

Overload 1
dbms_metadata_int.set_filter(
handle      IN NUMBER,
name        IN VARCHAR2,
value       IN VARCHAR2,
object_type IN VARCHAR2 DEFAULT NULL);
TBD
Overload 2 dbms_metadata_int.set_filter(
handle      IN NUMBER,
name        IN VARCHAR2,
value       IN BOOLEAN DEFAULT TRUE,
object_type IN VARCHAR2 DEFAULT NULL);
TBD
Overload 3 dbms_metadata_int.set_filter(
handle      IN NUMBER,
name        IN VARCHAR2,
value       IN NUMBER,
object_type IN VARCHAR2 DEFAULT NULL);
TBD
 
SET_OBJECTS_FETCHED
Set the count of objects fetched and their objnums

Overload 1
dbms_metadata_int.set_objects_fetched(
handle            IN NUMBER,
object_count      IN NUMBER,
object_numbers    IN t_num_coll,
dependent_objects IN t_num_coll);
TBD
Overload 2 dbms_metadata_int.set_objects_fetched(
handle            IN NUMBER,
object_count      IN NUMBER,
object_numbers    IN t_num_coll,
dependent_objects IN t_num_coll,
object_names      IN dbms_sql.varchar2_table);
TBD
Overload 3 dbms_metadata_int.set_objects_fetched(
handle         IN NUMBER,
object_count   IN NUMBER,
object_schemas IN dbms_sql.varchar2_table,
object_names   IN dbms_sql.varchar2_table,
object_levels  IN t_num_coll);
TBD
 
SET_PARSE_ITEM
Enables output parsing and specifies an object attribute to be parsed and returned dbms_metadata_int.set_parse_item(
handle      IN NUMBER,
name        IN VARCHAR2,
object_type IN VARCHAR2 DEFAULT NULL);
TBD
 
SET_REMAP_PARAM
Specifies values for a remap parameter to the XSL-T stylesheet identified by handle dbms_metadata_int.set_remap_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
old_value        IN VARCHAR2,
new_value        IN VARCHAR2,
object_type      IN VARCHAR2 DEFAULT NULL);
TBD
 
SET_TRANSFORM_PARAM
Specifies a value for a parameter to the XSL-T stylesheet identified by handle dbms_metadata_int.set_transform_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
value            IN VARCHAR2,
object_type      IN VARCHAR2 DEFAULT NULL);
TBD
Overload 2 dbms_metadata_int.set_transform_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
value            IN NUMBER,
object_type      IN VARCHAR2 DEFAULT NULL);
TBD
Overload 3 dbms_metadata_int.set_transform_param(
transform_handle IN NUMBER,
name             IN VARCHAR2,
value            IN BOOLEAN DEFAULT TRUE,
object_type      IN VARCHAR2 DEFAULT NULL);
TBD
 
SET_XMLFORMAT
Specifies formatting attributes for XML output dbms_metadata_int.set_xmlformat(
handle IN NUMBER,
name   IN VARCHAR2,
value  IN BOOLEAN DEFAULT TRUE);
DECLARE
 h NUMBER;
BEGIN
  h := dbms_metadata_int.openc('TABLE', 'LATEST', 'ORACLE', 'OPEN', USER);
  dbms_metadata_int.set_xmlformat(h, 'PRETTY');
  dbms_metadata_int.close(h);
END;
/

Related Topics
DBMS_METADATA
DBMS_METADATA_BUILD
DBMS_METADATA_DIFF
DBMS_METADATA_DPBUILD
DBMS_METADATA_HACK
DBMS_METADATA_UTIL
Packages

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