Summary of DBMS_METADATA Subprograms

Table 30-1 provides a summary of DBMS_METADATA subprograms.

Table 30-1  DBMS_METADATA Subprograms

Subprogram	Description
OPEN Procedure	Specifies the type of object to be retrieved, the version of its metadata, and the object model.
SET_FILTER Procedure	Specifies restrictions on the objects to be retrieved, for example, the object name or schema.
SET_COUNT Procedure	Specifies the maximum number of objects to be retrieved in a single FETCH_xxx call.
GET_QUERY Procedure	Returns the text of the queries that are used by FETCH_xxx.
SET_PARSE_ITEM Procedure	Enables output parsing by specifying an object attribute to be parsed and returned.
ADD_TRANSFORM Procedure	Specifies a transform that FETCH_xxx applies to the XML representation of the retrieved objects.
SET_TRANSFORM_PARAM Procedure	Specifies parameters to the XSLT stylesheet identified by transform_handle.
FETCH_xxx Procedure	Returns metadata for objects meeting the criteria established by OPEN, SET_FILTER, SET_COUNT, ADD_TRANSFORM, and so on.
CLOSE Procedure	Invalidates the handle returned by OPEN and cleans up the associated state.
GET_XML and GET_DDL Functions	Returns the metadata for the specified object as XML or DDL.
GET_DEPENDENT_XML and GET_DEPENDENT_DDL Functions	Returns the metadata for one or more dependent objects, specified as XML or DDL.
GET_GRANTED_XML and GET_GRANTED_DDL Functions	Returns the metadata for one or more granted objects, specified as XML or DDL.

OPEN Procedure

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

Syntax

DBMS_METADATA.OPEN (
   object_type  IN VARCHAR2,
   version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model        IN VARCHAR2 DEFAULT 'ORACLE', )
RETURN NUMBER;

Parameters

Table 30-2 provides descriptions of the parameters for the OPEN procedure.

Table 30-2  Open() Parameters

Parameter	Description
object_type	The type of object to be retrieved. Table 30-3 lists the valid type names and their meanings. These object types will be supported for the ORACLE model of metadata (see model in this table) in Oracle9i. Future models may support a different set of object types. The "Attributes" column specifies some object type attributes. Schema objects, such as tables, belong to schemas. Named objects have unique names (if they are schema objects, the name is unique to the schema). Dependent objects, such as indexes, are defined with reference to a base schema object. Granted objects are granted or assigned to a user or role and therefore have a named grantee. These differences are relevant when choosing object selection criteria. See "SET_FILTER Procedure" for more information.
version	The version of metadata to be extracted. Database objects or attributes that are incompatible with the version will not be extracted. Legal values for this parameter are: COMPATIBLE (default)--the version of the metadata corresponds to the database compatibility level. Note that database compatibility must be set to 9.0.1 or higher. LATEST--the version of the metadata corresponds to the database version. A specific database version, for example, 9.0.1.
model	Specifies which view to use, because the API can support multiple views on the metadata. Only the ORACLE model is supported in Oracle9i.

Table 30-3 provides the name, meaning, attributes, and notes for the DBMS_METADATA package object types. In the attributes column, S represents a schema object, N represents a named object, D represents a dependent object, and G represents a granted object.

Table 30-3  DBMS_METADATA: Object Types

Type Name	Meaning	Attributes	Notes
ASSOCIATION	associate statistics	D
AUDIT	audits of SQL statements	DG	Modeled as dependent, granted object. The base object name is the statement audit option name (for example, ALTER SYSTEM). There is no base object schema. The grantee is the user or proxy whose statements are audited.
AUDIT_OBJ	audits of schema objects	D	None
CLUSTER	clusters	SN	None
COMMENT	comments	D	None
CONSTRAINT	constraints	SND	Does not include: primary key constraint for IOT column NOT NULL constraints certain REF SCOPE and WITH ROWID constraints for tables with REF columns
CONTEXT	application contexts	N	None
DB_LINK	database links	SN	Modeled as schema objects because they have owners. For public links, the owner is PUBLIC. For private links, the creator is the owner.
DEFAULT_ROLE	default roles	G	Granted to a user by ALTER USER
DIMENSION	dimensions	SN	None
DIRECTORY	directories	N	None
FUNCTION	stored functions	SN	None
INDEX	indexes	SND	None
INDEXTYPE	indextypes	SN	None
JAVA_SOURCE	Java sources	SN	None
LIBRARY	external procedure libraries	SN	None
MATERIALIZED_VIEW	materialized views	SN	None
MATERIALIZED_VIEW_LOG	materialized view logs	D	None
OBJECT_GRANT	object grants	DG	None
OPERATOR	operators	SN	None
OUTLINE	stored outlines	N	None
PACKAGE	stored packages	SN	By default, both package specification and package body are retrieved. See "SET_FILTER Procedure".
PACKAGE_SPEC	package specifications	SN	None
PACKAGE_BODY	package bodies	SN	None
PROCEDURE	stored procedures	SN	None
PROFILE	profiles	N	None
PROXY	proxy authentications	G	Granted to a user by ALTER USER
REF_CONSTRAINT	referential constraint	SND	None
ROLE	roles	N	None
ROLE_GRANT	role grants	G	None
ROLLBACK_SEGMENT	rollback segments	N	None
SEQUENCE	sequences	SN	None
SYNONYM	synonyms	See notes.	Private synonyms are schema objects. Public synonyms are not, but for the purposes of this API, their schema name is PUBLIC. The name of a synonym is considered to be the synonym itself. For example, in CREATE PUBLIC SYNONYM FOO FOR BAR, the resultant object is considered to have name FOO and schema PUBLIC.
SYSTEM_GRANT	system privilege grants	G	None
TABLE	tables	SN	None
TABLESPACE	tablespaces	N	None
TABLESPACE_QUOTA	tablespace quotas	G	Granted with ALTER USER
TRIGGER	triggers	SND	None
TRUSTED_DB_LINK	trusted links	N	None
TYPE	user-defined types	SN	By default, both type and type body are retrieved. See "SET_FILTER Procedure".
TYPE_SPEC	type specifications	SN	None
TYPE_BODY	type bodies	SN	None
USER	users	N	None
VIEW	views	SN	None
XMLSCHEMA	XML schema	SN	The object's name is its URL (which may be longer than 30 characters). Its schema is the user who registered it.

