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 Metadata APIThis chapter contains the following topics:
Overview
Security Model
Rules and Limits
Subprograms for Retrieving Multiple Objects From the Database
Subprograms for Submitting XML to the Database
This section contains topics which relate to using the DBMS_METADATA package.
You can use the DBMS_METADATA package to retrieve metadata and also to submit XML, as described in the following sections.
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, and CLOSE retrieve multiple objects.
For use in SQL queries and for browsing: GET_XML, GET_DDL and GET_SXML 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.
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.
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 of SELECT_CATALOG_ROLE), you must make the program invokers-rights.
For all objects that have passwords (for example, db links, 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 the EXP_FULL_DATABASE role, and users who own an object can see all metadata for that object, including passwords.
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.
The DBMS_METADATA package defines, in the SYS schema, the following OBJECT and TABLE types.
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;
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 87-1 provides a summary, in alphabetical order, of DBMS_METADATA subprograms used to retrieve multiple objects from a database.
Table 87-2 provides a summary, in alphabetical order, of DBMS_METADATA subprograms used to submit XML metadata to a database.
Table 87-1 lists the subprograms used for retrieving multiple objects from the database.
Table 87-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 |
Table 87-2 lists the subprograms used for submitting XML to the database.
Table 87-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 |
|
|
|
Table 87-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 |
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:DBMS_METADATA.ADD_TRANSFORM ( handle IN NUMBER, name IN VARCHAR2, encoding IN VARCHAR2 DEFAULT NULL, object_type IN VARCHAR2 DEFAULT NULL) RETURN NUMBER;
Table 87-4 ADD_TRANSFORM Function Parameters
| Parameters | Description |
|---|---|
|
|
The handle returned from |
|
|
The name of the transform. If name contains a period, colon, or forward slash, it is interpreted as the URL of a user-supplied XSLT script. See Oracle XML DB Developer's Guide. Otherwise, name designates a transform implemented by
|
|
|
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 NULL and the URL is internal to the database (that is, it begins with /oradb/), then the encoding is assumed to be the database character set. |
|
|
The definition of this parameter depends upon whether you are retrieving objects or submitting XML metadata.
|
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.
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. 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.
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
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:Note:
The following notes apply only to object retrievalYou 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.
The 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.
See Also:
For more information about related subprograms: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);
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, but CONVERT gets its XML document from the caller
FETCH_DDL returns its results in a sys.ku$_ddls nested table, but CONVERT returns a sys.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.
INVALID_ARGVAL. A NULL 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.
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: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);
Table 87-7 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 |
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. By default, the CLOB is read into the buffer cache and has session duration, but these defaults can be overridden with the cache_lob and lob_duration parameters.
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, a practice that involves an expensive LOB copy.
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).
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 following GET_xxx functions let you fetch metadata for objects with a single call:
GET_XML
GET_DDL
GET_SXML
GET_DEPENDENT_XML
GET_DEPENDENT_DDL
GET_GRANTED_XML
GET_GRANTED_DDL
See Also:
For more information about related subprograms: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;
Table 87-8 GET_xxx Function Parameters
| Parameter | Description |
|---|---|
|
|
The type of object to be retrieved. This parameter takes the same values as the |
|
|
The object name. It is used internally in a |
|
|
The object schema. It is used internally in a |
|
|
The version of metadata to be extracted. This parameter takes the same values as the |
|
|
The object model 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. It is used internally in a |
|
|
The base object schema. It is used internally in a |
|
|
The grantee. It is used internally in a |
|
|
The maximum number of objects to return. See SET_COUNT Procedure . |
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, 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, 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.
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.
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;
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:This 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: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;
Table 87-10 Open Function Parameters
| Parameter | Description |
|---|---|
|
|
The type of object to be retrieved. Table 87-11 lists the valid type names and their meanings. These object types will be supported for the ORACLE model of metadata (see The Attributes column in Table 87-11 specifies some object type attributes:
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 ORACLE model is supported. |
|
|
|
The name of a database link to the database whose metadata is to be retrieved. If NULL (the default), metadata is retrieved from the database on which the caller is running |
Table 87-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 87-11 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 |
|
Does not include:
|
|
|
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 PUBLIC. For private links, the creator is the owner. |
|
|
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 |
|
|
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 DBMS_RLS.ADD_POLICY_CONTENT procedure |
|
|
fine-grained access-control policy groups |
|
Corresponds to the DBMS_RLS.CREATE_GROUP procedure |
|
|
fine-grained access-control policies |
|
Corresponds to DBMS_RLS.ADD_GROUPED_POLICY. Not modeled as named objects because policy names are not unique. |
|
|
resource consumer groups |
|
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 |
|
|
synonyms |
|
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 may be longer than 30 characters). Its schema is the user who registered it. |
Table 87-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 87-12 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 |
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.
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:DBMS_METADATA.OPENW (object_type IN VARCHAR2, version IN VARCHAR2 DEFAULT 'COMPATIBLE', model IN VARCHAR2 DEFAULT 'ORACLE') RETURN NUMBER;
Table 87-13 OPENW Function Parameters
| Parameter | Description |
|---|---|
|
|
The type of object to be submitted. Valid types names and their meanings are listed in Table 87-11. 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 |
An opaque handle to write context. This handle is used as input to the ADD_TRANSFORM, CONVERT, PUT, and CLOSE procedures.
This function submits an XML document containing object metadata to the database to create the object.
See Also:
For more information about related subprograms: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;
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.
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.
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:DBMS_METADATA.SET_COUNT ( handle IN NUMBER, value IN NUMBER, object_type_path IN VARCHAR2 DEFAULT NULL);
Table 87-15 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
|
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.
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: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);
Table 87-16 SET_FILTER Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The handle returned from |
|
|
The name of the filter. For each filter, Table 87-17 lists the The Datatype column of Table 87-17 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 87-17 describes the object type, name, datatype, and meaning of the filters available with the SET_FILTER procedure.
Table 87-17 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. |
|
Schema objects |
|
text |
Objects in this schema are selected. If the object type is SYNONYM, specify PUBLIC to select public synonyms. |
|
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 TABLESPACE_QUOTA, only UNLIMITED can be specified. |
|
Granted Objects |
|
text expression |
The filter value is combined with the attribute corresponding to the privilege or role name. By default, all privileges/roles are returned. |
|
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
|
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 87-18 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, and TABLE_EXPORT_OBJECTS.) See Table 87-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 87-18 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 |
|
|
|
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 |
|
|
|
Trusted links |
|
|
|
Types (both specification and body) and their dependent grants and audits |
|
|
|
Type bodies |
|
|
|
Type specifications |
|
|
|
User definitions |
|
|
|
Views |
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
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: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);
Table 87-19 SET_PARSE_ITEM Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The handle returned from |
|
|
The name of the object attribute to be parsed and returned. See Table 87-20 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 OPEN handle. When the
This parameter only applies when |
Table 87-20 describes the object type, name, and meaning of the items available in the SET_PARSE_ITEM procedure.
Table 87-20 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 value is returned. |
|
|
|
The name of the object's tablespace or, if the object is a partitioned table, the default tablespace is returned. For a TABLE_DATA object, this is always the tablespace where the rows are stored. |
|
|
|
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. |
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 information about using the Metadata API
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.
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.
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: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);
Table 87-21 describes the parameters for the SET_TRANSFORM_PARAM and SET_REMAP_PARAM procedures.
Table 87-21 SET_TRANSFORM_PARAM and SET_REMAP_PARAM Parameters
| Parameters | Description |
|---|---|
|
|
Either (1) the handle returned from Note that the handle returned by For |
|
|
The name of the parameter. Table 87-22 lists the transform parameters defined for the DDL transform, specifying the Table 87-23 describes the parameters for the MODIFY transform in the Table 87-24 describes the parameters for the MODIFY transform in the |
|
|
The value of the transform. This parameter is valid only for |
|
|
The old value for the remapping. This parameter is valid only for |
|
|
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. |
Table 87-22 describes the object type, name, datatype, and meaning of the parameters for the DDL transform in the SET_TRANSFORM_PARAM procedure.
Table 87-22 SET_TRANSFORM_PARAM: Transform Parameters for the DDL Transform
| Object Type | Name | Datatype | Meaning |
|---|---|---|---|
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
If |
|
|
|
|
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-hundreths of the current allocation. For example, 100 means 100%. If the object type is - in file specifications, the value of - - For other object types, |
Table 87-23 describes the object type, name, datatype, and meaning of the parameters for the MODIFY transform in the SET_TRANSFORM_PARAM procedure.
Table 87-23 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 87-24 describes the object type, name, datatype, and meaning of the parameters for the MODIFY transform in the SET_REMAP_PARAM procedure.
Table 87-24 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. |
|
|
|
|
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. |
|
|
|
|
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. |
|
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 |
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.
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 tenREMAP_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 nameDBMS_METADATA.SESSION_TRANSFORM.