50
DBMS_METADATA

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

This chapter contains the following topics:

• Using DBMS_METADATA
  • Overview
  • Security Model
  • Types
  • Rules and Limits
  • Organization of Subprograms
    • Subprograms for Retrieval of Multiple Objects from the Database
    • Subprograms for Submission of XML to the Database
• Summary of DBMS_METADATA Subprograms

Using DBMS_METADATA

• Overview
• Security Model
• Types
• Rules and Limits
• Organization of Subprograms

Overview

You can use the DBMS_METADATA package to retrieve metadata and also to submit XML.

• Retrieving Metadata
• Submitting XML

Retrieving Metadata

If you are retrieving metadata, you can specify:

• The kind of object to be retrieved. This can be either a particular object type (such as a table, index, or procedure) or a heterogeneous collection of object types that form a logical unit (such as a database export or schema export).
• Optional selection criteria, such as owner or name.
• Parse items (attributes of the returned objects to be parsed and returned separately).
• Optional transformations on the output, implemented by XSLT scripts. By default the output is represented in XML, but you can specify transformations (into SQL DDL, for example), which are implemented by XSLT (Extensible Stylesheet Language Transformation) stylesheets stored in the database or externally.

DBMS_METADATA provides the following retrieval interfaces:

• For programmatic use: OPEN, SET_FILTER, SET_COUNT, GET_QUERY, SET_PARSE_ITEM, ADD_TRANSFORM, SET_TRANSFORM_PARAM,SET_REMAP_PARAM, FETCH_xxx, and CLOSE retrieve multiple objects.
• For use in SQL queries and for browsing: GET_XML and GET_DDL return metadata for a single named object. The GET_DEPENDENT_XML, GET_DEPENDENT_DDL, GET_GRANTED_XML, and GET_GRANTED_DDL interfaces return metadata for one or more dependent or granted objects. These procedures do not support heterogeneous object types.

Submitting XML

If you are submitting XML, you specify:

