PL/SQL - DBMS_METADATA

> Procedural Languages > PL/SQL - (Procedure Language|PL) SQL

1 - 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

Advertising

3 - 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";

4 - Documentation / Reference

lang/plsql/dbms_metadata.txt · Last modified: 2017/09/06 22:18 by gerardnico