18 Using the Metadata API

Example 18-1 Using the DBMS_METADATA Programmatic Interface to Retrieve Data

1. Create a Metadata API program that creates a function named get_table_md, which will return the metadata for one table, timecards, in the hr schema. The content of such a program looks as follows. (For this example, we will name the program metadata_program.sql.)

   CREATE OR REPLACE FUNCTION get_table_md RETURN CLOB IS
   -- Define local variables.
   h NUMBER; --handle returned by OPEN
   th NUMBER; -- handle returned by ADD_TRANSFORM
   doc CLOB;
   BEGIN
   
   -- Specify the object type.
   h := DBMS_METADATA.OPEN('TABLE');
   
   -- Use filters to specify the particular object desired.
   DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR');
   DBMS_METADATA.SET_FILTER(h,'NAME','TIMECARDS');
   
    -- Request that the metadata be transformed into creation DDL.
   th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');
   
    -- Fetch the object.
   doc := DBMS_METADATA.FETCH_CLOB(h);
   
    -- Release resources.
   DBMS_METADATA.CLOSE(h);
   RETURN doc;
   END;
   / 
   
   

2. Connect as hr/hr.

3. Run the program to create the get_table_md function:

   SQL> @metadata_program

4. Use the newly created get_table_md function in a select operation. To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query:

   SQL> SET PAGESIZE 0
   SQL> SET LONG 1000000
   SQL> SELECT get_table_md FROM dual;
   
   

5. The output, which shows the metadata for the timecards table in the hr schema, looks similar to the following:

   CREATE TABLE "HR"."TIMECARDS"
      (    "EMPLOYEE_ID" NUMBER(6,0),
           "WEEK" NUMBER(2,0),
           "JOB_ID" VARCHAR2(10),
           "HOURS_WORKED" NUMBER(4,2),
            FOREIGN KEY ("EMPLOYEE_ID")
             REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
      ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
     STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
     TABLESPACE "EXAMPLE"
   
   

Example 18-2 Using the DBMS_METADATA Browsing Interface to Retrieve Data

SQL> SET PAGESIZE 0
SQL> SET LONG 1000000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TIMECARDS','HR') FROM dual;

The results will be the same as shown in step 5 for Example 18-1.

Example 18-3 Retrieving Multiple Objects

-- Because not all objects can be returned, they are stored in a table and queried at the end.

CONNECT scott/tiger
DROP TABLE my_metadata;
CREATE TABLE my_metadata (md clob);
CREATE OR REPLACE PROCEDURE get_tables_md IS
-- Define local variables
h       NUMBER;         -- handle returned by 'OPEN'
th      NUMBER;         -- handle returned by 'ADD_TRANSFORM'
doc     CLOB;           -- metadata is returned in a CLOB
BEGIN
 -- Specify the object type.
 h := DBMS_METADATA.OPEN('TABLE');

 -- Use filters to specify the schema.
 DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');

 -- Request that the metadata be transformed into creation DDL.
 th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

 -- Fetch the objects.
 LOOP
   doc := DBMS_METADATA.FETCH_CLOB(h);

  -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
   EXIT WHEN doc IS NULL;

   -- Store the metadata in a table.
   INSERT INTO my_metadata(md) VALUES (doc);
   COMMIT;
 END LOOP;
 
 -- Release resources.
 DBMS_METADATA.CLOSE(h);
END;
/
-- Execute the procedure.

EXECUTE get_tables_md;

-- See what was retrieved.

SET LONG 9000000
SET PAGES 0
SELECT * FROM my_metadata;

Example 18-4 Placing Conditions on Transforms

CREATE OR REPLACE FUNCTION get_table_md RETURN CLOB IS
 -- Define local variables.
 h    NUMBER;   -- handle returned by 'OPEN'
 th   NUMBER;   -- handle returned by 'ADD_TRANSFORM'
 doc  CLOB;
BEGIN
 -- Specify the object type. 
 h := DBMS_METADATA.OPEN('TABLE');

 -- Use filters to specify the particular object desired.
 DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR');
 DBMS_METADATA.SET_FILTER(h,'NAME','TIMECARDS');

 -- Request that the metadata be transformed into creation DDL.
 th := dbms_metadata.add_transform(h,'DDL');

 -- Specify that segment attributes are not to be returned.
 -- Note that this call uses the TRANSFORM handle, not the OPEN handle.
DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SEGMENT_ATTRIBUTES',false);

 -- Fetch the object.
 doc := DBMS_METADATA.FETCH_CLOB(h);

 -- Release resources.
 DBMS_METADATA.CLOSE(h);

 RETURN doc;
END;
/

When you execute the SQL statement (SELECT get_table_md FROM DUAL;), the output looks similar to the following:

CREATE TABLE "HR"."TIMECARDS"
   (    "EMPLOYEE_ID" NUMBER(6,0),
        "WEEK" NUMBER(2,0),
        "JOB_ID" VARCHAR2(10),
        "HOURS_WORKED" NUMBER(4,2),
         FOREIGN KEY ("EMPLOYEE_ID")
          REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
   )

The examples shown up to this point have used a single transform, the DDL transform. The Metadata API also enables you to specify multiple transforms, with the output of the first being the input to the next and so on.

Oracle supplies a transform called MODIFY that modifies an XML document. You can do things like change schema names or tablespace names. To do this, you use remap parameters and the SET_REMAP_PARAM procedure.

Example 18-5 shows a sample use of the SET_REMAP_PARAM procedure. It first adds the MODIFY transform and specifies remap parameters to change the schema name from hr to scott. It then adds the DDL transform. The output of the MODIFY transform is an XML document that becomes the input to the DDL transform. The end result is the creation DDL for the timecards table with all instances of schema hr changed to scott.

Example 18-5 Modifying an XML Document

CREATE OR REPLACE FUNCTION remap_schema RETURN CLOB IS
-- Define local variables.
h NUMBER; --handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB;
BEGIN

-- Specify the object type.
h := DBMS_METADATA.OPEN('TABLE');

-- Use filters to specify the particular object desired.
DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR');
DBMS_METADATA.SET_FILTER(h,'NAME','TIMECARDS');

-- Request that the schema name be modified.
th := DBMS_METADATA.ADD_TRANSFORM(h,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','HR','SCOTT');

-- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

-- Specify that segment attributes are not to be returned.

DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SEGMENT_ATTRIBUTES',false);

-- Fetch the object.
doc := DBMS_METADATA.FETCH_CLOB(h);

-- Release resources.
DBMS_METADATA.CLOSE(h);
RETURN doc;
END;
/ 

When you execute the SQL statement (SELECT remap_schema FROM DUAL;), the output looks similar to the following:

CREATE TABLE "SCOTT"."TIMECARDS"
   (    "EMPLOYEE_ID" NUMBER(6,0),
        "WEEK" NUMBER(2,0),
        "JOB_ID" VARCHAR2(10),
        "HOURS_WORKED" NUMBER(4,2),
         FOREIGN KEY ("EMPLOYEE_ID")
          REFERENCES "SCOTT"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE
   )

If you are familiar with XSLT, you can add your own user-written transforms to process the XML.

Example 18-6 Using Parse Items to Access Specific Metadata Attributes

DROP TABLE my_metadata;
CREATE TABLE my_metadata (
  object_type   VARCHAR2(30),
  name          VARCHAR2(30),
  md            CLOB);
CREATE OR REPLACE PROCEDURE get_tables_and_indexes IS
-- Define local variables.
h1      NUMBER;         -- handle returned by OPEN for tables
h2      NUMBER;         -- handle returned by OPEN for indexes
th1     NUMBER;         -- handle returned by ADD_TRANSFORM for tables
th2     NUMBER;         -- handle returned by ADD_TRANSFORM for indexes
doc     sys.ku$_ddls;   -- metadata is returned in sys.ku$_ddls,
                        --  a nested table of sys.ku$_ddl objects
ddl     CLOB;           -- creation DDL for an object
pi      sys.ku$_parsed_items;   -- parse items are returned in this object
                                -- which is contained in sys.ku$_ddl
objname VARCHAR2(30);   -- the parsed object name
BEGIN
 -- This procedure has an outer loop that fetches tables,
 -- and an inner loop that fetches indexes.

 -- Specify the object type: TABLE.
 h1 := DBMS_METADATA.OPEN('TABLE');

 -- Request that the table name be returned as a parse item.
 DBMS_METADATA.SET_PARSE_ITEM(h1,'NAME');

 -- Request that the metadata be transformed into creation DDL.
 th1 := DBMS_METADATA.ADD_TRANSFORM(h1,'DDL');

 -- Specify that segment attributes are not to be returned.
 DBMS_METADATA.SET_TRANSFORM_PARAM(th1,'SEGMENT_ATTRIBUTES',false);

 -- Set up the outer loop: fetch the TABLE objects.
 LOOP
   doc := dbms_metadata.fetch_ddl(h1);

-- When there are no more objects to be retrieved, FETCH_DDL returns NULL.
   EXIT WHEN doc IS NULL;

-- Loop through the rows of the ku$_ddls nested table.
   FOR i IN doc.FIRST..doc.LAST LOOP
     ddl := doc(i).ddlText;
     pi := doc(i).parsedItems;
     -- Loop through the returned parse items.
     IF pi IS NOT NULL AND pi.COUNT > 0 THEN
       FOR j IN pi.FIRST..pi.LAST LOOP
         IF pi(j).item='NAME' THEN
           objname := pi(j).value;
         END IF;
       END LOOP;
     END IF;
     -- Insert information about this object into our table.
     INSERT INTO my_metadata(object_type, name, md)
       VALUES ('TABLE',objname,ddl);
     COMMIT;
   END LOOP;

   -- Now fetch indexes using the parsed table name as
   --  a BASE_OBJECT_NAME filter.

   -- Specify the object type.
   h2 := DBMS_METADATA.OPEN('INDEX');

   -- The base object is the table retrieved in the outer loop.
   DBMS_METADATA.SET_FILTER(h2,'BASE_OBJECT_NAME',objname);

   -- Exclude system-generated indexes.
   DBMS_METADATA.SET_FILTER(h2,'SYSTEM_GENERATED',false);

   -- Request that the metadata be transformed into creation DDL.
   th2 := DBMS_METADATA.ADD_TRANSFORM(h2,'DDL');

   -- Specify that segment attributes are not to be returned.
   DBMS_METADATA.SET_TRANSFORM_PARAM(th2,'SEGMENT_ATTRIBUTES',false);

   -- Set up the inner loop: fetch the INDEX objects.
   LOOP
     DDL := DBMS_METADATA.FETCH_CLOB(h2);

     -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
     EXIT WHEN ddl IS NULL;

     -- Store the metadata in our table.
     INSERT INTO my_metadata(object_type, name, md)
       VALUES ('INDEX',NULL,ddl);
     COMMIT;
   END LOOP;
   DBMS_METADATA.CLOSE(h2);
 END LOOP;
 DBMS_METADATA.CLOSE(h1);
END;
/

-- Execute the procedure.

EXECUTE get_tables_and_indexes;

-- Perform a query to check what was retrieved.

SET LONG 9000000
SET PAGES 0
SELECT * FROM my_metadata;

Example 18-9 Filtering the Return of Heterogeneous Object Types

-- Create a table to store the retrieved objects.
CONNECT scott/tiger
DROP TABLE my_metadata;
CREATE TABLE my_metadata (md CLOB);
CREATE OR REPLACE PROCEDURE get_schema_md2 IS

-- Define local variables.
h       NUMBER;         -- handle returned by 'OPEN'
th      NUMBER;         -- handle returned by 'ADD_TRANSFORM'
doc     CLOB;           -- metadata is returned in a CLOB
BEGIN

 -- Specify the object type.
 h := DBMS_METADATA.OPEN('SCHEMA_EXPORT');

 -- Use filters to specify the schema.
 DBMS_METADATA.SET_FILTER(h,'SCHEMA','SCOTT');

 -- Use the fourth parameter to SET_FILTER to specify a filter
 -- that applies to a specific member object type.
 DBMS_METADATA.SET_FILTER(h,'NAME_EXPR','!=''MY_METADATA''','TABLE');

 -- Use the EXCLUDE_PATH_EXPR filter to exclude procedures.
 DBMS_METADATA.SET_FILTER(h,'EXCLUDE_PATH_EXPR','=''PROCEDURE''');

 -- Request that the metadata be transformed into creation DDL.
 th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

 -- Use the fourth parameter to SET_TRANSFORM_PARAM to specify a parameter
 --  that applies to a specific member object type.
DBMS_METADATA.SET_TRANSFORM_PARAM(th,'SEGMENT_ATTRIBUTES',false,'TABLE');

 -- Fetch the objects.
 LOOP
   doc := dbms_metadata.fetch_clob(h);

   -- When there are no more objects to be retrieved, FETCH_CLOB returns NULL.
   EXIT WHEN doc IS NULL;

   -- Store the metadata in the table.
   INSERT INTO my_metadata(md) VALUES (doc);
   COMMIT;
 END LOOP;
 
 -- Release resources.
 DBMS_METADATA.CLOSE(h);
END;
/
-- Execute the procedure.

EXECUTE get_schema_md2;

-- See what was retrieved.

SET LONG 9000000
SET PAGESIZE 0
SELECT * FROM my_metadata;