121 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.
See Also:
Oracle Database
Utilities for more information and for examples of using the DBMS_METADATA
package.
This chapter contains the following topics:
-
-
Subprograms for Retrieving Multiple Objects From the Database
-
Subprograms for Submitting XML to the Database
-
121.1 DBMS_METADATA Overview
You can use the DBMS_METADATA
package to retrieve metadata and to submit 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 (Extensible Stylesheet Language Transformation) scripts. By default the output is represented in XML, but you can specify transformations (into SQL DDL, for example), which are implemented by XSLT 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,
andCLOSE
retrieve multiple objects. -
For use in SQL queries and for browsing:
GET_XML
,GET_DDL
andGET_SXML
return metadata for a single named object. TheGET_DEPENDENT_XML
,GET_DEPENDENT_DDL
,GET_GRANTED_XML
, andGET_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 interface 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
.
121.2 DBMS_METADATA Security Model
The DBMS_METADATA
package considers a privileged user to be one who is connected as user SYS
or who has the SELECT_CATALOG_ROLE
role.
The object views of the Oracle metadata model implement security as follows:
-
Nonprivileged users can see the metadata of only their own 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 ofSELECT_CATALOG_ROLE
), you must make the program invokers-rights. -
For all objects that have passwords, except database links (for example, users and roles), the following rules apply:
-
A user who has the
SELECT_CATALOG_ROLE
can see all metadata for an object except the passwords for that object. -
The
SYS
user, users who have theEXP_FULL_DATABASE
role, and users who own an object can see all metadata for that object, including passwords.
-
-
For database links the password is never displayed. For security reasons Oracle restricts visibility of the password value to
SYS
users who query thelink$.passwordx
column directly. Instead of the password,DBMS_METADATA
returns the following invalid syntax:IDENTIFIED BY VALUES ':1'
A user who knows the password of the database link can manually replace the
:1
with the password.
121.3 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.
121.4 DBMS_METADATA Data Structures - Object and Table Types
The DBMS_METADATA
package defines, in the SYS
schema, the OBJECT
and TABLE
types shown in this code.
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, parsedItems 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;
Note:
The maximum size of the VARCHAR2
, NVARCHAR2
, and RAW
datatypes has been increased to 32 KB when the COMPATIBLE
initialization parameter is set to 12.0 and the MAX_STRING_SIZE
initialization parameter is set to EXTENDED
. The DBMS_METADATA
package supports this increased size unless the version of the metadata is earlier than Oracle Database 12c Release 1 (12.1).
121.5 DBMS_METADATA Subprogram Groupings
The DBMS_METADATA
subprograms 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 121-1 provides a summary, in alphabetical order, of
DBMS_METADATA
subprograms used to retrieve multiple objects from a database. -
Table 121-2 provides a summary, in alphabetical order, of
DBMS_METADATA
subprograms used to submit XML metadata to a database.
121.5.1 DBMS_METADATA Subprograms for Retrieving Multiple Objects From the Database
DBMS_METADATA
uses these subprograms used for retrieving multiple objects from the database.
Table 121-1 DBMS_METADATA Subprograms for Retrieving Multiple Objects
Subprogram | Description |
---|---|
Specifies a transform that |
|
Invalidates the handle returned by |
|
Returns metadata for objects meeting the criteria established by |
|
Returns the text of the queries that are used by |
|
Fetches the metadata for a specified object as XML, SXML, or DDL, using only a single call |
|
Specifies the type of object to be retrieved, the version of its metadata, and the object model |
|
Specifies the maximum number of objects to be retrieved in a single |
|
Specifies restrictions on the objects to be retrieved, for example, the object name or schema |
|
Enables output parsing by specifying an object attribute to be parsed and returned |
|
Specifies parameters to the XSLT stylesheets identified by |
121.5.2 DBMS_METADATA Subprograms for Submitting XML to the Database
DBMS_METADATA
uses these subprograms for submitting XML to the database.
Table 121-2 DBMS_METADATA Subprograms for Submitting XML
Subprogram | Description |
---|---|
Specifies a transform for the XML documents |
|
Closes the context opened with |
|
Converts an XML document to DDL |
|
Opens a write context |
|
Submits an XML document to the database |
|
Specifies an object attribute to be parsed |
|
|
121.6 Summary of All DBMS_METADATA Subprograms
This table lists the DBMS_METADATA
subprograms and briefly describes them.
Table 121-3 DBMS_METADATA Package Subprograms
Subprogram | Description |
---|---|
Specifies a transform that |
|
Invalidates the handle returned by |
|
Converts an XML document to DDL |
|
Returns metadata for objects meeting the criteria established by |
|
Fetches the metadata for a specified object as XML, SXML, or DDL, using only a single call |
|
Returns the text of the queries that are used by |
|
Specifies the type of object to be retrieved, the version of its metadata, and the object model |
|
Opens a write context |
|
Submits an XML document to the database |
|
Specifies the maximum number of objects to be retrieved in a single |
|
Specifies restrictions on the objects to be retrieved, for example, the object name or schema |
|
Enables output parsing by specifying an object attribute to be parsed and returned |
|
Specifies parameters to the XSLT stylesheets identified by |
121.6.1 ADD_TRANSFORM Function
The DBMS_METADATA.ADD_TRANSFORM
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
orPUT
applies to the XML representation of the submitted objects. It is possible to add more than one transform.See Also:
-
Subprograms for Retrieving Multiple Objects From the Database
-
"SET_TRANSFORM_PARAM and SET_REMAP_PARAM Procedures" for information about how to modify and customize transform output
-
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 121-4 ADD_TRANSFORM Function Parameters
Parameters | Description |
---|---|
|
The handle returned from |
|
The name of the transform. The name can be an internal keyword like Otherwise, name designates a transform implemented by See Table 121-5 for descriptions of available transforms. |
|
The name of the Globalization Support character set in which the stylesheet pointed to by name is encoded. This is only valid if name is a URL. If left NULL and the URL is external to the database, UTF-8 encoding is assumed. If left |
|
The definition of this parameter depends upon whether you are retrieving objects or submitting XML metadata.
|
The following table describes the transforms available on the ADD_TRANSFORM
function.
Because new transforms are occasionally added, you might want to query the DBMS_METADATA_TRANSFORMS
view to see all valid Oracle-supplied transforms for specific object types.
Table 121-5 Transforms Available on ADD_TRANSFORM Function
Object Type | Transform Name | Input Doc Type | Output Doc Type | Description |
---|---|---|---|---|
All |
DDL |
XML |
DDL |
Convert XML to SQL to create the object |
All |
MODIFY |
XML |
XML |
Modify XML document according to transform parameters |
Subset |
SXML |
XML |
SXML |
Convert XML to SXML |
Subset |
MODIFYSXML |
SXML |
SXML |
Modify SXML document according to transform parameters |
Subset |
SXMLDDL |
SXML |
DDL |
Convert SXML to DDL |
Subset |
ALTERXML |
SXML difference document |
ALTER_XML |
Generate ALTER_XML from SXML difference document. (See the The following parameters are valid for the ALTERXML transform:
|
Subset |
ALTERDDL |
ALTER_XML |
ALTER_DDL |
Convert ALTER_XML to ALTER_DDL |
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 the XSLT stylesheets to be used to transform the returned XML documents. -
You can call
ADD_TRANSFORM
more than once to apply multiple transforms to XML documents. The 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 a DDL transform is not an XML document. Therefore, no transform should be added after the DDL transform.
-
Each transform expects a certain format XML document as input. If the input document is unspecified, metadata XML format is assumed.
-
When the ALTERXML transform is used, parse items are returned in a
PARSE_LIST
element of the ALTER_XML document. EachPARSE_LIST_ITEM
element contains anITEM
and aVALUE
. For example:<PARSE_LIST> <PARSE_LIST_ITEM> <ITEM>XPATH</ITEM> <VALUE>/sxml:TABLE/sxml:RELATIONAL_TABLE/sxml:COL_LIST/sxml:COL_LIST_ITEM[14]</VALUE> </PARSE_LIST_ITEM> <PARSE_LIST_ITEM> <ITEM>NAME</ITEM> <VALUE>Z1</VALUE> </PARSE_LIST_ITEM> <PARSE_LIST_ITEM> <ITEM>CLAUSE_TYPE</ITEM> <VALUE>ADD_COLUMN</VALUE> </PARSE_LIST_ITEM> <PARSE_LIST_ITEM> <ITEM>COLUMN_ATTRIBUTE</ITEM> <VALUE>NOT_NULL</VALUE> </PARSE_LIST_ITEM> </PARSE_LIST>
Exceptions
-
INVALID_ARGVAL
. ANULL
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 toFETCH_xxx
for theOPEN
context. After the first call toFETCH_xxx
is made, no further calls toADD_TRANSFORM
for the currentOPEN
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.
-
121.6.2 CLOSE Procedure
This procedure is used for both retrieval and submission. This procedure invalidates the handle returned by OPEN
(or OPENW
) and cleans up the associated state.
See Also:
For more information about related subprograms:
Syntax
DBMS_METADATA.CLOSE (
handle IN NUMBER);
Parameters
Table 121-6 CLOSE Procedure Parameters
Parameter | Description |
---|---|
|
The handle returned from |
Usage Notes
Note:
The following notes apply only to object retrieval
You can prematurely terminate the stream of objects established by OPEN
or (OPENW)
.
-
If a call to
FETCH_xxx
returnsNULL,
indicating no more objects, a call toCLOSE
is made transparently. In this case, you can still callCLOSE
on the handle and not get an exception. (The call toCLOSE
is not required.) -
If you know that only one specific object will be returned, you should explicitly call
CLOSE
after the singleFETCH_xxx
call to free resources held by the handle.
Exceptions
-
INVALID_ARGVAL
. The value for thehandle
parameter isNULL
or invalid.
121.6.3 CONVERT Functions and Procedures
The DBMS_METADATA.CONVERT
functions and procedures transform input XML documents.
The CONVERT
functions return creation DDL. The CONVERT
procedures return either XML or DDL, depending on the specified transforms.
Syntax
The CONVERT
functions are as follows:
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;
The CONVERT
procedures are as follows:
DBMS_METADATA.CONVERT ( handle IN NUMBER, document IN sys.XMLType, result IN OUT NOCOPY CLOB); DBMS_METADATA.CONVERT ( handle IN NUMBER, document IN CLOB, result IN OUT NOCOPY CLOB);
Parameters
Table 121-7 CONVERT Subprogram Parameters
Parameter | Description |
---|---|
|
The handle returned from |
|
The XML document containing object metadata of the type of the |
result |
The converted document |
Return Values
Either XML or DDL, depending on the specified transforms.
Usage Notes
You can think of CONVERT
as the second half of FETCH_xxx
, either FETCH_DDL
(for the function variants) or FETCH_CLOB
(for the procedure variants). There are two differences:
-
FETCH_xxx
gets its XML document from the database, butCONVERT
gets its XML document from the caller -
FETCH_DDL
returns its results in asys.ku$_ddls
nested table, butCONVERT
returns asys.ku$_multi_ddls
nested table
The transforms specified with ADD_TRANSFORM
are applied in turn, and the result is returned to the caller. For the function variants, the DDL transform must be specified. If parse items were specified, they are returned in the parsedItems
column. Parse items are ignored by the procedure variants.
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 database version specified in OPENW
.
Exceptions
-
INVALID_ARGVAL
. ANULL
or invalid value was supplied for an input parameter. The error message text identifies the parameter. -
INCONSISTENT_OPERATION
. No transform was specified. The DDL transform was not specified (function variants only). -
INCOMPATIBLE_DOCUMENT
. The version of the XML document is not compatible with this version of the software.
121.6.4 FETCH_xxx Functions and Procedures
These functions and procedures 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.
See Also:
For more information about related subprograms:
Syntax
The FETCH
functions are as follows:
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, cache_lob IN BOOLEAN DEFAULT TRUE, lob_duration IN PLS INTEGER DEFAULT DBMS_LOB.SESSION) 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 121-8 FETCH_xxx Function Parameters
Parameters | Description |
---|---|
|
The handle returned from |
|
|
|
The duration for the temporary LOB created by |
|
The metadata for the objects, or |
|
A nested table containing the items specified by |
|
For heterogeneous object types, this is the full path name of the object type for the objects returned by the call to |
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 anXMLType
. 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 asys.ku$_ddls
nested table. It assumes that the DDL transform has been specified. Each row of thesys.ku$_ddls
nested table contains a single DDL statement in theddlText
column; if requested, parsed items for the DDL statement will be returned in theparsedItems
column. Multiple DDL statements may be returned under the following circumstances:-
When you call
SET_COUNT
to specify a count greater than1
-
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 bothCREATE TYPE
andCREATE TYPE BODY
statements. ATABLE
object can be transformed into aCREATE TABLE
, and one or moreALTER TABLE
statements
-
-
The
FETCH_CLOB
function simply returns the object, transformed or not, as a CLOB. By default, the CLOB is read into the buffer cache and has session duration, but these defaults can be overridden with thecache_lob
andlob_duration
parameters. -
The
FETCH_CLOB
procedure returns the objects by reference in anIN
OUT
NOCOPY
parameter. This is faster than the function variant, which returns LOBs by value, a practice that involves an expensive LOB copy. -
The
FETCH_XML_CLOB
procedure returns the XML metadata for the objects as a CLOB in anIN
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 anIN
OUT
NOCOPY
parameter, you must also create the LOB. -
If
SET_PARSE_ITEM
was called,FETCH_DDL
andFETCH_XML_CLOB
return attributes of the object's metadata (or the DDL statement) in asys.ku$_parsed_items
nested table. ForFETCH_XML_CLOB
, the nested table is anOUT
parameter. ForFETCH_DDL
, it is a column in the returnedsys.ku$_ddls
nested table. Each row of the nested table corresponds to an item specified bySET_PARSE_ITEM
and contains the following columns:-
item
—the name of the attribute as specified in thename
parameter toSET_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 byFETCH_xxx
, (becauseSET_COUNT
specified a count greater than 1) thenobject_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 ascendingobject_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 onitem
. -
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 byOPEN
. That is, programs will not intermix calls toFETCH_XML
,FETCH_DDL
,FETCH_CLOB
, and so on using the sameOPEN
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).
Caution:
Do not run functions and procedures within a query, because they can fail when the handle is for an open context for a heterogeneous object.Exceptions
Most exceptions raised during execution of the query are propagated to the caller. Also, the following exceptions may be raised:
-
INVALID_ARGVAL
. ANULL
or invalid value was supplied for an input parameter. The error message text identifies the parameter. -
INCONSISTENT_OPERATION
. EitherFETCH_XML
was called when the DDL transform had been specified, orFETCH_DD
L was called when the DDL transform had not been specified.
121.6.5 GET_xxx Functions
GET_xxx
functions let you fetch metadata for objects with a single call.
Functions
The GET_xxx
functions are:
GET_XML
GET_DDL
GET_SXML
GET_DEPENDENT_XML
GET_DEPENDENT_DDL
GET_GRANTED_XML
GET_GRANTED_DDL
See Also:
Subprograms for Retrieving Multiple Objects From the Database for more information about related subprograms
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;
DBMS_METADATA.GET_SXML (
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 'SXML')
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 121-9 GET_xxx Function Parameters
Parameter | Description |
---|---|
|
The type of object that you want to be retrieved. This parameter takes the same
values as the |
|
The object name. This object name is used internally in a |
|
The object schema. This object schema is used internally in a
|
|
The version of metadata that you want to be extracted. This parameter takes the same
values as the |
|
The object model that you want to use. This parameter takes the same values as the
|
|
The name of a transformation on the output. This parameter takes the same values as
the |
|
The base object name. The base object name is used internally in a
|
|
The base object schema. The base object schema is used internally in a
|
|
The grantee. The grantee is used internally in a |
|
The maximum number of objects to return. See SET_COUNT Procedure. |
Return Values
The metadata for the specified object as XML or DDL.
Usage Notes
-
These functions enable you to fetch metadata for objects with a single call. They encapsulate calls to
OPEN
,SET_FILTER
, and so on. The function that you use depends on the characteristics of the object type, and on whether you want XML, SXML, or DDL.GET_xxx
is used to fetch named objects, especially schema objects (tables, views).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, orGET_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
andGET_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, then session-level transform parameters are inherited.
-
If you start these functions from SQL*Plus, then to obtain complete, uninterrupted output, you should set the
PAGESIZE
to 0, and setLONG
to some large number. - Currently,
GET_DDL
for aUSER
object does not return theCONTAINER
clause.
Exceptions
INVALID_ARGVAL
. ANULL
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 running 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 System Grants Granted To
SCOTT
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT','SCOTT')
FROM DUAL;
Note:
If you use the packageDBMS_METADATA.GET_GRANTED_DDL
to grant system privileges to a
nonprivileged user granted system privileges, and you then attempt to use that
nonprivileged user to grant privileges to other users by using GRANT INHERIT
PRIVILEGES
, then you receive the error "ORA-31703: cannot grant
INHERIT PRIVILEGES
privilege on behalf of other users". The
DBMS_METADATA.GET_GRANTED_DDL
procedure returns only privileges
directly granted to the user.
Example: Fetch the DDL For All User SCOTT
Object Grants
on HR.EMPLOYEES
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT')
FROM dual ;
DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','SCOTT')
Example: Fetch the DDL for the Data Use Case Domain
DAY_OF_WEEK
SQL> SET LONG 2000000
SELECT DBMS_METADATA.GET_DDL('SQL_DOMAIN', 'DAY_OF_WEEK');
121.6.6 GET_QUERY Function
This function returns the text of the queries that are used by FETCH_xxx
. This function assists in debugging.
See Also:
For more information about related subprograms:
Syntax
DBMS_METADATA.GET_QUERY ( handle IN NUMBER) RETURN VARCHAR2;
Parameters
Table 121-10 GET_QUERY Function Parameters
Parameter | Description |
---|---|
|
The handle returned from |
Return Values
The text of the queries that will be used by FETCH_xxx.
Exceptions
-
INVALID_ARGVAL
. ANULL
or invalid value was supplied for thehandle
parameter.
121.6.7 OPEN Function
The DBMS_METADATA.OPEN
function 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.
See Also:
For more information about related subprograms, see:
Subprograms for Retrieving Multiple Objects From the Database
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 121-11 Open Function Parameters
Parameter | Description |
---|---|
|
The type of object to be retrieved. The table " The Attributes column in the
These attributes are relevant when choosing object selection criteria. See "SET_FILTER Procedure" for more information. |
|
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 as follows:
A specific database version. The value cannot be lower than 9.2.0. |
|
Specifies which view to use, because the API can support multiple views on the
metadata. Only the |
|
The name of a database link to the database whose metadata is to be retrieved. If
|
The table Table 121-12 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 121-12 DBMS_METADATA: Object Types
Type Name | Meaning | Attributes | Notes |
---|---|---|---|
|
queues |
|
Dependent on table |
|
additional metadata for queue tables |
|
Dependent on table |
|
transforms |
|
None |
|
associate statistics |
|
None |
|
audits of SQL statements |
|
Modeled as dependent, granted object. The base object name is the statement audit option name (for example, |
|
audits of schema objects |
|
None |
|
clusters |
|
None |
|
comments |
|
None |
|
constraints |
|
Constraints do not include the following:
|
|
application contexts |
|
None |
|
all metadata objects in a database |
|
Corresponds to a full database export |
|
database links |
|
Modeled as schema objects because they have owners. For public links, the owner is
|
|
default roles |
|
Granted to a user by |
|
dimensions |
|
None |
|
directories |
|
None |
|
fine-grained audit policies |
|
Not modeled as named object because policy names are not unique. |
|
stored functions |
|
None |
|
precomputed statistics on indexes |
|
The base object is the index's table. |
|
indexes |
|
None |
|
indextypes |
|
None |
|
Java sources |
|
None |
|
jobs |
|
None |
|
external procedure libraries |
|
None |
|
materialized views |
|
None |
|
materialized view logs |
|
None |
|
object grants |
|
None |
|
Grants |
G |
Modeled as user grants. Grants the privileges of one user to other
user in the form
|
|
operators |
|
None |
|
stored packages |
|
By default, both package specification and package body are retrieved. See "SET_FILTER Procedure". |
|
package specifications |
|
None |
|
package bodies |
|
None |
|
stored procedures |
|
None |
|
profiles |
|
None |
|
proxy authentications |
|
Granted to a user by |
|
referential constraint |
|
None |
|
refresh groups |
|
None |
|
resource cost info |
|
None |
|
driving contexts for enforcement of fine-grained access-control policies |
|
Corresponds to the |
|
fine-grained access-control policy groups |
|
Corresponds to the DBMS_RLS.CREATE_GROUP procedure |
|
fine-grained access-control policies |
|
Corresponds to |
|
resource consumer groups |
|
Oracle Data Pump does not use these object types. Instead, it exports resource manager objects as procedural objects. |
|
assign initial consumer groups to users |
|
None |
|
resource plans |
|
None |
|
resource plan directives |
|
Dependent on resource plan |
|
roles |
|
None |
|
role grants |
|
None |
|
rollback segments |
|
None |
|
all metadata objects in a schema |
|
Corresponds to user-mode export. |
|
sequences |
|
None |
SQL_DOMAIN |
Data Use Case Domains |
SN |
A Data Use Case Domain is a high-level dictionary object that belongs to a schema and encapsulates a set of properties and constraints. The attributes and constraints are defined and managed only for the domain, and are automatically applied to all the columns of the given domain. |
|
synonyms |
See notes |
Private synonyms are schema objects. Public synonyms are not, but for the purposes of this API, their schema name is |
|
system privilege grants |
|
None |
|
tables |
|
None |
|
metadata describing row data for a table, nested table, or partition |
|
For partitions, the object name is the partition name. For nested tables, the object name is the storage table name. The base object is the top-level table to which the table data belongs. For nested tables and partitioning, this is the top-level table (not the parent table or partition). For nonpartitioned tables and non-nested tables this is the table itself. |
|
metadata for a table and its associated objects |
|
Corresponds to table-mode export |
|
precomputed statistics on tables |
|
None |
|
tablespaces |
|
None |
|
tablespace quotas |
|
Granted with |
|
metadata for objects in a transportable tablespace set |
|
Corresponds to transportable tablespace export |
|
triggers |
|
None |
|
trusted links |
|
None |
|
user-defined types |
|
By default, both type and type body are retrieved. See "SET_FILTER Procedure". . |
|
type specifications |
|
None |
|
type bodies |
|
None |
|
users |
|
None |
|
views |
|
None |
|
XML schema |
|
The object's name is its URL (which can be longer than 30 characters). Its schema is the user who registered it. |
|
Real Application Security (RAS) user |
|
Corresponds to RAS users |
|
Real Application Security (RAS) role |
|
Corresponds to RAS roles |
|
Real Application Security (RAS) rolesets |
|
Corresponds to RAS rolesets |
|
Real Application Security (RAS) role grants |
|
Corresponds to RAS role grants |
|
Real Application Security (RAS) security class |
|
Corresponds to RAS security classes |
|
Real Application Security (RAS) data security policy |
|
Corresponds to RAS data security policies |
|
Real Application Security (RAS) ACL |
|
Corresponds to RAS access control lists (ACLs) and associated access control entries (ACEs) |
|
Real Application Security (RAS) ACL parameter |
|
Corresponds to RAS access control lists (ACL) parameters |
|
Real Application Security (RAS) namespace |
|
Corresponds to RAS namespaces. |
Table Table 121-13 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 121-13 Object Types Returned for the Heterogeneous Object Type
Object Type | DATABASE_EXPORT | SCHEMA_EXPORT | TABLE_EXPORT | TRANSPORTABLE_EXPORT |
---|---|---|---|---|
|
Yes |
No |
No |
No |
|
Yes |
No |
No |
No |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
Yes |
No |
Yes |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
No |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
|
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
No |
No |
No |
|
Yes |
No |
No |
Yes |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
|
No |
No |
|
Yes |
No |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
No |
No |
No |
|
Yes |
No |
No |
No |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
Yes |
No |
No |
|
Yes |
No |
No |
No |
|
Yes |
No |
No |
Yes |
|
Yes |
No |
No |
Yes |
|
Yes |
Table data is retrieved according to policy |
Table data is retrieved according to policy |
Yes |
|
Yes |
No |
No |
No |
|
Yes |
No |
No |
No |
|
Yes |
No |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
|
No |
No |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
No |
No |
No |
|
Yes |
|
No |
No |
|
Yes |
Yes |
Yes |
Yes |
|
Yes |
No |
No |
No |
|
Yes |
Yes |
No |
Yes, if the types are used by tables in the transportable set |
|
Yes |
Yes |
No |
Yes, if the types are used by tables in the transportable set |
|
Yes |
Yes |
No |
Yes, if the types are used by tables in the transportable set |
|
Yes |
|
No |
No |
|
Yes |
Yes |
No |
No |
|
Yes |
Yes |
No |
No |
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
. ANULL
or invalid value was supplied for an input parameter. The error message text identifies the parameter. -
INVALID_OBJECT_PARAM
. Theversion
ormodel
parameter was not valid for theobject_type
.
121.6.8 OPENW Function
This function specifies the type of object to be submitted and the object model. The return value is an opaque context handle.
See Also:
For more information about related subprograms:
Syntax
DBMS_METADATA.OPENW (object_type IN VARCHAR2, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE') RETURN NUMBER;
Parameters
Table 121-14 OPENW Function Parameters
Parameter | Description |
---|---|
|
The type of object to be submitted. Valid types names and their meanings are listed in Table 121-12. The type cannot be a heterogeneous object type. |
|
The version of DDL to be generated by the
|
|
Specifies which view to use. Only the Oracle proprietary (ORACLE) view is supported by |
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
. Themodel
parameter was not valid for theobject_type
.
121.6.9 PUT Function
This function submits an XML document containing object metadata to the database to create the object.
See Also:
For more information about related subprograms:
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 121-15 PUT Function Parameters
Parameter | Description |
---|---|
|
The handle returned from |
|
The XML document containing object metadata for the type of the |
|
Reserved for future use |
|
Detailed results of the operation. |
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 database version specified in OPENW
.
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.
121.6.10 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.
See Also:
For more information about related subprograms:
Syntax
DBMS_METADATA.SET_COUNT ( handle IN NUMBER, value IN NUMBER, object_type_path IN VARCHAR2 DEFAULT NULL);
Parameters
Table 121-16 SET_COUNT Procedure Parameters
Parameter | Description |
---|---|
|
The handle returned from |
|
The maximum number of objects to retrieve. |
|
A path name designating the object types to which the count value applies. By default, the count value applies to the object type of the
|
Exceptions
-
INVALID_ARGVAL
. ANULL
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 toFETCH_xxx
for theOPEN
context. After the first call toFETCH_xxx
is made, no further calls toSET_COUNT
for the currentOPEN
context are permitted. -
INCONSISTENT_ARGS
.object_type
parameter is not consistent with handle.
121.6.11 SET_FILTER Procedure
This procedure specifies restrictions on the objects to be retrieved, for example, the object name or schema.
See Also:
For more information about related subprograms:
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 121-17 SET_FILTER Procedure Parameters
Parameter | Description |
---|---|
|
The handle returned from |
|
The name of the filter. For each filter, Table 121-18 lists the The Datatype column of Table 121-18 also indicates whether a text filter is an expression filter. An expression filter 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. Note that in PL/SQL and SQL*Plus, two single quotes (not a double quote) are needed to represent an apostrophe. For example, an example of a
The filter value is combined with a particular object attribute to produce a |
|
The value of the filter. Text, Boolean, and Numeric filters are supported. |
|
A path name designating the object types to which the filter applies. By default, the filter applies to the object type of the |
Table 121-18 describes the object type, name, datatype, and meaning of the filters available with the SET_FILTER
procedure.
Table 121-18 SET_FILTER: Filters
Object Type | Name | Datatype | Meaning |
---|---|---|---|
Named objects |
|
Text |
Objects with this exact name are selected. |
Named objects |
|
Text expression |
The filter value is combined with the object attribute corresponding to the object name to produce a By default, all named objects of |
Named objects |
|
Text expression |
The filter value is combined with the attribute corresponding to the object name to specify objects that are to be excluded from the set of objects fetched. By default, all named objects of the object type are selected. |
|
|
Boolean |
If |
Schema objects |
|
Text |
Objects in this schema are selected. If the object type is |
Schema objects |
|
Text expression |
The filter value is combined with the attribute corresponding to the object's schema. The default is determined as follows: - if - otherwise, objects in the current schema are selected. |
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Text |
Objects in this tablespace (or having a partition in this tablespace) are selected. |
|
|
Text expression |
The filter value is combined with the attribute corresponding to the object's tablespace (or in the case of a partitioned table or index, the partition's tablespaces). By default, objects in all tablespaces are selected. |
|
|
Boolean |
If Defaults to |
|
|
Boolean |
If Defaults to |
Dependent Objects |
|
Text |
Objects are selected that are defined or granted on objects with this name. Specify |
Dependent Objects |
|
Text |
Objects are selected that are defined or granted on objects in this schema. If |
Dependent Objects |
|
Text expression |
The filter value is combined with the attribute corresponding to the name of the base object. Not valid for schema and database triggers. |
Dependent Objects |
|
Text expression |
The filter value is combined with the attribute corresponding to the name of the base object to specify objects that are to be excluded from the set of objects fetched. Not valid for schema and database triggers. |
Dependent Objects |
|
Text expression |
The filter value is combined with the attribute corresponding to the schema of the base object. |
Dependent Objects |
|
Text |
The object type of the base object. |
Dependent Objects |
|
Text expression |
The filter value is combined with the attribute corresponding to the object type of the base object. By default no filtering is done on object type. |
Dependent Objects |
|
Text |
The tablespace of the base object. |
Dependent Objects |
|
Text expression |
The filter value is combined with the attribute corresponding to the tablespaces of the base object. By default, no filtering is done on the tablespace. |
|
|
Boolean |
If |
Granted Objects |
|
Text |
Objects are selected that are granted to this user or role. Specify |
Granted Objects |
|
Text |
The name of the privilege or role to be granted. For |
Granted Objects |
|
Text expression |
The filter value is combined with the attribute corresponding to the grantee name. |
Granted Objects |
|
Text expression |
The filter value is combined with the attribute corresponding to the grantee name to specify objects that are to be excluded from the set of objects fetched. |
|
|
Text |
Object grants are selected that are granted by this user. |
|
|
Text |
A name longer than 30 characters. Objects with this exact name are selected. If the object name is 30 characters or less, the |
|
|
Text |
The filter value is combined with the attribute corresponding to the object's long name. By default, no filtering is done on the long name of an object. |
All objects |
|
|
The text of a The other filters are intended to meet the needs of the majority of users. Use |
All objects |
|
Text |
The edition filter is accepted for any object type, but affects only objects that support editions. The filter is only accepted for local objects (that is, the |
|
|
|
The schema whose objects are selected. |
|
|
|
The filter value is either: combined with the attribute corresponding to a schema name to produce a combined with the attribute corresponding to a base schema name to produce a By default the current user's objects are selected. |
|
|
|
If Defaults to |
|
|
|
Objects (tables and their dependent objects) in this schema are selected. |
|
|
|
The filter value is either: combined with the attribute corresponding to a schema name to produce a combined with the attribute corresponding to a base schema name to produce a By default the current user's objects are selected. |
|
|
|
The table with this exact name is selected along with its dependent objects. |
|
|
Text expression |
The filter value is combined with the attribute corresponding to a table name in the queries that fetch tables and their dependent objects. By default all tables in the selected schemas are selected, along with their dependent objects. |
Heterogeneous objects |
|
Text |
The fully qualified path name of the first object type in the heterogeneous collection to be retrieved. Objects normally fetched prior to this object type will not be retrieved. |
Heterogeneous objects |
|
Text |
The fully qualified path name of an object type after which the heterogeneous retrieval should begin. Objects of this type will not be retrieved, nor will objects normally fetched prior to this object type. |
Heterogeneous objects |
|
Text |
The fully qualified path name of an object type where the heterogeneous retrieval should end. Objects of this type will not be retrieved, nor will objects normally fetched after this object type. |
Heterogeneous objects |
|
Text |
The fully qualified path name of the last object type in the heterogeneous collection to be retrieved. Objects normally fetched after this object type will not be retrieved. |
Heterogeneous objects |
|
Text expression |
For these two filters, the filter value is combined with the attribute corresponding to an object type path name to produce a
|
Usage Notes
-
Each call to
SET_FILTER
causes aWHERE
condition to be added to the underlying query that fetches the set of objects. TheWHERE
conditions are concatenated with theAND
keyword so that you can use multipleSET_FILTER
calls to refine the set of objects to be returned. For example to specify that you want the object namedEMP
in schemaSCOTT
, 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 isSYS
or has theSELECT_CATALOG_ROLE
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 thename
is the current user -
TABLE_EXPORT
whereSCHEMA
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 ofSELECT_CATALOG_ROLE
), you must make the program invokers-rights. -
-
For heterogeneous object types, the
BEGIN_WITH
andBEGIN_AFTER
filters allow restart on an object type boundary. Appropriate filter values are returned by theFETCH_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
theNAME
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 aNAME_EXPR
filter explicitly for theTABLE
object type path. Table 121-19 lists valid object type path names for the major heterogeneous object types along with an explanation of the scope of each path name. (The same information is available in the following catalog views:DATABASE_EXPORT_OBJECTS
,SCHEMA_EXPORT_OBJECTS
, andTABLE_EXPORT_OBJECTS
.) See Table 121-18 for filters defined for each path name. These path names are valid in theINCLUDE_PATH_EXPR
andEXCLUDE_PATH_EXPR
filters. Path names marked with an asterisk (*) are only valid in those filters; they cannot be used as values of theSET_FILTER
object_type_path
parameter.
Table 121-19 Object Type Path Names for Heterogeneous Object Types
Heterogeneous Type | Path Name (*=valid only in xxx_PATH_EXPR) | Scope |
---|---|---|
|
|
Object audits on the selected tables |
|
|
Table and column comments for the selected tables |
|
|
Constraints (including referential constraints) on the selected tables |
|
|
Object grants on the selected tables |
|
|
Indexes (including domain indexes) on the selected tables |
|
|
Object grants on the selected tables |
|
|
Referential (foreign key) constraints on the selected tables |
|
|
Statistics on the selected tables |
|
|
Row data for the selected tables |
|
|
Triggers on the selected tables |
|
|
Statistics type associations for objects in the selected schemas |
|
|
Audits on all objects in the selected schemas |
|
|
Clusters in the selected schemas and their indexes |
|
|
Comments on all objects in the selected schemas |
|
|
Constraints (including referential constraints) on all objects in the selected schemas |
|
|
Private database links in the selected schemas |
|
|
Default roles granted to users associated with the selected schemas |
|
|
Dimensions in the selected schemas |
|
|
Functions in the selected schemas and their dependent grants and audits |
|
|
Grants on objects in the selected schemas |
|
|
Indexes (including domain indexes) on tables and clusters in the selected schemas |
|
|
Indextypes in the selected schemas and their dependent grants and audits |
|
|
Java sources in the selected schemas and their dependent grants and audits |
|
|
Jobs in the selected schemas |
|
|
External procedure libraries in the selected schemas |
|
|
Materialized views in the selected schemas |
|
|
Materialized view logs on tables in the selected schemas |
|
|
Grants on objects in the selected schemas |
|
|
Operators in the selected schemas and their dependent grants and audits |
|
|
Packages (both specification and body) in the selected schemas, and their dependent grants and audits |
|
|
Package bodies in the selected schemas |
|
|
Package specifications in the selected schemas |
|
|
The password history for users associated with the selected schemas |
|
|
Procedures in the selected schemas and their dependent grants and audits |
|
|
Referential (foreign key) constraints on tables in the selected schemas |
|
|
Refresh groups in the selected schemas |
|
|
Sequences in the selected schemas and their dependent grants and audits |
|
|
Statistics on tables and indexes in the selected schemas |
|
|
Private synonyms in the selected schemas |
|
|
Tables in the selected schemas and their dependent objects (indexes, constraints, triggers, grants, audits, comments, table data, and so on) |
|
|
Row data for tables in the selected schemas |
|
|
Tablespace quota granted to users associated with the selected schemas |
|
|
Triggers on tables in the selected schemas |
|
|
Oracle Real Application Security (RAS) security classes |
|
|
Oracle Real Application Security (RAS) data security policies |
|
|
Oracle Real Application Security (RAS) access control lists (ACLs) |
|
|
Types (both specification and body) in the selected schemas, and their dependent grants and audits |
|
|
Type bodies in the selected schemas |
|
|
Type specifications in the selected schemas |
|
|
User definitions for users associated with the selected schemas |
|
|
Views in the selected schemas and their dependent objects (grants, constraints, comments, audits) |
|
|
Statistics type associations for objects in the database |
|
|
Audits of SQL statements |
|
|
Audits on all objects in the database |
|
|
Clusters and their indexes |
|
|
Comments on all objects |
|
|
Constraints (including referential constraints) |
|
|
Application contexts |
|
|
Private and public database links |
|
|
Default roles granted to users in the database |
|
|
Dimensions in the database |
|
|
Directory objects in the database |
|
|
Fine-grained audit policies |
|
|
Functions |
|
|
Object and system grants |
|
|
Indexes (including domain indexes) on tables and clusters |
|
|
Indextypes and their dependent grants and audits |
|
|
Java sources and their dependent grants and audits |
|
|
Jobs |
|
|
External procedure libraries |
|
|
Materialized views |
|
|
Materialized view logs |
|
|
All object grants in the database |
|
|
Operators and their dependent grants and audits |
|
|
Packages (both specification and body) and their dependent grants and audits |
|
|
Package bodies |
|
|
Package specifications |
|
|
Password histories for database users |
|
|
The password complexity verification function |
|
|
Procedures and their dependent grants and objects |
|
|
Profiles |
|
|
Proxy authentications |
|
|
Referential (foreign key) constraints on tables in the database |
|
|
Refresh groups |
|
|
Resource cost information |
|
|
Fine-grained access-control driving contexts |
|
|
Fine-grained access-control policy groups |
|
|
Fine-grained access-control policies |
|
|
Roles |
|
|
Role grants to users in the database |
|
|
Rollback segments |
|
|
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 |
|
|
Sequences |
|
|
Statistics on tables and indexes |
|
|
Public and private synonyms |
|
|
System privilege grants |
|
|
Tables and their dependent objects (indexes, constraints, triggers, grants, audits, comments, table data, and so on) |
|
|
Row data for all tables |
|
|
Tablespace definitions |
|
|
Tablespace quota granted to users in the database |
|
|
Triggers on the database, on schemas, and on schema objects |
|
|
Oracle Real Application Security (RAS) users |
|
|
Oracle Real Application Security (RAS) roles |
|
|
Oracle Real Application Security (RAS) security classes |
|
|
Oracle Real Application Security (RAS) data security policies |
|
|
Oracle Real Application Security (RAS) access control lists (ACLs) |
|
|
Oracle Real Application Security (RAS) namespaces |
|
|
Trusted links |
|
|
Types (both specification and body) and their dependent grants and audits |
|
|
Type bodies |
|
|
Type specifications |
|
|
User definitions |
|
|
Views |
Exceptions
-
INVALID_ARGVAL
. ANULL
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 toFETCH_xxx
for theOPEN
context. After the first call toFETCH_xxx
is made, no further calls toSET_FILTER
are permitted. -
INCONSISTENT_ARGS
. The arguments are inconsistent. Possible inconsistencies include the following:-
The filter name is not valid for the object type associated with the
OPEN
context. -
The filter
name
is not valid for theobject_type_path
. -
The
object_type_path
is not part of the collection designated byhandle
. -
The filter
value
is the wrong datatype.
-
121.6.12 SET_PARSE_ITEM Procedure
This procedure is used for both retrieval and submission. This procedure enables output parsing and specifies an object attribute to be parsed and returned.
See Also:
For more information about related subprograms:
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 121-20 SET_PARSE_ITEM Procedure Parameters
Parameter | Description |
---|---|
|
The handle returned from |
|
The name of the object attribute to be parsed and returned. See Table 121-21 for the attribute object type, name, and meaning. |
|
Designates the object type to which the parse item applies (this is an object type name, not a path name). By default, the parse item applies to the object type of the
This parameter only applies when |
Table 121-21 describes the object type, name, and meaning of the items available in the SET_PARSE_ITEM
procedure.
Because new items are occasionally added, you can query the DBMS_METADATA_PARSE_ITEMS
view to see a complete list of valid parse items or to find valid parse items for a specific object type.
Table 121-21 SET_PARSE_ITEM: Parse Items
Object Type | Name | Meaning |
---|---|---|
All objects |
|
If If |
All objects |
|
If If |
Schema objects |
|
The object schema is returned. If the object is not a schema object, no value is returned. |
Named objects |
|
The object name is returned. If the object is not a named object, no |
|
|
The name of the object's tablespace or, if the object is a partitioned table, the default tablespace is returned. For a |
|
|
If the trigger is enabled, |
|
|
The grantor is returned. |
Dependent objects (including domain index secondary tables) |
|
The name of the base object is returned. If the object is not a dependent object, no value is returned. |
Dependent objects (including domain index secondary tables) |
|
The schema of the base object is returned. If the object is not a dependent object, no value is returned. |
Dependent objects (including domain index secondary tables) |
|
The object type of the base object is returned. If the object is not a dependent object, no value is returned. |
Granted objects |
|
The grantee is returned. If the object is not a granted object, no value is returned. |
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:
If Object Is | NAME, SCHEMA | BASE_OBJECT_NAME, BASE_OBJECT_SCHEMA |
---|---|---|
nonpartitioned table |
table name, schema |
table name, schema |
table partition |
partition name, schema |
table name, schema |
nested table |
storage table name, schema |
name and schema of top-level table (not the parent nested table) |
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.
See Also:
-
Oracle Database Utilities for more information about using the metadata APIs.
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
. ANULL
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 toFETCH_xxx
for theOPEN
context. After the first call toFETCH_xxx
is made, no further calls toSET_PARSE_ITEM
are permitted. -
INCONSISTENT_ARGS
. The attributename
is not valid for the object type associated with theOPEN
context.
121.6.13 SET_TRANSFORM_PARAM and SET_REMAP_PARAM Procedures
These procedures are used for both retrieval and submission. 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.
See Also:
For more information about related subprograms:
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 121-22 describes the parameters for the SET_TRANSFORM_PARAM
and SET_REMAP_PARAM
procedures.
Because new parameters are occasionally added, you might want to query the DBMS_METADATA_TRANSFORM_PARAMS
view to see all the valid transform parameters for each transform or to find valid transform parameters for specific object types.
Table 121-22 SET_TRANSFORM_PARAM and SET_REMAP_PARAM Parameters
Parameters | Description |
---|---|
|
The name of the transform parameter. For descriptions of the parameters available for each transform on the Table 121-23 - DDL transform Table 121-24 - MODIFY transform Table 121-26 - SXML transform Table 121-27 - MODIFYSXML transform Table 121-28 - SXMLDDL transform For descriptions of the parameters available for the MODIFY transform on the For descriptions of the parameters available for the ALTERXML transform, see Table 121-4. |
|
The new value for the remapping. This parameter is valid
only for |
|
Designates the object type to which the transform or remap parameter applies. By default, it applies to the same object type as the transform. In cases where the transform applies to all object types within a heterogeneous collection, the following apply:
This allows a caller who has added a transform to a heterogeneous collection to specify different transform parameters for different object types within the collection. |
|
The old value for the remapping. This parameter is valid only
for |
|
Either (1) the handle returned from
Note that the handle returned by For |
|
The value of the transform. This parameter is valid only for |
Table 121-23 describes the object type, name, datatype, and meaning of the parameters for the DDL transform in the SET_TRANSFORM_PARAM
procedure.
Table 121-23 SET_TRANSFORM_PARAM: Transform Parameters for the DDL Transform
Object Type | Name | Datatype | Meaning |
---|---|---|---|
USER , TABLE , CLUSTER , VIEW , MATERIALIZED_VIEW PROCEDURE , FUNCTION , PACKAGE , TYPE , TRIGGER |
COLLATION_CLAUSE |
Text |
There are three possible values:
|
|
OMIT_ENCRYPTION_CLAUSE |
|
If set to If set to |
|
DWCS_CVT_IOTS |
|
If set to If set to |
|
DWCS_CVT_CONSTRAINTS |
|
If set to If set to |
|
CONSTRAINT_USE_DEFAULT_INDEX |
|
This transform parameter affects the generation of index relating the If set to |
|
CONSTRAINT_NAME_FROM_INDEX |
|
This transform parameter affects the generation of If set to |
|
INCLUDE_SHARDING_CLAUSES |
|
This transform parameter enables sharding keywords for the
transform to facilitate creating sharded tables, sequences, tablespaces
and tablespace set. Options: Default: When set to
When the |
|
|
|
If |
|
|
|
If |
|
|
|
If |
|
|
|
If |
|
|
|
If
TRUE , include 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.
Note: TheCONSTRAINTS_AS_ALTER parameter has no effect when
an index and a constraint are created for a table in two separate DDL
statements.
|
|
|
|
If |
|
|
|
If |
|
|
|
If |
|
|
|
If |
|
|
|
If |
|
|
|
If |
|
|
|
If |
|
|
|
If |
|
|
|
If |
|
|
|
If Note: This object type is being deprecated. |
|
|
|
Calling |
|
|
|
If |
|
|
|
The name of a user from whom the role must be revoked. If this is a non-null string and if the Note: When you issue a Defaults to null string. |
|
|
|
If Defaults to |
|
|
|
A number representing the percentage by which space allocation for the object type is to be modified. The value is the number of one-hundredths of the current allocation. For example, 100 means 100%. If the object type is - in file specifications, the value of - - For other object types, |
|
|
|
Specifies the storage type to use for LOB segments. The options are as follows:
Specifying this transform changes the LOB storage for all tables in the job, including tables that provide storage for materialized views. |
|
|
|
Specifies a table compression clause (for example, Specify Specifying this transform changes the compression type for all tables in the job, including tables that provide storage for materialized views. |
Table 121-24 describes the object type, name, datatype, and meaning of the parameters for the MODIFY transform in the SET_TRANSFORM_PARAM
procedure.
Table 121-24 SET_TRANSFORM_PARAM: Transform Parameters for the MODIFY Transform
Object Type | Name | Datatype | Meaning |
---|---|---|---|
|
|
|
A number designating the object row for an object. The object in the document that corresponds to this number will be copied to the output document. This parameter is additive. By default, all objects are copied to the output document. |
Table 121-25 describes the object type, name, datatype, and meaning of the parameters for the MODIFY transform in the SET_REMAP_PARAM
procedure.
Table 121-25 SET_REMAP_PARAM: Transform Parameters for the MODIFY Transform
Object Type | Name | Datatype | Meaning |
---|---|---|---|
|
|
|
Objects in the document will have their filespecs renamed as follows: any filespec matching This parameter is additive. By default, filespecs are not renamed. |
Named objects and all objects dependent on named objects |
|
Text |
Any named object in the document whose name matches Any dependent object whose base object name matches This parameter is additive. By default, names are not remapped. (Use |
|
|
|
Any schema object in the document whose name matches Any dependent object whose base object schema name matches Any granted object whose grantee name matches Any user whose name matches This parameter is additive. By default, schemas are not remapped. NOTE: The mapping may not be 100 percent complete because there are certain schema references that Import is not capable of finding. For example, Import will not find schema references embedded within the body of definitions of triggers, types, views, procedures, and packages. |
|
|
|
Objects in the document will have their tablespaces renamed as follows: any tablespace name matching This parameter is additive. By default, tablespaces are not remapped. |
Table 121-26 SET_TRANSFORM_PARAM: Transform Parameters for the SXML Transform
Object type | Name | Datatype | Meaning |
---|---|---|---|
USER , TABLE , CLUSTER , VIEW , MATERIALIZED_VIEW PROCEDURE , FUNCTION , PACKAGE , TYPE , TRIGGER |
COLLATION_CLAUSE |
Text |
There are three possible values:
|
|
|
Boolean |
|
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If |
Table 121-27 SET_TRANSFORM_PARAM: Transform Parameters for the MODIFYSXML Transform
Object type | Name | Datatype | Meaning |
---|---|---|---|
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Text |
Any This does not apply to column names. (See |
|
|
Text |
Any |
|
|
Text |
Any column in the document whose name matches |
|
|
Boolean |
If |
|
|
Boolean |
If |
Table 121-28 SET_TRANSFORM_PARAM: Transform Parameters for the SXMLDDL Transform
Object type | Name | Datatype | Meaning |
---|---|---|---|
USER , TABLE , CLUSTER , VIEW , MATERIALIZED_VIEW PROCEDURE , FUNCTION , PACKAGE , TYPE , TRIGGER |
COLLATION_CLAUSE |
Text |
There are three possible values:
|
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If |
|
|
Boolean |
If Defaults to |
|
|
Boolean |
If |
|
|
Boolean |
If |
Exceptions
-
INVALID_ARGVAL.
ANULL
or invalid value was supplied for an input parameter. The error message text identifies the parameter. -
INVALID_OPERATION.
EitherSET_TRANSFORM_PARAM
orSET_REMAP_PARAM
was called after the first call toFETCH_xxx
for theOPEN
context. After the first call toFETCH_xxx
is made, no further calls toSET_TRANSFORM_PARAM
orSET_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 theOPEN
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');
Note:
The number of remap parameters that can be specified for a MODIFY
transform is limited to ten. That is, you can specify up to ten REMAP_DATAFILE
parameters, up to ten REMAP_SCHEMA
parameters and so on. Additional instances are ignored. To work around this, you can perform another DBMS_METADATA.ADD_TRANSFORM
and specify additional remap parameters.
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.
Note:
The enumerated constant must be prefixed with the package name DBMS_METADATA.SESSION_TRANSFORM
.