• The type of object
• Optional transform parameters to modify the object (for example, changing the object's owner)
• Parse items (attributes of the submitted objects to be parsed and submitted separately)
• Whether to execute the operation or simply return the generated DDL

DBMS_METADATA provides a programmatic interfaces for submission of XML. It is comprised of the following procedures: OPENW, ADD_TRANSFORM, SET_TRANSFORM_PARAM, SET_REMAP_PARAM, SET_PARSE_ITEM, CONVERT, PUT, and CLOSE.

Security Model

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 public synonyms, system privileges granted to them, and object 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.
• In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights.

Types

The following types, used by the DBMS_METADATA package, are defined in the SYS schema.

CREATE TYPE sys.ku$_parsed_item AS OBJECT (
item        VARCHAR2(30),
value       VARCHAR2(4000),
object_row NUMBER )
/

CREATE PUBLIC SYNONYM ku$_parsed_item FOR sys.ku$_parsed_item;

CREATE TYPE sys.ku$_parsed_items IS TABLE OF sys.ku$_parsed_item
/

CREATE PUBLIC SYNONYM ku$_parsed_items FOR sys.ku$_parsed_items;

CREATE TYPE sys.ku$_ddl AS OBJECT (
ddlText    CLOB,
parsedItem sys.ku$_parsed_items )
/  

CREATE PUBLIC SYNONYM ku$_ddl FOR sys.ku$_ddl;

CREATE TYPE sys.ku$_ddls IS TABLE OF sys.ku$_ddl
/

CREATE PUBLIC SYNONYM ku$_ddls FOR sys.ku$_ddls;

CREATE TYPE sys.ku$_multi_ddl AS OBJECT (
                    object_row NUMBER,
                    ddls       sys.ku$_ddls )
/

CREATE OR REPLACE PUBLIC SYNONYM ku$_multi_ddl FOR sys.ku$_multi_ddl;

CREATE TYPE sys.ku$_multi_ddls IS TABLE OF sys.ku$_multi_ddl;
/

CREATE OR REPLACE PUBLIC SYNONYM ku$_multi_ddls FOR
                          sys.ku$_multi_ddls;

CREATE TYPE sys.ku$_ErrorLine IS OBJECT (
errorNumber    NUMBER,
errorText      VARCHAR2(2000) )
/

CREATE PUBLIC SYNONYM ku$_ErrorLine FOR sys.ku$_ErrorLine;

CREATE TYPE sys.ku$_ErrorLines IS TABLE OF sys.ku$_ErrorLine
/
CREATE PUBLIC SYNONYM ku$ErrorLines FOR sys.ku$_ErrorLines;

CREATE TYPE sys.ku$_SubmitResult AS OBJECT (
ddl sys.ku$_ddl,
errorLines sys.ku$_ErrorLines );
/

CREATE TYPE sys.ku$_SubmitResults IS TABLE OF sys.ku$_SubmitResult
/

CREATE PUBLIC SYNONYM ku$_SubmitResults FOR sys.ku$_SubmitResults;

Rules and Limits

In an Oracle Shared Server (OSS) environment, the DBMS_METADATA package must disable session migration and connection pooling. This results in any shared server process that is serving a session running the package to effectively become a default, dedicated server for the life of the session. You should ensure that sufficient shared servers are configured when the package is used and that the number of servers is not artificially limited by too small a value for the MAX_SHARED_SERVERS initialization parameter.

Organization of Subprograms

The DBMS_METADATA subprograms are used to retrieve objects from, and submit XML to, a database. Some subprograms are used for both activities, while others are used only for retrieval or only for submission.

• Table 50-3 provides a summary of DBMS_METADATA subprograms used to retrieve multiple objects from a database.
• Table 50-2 provides a summary of DBMS_METADATA subprograms used to submit XML metadata to a database.

Subprograms for Retrieval of Multiple Objects from the Database

Table 50-1  DBMS_METADATA Subprograms For Retrieving Multiple Objects

Subprograms for Submission of XML to the Database

Table 50-2  DBMS_METADATA Subprograms For Submitting XML

Summary of DBMS_METADATA Subprograms

Table 50-3  DBMS_METADATA Package Subprograms

ADD_TRANSFORM Function

This function is used for both retrieval and submission:

• When this procedure is used to retrieve objects, it specifies a transform that FETCH_xxx applies to the XML representation of the retrieved objects.
• When used to submit objects, it specifies a transform that CONVERT or PUT applies to the XML representation of the submitted objects. It is possible to add more than one transform.

  See Also: For more information about related subprograms: Subprograms for Retrieval of Multiple Objects from the Database Subprograms for Submission of XML to the Database

Syntax

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

Parameters

Table 50-4  ADD_TRANSFORM Parameters

handle

name

encoding

object_type

Return Values

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

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 XML documents. Transforms are applied 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 output of the DDL transform is not an XML document. Therefore, no transform should be added after the DDL transform.

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.
• INCONSISTENT_ARGS. The arguments are inconsistent. Possible inconsistencies include the following:
  • encoding is specified even though name is not a URL
  • object_type is not part of the collection designated by handle

CLOSE Procedure

This procedure invalidates the handle returned by OPEN (or OPENW) and cleans up the associated state.

Syntax

DBMS_METADATA.CLOSE (
   handle  IN NUMBER);

Parameters

Table 50-5  CLOSE Parameters

handle

Usage Notes

You can prematurely terminate the stream of objects established by OPEN or (OPENW).

• 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.

Exceptions

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

CONVERT Function

This function transforms an input XML document into creation DDL.

Syntax

DBMS_METADATA.CONVERT (
   handle  IN NUMBER,
   document IN sys.XMLType)
 RETURN sys.ku$_multi_ddls;

DBMS_METADATA.CONVERT (
  handle  IN NUMBER,
  document IN CLOB)
 RETURN sys.ku$_multi_ddls;

Parameters

Table 50-6  CONVERT Function Parameters

handle

document

Return Values

DDL to create the object(s).

Usage Notes

You can think of CONVERT as the second half of FETCH_xxx. The difference is that FETCH_xxx gets its XML document from the database, but CONVERT gets its XML document from the caller. The transforms specified with ADD_TRANSFORM are applied in turn, and the result is returned to the caller in a sys.ku$_multi_ddls nested table. The DDL transform must be specified. If parse items were specified, they are returned in the parsedItems column.

The encoding of the XML document is embedded in its CLOB or XMLType representation. The version of the metadata is embedded in the XML. The generated DDL is valid for the current database compatibility level.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• INCONSISTENT_OPERATION. The DDL transform was not specified.
• INCOMPATIBLE_DOCUMENT. The version of the XML document is not compatible with this version of the software.

FETCH_xxx Functions

These functions return 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

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

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

DBMS_METADATA.FETCH_CLOB (
   handle  IN NUMBER)
RETURN CLOB;

The FETCH procedures are as follows:

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

DBMS_METADATA.FETCH_XML_CLOB (
   handle  IN NUMBER,
   doc     IN OUT NOCOPY CLOB,
   parsed_items OUT sys.ku$_parsed_items,
   object_type_path OUT VARCHAR2);

Parameters

Table 50-7  FETCH_xxx Function Parameters

Return Values

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

Usage Notes

These functions and procedures return metadata for objects meeting the criteria established by the call to OPEN that returned the handle, and subsequent calls to 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:

• The FETCH_XML function returns the XML metadata for an object as an XMLType. It assumes that if any transform has been specified, that transform will produce an XML document. In particular, it assumes that the DDL transform has not been specified.
• The FETCH_DDL function returns the DDL (to create the object) 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 that has a DDL transform applied to it can be transformed into both CREATE TYPE and CREATE TYPE BODY statements. A TABLE object can be transformed into a CREATE TABLE, and one or more ALTER TABLE statements
• The FETCH_CLOB function simply returns the object, transformed or not, as a CLOB.
• The FETCH_CLOB procedure returns the objects by reference in an IN OUT NOCOPY parameter. This is faster than the function variant, which returns LOBs by value.
• The FETCH_XML_CLOB procedure returns the XML metadata for the objects as a CLOB in an IN OUT NOCOPY parameter. This helps to avoid LOB copies, which can consume a lot of resources. It also returns a nested table of parse items and the full path name of the object type of the returned objects.
• All LOBs returned by FETCH_xxx are temporary LOBs. You must free the LOB. If the LOB is supplied as an IN OUT NOCOPY parameter, you must also create the LOB.
• If SET_PARSE_ITEM was called, FETCH_DDL and FETCH_XML_CLOB return attributes of the object's metadata (or the DDL statement) in a sys.ku$_parsed_items nested table. For FETCH_XML_CLOB, the nested table is an OUT parameter. For FETCH_DDL, it is a column in the returned sys.ku$_ddls nested table. Each row of the 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--a positive integer indicating the object to which the parse item applies. If multiple objects are returned by FETCH_xxx, (because SET_COUNT specified a count greater than 1) then object_row=1 for all items for the first object, 2 for the second, and so on.
• The rows of the sys.ku$_parsed_items nested table are ordered by ascending object_row, but otherwise the row order is undetermined. To find a particular parse item within an object row the caller must search the table for a match on item.
• In general there is no guarantee that a requested parse item will be returned. For example, the parse item may not apply to the object type or to the particular line of DDL, or the item's value may be NULL.
• If SET_PARSE_ITEM was not called, NULL is returned as the value of the parsed items nested table.
• It is expected that the same variant of FETCH_xxx will be called for all objects selected by OPEN. That is, programs will not intermix calls to FETCH_XML, FETCH_DDL, FETCH_CLOB, and so on using the same OPEN handle. The effect of calling different variants is undefined; it might do what you expect, but there are no guarantees.
• Every object fetched will be internally consistent with respect to on-going DDL (and the subsequent recursive DML) operations against the dictionary. In some cases, multiple queries may be issued, either because the object type is heterogeneous or for performance reasons (for example, one query for heap tables, one for index-organized tables). Consequently the FETCH_xxx calls may in fact be fetches from different underlying cursors (meaning that read consistency is not guaranteed).

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 FETCH_XML was called when the DDL transform had been specified, or FETCH_DDL was called when the DDL transform had not been specified.

The GET_xxx Functions

The following GET_xxx functions let you fetch metadata for objects with a single call:

• GET_XML
• GET_DDL
• GET_DEPENDENT_XML
• GET_DEPENDENT_DDL
• GET_GRANTED_XML
• GET_GRANTED_DDL

  See Also: For more information about related subprograms: Subprograms for Retrieval of Multiple Objects from the Database

Syntax

DBMS_METADATA.GET_XML (                                                   
object_type     IN VARCHAR2,
name            IN VARCHAR2 DEFAULT NULL,
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 DEFAULT NULL,
schema          IN VARCHAR2 DEFAULT NULL,
version         IN VARCHAR2 DEFAULT 'COMPATIBLE',
model           IN VARCHAR2 DEFAULT 'ORACLE',
transform       IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

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;

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 50-8  GET_xxx Function Parameters

Return Values

The metadata for the specified object as XML or DDL.

Usage Notes

• These functions allow you to fetch metadata for objects with a single call. They encapsulate calls to OPEN, SET_FILTER, and so on. The function you use depends on the characteristics of the object type and on whether you want XML or DDL.
  • GET_xxx is used to fetch named objects, especially schema objects (tables, views). They can also be used with nameless objects, such as RESOURCE_COST.
  • GET_DEPENDENT_xxx is used to fetch dependent objects (audits, object grants).
  • GET_GRANTED_xxx is used to fetch granted objects (system grants, role grants).
• For some object types you can use more than one function. For example, you can use GET_xxx to fetch an index by name, or GET_DEPENDENT_xxx to fetch the same index by specifying the table on which it is defined.
• GET_xxx only returns a single named object.
• For GET_DEPENDENT_xxx and GET_GRANTED_xxx, an arbitrary number of dependent or granted objects can match the input criteria. You can specify an object count when fetching these objects. (The default count of 10000 should be adequate in most cases.)
• If the DDL transform is specified, session-level transform parameters are inherited.
• If you invoke these functions from SQL*Plus, you should set the PAGESIZE to 0 and set LONG to some large number to get complete, uninterrupted output.

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.

Examples

Example: Fetch 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 LONG 2000000
SET PAGESIZE 0
SELECT DBMS_METADATA.GET_XML('TABLE','EMP','SCOTT')
FROM DUAL;

Example: Fetch the DDL for all Complete Tables in the Current Schema, Filter 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 LONG 2000000
SET PAGESIZE 0
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');

Example: Fetch the DDL For All Object Grants On HR.EMPLOYEES

SELECT DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',
    'EMPLOYEES','HR') FROM DUAL;

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

SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
    FROM DUAL;

GET_QUERY Procedure

This procedure 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 50-9  GET_QUERY Procedure Parameters

Return Values

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.

OPEN Procedure

This procedure 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', 
   network_link IN VARCHAR2 DEFAULT NULL)
 RETURN NUMBER;

