There may be times when you need to retrieve collections of objects in which the objects are of different types, but comprise a logical unit. For example, you might need to retrieve all the objects in a database or a schema, or a table and all its dependent indexes, constraints, grants, audits, and so on. To make such a retrieval possible, the
DBMS_METADATA API provides several heterogeneous object types. A heterogeneous object type is an ordered set of object types.
Oracle supplies the following heterogeneous object types:
TABLE_EXPORT - a table and its dependent objects
SCHEMA_EXPORT - a schema and its contents
DATABASE_EXPORT - the objects in the database
These object types were developed for use by the Data Pump Export utility, but you can use them in your own applications.
You can use only the programmatic retrieval interface (
CLOSE) with these types, not the browsing interface or the submit interface.
You can specify filters for heterogeneous object types, just as you do for the homogeneous types. For example, you can specify the
NAME filters for
TABLE_EXPORT, or the
SCHEMA filter for
Example 21-8 shows how to retrieve the object types in the
scott schema. Connect as user
scott. The password is
Example 21-8 Retrieving Heterogeneous Object Types
Create a table to store the retrieved objects:
DROP TABLE my_metadata; CREATE TABLE my_metadata (md CLOB); CREATE OR REPLACE PROCEDURE get_schema_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('SCHEMA_EXPORT'); -- 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 the table. INSERT INTO my_metadata(md) VALUES (doc); COMMIT; END LOOP; -- Release resources. DBMS_METADATA.CLOSE(h); END; /
Execute the procedure:
Perform the following query to see what was retrieved:
SET LONG 9000000 SET PAGESIZE 0 SELECT * FROM my_metadata;
In this example, objects are returned ordered by object type; for example, all tables are returned, then all grants on tables, then all indexes on tables, and so on. The order is, generally speaking, a valid creation order. Thus, if you take the objects in the order in which they were returned and use the submit interface to re-create them in the same order in another schema or database, then there will usually be no errors. (The exceptions usually involve circular references; for example, if package A contains a call to package B, and package B contains a call to package A, then one of the packages will need to be recompiled a second time.)