Returns

An opaque handle to the class of objects. This handle is used as input to SET_FILTER, SET_COUNT, ADD_TRANSFORM, GET_QUERY, SET_PARSE_ITEM, FETCH_xxx, and CLOSE.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• INVALID_OBJECT_PARAM. The version or model parameter was not valid for the object_type.

SET_FILTER Procedure

SET_FILTER specifies restrictions on the objects to be retrieved, for example, the object name or schema.

Syntax

DBMS_METADATA.SET_FILTER (
   handle  IN NUMBER,
   name    IN VARCHAR2,
   value   IN VARCHAR2);
DBMS_METADATA.SET_FILTER (        
   handle  IN NUMBER,
   name    IN VARCHAR2,
   value   IN BOOLEAN DEFAULT TRUE);

Parameters

Table 30-4 describes the parameters for the SET_FILTER procedure.

Table 30-4  SET_FILTER Parameters

Parameter	Description
handle	The handle returned from OPEN.
name	The name of the filter. For each filter, Table 30-5 lists the object_type it applies to, its name, its datatype (text or Boolean) and its meaning or effect (including its default value, if any).
value	The value of the filter.

Table 30-5 describes the object type, name, datatype, and meaning of the filters available with the SET_FILTER procedure.

Table 30-5  SET_FILTER: Filters

Object Type	Name	Datatype	Meaning
Named objects	NAME	text	Objects with this exact name are selected.
	NAME_EXPR	text	The filter value is the right-hand side of a SQL comparison, that is, a SQL comparison operator (=,!=, and so on) and the value compared against. The value must contain parentheses and quotation marks where appropriate. In PL/SQL and SQL*Plus, two single quotes (not a double quote) are needed to represent an apostrophe. For example: 'IN (''DEPT'',''EMP'')' The filter value is combined with the object attribute corresponding to the object name to produce a WHERE condition in the query that fetches the objects. In the preceding example, objects named DEPT and EMP are retrieved. By default, all named objects of object_type are selected.
Schema objects	SCHEMA	text	Objects in this schema are selected.
	SCHEMA_EXPR	text	The filter value is the right-hand side of a SQL comparison. The filter value is combined with the object attribute corresponding to the object schema to produce a WHERE condition in the query that fetches the objects. See NAME_EXPR for syntax details. Default: - if BASE_OBJECT_SCHEMA is specified, then objects in that schema are selected; - otherwise, objects in the current schema are selected. See "Security" .
PACKAGE, TYPE	SPECIFICATION	Boolean	If TRUE, retrieve the package or type specification. Defaults to TRUE.
	BODY	Boolean	If TRUE, retrieve the package or type body. Defaults to TRUE.
TABLE	TABLESPACE	text	Objects in this tablespace (or having a partition in this tablespace) are selected.
	TABLESPACE_EXPR	text	The filter value is the right-hand side of a SQL comparison. The filter value is combined with the attribute corresponding to the object's tablespace (or in the case of a partitioned table, the partition's tablespaces) to produce a WHERE condition in the query that fetches the objects. See NAME_EXPR for syntax details. By default, objects in all tablespaces are selected.
Dependent Objects	BASE_OBJECT_NAME	text	Objects are selected that are defined or granted on objects with this name. Specify SCHEMA for triggers on schemas. Specify DATABASE for database triggers. Column-level comments cannot be selected by column name; the base object name must be the name of the table, view, or materialized view containing the column.
	BASE_OBJECT_SCHEMA	text	Objects are selected that are defined or granted on objects in this schema. If BASE_OBJECT_NAME is specified with a value other than SCHEMA or DATABASE, this defaults to the current schema.
INDEX, TRIGGER	SYSTEM_GENERATED	Boolean	If TRUE, select indexes or triggers even if they are system-generated. If FALSE, omit system-generated indexes or triggers. Defaults to TRUE.
Granted Objects	GRANTEE	text	Objects are selected that are granted to this user or role. Specify PUBLIC for grants to PUBLIC.
OBJECT_GRANT	GRANTOR	text	Object grants are selected that are granted by this user.
SYNONYM, JAVA_SOURCE, XMLSCHEMA	LONGNAME	text	A name longer than 30 characters. Objects with this exact name are selected. If the object name is 30 characters or less, the NAME filter must be used.
	LONGNAME_EXPR	text	The filter value is the right-hand side of a SQL comparison. The filter value is combined with the attribute corresponding to the object's long name to produce a WHERE condition in the query that fetches the objects. See NAME_EXPR for syntax details. By default, no filtering is done on the long name of an object.