Parameters

Table 50-10  Open Procedure Parameters

Table 50-11 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, G represents a granted object, and H represents a heterogeneous object.

Table 50-11  DBMS_METADATA: Object Types

Table 50-12 lists the types of objects returned for the major heterogeneous object types. For SCHEMA_EXPORT, certain object types are only returned if the INCLUDE_USER filter is specified at TRUE. In the table, such object types are marked INCLUDE_USER.

Table 50-12  Object Types Returned for the Heterogeneous Object Type

Return Values

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.

OPENW Procedure

This procedure specifies the type of object to be submitted and the object model. The return value is an opaque context handle.

Syntax

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

Parameters

Table 50-13  OPENW Function Parameters

Return Values

An opaque handle to write context. This handle is used as input to the ADD_TRANSFORM, CONVERT, PUT, and CLOSE procedures.

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 model parameter was not valid for the object_type.

PUT Function

This function submits an XML document containing object metadata to the database to create the objects.

Syntax

DBMS_METADATA.PUT (
   handle  IN NUMBER,
   document IN sys.XMLType,
   flags    IN NUMBER,
   results  IN OUT NOCOPY sys.ku$_SubmitResults)
 RETURN BOOLEAN;

DBMS_METADATA.PUT (
   handle  IN NUMBER,
   document IN CLOB,
   flags    IN NUMBER,
   results  IN OUT NOCOPY sys.ku$_SubmitResults)
