Skip Headers

Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)

Part Number A96612-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

DBMS_METADATA , 2 of 2


Summary of DBMS_METADATA Subprograms

Table 30-1 provides a summary of DBMS_METADATA subprograms.

Table 30-1  DBMS_METADATA Subprograms
Subprogram Description

OPEN Procedure

Specifies the type of object to be retrieved, the version of its metadata, and the object model.

SET_FILTER Procedure

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

SET_COUNT Procedure

Specifies the maximum number of objects to be retrieved in a single FETCH_xxx call.

GET_QUERY Procedure

Returns the text of the queries that are used by FETCH_xxx.

SET_PARSE_ITEM Procedure

Enables output parsing by specifying an object attribute to be parsed and returned.

ADD_TRANSFORM Procedure

Specifies a transform that FETCH_xxx applies to the XML representation of the retrieved objects.

SET_TRANSFORM_PARAM Procedure

Specifies parameters to the XSLT stylesheet identified by transform_handle.

FETCH_xxx Procedure

Returns metadata for objects meeting the criteria established by OPEN, SET_FILTER, SET_COUNT, ADD_TRANSFORM, and so on.

CLOSE Procedure

Invalidates the handle returned by OPEN and cleans up the associated state.

GET_XML and GET_DDL Functions

Returns the metadata for the specified object as XML or DDL.

GET_DEPENDENT_XML and GET_DEPENDENT_DDL Functions

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

GET_GRANTED_XML and GET_GRANTED_DDL Functions

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

OPEN Procedure

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

Syntax

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

Parameters

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

Table 30-2  Open() Parameters
Parameter Description

object_type

The type of object to be retrieved. Table 30-3 lists the valid type names and their meanings. These object types will be supported for the ORACLE model of metadata (see model in this table) in Oracle9i. Future models may support a different set of object types.

The "Attributes" column specifies some object type attributes. Schema objects, such as tables, belong to schemas. Named objects have unique names (if they are schema objects, the name is unique to the schema). Dependent objects, such as indexes, are defined with reference to a base schema object. Granted objects are granted or assigned to a user or role and therefore have a named grantee.

These differences are relevant when choosing object selection criteria. See "SET_FILTER Procedure" for more information.

version

The version of metadata to be extracted. Database objects or attributes that are incompatible with the version will not be extracted. Legal values for this parameter are:

COMPATIBLE (default)--the version of the metadata corresponds to the database compatibility level. Note that database compatibility must be set to 9.0.1 or higher.

LATEST--the version of the metadata corresponds to the database version.

A specific database version, for example, 9.0.1.

model

Specifies which view to use, because the API can support multiple views on the metadata. Only the ORACLE model is supported in Oracle9i.

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

Table 30-3  DBMS_METADATA: Object Types
Type Name Meaning Attributes Notes

ASSOCIATION

associate statistics

D

AUDIT

audits of SQL statements

DG

Modeled as dependent, granted object. The base object name is the statement audit option name (for example, ALTER SYSTEM). There is no base object schema. The grantee is the user or proxy whose statements are audited.

AUDIT_OBJ

audits of schema objects

D

None

CLUSTER

clusters

SN

None

COMMENT

comments

D

None

CONSTRAINT

constraints

SND

Does not include:

  • primary key constraint for IOT
  • column NOT NULL constraints
  • certain REF SCOPE and WITH ROWID constraints for tables with REF columns

CONTEXT

application contexts

N

None

DB_LINK

database links

SN

Modeled as schema objects because they have owners. For public links, the owner is PUBLIC. For private links, the creator is the owner.

DEFAULT_ROLE

default roles

G

Granted to a user by ALTER USER

DIMENSION

dimensions

SN

None

DIRECTORY

directories

N

None

FUNCTION

stored functions

SN

None

INDEX

indexes

SND

None

INDEXTYPE

indextypes

SN

None

JAVA_SOURCE

Java sources

SN

None

LIBRARY

external procedure libraries

SN

None

MATERIALIZED_VIEW

materialized views

SN

None

MATERIALIZED_VIEW_LOG

