|Oracle9i Supplied PL/SQL Packages and Types Reference
Release 1 (9.0.1)
Part Number A89852-02
DBMS_METADATA , 9 of 11
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.
FETCH functions and procedures are:
Oracle9i Application Developer's Guide - XML, Chapter 9, "XMLType: Native Storage of XML in the Database" for a description of XMLType.
The following types comprise the return nested table type
sys.ku$_parsed_itemAS OBJECT ( item VARCHAR2(30), value VARCHAR2(4000), parent NUMBER ); TYPE
sys.ku$_parsed_itemsIS TABLE OF sys.ku$_parsed_item; TYPE
sys.ku$_ddlAS OBJECT ( ddlText CLOB, parsedItems sys.ku$_parsed_items ); TYPE
sys.ku$_ddlsIS TABLE OF sys.ku$_ddl; FUNCTION
fetch_clob(handle IN NUMBER) RETURN CLOB; PROCEDURE
fetch_clob(handle IN NUMBER, doc IN OUT NOCOPY CLOB);
The handle returned from
The metadata for the objects or
The metadata for the objects or
NULL if all objects have been returned.
Most exceptions raised during execution of the query are propagated to the caller. Also, the following exceptions may be raised:
NULLor invalid value was supplied for an input parameter. The error message text identifies the parameter.
INCONSISTENT_OPERATION. Either (1)
FETCH_XMLwas called when the DDL transform had been specified, or (2)
FETCH_DDL was called when the DDL transform had not been specified.
These functions and procedures return metadata for objects meeting the criteria established by calls to
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
NULL and cause the stream created by
OPEN to be transparently closed.
There are several different
FETCH_xxx functions and procedures:
FETCH_XMLreturns the XML metadata for an object as an XMLType. It assumes that if any transform has been specified, the transform will produce an XML document. In particular, it assumes that the DDL transform has not been specified.
FETCH_DDLreturns the creation DDL in a
sys.ku$_ddlsnested table. It assumes that the DDL transform has been specified. Each row of the
sys.ku$_ddlsnested table contains a single DDL statement in the
ddlTextcolumn; if requested, parsed items for the DDL statement will be returned in the
parsedItemscolumn. Multiple DDL statements may be returned under the following circumstances:
SET_COUNTto specify a count greater than
TYPEobject can be transformed into both
CREATE TYPE BODYstatements. A
TABLEobject can be transformed into a
CREATE TABLE, zero or more
CREATE INDEXstatements, and zero or more
FETCH_CLOBsimply returns the object, transformed or not, as a CLOB.
FETCH_CLOB comes in both function and procedure variants. The procedure variant returns the object by reference in an
IN OUT NOCOPY parameter.
All LOBs returned by
FETCH_xxx are temporary LOBs. You must free the LOB. The same applies to the
SET_PARSE_ITEM was called,
FETCH_DDL returns attributes of the DDL statement in a
sys.ku$_parsed_items nested table, which is a column in the returned
sys.ku$_ddls nested table. Each row of the
sys.ku$_parsed_items nested table corresponds to an item specified by
SET_PARSE_ITEM and contains the following columns:
item--The name of the attribute as specified in the
value--The attribute value, or
NULLif the attribute is not present in the DDL statement.
parent--For future use.
The order of the rows is undetermined; to find a particular item you must search the table for a match on
SET_PARSE_ITEM was not called,
NULL is returned as the value of the
sys.ku$_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, that programs will not intermix calls to
FETCH_CLOB using the same
OPEN handle. The effect of calling different variants is undefined; it may not do what you expect.