RETURN BOOLEAN;

Parameters

Table 50-14  PUT Function Parameters

handle

document

flags

results

Return Values

TRUE if all SQL operations succeeded; FALSE if there were any errors.

Usage Notes

The PUT function converts the XML document to DDL just as CONVERT does (applying the specified transforms in turn) and then submits each resultant DDL statement to the database. As with CONVERT, the DDL transform must be specified. The DDL statements and associated parse items are returned in the sys.ku$_SubmitResults nested table. With each DDL statement is a nested table of error lines containing any errors or exceptions raised by the statement.

The encoding of the XML document is embedded in its CLOB or XMLType representation. The version of the metadata is embedded in the XML. The generated DDL is valid for the current database compatibility level.

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• INCONSISTENT_OPERATION. The DDL transform was not specified.
• INCOMPATIBLE_DOCUMENT. The version of the XML document is not compatible with this version of the software.

SET_COUNT Procedure

This procedure 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. You can use the SET_COUNT procedure to 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.

For heterogeneous object types, a single FETCH_xxx operation only returns objects of a single object type.

Syntax

DBMS_METADATA.SET_COUNT (
   handle           IN NUMBER,
   value            IN NUMBER,
   object_type_path IN VARCHAR2 DEFAULT NULL);

Parameters

Table 50-15  SET_COUNT Procedure Parameters

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.
• INCONSISTENT_ARGS. object_type parameter is not consistent with handle.

SET_FILTER Procedure

This procedure 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,
   object_type_path IN VARCHAR2 DEFAULT NULL);

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

DBMS_METADATA.SET_FILTER (
   handle            IN NUMBER,
   name              IN VARCHAR2,
   value             IN NUMBER,
   object_type_path  IN VARCHAR2 DEFAULT NULL);

Parameters

Table 50-16  SET_FILTER Procedure Parameters

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

Table 50-17  SET_FILTER: Filters

CUSTOM_FILTER

SCHEMA

SCHEMA_EXPR

INCLUDE_USER

SCHEMA