materialized view logs

D

None

OBJECT_GRANT

object grants

DG

None

OPERATOR

operators

SN

None

OUTLINE

stored outlines

N

None

PACKAGE

stored packages

SN

By default, both package specification and package body are retrieved. See "SET_FILTER Procedure".

PACKAGE_SPEC

package specifications

SN

None

PACKAGE_BODY

package bodies

SN

None

PROCEDURE

stored procedures

SN

None

PROFILE

profiles

N

None

PROXY

proxy authentications

G

Granted to a user by ALTER USER

REF_CONSTRAINT

referential constraint

SND

None

ROLE

roles

N

None

ROLE_GRANT

role grants

G

None

ROLLBACK_SEGMENT

rollback segments

N

None

SEQUENCE

sequences

SN

None

SYNONYM

synonyms

See notes.

Private synonyms are schema objects. Public synonyms are not, but for the purposes of this API, their schema name is PUBLIC. The name of a synonym is considered to be the synonym itself. For example, in CREATE PUBLIC SYNONYM FOO FOR BAR, the resultant object is considered to have name FOO and schema PUBLIC.

SYSTEM_GRANT

system privilege grants

G

None

TABLE

tables

SN

None

TABLESPACE

tablespaces

N

None

TABLESPACE_QUOTA

tablespace quotas

G

Granted with ALTER USER

TRIGGER

triggers

SND

None

TRUSTED_DB_LINK

trusted links

N

None

TYPE

user-defined types

SN

By default, both type and type body are retrieved. See "SET_FILTER Procedure".

TYPE_SPEC

type specifications

SN

None

TYPE_BODY

type bodies

SN

None

USER

users

N

None

VIEW

views

SN

None

XMLSCHEMA

XML schema

SN

The object's name is its URL (which may be longer than 30 characters). Its schema is the user who registered it.

Returns

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

Exceptions

SET_FILTER Procedure

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

Syntax

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

Parameters

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

Table 30-4  SET_FILTER Parameters
Parameter Description

handle

The handle returned from OPEN.

name

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

value

The value of the filter.

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

Table 30-5  SET_FILTER: Filters
Object Type Name Datatype Meaning

Named objects

NAME

text

Objects with this exact name are selected.

NAME_EXPR

text

The filter value is the right-hand side of a SQL comparison, that is, a SQL comparison operator (=,!=, and so on) and the value compared against. The value must contain parentheses and quotation marks where appropriate. In PL/SQL and SQL*Plus, two single quotes (not a double quote) are needed to represent an apostrophe. For example:

'IN (''DEPT'',''EMP'')'

The filter value is combined with the object attribute corresponding to the object name to produce a WHERE condition in the query that fetches the objects. In the preceding example, objects named DEPT and EMP are retrieved.

By default, all named objects of object_type are selected.

Schema objects

SCHEMA

text

Objects in this schema are selected.

SCHEMA_EXPR

text

The filter value is the right-hand side of a SQL comparison. The filter value is combined with the object attribute corresponding to the object schema to produce a WHERE condition in the query that fetches the objects. See NAME_EXPR for syntax details.

Default:

- if BASE_OBJECT_SCHEMA is specified, then objects in that schema are selected;

- otherwise, objects in the current schema are selected.

See "Security" .

PACKAGE,

TYPE

SPECIFICATION

Boolean

If TRUE, retrieve the package or type specification. Defaults to TRUE.

BODY

Boolean

If TRUE, retrieve the package or type body. Defaults to TRUE.

TABLE

TABLESPACE

text

Objects in this tablespace (or having a partition in this tablespace) are selected.

TABLESPACE_EXPR

text

The filter value is the right-hand side of a SQL comparison. The filter value is combined with the attribute corresponding to the object's tablespace (or in the case of a partitioned table, the partition's tablespaces) to produce a WHERE condition in the query that fetches the objects. See NAME_EXPR for syntax details. By default, objects in all tablespaces are selected.

Dependent Objects

BASE_OBJECT_NAME

text

