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:

FUNCTION fetch_xml
  (handle IN NUMBER) 
   RETURN sys.XMLType;		

See Also: Oracle9i Application Developer's Guide - XML, Chapter 9, "XMLType: Native Storage of XML in the Database" for a description of XMLType.

FUNCTION 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),
  parent 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;

FUNCTION fetch_clob (handle IN NUMBER) 
    RETURN CLOB;		
PROCEDURE fetch_clob (handle IN NUMBER,
    doc IN OUT NOCOPY CLOB);		

Parameters

Table 28-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:

• INVALID_ARGVAL. A NULL or invalid value was supplied for an input parameter. The error message text identifies the parameter.

• INCONSISTENT_OPERATION. Either (1) FETCH_XML was called when the DDL transform had been specified, or (2) FETCH_DDL was called when the DDL transform had not been specified.

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_XML returns 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_DDL returns the creation DDL 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 can be transformed into both CREATE TYPE and CREATE TYPE BODY statements. A TABLE object can be transformed into a CREATE TABLE, zero or more CREATE INDEX statements, and zero or more ALTER TABLE statements.

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

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

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