SCHEMA_EXPR

NAME

NAME_EXPR

BEGIN_WITH

BEGIN_AFTER

END_BEFORE

END_WITH

INCLUDE_PATH_
EXPR, EXCLUDE_
PATH_EXPR

Usage Notes

• Each call to SET_FILTER causes a WHERE condition to be added to the underlying query that fetches the set of objects. The WHERE conditions are ANDed together, so you can use multiple SET_FILTER calls to refine the set of objects to be returned. For example to specify that you want the object named EMP in schema SCOTT, do the following:

   SET_FILTER(handle,'SCHEMA','SCOTT');
   SET_FILTER(handle,'NAME','EMP');
  
  

• 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:

  SET_FILTER(handle,'NAME_EXPR','>=''FELIX''');
  SET_FILTER(handle,'NAME_EXPR','<=''OSCAR''');
  
  

• 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 current user
  • Public synonyms
  • System privileges granted to the current user or to PUBLIC
  • Grants on objects for which the current user is owner, grantor, or grantee (either explicitly or as PUBLIC).
  • SCHEMA_EXPORT where the name is the current user
  • TABLE_EXPORT where SCHEMA is the current user

  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.

  In stored procedures, functions, and definers-rights packages, roles (such as SELECT_CATALOG_ROLE) are disabled. Therefore, such a PL/SQL program can only fetch metadata for objects in its own schema. If you want to write a PL/SQL program that fetches metadata for objects in a different schema (based on the invoker's possession of SELECT_CATALOG_ROLE), you must make the program invokers-rights.

• For heterogeneous object types, the BEGIN_WITH and BEGIN_AFTER filters allow restart on an object type boundary. Appropriate filter values are returned by the FETCH_XML_CLOB procedure.

  Filters on heterogeneous objects provide default values for filters on object types within the collection. You can override this default for a particular object type by specifying the appropriate filter for the specific object type path. For example, for SCHEMA_EXPORT the NAME filter specifies the schema to be fetched including all the tables in the schema, but you can further restrict this set of tables by supplying a NAME_EXPR filter explicitly for the TABLE object type path. Table 50-18 lists valid object type path names for the major heterogeneous object types along with an explanation of the scope of each path name. (See Table 50-17 for filters defined for each path name.) These path names are valid in the INCLUDE_PATH_EXPR and EXCLUDE_PATH_EXPR filters. Path names marked with an asterisk (*) are only valid in those filters; they cannot be used as values of the SET_FILTER object_type_path parameter.

  Table 50-18  Object Type Path Names for Heterogeneous Object Types

  Heterogeneous Type	Path Name (*=valid only in xxx_PATH_EXPR)	Scope
  TABLE_EXPORT	AUDIT_OBJ	Object audits on the selected tables
  TABLE_EXPORT	COMMENT	Table and column comments for the selected tables
  TABLE_EXPORT	CONSTRAINT	Constraints (including referential constraints) on the selected tables
  TABLE_EXPORT	*GRANT	Object grants on the selected tables
  TABLE_EXPORT	INDEX	Indexes (including domain indexes) on the selected tables
  TABLE_EXPORT	OBJECT_GRANT	Object grants on the selected tables
  TABLE_EXPORT	REF_CONSTRAINT	Referential (foreign key) constraints on the selected tables
  TABLE_EXPORT	STATISTICS	Statistics on the selected tables
  TABLE_EXPORT	TABLE_DATA	Row data for the selected tables
  TABLE_EXPORT	TRIGGER	Triggers on the selected tables
  SCHEMA_EXPORT	ASSOCIATION	Statistics type associations for objects in the selected schemas
  SCHEMA_EXPORT	AUDIT_OBJ	Audits on all objects in the selected schemas
  SCHEMA_EXPORT	CLUSTER	Clusters in the selected schemas and their indexes
  SCHEMA_EXPORT	COMMENT	Comments on all objects in the selected schemas
  SCHEMA_EXPORT	CONSTRAINT	Constraints (including referential constraints) on all objects in the selected schemas
  SCHEMA_EXPORT	DB_LINK	Private database links in the selected schemas
  SCHEMA_EXPORT	DEFAULT_ROLE	Default roles granted to users associated with the selected schemas
  SCHEMA_EXPORT	DIMENSION	Dimensions in the selected schemas
  SCHEMA_EXPORT	FUNCTION	Functions in the selected schemas and their dependent grants and audits
  SCHEMA_EXPORT	*GRANT	Grants on objects in the selected schemas
  SCHEMA_EXPORT	INDEX	Indexes (including domain indexes) on tables and clusters in the selected schemas
  SCHEMA_EXPORT	INDEXTYPE	Indextypes in the selected schemas and their dependent grants and audits
  SCHEMA_EXPORT	JAVA_SOURCE	Java sources in the selected schemas and their dependent grants and audits
  SCHEMA_EXPORT	JOB	Jobs in the selected schemas
  SCHEMA_EXPORT	LIBRARY	External procedure libraries in the selected schemas
  SCHEMA_EXPORT	MATERIALIZED_VIEW	Materialized views in the selected schemas
  SCHEMA_EXPORT	MATERIALIZED_VIEW_LOG	Materialized view logs on tables in the selected schemas
  SCHEMA_EXPORT	OBJECT_GRANT	Grants on objects in the selected schemas
  SCHEMA_EXPORT	OPERATOR	Operators in the selected schemas and their dependent grants and audits
  SCHEMA_EXPORT	PACKAGE	Packages (both specification and body) in the selected schemas, and their dependent grants and audits
  SCHEMA_EXPORT	PACKAGE_BODY	Package bodies in the selected schemas
  SCHEMA_EXPORT	PACKAGE_SPEC	Package specifications in the selected schemas
  SCHEMA_EXPORT	PASSWORD_HISTORY	The password history for users associated with the selected schemas
  SCHEMA_EXPORT	PROCEDURE	Procedures in the selected schemas and their dependent grants and audits
  SCHEMA_EXPORT	REF_CONSTRAINT	Referential (foreign key) constraints on tables in the selected schemas
  SCHEMA_EXPORT	REFRESH_GROUP	Refresh groups in the selected schemas
  SCHEMA_EXPORT	SEQUENCE	Sequences in the selected schemas and their dependent grants and audits
  SCHEMA_EXPORT	STATISTICS	Statistics on tables and indexes in the selected schemas
  SCHEMA_EXPORT	SYNONYM	Private synonyms in the selected schemas
  SCHEMA_EXPORT	TABLE	Tables in the selected schemas and their dependent objects (indexes, constraints, triggers, grants, audits, comments, table data, and so on)
  SCHEMA_EXPORT	TABLE_DATA	Row data for tables in the selected schemas
  SCHEMA_EXPORT	TABLESPACE_QUOTA	Tablespace quota granted to users associated with the selected schemas
  SCHEMA_EXPORT	TRIGGER	Triggers on tables in the selected schemas
  SCHEMA_EXPORT	TYPE	Types (both specification and body) in the selected schemas, and their dependent grants and audits
  SCHEMA_EXPORT	TYPE_BODY	Type bodies in the selected schemas
  SCHEMA_EXPORT	TYPE_SPEC	Type specifications in the selected schemas
  SCHEMA_EXPORT	USER	User definitions for users associated with the selected schemas
  SCHEMA_EXPORT	VIEW	Views in the selected schemas and their dependent objects (grants, constraints, comments, audits)
  DATABASE_EXPORT	ASSOCIATION	Statistics type associations for objects in the database
  DATABASE_EXPORT	AUDIT	Audits of SQL statements
  DATABASE_EXPORT	AUDIT_OBJ	Audits on all objects in the database
  DATABASE_EXPORT	CLUSTER	Clusters and their indexes
  DATABASE_EXPORT	COMMENT	Comments on all objects
  DATABASE_EXPORT	CONSTRAINT	Constraints (including referential constraints)
  DATABASE_EXPORT	CONTEXT	Application contexts
  DATABASE_EXPORT	DB_LINK	Private and public database links
  DATABASE_EXPORT	DEFAULT_ROLE	Default roles granted to users in the database
  DATABASE_EXPORT	DIMENSION	Dimensions in the database
  DATABASE_EXPORT	DIRECTORY	Directory objects in the database
  DATABASE_EXPORT	FGA_POLICY	Fine-grained audit policies
  DATABASE_EXPORT	FUNCTION	Functions
  DATABASE_EXPORT	* GRANT	Object and system grants
  DATABASE_EXPORT	INDEX	Indexes (including domain indexes) on tables and clusters
  DATABASE_EXPORT	INDEXTYPE	Indextypes and their dependent grants and audits
  DATABASE_EXPORT	JAVA_SOURCE	Java sources and their dependent grants and audits
  DATABASE_EXPORT	JOB	Jobs
  DATABASE_EXPORT	LIBRARY	External procedure libraries
  DATABASE_EXPORT	MATERIALIZED_VIEW	Materialized views
  DATABASE_EXPORT	MATERIALIZED_VIEW_LOG	Materialized view logs
  DATABASE_EXPORT	OBJECT_GRANT	All object grants in the database
  DATABASE_EXPORT	OPERATOR	Operators and their dependent grants and audits
  DATABASE_EXPORT	PACKAGE	Packages (both specification and body) and their dependent grants and audits
  DATABASE_EXPORT	PACKAGE_BODY	Package bodies
  DATABASE_EXPORT	PACKAGE_SPEC	Package specifications
  DATABASE_EXPORT	PASSWORD_HISTORY	Password histories for database users
  DATABASE_EXPORT	*PASSWORD_VERIFY_FUNCTION	The password complexity verification function
  DATABASE_EXPORT	PROCEDURE	Procedures and their dependent grants and objects
  DATABASE_EXPORT	PROFILE	Profiles
  DATABASE_EXPORT	PROXY	Proxy authentications
  DATABASE_EXPORT	REF_CONSTRAINT	Referential (foreign key) constraints on tables in the database
  DATABASE_EXPORT	REFRESH_GROUP	Refresh groups
  DATABASE_EXPORT	*RESOURCE_ COST	Resource cost information
  DATABASE_EXPORT	RLS_CONTEXT	Fine-grained access-control driving contexts
  DATABASE_EXPORT	RLS_GROUP	Fine-grained access-control policy groups
  DATABASE_EXPORT	RLS_POLICY	Fine-grained access-control policies
  DATABASE_EXPORT	ROLE	Roles
  DATABASE_EXPORT	ROLE_GRANT	Role grants to users in the database
  DATABASE_EXPORT	ROLLBACK_SEGMENT	Rollback segments
  DATABASE_EXPORT	*SCHEMA (named object)	Database schemas including for each schema all related and dependent objects: user definitions and their attributes (default roles, role grants, tablespace quotas, and so on), objects in the schema (tables, view, packages, types, and so on), and their dependent objects (grants, audits, indexes, constraints, and so on). The NAME and NAME_EXPR filters can be used with this object type path name to designate the database schemas to be fetched.
  DATABASE_EXPORT	SEQUENCE	Sequences
  DATABASE_EXPORT	STATISTICS	Statistics on tables and indexes
  DATABASE_EXPORT	SYNONYM	Public and private synonyms
  DATABASE_EXPORT	SYSTEM_GRANT	System privilege grants
  DATABASE_EXPORT	TABLE	Tables and their dependent objects (indexes, constraints, triggers, grants, audits, comments, table data, and so on)
  DATABASE_EXPORT	TABLE_DATA	Row data for all tables
  DATABASE_EXPORT	TABLESPACE	Tablespace definitions
  DATABASE_EXPORT	TABLESPACE_QUOTA	Tablespace quota granted to users in the database
  DATABASE_EXPORT	TRIGGER	Triggers on the database, on schemas, and on schema objects
  DATABASE_EXPORT	TRUSTED_DB_LINK	Trusted links
  DATABASE_EXPORT	TYPE	Types (both specification and body) and their dependent grants and audits
  DATABASE_EXPORT	TYPE_BODY	Type bodies
  DATABASE_EXPORT	TYPE_SPEC	Type specifications
  DATABASE_EXPORT	USER	User definitions
  DATABASE_EXPORT	VIEW	Views

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 are permitted.
• INCONSISTENT_ARGS. The arguments are inconsistent. Possible inconsistencies include the following:
  • filter name not valid for the object type associated with the OPEN context
  • filter name not valid for the object_type_path
  • object_type_path not part of the collection designated by handle
  • filter value is the wrong datatype

SET_PARSE_ITEM Procedure

This procedure enables output parsing and specifies an object attribute to be parsed and returned.

Syntax

The following syntax applies when SET_PARSE_ITEM is used for object retrieval:

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

The following syntax applies when SET_PARSE_ITEM is used for XML submission:

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

Parameters

Table 50-19  SET_PARSE_ITEM Procedure Parameters

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

Table 50-20  SET_PARSE_ITEM: Parse Items

Usage Notes

These notes apply when using SET_PARSE_ITEM to retrieve objects.

By default, the FETCH_xxx routines return an object's metadata as XML or creation DDL. By calling SET_PARSE_ITEM you can request that individual attributes of the object be returned as well.

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.

For TABLE_DATA objects, the following parse item return values are of interest:

Tables are not usually thought of as dependent objects. However, secondary tables for domain indexes are dependent on the domain indexes. Consequently, the BASE_OBJECT_NAME, BASE_OBJECT_SCHEMA and BASE_OBJECT_TYPE parse items for secondary TABLE objects return the name, schema, and type of the domain index.

By default, the CONVERT and PUT procedures simply transform an object's XML metadata to DDL. By calling SET_PARSE_ITEM you can request that individual attributes of the object be returned as well.

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.

SET_TRANSFORM_PARAM and SET_REMAP_PARAM Procedures

SET_TRANSFORM_PARAM and SET_REMAP_PARAM specify parameters to the XSLT stylesheet identified by transform_handle.Use them 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),
   object_type       IN VARCHAR2 DEFAULT NULL);

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