Objects are selected that are defined or granted on objects with this name. Specify SCHEMA for triggers on schemas. Specify DATABASE for database triggers. Column-level comments cannot be selected by column name; the base object name must be the name of the table, view, or materialized view containing the column.

BASE_OBJECT_SCHEMA

text

Objects are selected that are defined or granted on objects in this schema. If BASE_OBJECT_NAME is specified with a value other than SCHEMA or DATABASE, this defaults to the current schema.

INDEX, TRIGGER

SYSTEM_GENERATED

Boolean

If TRUE, select indexes or triggers even if they are system-generated. If FALSE, omit system-generated indexes or triggers. Defaults to TRUE.

Granted Objects

GRANTEE

text

Objects are selected that are granted to this user or role. Specify PUBLIC for grants to PUBLIC.

OBJECT_GRANT

GRANTOR

text

Object grants are selected that are granted by this user.

SYNONYM, JAVA_SOURCE, XMLSCHEMA

LONGNAME

text

A name longer than 30 characters. Objects with this exact name are selected. If the object name is 30 characters or less, the NAME filter must be used.

LONGNAME_EXPR

text

The filter value is the right-hand side of a SQL comparison. The filter value is combined with the attribute corresponding to the object's long name to produce a WHERE condition in the query that fetches the objects. See NAME_EXPR for syntax details. By default, no filtering is done on the long name of an object.

All objects

CUSTOM_FILTER

text

The text of a WHERE condition. The condition is appended to the query that fetches the objects. By default, no custom filter is used. The other filters are intended to meet the needs of the majority of users. Use CUSTOM_FILTER when no defined filters exists for your purpose. Of necessity such a filter depends on the detailed structure of the UDTs and views used in the query that are defined in admin/catmeta.sql. Because filters may change from version to version, upward compatibility is not guaranteed.

Exceptions

Security

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

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

Usage Notes

SET_COUNT Procedure

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

Syntax

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

Parameters

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

Table 30-6  SET_COUNT Parameters
Parameter Description

handle

The handle returned from OPEN.

value

The number of objects to retrieve.

Exceptions

GET_QUERY Procedure

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

Syntax

DBMS_METADATA.GET_QUERY (
   handle  IN NUMBER)
RETURN VARCHAR2;

Parameters

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

Table 30-7  GET_QUERY Parameters
Parameter Description

handle

The handle returned from OPEN.

Returns

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

Exceptions

SET_PARSE_ITEM Procedure

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

Syntax

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

Parameters

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

Table 30-8  SET_PARSE_ITEM Parameters
Parameter Description

handle

The handle returned from OPEN.

name

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

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

Table 30-9  SET_PARSE_ITEM: Parse Items
Object Type Name Meaning

All objects

VERB

For every row in the sys.ku$_ddls nested table returned by fetch_ddl the verb in the corresponding ddlText is returned. If the ddlText is a SQL DDL statement, then the SQL verb (for example, CREATE, GRANT, AUDIT) is returned. If the ddlText is a procedure call (for example., DBMS_RLS.ADD_POLICY_CONTEXT) then the package.procedure-name is returned.

OBJECT_TYPE

If the ddlText is a SQL DDL statement whose verb is CREATE or ALTER, the object type as used in the DDL statement is returned, for example, TABLE, PACKAGE BODY, and so on. Otherwise, an object type name from Table 30-3, " DBMS_METADATA: Object Types" is returned.

SCHEMA

The object schema is returned. If the object is not a schema object, NULL is returned.

NAME

The object name is returned. If the object is not a named object, NULL is returned.

TABLE,

INDEX

TABLESPACE

The tablespace name of the table or index is returned.

TRIGGER

ENABLE

If the trigger is enabled, ENABLE is returned. If the trigger is disabled, DISABLE is returned.

Exceptions

Usage Notes

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

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

See Also:

ADD_TRANSFORM Procedure

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

Syntax

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

Parameters

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

Table 30-10  ADD_TRANSFORM Parameters
Parameters Description

handle

The handle returned from OPEN.

name

