PL/SQL - DBMS_METADATA
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
Articles Related
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";