DBMS_METADATA.SET_TRANSFORM_PARAM (
   transform_handle   IN NUMBER,
   name               IN VARCHAR2,
   value              IN NUMBER,
   object_type        IN VARCHAR2 DEFAULT NULL);

DBMS_METADATA.SET_REMAP_PARAM              (
   transform_handle   IN NUMBER,
   name               IN VARCHAR2,
   old_value          IN VARCHAR2,
   new_value          IN VARCHAR2,
   object_type        IN VARCHAR2 DEFAULT NULL);

Parameters

Table 50-21 describes the parameters for the SET_TRANSFORM_PARAM and SET_REMAP_PARAM procedures.

Table 50-21  SET_TRANSFORM_PARAM and SET_REMAP_PARAM Parameters

transform_handle

name

value

old_value

new_value

object_type

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

Table 50-22  SET_TRANSFORM_PARAM: Transform Parameters for the DDL Transform

PRETTY

SQLTERMINATOR

SEGMENT_ATTRIBUTES

STORAGE

TABLESPACE

CONSTRAINTS

REF_CONSTRAINTS

CONSTRAINTS_AS_ALTER

OID

SIZE_BYTE_KEYWORD

SEGMENT_ATTRIBUTES

STORAGE

TABLESPACE

SPECIFICATION

BODY

OID

SPECIFICATION

BODY

FORCE

INSERT

DEFAULT

INHERIT

REVOKE_FROM

REUSE

Table 50-23 describes the object type, name, datatype, and meaning of the parameters for the MODIFY transform in the SET_TRANSFORM_PARAM procedure.

Table 50-23  SET_TRANSFORM_PARAM: Transform Parameters for the MODIFY Transform

OBJECT_ROW

Table 50-24 describes the object type, name, datatype, and meaning of the parameters for the MODIFY transform in the SET_REMAP_PARAM procedure.

Table 50-24  SET_REMAP_PARAM: Transform Parameters for the MODIFY Transform

REMAP_DATAFILE

REMAP_SCHEMA

REMAP_TABLESPACE

Exceptions

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.
• INVALID_OPERATION. Either SET_TRANSFORM_PARAM or SET_REMAP_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 or SET_REMAP_PARAM are permitted.
• INCONSISTENT_ARGS. The arguments are inconsistent. This can mean the following:
  • The transform parameter name is not valid for the object type associated with the OPEN context or for the transform associated with the transform handle.
  • The transform applies to all object types in a heterogeneous collection, but object_type is not part of the collection.

Usage Notes

XSLT allows parameters to be passed to stylesheets. You call SET_TRANSFORM_PARAM or SET_REMAP_PARAM to specify the value of a parameter to be passed to the stylesheet identified by transform_handle.

Normally, if you call SET_TRANSFORM_PARAMETER multiple times for the same parameter name, each call overrides the prior call. For example, the following sequence simply sets the STORAGE transform parameter to TRUE.

SET_TRANSFORM_PARAM(tr_handle,'STORAGE',false);
SET_TRANSFORM_PARAM(tr_handle,'STORAGE',true);

However, some transform parameters are additive which means that all specified parameter values are applied to the document, not just the last one. For example, the OBJECT_ROW parameter to the MODIFY transform is additive. If you specify the following, then both specified rows are copied to the output document.

SET_TRANSFORM_PARAM(tr_handle,'OBJECT_ROW',5);
SET_TRANSFORM_PARAM(tr_handle,'OBJECT_ROW',8);

The REMAP_TABLESPACE parameter is also additive. If you specify the following, then tablespaces TBS1 and TBS3 are changed to TBS2 and TBS4, respectively.

SET_REMAP_PARAM(tr_handle,'REMAP_TABLESPACE','TBS1','TBS2');
SET_REMAP_PARAM(tr_handle,'REMAP_TABLESPACE','TBS3','TBS4');

The order in which the transformations are performed is undefined. For example, if you specify the following, the result is undefined.

SET_REMAP_PARAM(tr_handle,'REMAP_TABLESPACE','TBS1','TBS2');
SET_REMAP_PARAM(tr_handle,'REMAP_TABLESPACE','TBS2','TBS3');

The GET_DDL, GET_DEPENDENT_DDL, and GET_GRANTED_DDL functions allow 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, GET_DEPENDENT_DDL, and GET GRANTED_DDL inherit these parameters when they invoke the DDL transform.