The name of the transform. If the name is DDL, creation DDL will be generated using XSLT stylesheets stored within the Oracle dictionary. If the name contains a period (.), colon (:) or forward slash (/), it is interpreted as the URL of a user-supplied XSLT stylesheet. See Oracle9i XML Database Developer's Guide - Oracle XML DB.

encoding

The name of NLS character set (see National Language Support Guide) in which the stylesheet pointed to by name is encoded. This is only valid if the name is a URL. If left NULL and the URL is external to the database (e.g, /usr/williams/xsl/mystylesheet.xsl), UTF-8 encoding is assumed. If left NULL and the URL is internal to the database, that is, it begins with /oradb/, then the database character set is assumed to be the encoding.

Returns

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

Exceptions

Usage Notes

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

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

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

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


Note:

The output of the DDL transform is not an XML document. Therefore, no transform should be added after the DDL transform.


SET_TRANSFORM_PARAM Procedure

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

Syntax

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

Parameters

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

Table 30-11  SET_TRANSFORM_PARAM Parameters
Parameters Description

transform_handle

Either (1) the handle returned from ADD_TRANSFORM, or (2) the enumerated constant SESSION_TRANSFORM that designates the DDL transform for the whole session. Note that the handle returned by OPEN is not a valid transform handle.

name

The name of the parameter. Table 30-12 lists the transform parameters defined for the DDL transform, specifying the object_type it applies to, its datatype (in this case, always Boolean) and its meaning or effect (including its default value, if any).

value

The value of the transform.

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

Table 30-12  SET_TRANSFORM_PARAM: Transform Parameters for the DDL Transform
Object Type Name Datatype Meaning

All objects

PRETTY

Boolean

If TRUE, format the output with indentation and line feeds. Defaults to TRUE.

SQLTERMINATOR

Boolean

If TRUE, append a SQL terminator (; or /) to each DDL statement. Defaults to FALSE.

TABLE

SEGMENT_ATTRIBUTES

Boolean

If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.

STORAGE

Boolean

If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TABLESPACE

Boolean

If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TABLE

CONSTRAINTS

Boolean

If TRUE, emit all non-referential table constraints. Defaults to TRUE.

REF_CONSTRAINTS

Boolean

If TRUE, emit all referential constraints (foreign key and scoped refs). Defaults to TRUE.

CONSTRAINTS_AS_ALTER

Boolean

If TRUE, emit table constraints as separate ALTER TABLE (and, if necessary, CREATE INDEX) statements. If FALSE, specify table constraints as part of the CREATE TABLE statement. Defaults to FALSE. Requires that CONSTRAINTS be TRUE.

OID

Boolean

If TRUE, emit the OID clause for object tables. Defaults to FALSE.

SIZE_BYTE_KEYWORD

Boolean

If TRUE, emit the BYTE keyword as part of the size specification of CHAR and VARCHAR2 columns that use byte semantics. If FALSE, omit the keyword. Defaults to FALSE.

INDEX

SEGMENT_ATTRIBUTES

Boolean

If TRUE, emit segment attributes (physical attributes, storage attributes, tablespace, logging). Defaults to TRUE.

STORAGE

Boolean

If TRUE, emit storage clause. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TABLESPACE

Boolean

If TRUE, emit tablespace. (Ignored if SEGMENT_ATTRIBUTES is FALSE.) Defaults to TRUE.

TYPE

SPECIFICATION

Boolean

If TRUE, emit the type specification. Defaults to TRUE.

BODY

Boolean

If TRUE, emit the type body. Defaults to TRUE.

PACKAGE

SPECIFICATION

Boolean

If TRUE, emit the package specification. Defaults to TRUE.

BODY

Boolean

If TRUE, emit the package body. Defaults to TRUE.

VIEW

FORCE

Boolean

If TRUE, use the FORCE keyword in the CREATE VIEW statement. Defaults to TRUE.

All objects

DEFAULT

Boolean

Calling SET_TRANSFORM_PARAM with this parameter set to TRUE has the effect of resetting all parameters for the transform to their default values. Setting this FALSE has no effect. There is no default.