All objects	CUSTOM_FILTER	text	The text of a WHERE condition. The condition is appended to the query that fetches the objects. By default, no custom filter is used. The other filters are intended to meet the needs of the majority of users. Use CUSTOM_FILTER when no defined filters exists for your purpose. Of necessity such a filter depends on the detailed structure of the UDTs and views used in the query that are defined in admin/catmeta.sql. Because filters may change from version to version, upward compatibility is not guaranteed.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• INVALID_OPERATION. SET_FILTER was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to SET_FILTER for the current OPEN context are permitted.
• INCONSISTENT_ARGS. The filter name is not valid for the object type associated with the OPEN context, or the filter value is the wrong datatype.

Security

With SET_FILTER, you can specify the schema of objects to be retrieved, but security considerations may override this specification. If the caller is SYS or has SELECT_CATALOG_ROLE, then any object can be retrieved; otherwise, only the following can be retrieved:

• Schema objects owned by the caller
• Public synonyms
• System privileges granted to the caller or to PUBLIC
• Grants on objects for which the caller is owner, grantor or grantee (either explicitly or as PUBLIC).

If you request objects that you are not privileged to retrieve, no exception is raised; the object is not retrieved, as if it did not exist.

Usage Notes

• You can use the same text expression filter multiple times with different values. All the filter conditions will be applied to the query. For example, to get objects with names between Felix and Oscar, do the following:

  dbms_metadata.set_filter(handle,'NAME_EXPR','>=''FELIX''');
  dbms_metadata.set_filter(handle,'NAME_EXPR','<=''OSCAR''');
  
  

• For dependent objects such as triggers, grants, and indexes, the following conditions apply:
  • When connected as a nonprivileged user -- If BASE_OBJECT_NAME is specified as a filter, BASE_OBJECT_SCHEMA defaults to the current schema:

        dbms_metadata.set_filter(h,'BASE_OBJECT_NAME','EMP');
    
    

  • When connected as a privileged user with SELECT_CATALOG_ROLE -- The schema defaults to BASE_OBJECT_SCHEMA if specified; otherwise it defaults to the current schema. For example, to see all indexes in SCOTT that are defined on SCOTT.EMP, the filters are:

       dbms_metadata.set_filter(h,'BASE_OBJECT_NAME','EMP');
       dbms_metadata.set_filter(h,'BASE_OBJECT_SCHEMA','SCOTT');
    

    To see indexes in other schemas:

       dbms_metadata.set_filter(h,'SCHEMA_EXPR','LIKE ''%''');
    

  Some indexes and triggers are system generated (such as indexes used to enforce unique constraints). Set the SYSTEM_GENERATED filter to FALSE so that you do not retrieve them.

SET_COUNT Procedure

SET_COUNT specifies the maximum number of objects to be retrieved in a single FETCH_xxx call. By default, each call to FETCH_xxx returns one object. With SET_COUNT, you can override this default. If FETCH_xxx is called from a client, specifying a count value greater than 1 can result in fewer server round trips and, therefore, improved performance. Note that the procedure stops when NULL is returned, but not if less than the maximum number of objects is returned.

Syntax

DBMS_METADATA.SET_COUNT (
   handle IN NUMBER,
   value  IN NUMBER);

Parameters

Table 30-6 describes the parameters for the SET_COUNT procedure.

Table 30-6  SET_COUNT Parameters

Parameter	Description
handle	The handle returned from OPEN.
value	The number of objects to retrieve.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• INVALID_OPERATION. SET_COUNT was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to SET_COUNT for the current OPEN context are permitted.

GET_QUERY Procedure

GET_QUERY returns the text of the queries that are used by FETCH_xxx. This function assists in debugging.

Syntax

DBMS_METADATA.GET_QUERY (
   handle  IN NUMBER)
RETURN VARCHAR2;

Parameters

Table 30-7 describes the parameters for the GET_QUERY procedure.

Table 30-7  GET_QUERY Parameters

Parameter	Description
handle	The handle returned from OPEN.

Returns

The text of the queries that will be used by FETCH_xxx.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for the handle parameter.

SET_PARSE_ITEM Procedure

SET_PARSE_ITEM enables output parsing by specifying an object attribute to be parsed and returned. It should only be used in conjunction with FETCH_DDL.

Syntax

DBMS_METADATA.SET_PARSE_ITEM (
   handle   IN NUMBER,
   name     IN VARCHAR2);

Parameters

Table 30-8 describes the parameters for the SET_PARSE_ITEM procedure.

Table 30-8  SET_PARSE_ITEM Parameters

Parameter	Description
handle	The handle returned from OPEN.
name	The name of the object attribute to be parsed and returned. See Table 30-9 for the attribute object type, name, and meaning.

Table 30-9 describes the object type, name, and meaning of the items available in the SET_PARSE_ITEM procedure.

Table 30-9  SET_PARSE_ITEM: Parse Items

Object Type	Name	Meaning
All objects	VERB	For every row in the sys.ku$_ddls nested table returned by fetch_ddl the verb in the corresponding ddlText is returned. If the ddlText is a SQL DDL statement, then the SQL verb (for example, CREATE, GRANT, AUDIT) is returned. If the ddlText is a procedure call (for example., DBMS_RLS.ADD_POLICY_CONTEXT) then the package.procedure-name is returned.
	OBJECT_TYPE	If the ddlText is a SQL DDL statement whose verb is CREATE or ALTER, the object type as used in the DDL statement is returned, for example, TABLE, PACKAGE BODY, and so on. Otherwise, an object type name from Table 30-3, " DBMS_METADATA: Object Types" is returned.
	SCHEMA	The object schema is returned. If the object is not a schema object, NULL is returned.
	NAME	The object name is returned. If the object is not a named object, NULL is returned.
