Oracle | DBMS_METADATA

Oracle DBMS_METADATA_INT Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Implements the privileged functions of the mdAPI (Metadata API). The package is called by DBMS_METADATA and DBMS_METADATA_DIFF.

AUTHID DEFINER

Constants

Name	Data Type	Value
metafilter$.properties Flags
mf_flg_bool	NUMBER	1
mf_flg_expr	NUMBER	2
mf_flg_custom	NUMBER	4
mf_flg_has_default	NUMBER	8
mf_flg_numeric	NUMBER	16
mf_flg_filter_leaf	NUMBER	32
mf_flg_filter_branch	NUMBER	64
mf_flg_ordered	NUMBER	128
mf_flg_total_order	NUMBER	256
mf_flg_not_null	NUMBER	512
mf_flg_order_objnum	NUMBER	1024
mf_flg_special_bool	NUMBER	2048
mf_flg_bind_var	NUMBER	4096
mf_flg_order_objnum_nob	NUMBER	8192
mf_flg_OBJNUM_NAME	NUMBER	16384
mf_flg_tsnum	NUMBER	32768
mf_flg_name_schema	NUMBER	65536
mf_flg_pseudo	NUMBER	131072
mf_flg_application	NUMBER	262144
mf_flg_app_default	NUMBER	524288
metascript$.properties Flags
ms_flg_leaf	NUMBER	1
ms_flg_save_objnum	NUMBER	2
ms_flg_save_sort	NUMBER	4
ms_flg_save_num_name	NUMBER	8
ms_flg_gen_filter	NUMBER	16
ms_flg_save_tsnum1	NUMBER	32
ms_flg_save_tsnum2	NUMBER	64
ms_flg_save_name_sch	NUMBER	128
ms_flg_exec_during_ana	NUMBER	256
ms_flg_application	NUMBER	512
ms_flg_object_objnum	NUMBER	1024

Dependencies

DBMS_ASSERT	DBMS_SYS_ERROR	PARTOBJ$
DBMS_LOB	DBMS_UTILITY	PLITBLM
DBMS_METADATA	DBMS_XMLDOM	SUBCOLTYPE$
DBMS_METADATA_BUILD	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_STANDARD

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
31600	invalid input value NULL for parameter HANDLE in function CLOSE
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

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

Security Model Owned by SYS with EXECUTE granted to the EXECUTE_CATALOG_ROLE role

Direct access to some objects in this package is prevented by means of an Accessible By clause.

Source {ORACLE_HOME}/rdbms/admin/dbmsmeti.sql

Subprograms

ADD_TRANSFORM	GET_XML_INPUTS	SET_DEBUG
CLOSE	IS_ATTR_VALID_ON_10	SET_FILTER
COMPARE	MODIFY_VAT	SET_OBJECTS_FETCHED
DO_PARSE_TRANSFORM	NEXT_OBJECT	SET_PARSE_ITEM
DO_TRANSFORM	OPEN	SET_REMAP_PARAM
GET_OBJECT_TYPE_INFO	OPENC	SET_TRANSFORM_ENGINE
GET_PARSE_DELIM	OPENW	SET_TRANSFORM_PARAM
GET_QUERY	PRINT_CTXS	SET_XMLFORMAT
GET_VIEW_FILTER_INPUTS	SET_COUNT

ADD_TRANSFORM

Specify a transform to be applied to the XML representation of objects returned by FETCH_xxx

This function is protected with an ACCESSIBLE BY clause and is only accessible from dbms_metadata 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

Cleanup all context associated with handle dbms_metadata_int.close(handle IN NUMBER);

See OPEN, OPENC, and OPENW Demos Below

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

        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;

      /

      

      PL/SQL procedure successfully completed.

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_ENGINE

Sets the transform engine LPX or XmlXvm dbms_metadata_int.set_remap_param( handle IN NUMBER, value IN BOOLEAN);

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;

      /