INHERIT

Boolean

If TRUE, inherits session-level parameters. Defaults to FALSE. If an application calls ADD_TRANSFORM to add the DDL transform, then by default the only transform parameters that apply are those explicitly set for that transform handle. This has no effect if the transform handle is the session transform handle.

Exceptions

Usage Notes

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

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


Note:

The enumerated constant must be prefixed with the package name DBMS_METADATA.SESSION_TRANSFORM.


FETCH_xxx Procedure

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

Syntax

The FETCH functions and procedures are:

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

Oracle9i XML Database Developer's Guide - Oracle XML DB for a description of XMLType

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

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

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

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

Parameters

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

Table 30-13  FETCH_xxx Parameters
Parameters Description

handle

The handle returned from OPEN.

doc (procedure fetch_clob)

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

Returns

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

Exceptions

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

Usage Notes

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

There are several different FETCH_xxx functions and procedures:

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

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

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

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

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

When Variants of FETCH_xxx Are Called

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

CLOSE Procedure

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

Syntax

DBMS_METADATA.CLOSE (
   handle  IN NUMBER);

Parameters

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

Table 30-14  CLOSE Parameters
Parameter Description

handle

The handle returned from OPEN.

Exceptions

Usage Notes

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

Example: Retrieving Payroll Tables and their Indexes as DDL

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

CREATE OR REPLACE PACKAGE dbms_metadata_example AS

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

-- Global Variables

fileHandle   UTL_FILE.FILE_TYPE;

-- Exception initialization

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

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

  PROCEDURE write_lob(doc IN CLOB) IS

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

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

-- Public routines

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

PROCEDURE  get_payroll_tables IS

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

BEGIN

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

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

-- Call 'set_count' to request retrieval of one table at a time. 
-- This call is not actually necessary because 1 is the default.
  dbms_metadata.set_count(tableOpenHandle, 1);

-- Retrieve tables whose name starts with 'PAYROLL'. When the filter is
-- 'NAME_EXPR', the filter value string must include the SQL operator. This
-- gives the caller flexibility to use LIKE, IN, NOT IN, subqueries, and so on.
  dbms_metadata.set_filter(tableOpenHandle, 'NAME_EXPR', 'LIKE ''PAYROLL%''');

-- Tell Metadata API to parse out each table's schema and name separately
-- so we can use them to set up the calls to retrieve its indexes.
  dbms_metadata.set_parse_item(tableOpenHandle, 'SCHEMA');
  dbms_metadata.set_parse_item(tableOpenHandle, 'NAME');

-- Add the DDL transform so we get SQL creation DDL
  tableTransHandle := dbms_metadata.add_transform(tableOpenHandle, 'DDL');

-- Tell the XSL stylesheet we don't want physical storage information (storage,
-- tablespace, etc), and that we want a SQL terminator on each DDL. Notice that
-- these calls use the transform handle, not the open handle.
  dbms_metadata.set_transform_param(tableTransHandle,
      'SEGMENT_ATTRIBUTES', FALSE);
  dbms_metadata.set_transform_param(tableTransHandle,
      'SQLTERMINATOR', TRUE);

-- Ready to start fetching tables. We use the FETCH_DDL interface (rather than
-- FETCH_XML or FETCH_CLOB). This interface returns a SYS.KU$_DDLS; a table of
-- SYS.KU$_DDL objects. This is a table because some object types return
-- multiple DDL statements (like types / pkgs which have create header and 
-- body statements). Each KU$_DDL has a CLOB containing the 'CREATE TABLE'
-- statement plus a nested table of the parse items specified. In our case,
-- we asked for two parse items; Schema and Name.

  LOOP
    tableDDLs := dbms_metadata.fetch_ddl(tableOpenHandle);
    EXIT WHEN tableDDLs IS NULL;   -- Get out when no more payroll tables

