Accessing Specific Metadata Attributes

It is often desirable to access specific attributes of an object's metadata, for example, its name or schema. You could get this information by parsing the returned metadata, but the DBMS_METADATA API provides another mechanism; you can specify parse items, specific attributes that will be parsed out of the metadata and returned in a separate data structure. To do this, you use the SET_PARSE_ITEM procedure.

Example 21-6 fetches all tables in a schema. For each table, a parse item is used to get its name. The name is then used to get all indexes on the table. The example illustrates the use of the FETCH_DDL function, which returns metadata in a sys.ku$_ddls object.

This example assumes you are connected to a schema that contains some tables and indexes. It also creates a table named my_metadata.

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

Create a table named my_metadata and a procedure named get_tables_and_indexes, as follows:

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
idxddls sys.ku$_ddls;   -- metadata is returned in sys.ku$_ddls,
                        --  a nested table of sys.ku$_ddl objects
idxname VARCHAR2(30);   -- the parsed index 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 index name be returned as a parse item.
   DBMS_METADATA.SET_PARSE_ITEM(h2,'NAME');
 
   -- 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);
  
 
   LOOP
    idxddls := dbms_metadata.fetch_ddl(h2);
 
    -- When there are no more objects to  be retrieved, FETCH_DDL returns NULL.
    EXIT WHEN idxddls IS NULL;
 
      FOR i in idxddls.FIRST..idxddls.LAST LOOP
        ddl := idxddls(i).ddlText;
        pi  := idxddls(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
              idxname := pi(j).value;
            END IF;
          END LOOP;
         END IF;
   
         -- Store the metadata in our table.
          INSERT INTO my_metadata(object_type, name, md)
            VALUES ('INDEX',idxname,ddl);
         COMMIT;
       END LOOP;  -- for loop
  END LOOP;
  DBMS_METADATA.CLOSE(h2);
 END LOOP;
 DBMS_METADATA.CLOSE(h1);
END;
/

Execute the procedure:
```
EXECUTE get_tables_and_indexes;
```

Perform the following query to see what was retrieved:

SET LONG 9000000
SET PAGES 0
SELECT * FROM my_metadata;