TABLE, INDEX	TABLESPACE	The tablespace name of the table or index is returned.
TRIGGER	ENABLE	If the trigger is enabled, ENABLE is returned. If the trigger is disabled, DISABLE is returned.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• INVALID_OPERATION. SET_PARSE_ITEM was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to SET_PARSE_ITEM are permitted.
• INCONSISTENT_ARGS. The attribute name is not valid for the object type associated with the OPEN context.

Usage Notes

By default fetch_ddl returns object metadata as creation DDL. By calling SET_PARSE_ITEM, you can request that individual attributes of the object be returned also, to avoid the tedious process of parsing SQL text. This is useful when fetching objects based on the value of a returned object, for example, fetching indexes for a returned table.

You can call SET_PARSE_ITEM multiple times to ask for multiple items to be parsed and returned. Parsed items are returned in the sys.ku$_parsed_items nested table. An example of using sys.ku$_parsed_items is shown within Example: Retrieving Payroll Tables and their Indexes as DDL.

ADD_TRANSFORM Procedure

ADD_TRANSFORM specifies a transform that FETCH_xxx applies to the XML representation of the retrieved objects. It is possible to add more than one transform.

Syntax

DBMS_METADATA.ADD_TRANSFORM (
   handle    IN NUMBER,
   name      IN VARCHAR2,
   encoding  IN VARCHAR2 DEFAULT NULL)
RETURN NUMBER;

Parameters

Table 30-10 describes the parameters for the ADD_TRANSFORM procedure.

Table 30-10  ADD_TRANSFORM Parameters

Parameters	Description
handle	The handle returned from OPEN.
name	The name of the transform. If the name is DDL, creation DDL will be generated using XSLT stylesheets stored within the Oracle dictionary. If the name contains a period (.), colon (:) or forward slash (/), it is interpreted as the URL of a user-supplied XSLT stylesheet. See Oracle9i XML Database Developer's Guide - Oracle XML DB.
encoding	The name of NLS character set (see National Language Support Guide) in which the stylesheet pointed to by name is encoded. This is only valid if the name is a URL. If left NULL and the URL is external to the database (e.g, /usr/williams/xsl/mystylesheet.xsl), UTF-8 encoding is assumed. If left NULL and the URL is internal to the database, that is, it begins with /oradb/, then the database character set is assumed to be the encoding.

Returns

An opaque handle to the transform. This handle is used as input to SET_TRANSFORM_PARAM. Note that this handle is different from the handle returned by OPEN; it refers to the transform, not the set of objects to be retrieved.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• INVALID_OPERATION. ADD_TRANSFORM was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to ADD_TRANSFORM for the current OPEN context are permitted.

Usage Notes

With no transforms added, objects are returned by default as XML documents. You call ADD_TRANSFORM to specify an XSLT stylesheet to transform the returned documents.

You can call ADD_TRANSFORM more than once to apply multiple transforms to the returned XML documents. FETCH_xxx will apply the transforms in the order in which they were specified, the output of the first transform being used as input to the second, and so on.

The encoding parameter must be specified if either of the following is true:

• The XSL stylesheet pointed to by an external URL is encoded in a character set that is not a subset of UTF-8
• The XSL stylesheet pointed to by a database-internal URL is encoded in a character set that is not a subset of the database character set.

An example of the latter might be if the database-internal URL pointed to an NCLOB or NVARCHAR column. Normally, this need not be specified, although explicitly setting it to US7ASCII (if applicable) results in slightly better XML parsing performance.

SET_TRANSFORM_PARAM Procedure

SET_TRANSFORM_PARAM specifies parameters to the XSLT stylesheet identified by transform_handle.Use it to modify or customize the output of the transform.

Syntax

DBMS_METADATA.SET_TRANSFORM_PARAM (
   transform_handle  IN NUMBER,
   name                    IN VARCHAR2,
   value                   IN VARCHAR2);
DBMS_METADATA.SET_TRANSFORM_PARAM (
   transform_handle  IN NUMBER,
   name                    IN VARCHAR2,
   value                   IN BOOLEAN DEFAULT TRUE);

Parameters

Table 30-11 describes the parameters for the SET_TRANSFORM_PARAM procedure.

Table 30-11  SET_TRANSFORM_PARAM Parameters

Parameters	Description
transform_handle	Either (1) the handle returned from ADD_TRANSFORM, or (2) the enumerated constant SESSION_TRANSFORM that designates the DDL transform for the whole session. Note that the handle returned by OPEN is not a valid transform handle.
name	The name of the parameter. Table 30-12 lists the transform parameters defined for the DDL transform, specifying the object_type it applies to, its datatype (in this case, always Boolean) and its meaning or effect (including its default value, if any).
value	The value of the transform.

Table 30-12 describes the object type, name, datatype, and meaning of the parameters for the DDL transform in the SET_TRANSFORM_PARAM procedure.

Table 30-12  SET_TRANSFORM_PARAM: Transform Parameters for the DDL Transform

Object Type	Name	Datatype	Meaning
All objects	PRETTY	Boolean	If TRUE, format the output with indentation and line feeds. Defaults to TRUE.
	SQLTERMINATOR	Boolean	If TRUE, append a SQL terminator (; or /) to each DDL statement. Defaults to FALSE.
TABLE	SEGMENT_ATTRIBUTES	Boolean	If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.
	STORAGE	Boolean	If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
	TABLESPACE	Boolean	If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
TABLE	CONSTRAINTS	Boolean	If TRUE, emit all non-referential table constraints. Defaults to TRUE.
	REF_CONSTRAINTS	Boolean	If TRUE, emit all referential constraints (foreign key and scoped refs). Defaults to TRUE.
	CONSTRAINTS_AS_ALTER	Boolean	If TRUE, emit table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements. If FALSE, specify table constraints as part of the CREATE TABLE statement. Defaults to FALSE. Requires that CONSTRAINTS be TRUE.
	OID	Boolean	If TRUE, emit the OID clause for object tables. Defaults to FALSE.
	SIZE_BYTE_KEYWORD	Boolean	If TRUE, emit the BYTE keyword as part of the size specification of CHAR and VARCHAR2 columns that use byte semantics. If FALSE, omit the keyword. Defaults to FALSE.
INDEX	SEGMENT_ATTRIBUTES	Boolean	If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.
	STORAGE	Boolean	If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
	TABLESPACE	Boolean	If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.
TYPE	SPECIFICATION	Boolean	If TRUE, emit the type specification. Defaults to TRUE.
	BODY	Boolean	If TRUE, emit the type body. Defaults to TRUE.
PACKAGE	SPECIFICATION	Boolean	If TRUE, emit the package specification. Defaults to TRUE.
	BODY	Boolean	If TRUE, emit the package body. Defaults to TRUE.
VIEW	FORCE	Boolean	If TRUE, use the FORCE keyword in the CREATE VIEW statement. Defaults to TRUE.
All objects	DEFAULT	Boolean	Calling SET_TRANSFORM_PARAM with this parameter set to TRUE has the effect of resetting all parameters for the transform to their default values. Setting this FALSE has no effect. There is no default.
	INHERIT	Boolean	If TRUE, inherits session-level parameters. Defaults to FALSE. If an application calls ADD_TRANSFORM to add the DDL transform, then by default the only transform parameters that apply are those explicitly set for that transform handle. This has no effect if the transform handle is the session transform handle.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• INVALID_OPERATION. SET_TRANSFORM_PARAM was called after the first call to FETCH_xxx for the OPEN context. After the first call to FETCH_xxx is made, no further calls to SET_TRANSFORM_PARAM are permitted.
• INCONSISTENT_ARGS. The transform parameter name is not valid for the object type associated with the OPEN context.

Usage Notes

XSLT allows parameters to be passed to stylesheets. You call SET_TRANSFORM_PARAM to specify the value of a parameter to be passed to the stylesheet identified by transform_handle. The most general way to specify stylesheet parameter values is as text strings. However, for the DDL transform, it is convenient to expose some parameters as Booleans. Consequently, two variants of the procedure are provided.

The GET_DDL function allows the casual browser to extract the creation DDL for an object. So that you can specify transform parameters, this package defines an enumerated constant SESSION_TRANSFORM as the handle of the DDL transform at the session level. You can call SET_TRANSFORM_PARAM using DBMS_METADATA.SESSION_TRANSFORM as the transform handle to set transform parameters for the whole session. GET_DDL inherits these parameters when it invokes the DDL transform.

FETCH_xxx Procedure

FETCH_xxx returns metadata for objects meeting the criteria established by OPEN, SET_FILTER, SET_COUNT, ADD_TRANSFORM, and so on. See "Usage Notes" for the variants.

Syntax

The FETCH functions and procedures are:

DBMS_METADATA.FETCH_XML (
   handle  IN NUMBER) 
RETURN sys.XMLType;       

DBMS_METADATA.FETCH_DDL (
   handle  IN NUMBER)
RETURN sys.ku$_ddls;

The following types comprise the return nested table type sys.ku$_ddls:

TYPE sys.ku$_parsed_item AS OBJECT (
  item       VARCHAR2(30),
  value      VARCHAR2(4000),
  object-row NUMBER );
TYPE sys.ku$_parsed_items IS TABLE OF sys.ku$_parsed_item;
TYPE sys.ku$_ddl AS OBJECT (
  ddlText CLOB,
  parsedItems sys.ku$_parsed_items ); 
TYPE sys.ku$_ddls IS TABLE OF sys.ku$_ddl;

DBMS_METADATA.FETCH_CLOB (
   handle  IN NUMBER) 
RETURN CLOB;      
DBMS_METADATA.FETCH_CLOB (
   handle  IN NUMBER,
   doc     IN OUT NOCOPY CLOB);       

Parameters

Table 30-13 describes the parameters for the FETCH_xxx procedure.

Table 30-13  FETCH_xxx Parameters

Parameters	Description
handle	The handle returned from OPEN.
doc (procedure fetch_clob)	The metadata for the objects or NULL if all objects have been returned.

Returns

The metadata for the objects or NULL if all objects have been returned.

Exceptions

Most exceptions raised during execution of the query are propagated to the caller. Also, the following exceptions may be raised:

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• INCONSISTENT_OPERATION. Either (1) FETCH_XML was called when the DDL transform had been specified, or (2) FETCH_DDL was called when the DDL transform had not been specified.

Usage Notes

These functions and procedures return metadata for objects meeting the criteria established by calls to OPEN, SET_FILTER, SET_COUNT, ADD_TRANSFORM, and so on. Each call to FETCH_xxx returns the number of objects specified by SET_COUNT (or less, if fewer objects remain in the underlying cursor) until all objects have been returned. After the last object is returned, subsequent calls to FETCH_xxx return NULL and cause the stream created by OPEN to be transparently closed.

There are several different FETCH_xxx functions and procedures:

• FETCH_XML returns the XML metadata for an object as an XMLType. It assumes that if any transform has been specified, the transform will produce an XML document. In particular, it assumes that the DDL transform has not been specified.
• FETCH_DDL returns the creation DDL in a sys.ku$_ddls nested table. It assumes that the DDL transform has been specified. Each row of the sys.ku$_ddls nested table contains a single DDL statement in the ddlText column; if requested, parsed items for the DDL statement will be returned in the parsedItems column. Multiple DDL statements may be returned under the following circumstances:
  • When you call SET_COUNT to specify a count greater than 1
  • When an object is transformed into multiple DDL statements. For example, A TYPE object can be transformed into both CREATE TYPE and CREATE TYPE BODY statements. A TABLE object can be transformed into a CREATE TABLE, zero or more CREATE INDEX statements, and zero or more ALTER TABLE statements.
• FETCH_CLOB simply returns the object, transformed or not, as a CLOB.

FETCH_CLOB comes in both function and procedure variants. The procedure variant returns the object by reference in an IN OUT NOCOPY parameter.

All LOBs returned by FETCH_xxx are temporary LOBs. You must free the LOB. The same applies to the XMLType object.

If SET_PARSE_ITEM was called, FETCH_DDL returns attributes of the DDL statement in a sys.ku$_parsed_items nested table, which is a column in the returned sys.ku$_ddls nested table. Each row of the sys.ku$_parsed_items nested table corresponds to an item specified by SET_PARSE_ITEM and contains the following columns:

• item--The name of the attribute as specified in the name parameter to SET_PARSE_ITEM.
• value--The attribute value, or NULL if the attribute is not present in the DDL statement.
• object-row--For future use.

The order of the rows is undetermined; to find a particular item you must search the table for a match on item.

If SET_PARSE_ITEM was not called, NULL is returned as the value of the sys.ku$_parsed_items nested table.

When Variants of FETCH_xxx Are Called

It is expected that the same variant of FETCH_xxx will be called for all objects selected by OPEN, that is, that programs will not intermix calls to FETCH_XML, FETCH_DDL, and FETCH_CLOB using the same OPEN handle. The effect of calling different variants is undefined; it may not do what you expect.

CLOSE Procedure

CLOSE invalidates the handle returned by OPEN and cleans up the associated state.

Syntax

DBMS_METADATA.CLOSE (
   handle  IN NUMBER);

Parameters

Table 30-14 describes the parameters for the CLOSE procedure.

Table 30-14  CLOSE Parameters

Parameter	Description
handle	The handle returned from OPEN.

Exceptions

• INVALID_ARGVAL. The value for the handle parameter is NULL or invalid.

Usage Notes

You can prematurely terminate the stream of objects established by OPEN.

• If a call to FETCH_xxx returns NULL, indicating no more objects, a call to CLOSE is made transparently. In this case, you can still call CLOSE on the handle and not get an exception. (The call to CLOSE is not required.)
• If you know that only one specific object will be returned, you should explicitly call CLOSE after the single FETCH_xxx call to free resources held by the handle.

Example: Retrieving Payroll Tables and their Indexes as DDL

This example retrieves the creation DDL for all tables in the current schema whose 
names begin with PAYROLL. For each table it also returns the creation DDL for the 
indexes defined on the table. The returned DDL is written to an output file.

CREATE OR REPLACE PACKAGE dbms_metadata_example AS

   PROCEDURE get_payroll_tables;
END;
/
CREATE OR REPLACE PACKAGE BODY dbms_metadata_example AS

-- Global Variables

fileHandle   UTL_FILE.FILE_TYPE;

-- Exception initialization

file_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT(file_not_found, -1309);

-- Package-private routine to write a CLOB to an output file.

  PROCEDURE write_lob(doc IN CLOB) IS

     outString        varchar2(32760);
     cloblen             number;
     offset               number := 1;
     amount               number;

BEGIN
  cloblen := dbms_lob.getlength(doc);
  WHILE cloblen > 0
  LOOP
    IF cloblen > 32760 THEN
      amount := 32760;
    ELSE
      amount := cloblen;
    END IF;
    outString := dbms_lob.substr(doc, amount, offset);
    utl_file.put(fileHandle, outString);
    utl_file.fflush(fileHandle);
    offset  := offset  + amount;
    cloblen := cloblen - amount;
  END LOOP;
  RETURN;
END;

-- Public routines

-- GET_PAYROLL_TABLES: Fetch DDL for payroll tables and their indexes.

PROCEDURE  get_payroll_tables IS

tableOpenHandle              NUMBER;
indexOpenHandle              NUMBER;
tableTransHandle        NUMBER;
indexTransHandle        NUMBER;
schemaName                    VARCHAR2(30);
tableName                  VARCHAR2(30);
tableDDLs                  sys.ku$_ddls;
tableDDL                    sys.ku$_ddl;
parsedItems                  sys.ku$_parsed_items;
indexDDL                    CLOB;

BEGIN

--  open the output file... note that the 1st param. (dir. path) must be
--  included in the database's UTL_FILE_DIR init. parameter.
--
  BEGIN
    fileHandle := utl_file.fopen('/private/xml', 'ddl.out', 'w', 32760);
  EXCEPTION
    WHEN OTHERS THEN
        RAISE file_not_found;
  END;

-- Open a handle for tables in the current schema.
  tableOpenHandle := dbms_metadata.open('TABLE');

-- Call 'set_count' to request retrieval of one table at a time. 
-- This call is not actually necessary because 1 is the default.
  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, and so on.
  dbms_metadata.set_filter(tableOpenHandle, 'NAME_EXPR', 'LIKE ''PAYROLL%''');

-- Tell Metadata API to parse out each table's schema and name separately
-- so we can use them to set up the calls to retrieve its indexes.
  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 TABLE'
-- statement plus a nested table of the parse items specified. In our case,
-- we asked for two parse items; Schema and Name.

  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,
-- for example, 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 file, then retrieve the
-- parsed schema and table names.
    tableDDL := tableDDLs(1);
    write_lob(tableDDL.ddltext);
    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;
 
-- Then use the schema and table names to set up a 2nd stream for retrieval of
-- the current table's indexes.
-- (Note that we don't have to specify a SCHEMA filter for the indexes,
-- Because SCHEMA defaults to the value of BASE_OBJECT_SCHEMA.)
    indexOpenHandle := dbms_metadata.open('INDEX');
    dbms_metadata.set_filter(indexOpenHandle,'BASE_OBJECT_SCHEMA',schemaName);
    dbms_metadata.set_filter(indexOpenHandle,'BASE_OBJECT_NAME',tableName);

-- Add the DDL transform and set the same transform options we did for tables
    indexTransHandle := dbms_metadata.add_transform(indexOpenHandle, 'DDL');
    dbms_metadata.set_transform_param(indexTransHandle,
                       'SEGMENT_ATTRIBUTES', FALSE);
    dbms_metadata.set_transform_param(indexTransHandle,
                       'SQLTERMINATOR', TRUE);

-- Retrieve index DDLs as CLOBs and write them to the output file.
    LOOP
      indexDDL := dbms_metadata.fetch_clob(indexOpenHandle);
      EXIT WHEN indexDDL IS NULL;
      write_lob(indexDDL);
    END LOOP;

-- Free resources allocated for index stream.
    dbms_metadata.close(indexOpenHandle);

  END LOOP;

-- Free resources allocated for table stream and close output file.
  dbms_metadata.close(tableOpenHandle);
  utl_file.fclose(fileHandle);
  RETURN;

END;  -- of procedure get_payroll_tables

END dbms_metadata_example;
/

GET_XML and GET_DDL Functions

GET_XML and GET_DDL return the metadata for the specified object as XML or DDL.

Syntax

DBMS_METADATA.GET_XML (
   object_type  IN VARCHAR2,
   name         IN VARCHAR2,
   schema       IN VARCHAR2 DEFAULT NULL,
   version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model        IN VARCHAR2 DEFAULT 'ORACLE',
   transform    IN VARCHAR2 DEFAULT NULL)
RETURN CLOB;

DBMS_METADATA.GET_DDL (                           
   object_type  IN VARCHAR2,
   name         IN VARCHAR2,
   schema       IN VARCHAR2 DEFAULT NULL,
   version      IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model        IN VARCHAR2 DEFAULT 'ORACLE',
   transform    IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

Parameters

Table 30-15 describes the parameters for the GET_xxx function.

Table 30-15  GET_xxx Parameters 

Parameter	Description
object_type	The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter.
name	An object name (case-sensitive). If object_type is SYNONYM and name is longer than 30 characters, then name will be treated as a LONGNAME filter. See Table 30-5.
schema	A schema name (case sensitive). The default is the current schema if object_type refers to a schema object; otherwise the default is NULL.
version	The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.
model	The object model to use. This parameter takes the same values as the OPEN model parameter.
transform	The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_XML this must not be DDL.

Returns

The metadata for the specified object as XML or DDL.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• OBJECT_NOT_FOUND. The specified object was not found in the database.

Usage Notes

These functions provide a simple way to return the metadata for a single object. Conceptually each GET_xxx call is comprised of an OPEN, one or two SET_FILTER calls, optionally an ADD_TRANSFORM, a FETCH_xxx and a CLOSE. The object_type parameter has the same semantics as in OPEN. The schema and name parameters are used for filtering. If a transform is specified, schema-level transform flags are inherited.

This function can only be used to fetch named objects. It cannot be used to fetch objects of type OBJECT_GRANT or SYSTEM_GRANT. To fetch these objects, use the programmatic interface.

Example 1. Fetching the XML Representation of SCOTT.EMP

To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query.

set pagesize 0
set long 90000
SELECT DBMS_METADATA.GET_XML
  (
   'TABLE','EMP','SCOTT')
    FROM DUAL;

Example 2. Fetching the DDL for all Complete Tables in the Current Schema, Filtering Out Nested Tables and Overflow Segments

This example fetches the DDL for all "complete" tables in the current schema, filtering out nested tables and overflow segments. The example uses SET_TRANSFORM_PARAM (with the handle value = DBMS_METADATA.SESSION_TRANSFORM meaning "for the current session") to specify that storage clauses are not to be returned in the SQL DDL. Afterwards, the example resets the session-level parameters to their defaults. (To generate complete, uninterrupted output, set the PAGESIZE to 0 and set LONG to some large number, as shown, before executing your query.)

set pagesize 0
set long 90000
execute DBMS_METADATA.SET_TRANSFORM_PARAM(
  DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_ALL_TABLES u
     WHERE u.nested='NO' 
     AND (u.iot_type is null or u.iot_type='IOT');
execute DBMS_METADATA.SET_TRANSFORM_PARAM(
   DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');

GET_DEPENDENT_XML and GET_DEPENDENT_DDL Functions

The GET_DEPENDENT_XML and GET_DEPENDENT_DDL functions return metadata for one or more dependent objects.

Syntax

DBMS_METADATA.GET_DEPENDENT_XML (
   object_type          IN VARCHAR2,
   base_object_name     IN VARCHAR2,
   base_object_schema   IN VARCHAR2 DEFAULT NULL,
   version              IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model                IN VARCHAR2 DEFAULT 'ORACLE',
   transform            IN VARCHAR2 DEFAULT NULL,
   object_count         IN NUMBER   DEFAULT 10000)
RETURN CLOB;

DBMS_METADATA.GET_DEPENDENT_DDL (
   object_type          IN VARCHAR2,
   base_object_name     IN VARCHAR2,
   base_object_schema   IN VARCHAR2 DEFAULT NULL,
   version              IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model                IN VARCHAR2 DEFAULT 'ORACLE',
   transform            IN VARCHAR2 DEFAULT DDL,
   object_count         IN NUMBER   DEFAULT 10000)
RETURN CLOB;

Parameters

Table 30-16 describes the parameters for the GET_DEPENDENT_xxx function.

Table 30-16  GET_DEPENDENT_xxx Parameters

Parameter	Description
object_type	The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter. See Table 30-2, " Open() Parameters". The attributes of the object type must be appropriate to the function. For GET_DEPENDENT_xxx it must be a dependent object.
base_object_name	The base object name, which will be used internally in a BASE_OBJECT_NAME filter.
base_object_schema	The base object schema, which will be used internally in a BASE_OBJECT_SCHEMA filter. The default is the current user.
version	The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.
model	The object model to use. This parameter takes the same values as the OPEN model parameter.
transform	The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_DEPENDENT_XML this must not be DDL.
object_count	The maximum number of objects to return.

Returns

The metadata for the objects as XML or DDL.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• OBJECT_NOT_FOUND. The specified object was not found in the database.

Usage Notes

The GET_DEPENDENT_xxx functions allow you to fetch metadata for dependent objects with a single call. For some object types, you can use more than one function. For example, you can use GET_xxx to fetch an index by its name or you can use GET_DEPENDENT_xxx to fetch the same index by specifying the table on which it is defined.

An arbitrary number of dependent objects may match the input criteria for GET_DEPENDENT_xxx. You can specify an object count when fetching these objects, although the default count of 10000 should usually be adequate.

If the DDL transform is specified, session-level transform parameters are inherited.

If you invoke these functions from SQL*Plus, you should use the SET LONG and SET PAGESIZE commands to generate complete, uninterrupted output.

Example: Fetch the DDL For All Object Grants On SCOTT.EMP

SQL> SET PAGESIZE 0
SQL> SET LONG 90000
SQL> SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',
> 'EMP', 'SCOTT') FROM DUAL;

GET_GRANTED_XML and GET_GRANTED_DDL Functions

The GET_GRANTED_XML and GET_GRANTED_DDL functions return metadata for one or more granted objects.

Syntax

DBMS_METADATA.GET_GRANTED_XML (
   object_type          IN VARCHAR2,
   grantee              IN VARCHAR2 DEFAULT NULL,
   version              IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model                IN VARCHAR2 DEFAULT 'ORACLE',
   transform            IN VARCHAR2 DEFAULT NULL,
   object_count         IN NUMBER   DEFAULT 10000)
RETURN CLOB;

DBMS_METADATA.GET_GRANTED_DDL (
   object_type          IN VARCHAR2,
   grantee              IN VARCHAR2 DEFAULT NULL,
   version              IN VARCHAR2 DEFAULT 'COMPATIBLE',
   model                IN VARCHAR2 DEFAULT 'ORACLE',
   transform            IN VARCHAR2 DEFAULT DDL,
   object_count         IN NUMBER   DEFAULT 10000)
RETURN CLOB;

Parameters

Table 30-17 describes the parameters for the GET_GRANTED_xxx function.

Table 30-17  GET_GRANTED_xxx Parameters

Parameter	Description
object_type	The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter. See Table 30-2, " Open() Parameters". The attributes of the object type must be appropriate to the function. For GET_GRANTED_xxx it must be a granted object
grantee	The grantee. It will be used internally in a GRANTEE filter. The default is the current user.
version	The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.
model	The object model to use. This parameter takes the same values as the OPEN model parameter.
transform	The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_GRANTED_XML this must not be DDL.
object_count	The maximum number of objects to return.

Returns

The metadata for the objects as XML or DDL.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• OBJECT_NOT_FOUND. The specified object was not found in the database.

Usage Notes

The GET_GRANTED_xxx functions allow you to fetch metadata for dependent objects with a single call.

An arbitrary number of granted objects may match the input criteria for GET_GRANTED_xxx. You can specify an object count when fetching these objects, although the default count of 10000 should usually be adequate.

If the DDL transform is specified, session-level transform parameters are inherited.

If you invoke these functions from SQL*Plus, you should use the SET LONG and SET PAGESIZE commands to generate complete, uninterrupted output.

Example: Fetch the DDL For All System Grants Granted to SCOTT

SQL> SET PAGESIZE 0
SQL> SET LONG 90000
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
> FROM DUAL;