-- In our case, we know there is only one row in tableDDLs (a KU$_DDLS tbl obj)
-- for the current table. Sometimes tables have multiple DDL statements,
-- for example, if constraints are applied as ALTER TABLE statements, 
-- but we didn't ask for that option. 
-- So, rather than writing code to loop through tableDDLs,
-- we'll just work with the 1st row.
--
-- First, write the CREATE TABLE text to our output file, then retrieve the
-- parsed schema and table names.
    tableDDL := tableDDLs(1);
    write_lob(tableDDL.ddltext);
    parsedItems := tableDDL.parsedItems;

-- Must check the name of the returned parse items as ordering isn't guaranteed
    FOR i IN 1..2 LOOP
      IF parsedItems(i).item = 'SCHEMA'
      THEN
        schemaName := parsedItems(i).value;
      ELSE
        tableName  := parsedItems(i).value;
      END IF;
    END LOOP;
 
-- Then use the schema and table names to set up a 2nd stream for retrieval of
-- the current table's indexes.
-- (Note that we don't have to specify a SCHEMA filter for the indexes,
-- Because SCHEMA defaults to the value of BASE_OBJECT_SCHEMA.)
    indexOpenHandle := dbms_metadata.open('INDEX');
    dbms_metadata.set_filter(indexOpenHandle,'BASE_OBJECT_SCHEMA',schemaName);
    dbms_metadata.set_filter(indexOpenHandle,'BASE_OBJECT_NAME',tableName);

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

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

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

  END LOOP;

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

END;  -- of procedure get_payroll_tables

END dbms_metadata_example;
/

GET_XML and GET_DDL Functions

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

Syntax

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

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

Parameters

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

Table 30-15  GET_xxx Parameters 
Parameter Description

object_type

The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter.

name

An object name (case-sensitive). If object_type is SYNONYM and name is longer than 30 characters, then name will be treated as a LONGNAME filter. See Table 30-5.

schema

A schema name (case sensitive). The default is the current schema if object_type refers to a schema object; otherwise the default is NULL.

version

The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.

model

The object model to use. This parameter takes the same values as the OPEN model parameter.

transform

The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_XML this must not be DDL.

Returns

The metadata for the specified object as XML or DDL.

Exceptions

Usage Notes

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

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

Example 1. Fetching the XML Representation of SCOTT.EMP

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

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

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

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

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

GET_DEPENDENT_XML and GET_DEPENDENT_DDL Functions

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

Syntax

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

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

Parameters

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

Table 30-16  GET_DEPENDENT_xxx Parameters
Parameter Description

object_type

The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter. See Table 30-2, " Open() Parameters". The attributes of the object type must be appropriate to the function. For GET_DEPENDENT_xxx it must be a dependent object.

base_object_name

The base object name, which will be used internally in a BASE_OBJECT_NAME filter.

base_object_schema

The base object schema, which will be used internally in a BASE_OBJECT_SCHEMA filter. The default is the current user.

version

The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.

model

The object model to use. This parameter takes the same values as the OPEN model parameter.

transform

The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_DEPENDENT_XML this must not be DDL.

object_count

The maximum number of objects to return.

Returns

The metadata for the objects as XML or DDL.

Exceptions

Usage Notes

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

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

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

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

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

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

GET_GRANTED_XML and GET_GRANTED_DDL Functions

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

Syntax

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

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

Parameters

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

Table 30-17  GET_GRANTED_xxx Parameters
Parameter Description

object_type

The type of object to be retrieved. This parameter takes the same values as the OPEN object_type parameter. See Table 30-2, " Open() Parameters". The attributes of the object type must be appropriate to the function. For GET_GRANTED_xxx it must be a granted object

grantee

The grantee. It will be used internally in a GRANTEE filter. The default is the current user.

version

The version of metadata to be extracted. This parameter takes the same values as the OPEN version parameter.

model

The object model to use. This parameter takes the same values as the OPEN model parameter.

transform

The name of a transformation on the output. This parameter takes the same values as the ADD_TRANSFORM name parameter. For GET_GRANTED_XML this must not be DDL.

object_count

The maximum number of objects to return.

Returns

The metadata for the objects as XML or DDL.

Exceptions

Usage Notes

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

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

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

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

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

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

Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 2000, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback