特定のメタデータ属性へのアクセス

名前やスキーマなどのオブジェクトのメタデータの特定の属性へアクセスすることが必要な場合もあります。属性の情報は返されたメタデータを解析して取得できますが、DBMS_METADATA APIには、別の機能もあります。メタデータから解析され、別のデータ構造に返される特定の属性を解析項目に指定できます。これを行うには、SET_PARSE_ITEMプロシージャを使用します。

例21-6では、スキーマ内のすべての表をフェッチします。各表では、名前を取得するために解析項目を使用します。次に表のすべての索引を取得するために名前を使用します。次の例に、sys.ku$_ddlsオブジェクトのメタデータを返すFETCH_DDLファンクションの使用を示します。

この例では、いくつかの表および索引を含むスキーマに接続していることとします。また、my_metadataという名前の表も作成します。

例21-6 特定のメタデータ属性にアクセスするための解析項目の使用

1. 次のように、my_metadataという名前の表と、get_tables_and_indexesという名前のプロシージャを作成します。

   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;
   /
   

2. 次のようにプロシージャを実行します。

   EXECUTE get_tables_and_indexes;
   

3. 次の問合せを実行して、取得された内容を確認します。

   SET LONG 9000000
   SET PAGES 0
   SELECT * FROM my_metadata;