This chapter describes the Metadata application programming interface (API), which you can use to extract and manipulate complete representations of the metadata for database objects. The following topics are discussed in this chapter:

The Metadata API is available as of Oracle9i release 9.0.1, whenever the instance is operational. It is not available in Oracle Lite.

Previous Methods Used to Extract Metadata

An object's metadata is distributed in normalized fashion across the database dictionary. In prior releases, you first had to understand how and where your object's metadata was represented in the dictionary, then you had to issue multiple queries to extract the object's full representation. Once the metadata was extracted, you would typically perform the following tasks:

Transform it in some way, such as changing the object's tablespace, changing a column datatype, changing an object's owner, and so on.

Convert it to SQL DDL text for execution on the source or some other database.

Before Oracle9i release 9.0.1, there was no assistance for either of these steps.

Metadata API Components

Underlying the Metadata API is an object model of the Oracle database dictionary consisting of a series of user-defined types (UDTs) and corresponding object views. The UDTs provide the aggregation of each object class's metadata, and the object views map the UDTs' attributes onto the appropriate base relational tables in the dictionary. The Metadata API generates queries against these object views to retrieve aggregated database object definitions.

The results of these queries are converted into XML documents by the XML SQL Utility (XSU), which was also introduced in Oracle9i release 9.0.1. When the caller requests DDL output, the Metadata API uses the appropriate implementation of the Oracle server's integral XML parser and XSL processor to convert the XML documents into creation DDL.

Metadata API Features

The Metadata API has the following features:

Provides a powerful PL/SQL interface for detailed programmatic control or casual browsing.

Internet Computing

The Metadata API uses two internet standards, XML and XSLT, for encoding and transforming object metadata. Use of an industry-standard format for metadata encoding (rather than a proprietary format) allows you to use standard tools to parse and transform the output.

There is currently no industry-standard XML model for database metadata, so the Metadata API uses a model optimized for generating Oracle DDL. Document element names are derived directly from attributes of the UDTs in the Oracle database dictionary model. As standard models emerge, the Metadata API will support the ability to plug them in. Older documents can be converted to alternate models with XSLT.

How Is the Metadata API Implemented?

The Metadata API is implemented using the PL/SQL DBMS_METADATA package. The DBMS_METADATA package allows you to retrieve metadata from the database dictionary. It provides a flexible and extensible means for object selection. You can use DBMS_METADATA to extract database object metadata in XML and DDL.

A description of the types and public interface defined by the Metadata API is in the following location:

$ORACLE_HOME/rdbms/admin/dbmsmeta.sql

DBMS_METADATA and Security

The object views of the Oracle metadata model implement security as follows:

Nonprivileged users can see the metadata of only their own objects.

SYS and users with SELECT_CATALOG_ROLE can see all objects.

Nonprivileged users can also retrieve object and system privileges granted to them or by them to others. This also includes privileges granted to PUBLIC.

If callers request objects they are not privileged to retrieve, no exception is raised; the object is simply not retrieved.

If nonprivileged users are granted some form of access to an object in someone else's schema, they will be able to retrieve the grant specification through the Metadata API, but not the object's actual metadata.

DBMS_METADATA Programmatic Interface

The DBMS_METADATA programmatic interface is for fine-grained, detailed control:

The following procedures are provided: OPEN, SET_FILTER, SET_PARSE_ITEM, SET_COUNT, ADD_TRANSFORM, SET_TRANSFORM_PARAM, FETCH_xxx, CLOSE

Metadata is expressed as XML. This allows industry-standard metadata transformations using XSLT.

You can ask DBMS_METADATA to return metadata as DDL. The API uses XSL scripts internally to transparently perform the conversion.

You can invoke an XSL script, using either the Oracle XML parser or some third-party tool, to do an offline conversion of the XML representation.

Table 15-1 Procedures for the DBMS_METADATA Programmatic Interface

Specifies type of object to be retrieved, version of its metadata, and object model. Return value is an opaque context handle for the set of objects to be used in subsequent calls.

BMS_METADATA.SET_FILTER()

Specifies restrictions on objects to be retrieved, such as, object name or schema. Allows specification of base objects for dependent objects such as indexes and triggers.

DBMS_METADATA.SET_COUNT()

Specifies number of objects to be retrieved in a single FETCH_xxx call. By default, each call to FETCH_xxx returns one object.

DBMS_METADATA.GET_QUERY()

Returns text of query (or queries) used by FETCH_xxx. This text is provided to assist in debugging.

DBMS_METADATA.SET_PARSE_ITEM()

Enables output parsing and specifies an object attribute to be parsed and returned. This frees the caller from having to parse SQL DDL for key attributes.

DBMS_METADATA.ADD_TRANSFORM()

Specifies a transform that FETCH_xxx applies to the XML representation of retrieved objects. You can add more than one transform. By default (with no transforms added), objects are returned as XML documents. Call the ADD_TRANSFORM procedure to specify an XSLT script to transform the returned documents. If 'DDL' is specified, the objects' creation DDL is returned from subsequent FETCH_xxx calls. The ADD_TRANSFORM procedure returns an opaque transform handle different from that returned by OPEN.

DBMS_METADATA.SET_TRANSFORM_PARAM()

Specifies parameters to the XSLT stylesheet identified by the transform_handle returned from the ADD_TRANSFORM procedure.

For the DDL transform, these parameters alter the form of the DDL. For example, constraints may be requested as column constraints or ALTERTABLE statements.

DBMS_METADATA.FETCH_xxx()

The FETCH_xxx routines return metadata for objects meeting the criteria established by the OPEN, SET_FILTER, SET_COUNT, and ADD_TRANSFORM procedures.

FETCH_XML and FETCH_DDL return the metadata as XML and SQL DDL, respectively. The FETCH_CLOB routines return either XML or DDL as denoted by the transforms specified.

Figure 15-1 Using DBMS_METADATA.FETCH_XML()

Using the DBMS_METADATA.FETCH_DDL Procedure

Open the object type using the DBMS_METADATA.OPEN()procedure. Object types that you can open include, but are not limited to, tables, indexes, types, packages, and synonyms.

Specify which objects to retrieve using the DBMS_METADATA.SET_FILTER()procedure.

Specify what transforms are to be invoked on the output. Use the DBMS_METADATA.ADD_TRANSFORM() procedure to add a transform. The last transform added must be the "DDL" transform.

Use the DBMS_METADATA.SET_TRANSFORM_PARAM()procedure to customize the DDL. For example, you could use it to exclude storage clauses on table definitions. Transform parameters are specific to the object type chosen.

Fetch the DDL using the DBMS_METADATA.FETCH_DDL()procedure. An example of the DDL processing is re-creating objects in another schema or database.

If the result of this operation is NULL, then call the DBMS_METADATA.CLOSE()procedure.

Figure 15-2 Using DBMS_METADATA.FETCH_DDL()

Performance Tips for the Programmatic Interface of the Metadata API

This section describes how to enhance performance when using the programmatic interface of the Metadata API.

Fetch all of one type of object before fetching the next. For example, if you are retrieving the definitions of all objects in your schema, first fetch all the tables, then all the indexes, then all the triggers, and so on. This will be much faster than nesting OPEN contexts; that is, fetch one table then all of its indexes, grants, and triggers, then the next table and all of its indexes, grants, and triggers, and so on. The Metadata API Example reflects this second, less efficient means, but its purpose is to demonstrate most of the programmatic calls, which are best shown by this method.

Use the SET_COUNT procedure to retrieve more than one object at a time. This minimizes server round trips and eliminates many redundant function calls.

Use the procedure rather than function form of FETCH_CLOB. The procedure form returns the output CLOB by reference through the INOUTNOCOPY specifier. The function form returns the output CLOB by value requiring an extra LOB copy.

When writing a PL/SQL package that calls the Metadata API, declare LOB variables and objects that contain LOBs (such as SYS.KU$_DDLS) at package scope rather than within individual functions. This eliminates the creation and deletion of LOB duration structures upon function entrance and exit, which are very expensive operations.

Table 15-2 Procedures for the DBMS_METADATA Browsing Interface

PL/SQL Procedure Name

Description

DBMS_METADATA.GET_xxx()

Provides a way to return metadata for a single object. Each GET_xxx call consists of an OPEN procedure, one or two SET_FILTER calls, optionally an ADD_TRANSFORM procedure, a FETCH_xxx call, and a CLOSE procedure.

The object_type parameter has the same semantics as in the OPEN procedure. schema and name are used for filtering.

If a transform is specified, session-level transform flags are inherited.

DBMS_METADATA.GET_DEPENDENT_xxx()

Returns the metadata for one or more dependent objects, specified as XML or DDL.

DBMS_METADATA.GET_GRANTED_xxx()

Returns the metadata for one or more granted objects, specified as XML or DDL.

Example: Using the DBMS_METADATA Browsing Interface

The following SQL*Plus query will display the creation DDL for all tables in the current user's schema. To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query.

Metadata API Example

The detailed Metadata API programming example in this section, PAYROLL_DEMO, retrieves the DDL for all tables in the MDDEMO schema that start with 'PAYROLL'. It then fetches the DDL for grants, indexes, and triggers defined on those tables. This script can be found in the file rdbms/demo/mddemo.sql in your Oracle home directory.

mddemo.sql

-- This script demonstrates how to use the Metadata API. It first
-- establishes a schema (MDDEMO) and some payroll users, then creates three
-- payroll-like tables within it along with associated indexes, triggers
-- and grants.
-- It then creates a package PAYROLL_DEMO that shows common usage of the
-- Metadata API. The procedure GET_PAYROLL_TABLES retrieves the DDL for the
-- two tables in this schema that start with 'PAYROLL' then for each one,
-- retrieves the DDL for its associate dependent objects; indexes, grants
-- and triggers. All the DDL is written to a table named "MDDEMO"."DDL".
-- First, Install the demo. cd to rdbms/demo:
-- > sqlplus system/manager
-- SQL> @mddemo
-- Then, run it.
-- > sqlplus mddemo/mddemo
-- SQL> set long 40000
-- SQL> set pages 0
-- SQL> call payroll_demo.get_payroll_tables();
-- SQL> select ddl from DDL order by seqno;
Rem Set up schema for demo pkg. PAYROLL_DEMO.
connect system/manager
drop user mddemo cascade;
drop user mddemo_clerk cascade;
drop user mddemo_mgr cascade;
create user mddemo identified by mddemo;
GRANT resource, connect, create session
, create table
, create procedure
, create sequence
, create trigger
, create view
, create synonym
, alter session
TO mddemo;
create user mddemo_clerk identified by clerk;
create user mddemo_mgr identified by mgr;
connect mddemo/mddemo
Rem Create some payroll-like tables...
create table payroll_emps
( lastname varchar2(60) not null,
firstname varchar2(20) not null,
mi varchar2(2),
suffix varchar2(10),
DOB date not null,
badge_no number(6) primary key,
exempt varchar(1) not null,
salary number (9,2),
hourly_rate number (7,2)
)
/
create table payroll_timecards
badge_no number(6) references payroll_emps (badge_no),
week number(2),
job_id number(5),
hours_worked number(4,2)
)
/
-- This is a dummy table used only to show that tables NOT starting with
-- 'PAYROLL' are NOT retrieved by payroll_demo.get_payroll_tables
create table audit_trail
(action_time DATE,
lastname VARCHAR2(60),
action LONG
)
/
Rem Then, create some grants...
grant update (salary,hourly_rate) on payroll_emps to mddemo_clerk;
grant ALL on payroll_emps to mddemo_mgr with grant option;
grant insert,update on payroll_timecards to mddemo_clerk;
grant ALL on payroll_timecards to mddemo_mgr with grant option;
Rem Then, create some indexes...
create index i_payroll_emps_name on payroll_emps(lastname);
create index i_payroll_emps_dob on payroll_emps(DOB);
create index i_payroll_timecards_badge on payroll_timecards(badge_no);
Rem Then, create some triggers (and required procedure)...
create or replace procedure check_sal( salary in number) as
begin
return; -- Fairly loose security here...
end;
/
create or replace trigger salary_trigger before insert or update of salary on
payroll_emps
for each row when (new.salary > 150000)
call check_sal(:new.salary)
/
create or replace trigger hourly_trigger before update of hourly_rate on
payroll_emps
for each row
begin :new.hourly_rate:=:old.hourly_rate;end;
/
--
-- Set up a table to hold the generated DDL
--
CREATE TABLE ddl (ddl CLOB, seqno NUMBER);
Rem Finally, create the PAYROLL_DEMO package itself.
CREATE OR REPLACE PACKAGE payroll_demo AS
PROCEDURE get_payroll_tables;
END;
/
CREATE OR REPLACE PACKAGE BODY payroll_demo AS
-- GET_PAYROLL_TABLES: Fetch DDL for payroll tables and their dependent objects
PROCEDURE get_payroll_tables IS
tableOpenHandle NUMBER;
depObjOpenHandle NUMBER;
tableTransHandle NUMBER;
indexTransHandle NUMBER;
schemaName VARCHAR2(30);
tableName VARCHAR2(30);
tableDDLs sys.ku$_ddls;
tableDDL sys.ku$_ddl;
parsedItems sys.ku$_parsed_items;
depObjDDL CLOB;
seqNo NUMBER := 1;
TYPE obj_array_t IS VARRAY(3) OF VARCHAR2(30);
-- Load this array with the dependent object classes to be retrieved...
obj_array obj_array_t := obj_array_t('OBJECT_GRANT', 'INDEX', 'TRIGGER');
BEGIN
-- Open a handle for tables in the current schema.
tableOpenHandle := dbms_metadata.open('TABLE');
-- Tell mdAPI to retrieve one table at a time. This call is not actually
-- necessary since 1 is the default... just showing the call.
dbms_metadata.set_count(tableOpenHandle, 1);
-- Retrieve tables whose name starts with 'PAYROLL'. When the filter is
-- 'NAME_EXPR', the filter value string must include the SQL operator. This
-- gives the caller flexibility to use LIKE, IN, NOT IN, subqueries, etc.
dbms_metadata.set_filter(tableOpenHandle, 'NAME_EXPR', 'LIKE ''PAYROLL%''');
-- Tell the mdAPI to parse out each table's schema and name separately so we
-- can use them to set up the calls to retrieve its dependent objects.
dbms_metadata.set_parse_item(tableOpenHandle, 'SCHEMA');
dbms_metadata.set_parse_item(tableOpenHandle, 'NAME');
-- Add the DDL transform so we get SQL creation DDL
tableTransHandle := dbms_metadata.add_transform(tableOpenHandle, 'DDL');
-- Tell the XSL stylesheet we don't want physical storage information (storage,
-- tablespace, etc), and that we want a SQL terminator on each DDL. Notice that
-- these calls use the transform handle, not the open handle.
dbms_metadata.set_transform_param(tableTransHandle,
'SEGMENT_ATTRIBUTES', FALSE);
dbms_metadata.set_transform_param(tableTransHandle,
'SQLTERMINATOR', TRUE);
-- Ready to start fetching tables. We use the FETCH_DDL interface (rather than
-- FETCH_XML or FETCH_CLOB). This interface returns a SYS.KU$_DDLS; a table of
-- SYS.KU$_DDL objects. This is a table because some object types return
-- multiple DDL statements (like types / pkgs which have create header and
-- body statements). Each KU$_DDL has a CLOB containing the 'CREATE foo'
-- statement plus a nested table of the parse items specified. In our case,
-- we asked for two parse items; Schema and Name. (NOTE: See admin/dbmsmeta.sql
-- for a more detailed description of these types)
LOOP
tableDDLs := dbms_metadata.fetch_ddl(tableOpenHandle);
EXIT WHEN tableDDLs IS NULL; -- Get out when no more payroll tables
-- In our case, we know there is only one row in tableDDLs (a KU$_DDLS tbl obj)
-- for the current table. Sometimes tables have multiple DDL statements;
-- eg, if constraints are applied as ALTER TABLE statements, but we didn't ask
-- for that option. So, rather than writing code to loop through tableDDLs,
-- we'll just work with the 1st row.
--
-- First, write the CREATE TABLE text to our output table then retrieve the
-- parsed schema and table names.
tableDDL := tableDDLs(1);
INSERT INTO ddl VALUES(tableDDL.ddltext, seqNo);
seqNo := seqNo + 1;
parsedItems := tableDDL.parsedItems;
-- Must check the name of the returned parse items as ordering isn't guaranteed
FOR i IN 1..2 LOOP
IF parsedItems(i).item = 'SCHEMA'
THEN
schemaName := parsedItems(i).value;
ELSE
tableName := parsedItems(i).value;
END IF;
END LOOP;
-- Now, we want to retrieve all the dependent objects defined on the current
-- table: indexes, triggers and grants. Since all 'dependent' object types
-- have BASE_OBJECT_NAME and BASE_OBJECT_SCHEMA in common as filter criteria,
-- we'll set up a loop to get all objects of the 3 types, just changing the
-- OPEN context in each pass through the loop. Transform parameters are
-- different for each object type, so we'll only use one that's common to all;
-- SQLTERMINATOR.
FOR i IN 1..3 LOOP
depObjOpenHandle := dbms_metadata.open(obj_array(i));
dbms_metadata.set_filter(depObjOpenHandle,'BASE_OBJECT_SCHEMA',
schemaName);
dbms_metadata.set_filter(depObjOpenHandle,'BASE_OBJECT_NAME',tableName);
-- Add the DDL transform and say we want a SQL terminator
indexTransHandle := dbms_metadata.add_transform(depObjOpenHandle, 'DDL');
dbms_metadata.set_transform_param(indexTransHandle,
'SQLTERMINATOR', TRUE);
-- Retrieve dependent object DDLs as CLOBs and write them to table DDL.
LOOP
depObjDDL := dbms_metadata.fetch_clob(depObjOpenHandle);
EXIT WHEN depObjDDL IS NULL;
INSERT INTO ddl VALUES(depObjDDL, seqNo);
seqNo := seqNo + 1;
END LOOP;
-- Free resources allocated for current dependent object stream.
dbms_metadata.close(depObjOpenHandle);
END LOOP; -- End of fetch dependent objects loop
END LOOP; -- End of fetch table loop
-- Free resources allocated for table stream and close output file.
dbms_metadata.close(tableOpenHandle);
RETURN;
END; -- of procedure get_payroll_tables
END payroll_demo;
/

PAYROLL_DEMO Output

This is the output obtained from executing the procedure, mddemo.payroll_demo.get_payroll_tables. The output is obtained by executing the following query as user mddemo: