Retrieving Multiple Objects

In Example 21-1, the FETCH_CLOB procedure was called only once, because it was known that there was only one object. However, you can also retrieve multiple objects, for example, all the tables in schema scott. To do this, you need to use the following construct:

  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;
  END LOOP;

Example 21-3 demonstrates use of this construct and retrieving multiple objects. Connect as user scott for this example. The password is tiger.

Example 21-3 Retrieving Multiple Objects

Create a table named my_metadata and a procedure named get_tables_md, as follows. Because not all objects can be returned, they are stored in a table and queried at the end.

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

Query the my_metadata table to see what was retrieved:

SET LONG 9000000
SET PAGES 0
SELECT * FROM my_metadata;