PL/SQL - DBMS_METADATA

Card Puncher Data Processing

About

The DBMS_METADATA permits to get metadata obejct definition in the form of:

  • a DDL statement
  • or an XML file

You can also MODIFY the XML metadata before applying it in order to recreate it.

See the guide: Database Utilities - Using the Metadata API

OPEN, TRANSFORM, FETCH

OPEN, TRANSFORM, FETCH example to get the GRANTS of a table

set serveroutput on;
DECLARE
v_handle NUMBER;
v_transform_handle NUMBER;
-- sys.ku$_ddls typpe IS TABLE OF sys.ku$_ddl
-- sys.ku$_ddl is the following object OBJECT (
--     ddlText        CLOB,
--     parsedItem     sys.ku$_parsed_items )
v_ddls  sys.ku$_ddls;
v_ddl   sys.ku$_ddl;
BEGIN
  v_handle := DBMS_METADATA.OPEN ('OBJECT_GRANT');
  v_transform_handle := DBMS_METADATA.ADD_TRANSFORM(v_handle, 'DDL');
  DBMS_METADATA.SET_TRANSFORM_PARAM(v_transform_handle,'PRETTY', TRUE);
  DBMS_METADATA.SET_TRANSFORM_PARAM(v_transform_handle,'SQLTERMINATOR',TRUE);
  DBMS_METADATA.SET_FILTER(v_handle, 'BASE_OBJECT_NAME', 'TABLE_NAME' );
  DBMS_METADATA.SET_FILTER(v_handle, 'BASE_OBJECT_SCHEMA', 'OWNER_NAME' );
  DBMS_METADATA.SET_COUNT (v_handle, 10); -- 10 object by call to fetch
  v_ddls := DBMS_METADATA.FETCH_DDL(v_handle);
  while (v_ddls is not null) 
  LOOP
    FOR indx IN 1 .. v_ddls.COUNT
    LOOP
       v_ddl := v_ddls(indx);
      DBMS_OUTPUT.put_line ('Output: ' || v_ddl.ddlText);
    END LOOP;
    v_ddls := DBMS_METADATA.FETCH_DDL(v_handle);
    -- The FETCH_DDL function returns the DDL (to create the object) 
    -- in a sys.ku$_ddls nested table. 
    -- Each row of the sys.ku$_ddls nested table contains a single DDL statement in the ddlText column; 
    -- if requested, parsed items for the DDL statement will be returned in the parsedItems column. 
  END LOOP;
  DBMS_METADATA.CLOSE (v_handle);
END;
/
PL/SQL procedure successfully completed.
Output: GRANT SELECT ON "OWNER_NAME"."TABLE_NAME" TO "REPORT_ADMINS";
Output: GRANT SELECT ON "OWNER_NAME"."TABLE_NAME" TO "SHAREPOINT";

Documentation / Reference





Discover More
Card Puncher Data Processing
Oracle Database - Table

A table name must follow the naming convention of database object. Add DataType Create Table ...
Card Puncher Data Processing
Oracle Partition - Metadata (View)

How to get information on the partition and sub-partitions View Description (ALL|DBA|USER)_TABLES the PARTITIONED column value is YES (ALL|DBA|USER)_PART_TABLES type of partitioning scheme,...



Share this page:
Follow us:
Task Runner