6
Describing Schema Metadata

This chapter discusses the use of the OCIDescribeAny()function to obtain information about schema elements. The following topics are covered in this chapter:

• Describing Schema Metadata
• Using OCIDescribeAny()
• Examples Using OCIDescribeAny()

Describing Schema Metadata

This chapter discusses the use of the OCIDescribeAny() function to describe schema objects.

Using OCIDescribeAny()

The OCIDescribeAny() function enables you to perform an explicit describe of one of the following schema objects, and their subschema objects:

• tables and views
• synonyms
• procedures
• functions
• packages
• sequences
• collections
• types
• schemas
• databases

Information about other schema elements (procedure/function arguments, columns, type attributes, and type methods) is available through a describe of one of the above schema objects or an explicit describe of the subschema object.

When an application describes a table, it can then retrieve information about that table's columns. Additionally, OCIDescribeAny() can directly describe subschema objects such as columns of a table, packages of a function, or fields of a type if the user knows the name of the subschema object.

The OCIDescribeAny() call requires a describe handle as one of its parameters. The describe handle must have been previously allocated with a call to OCIHandleAlloc(). After the call to OCIDescribeAny(), an application can retrieve information about the described object from the describe handle.

The information returned by OCIDescribeAny() is organized hierarchically like a tree. For example, Figure 6-1 shows how the description of a certain table might be organized.

Figure 6-1 OCIDescribeAny() Table Description

Text description of the illustration lnoci031.gif

The describe handle returned by OCIDescribeAny() has an attribute, OCI_ATTR_PARAM, that points to such a description tree. Each node of the tree has attributes associated with the node and attributes (which are like recursive describe handles) that point to subtrees containing more information. If all the attributes are homogenous, as in case of elements of a list, such as a column list, then we refer to them as parameters. In this chapter, the terms handle and parameter are used interchangeably. The attributes associated with any node are returned by OCIAttrGet(), and the parameters are returned by OCIParamGet().

For example, an OCIAttrGet() on the describe handle for the table can return a handle to the column-list information. An application can then use OCIParamGet() to retrieve the handle to the column description of a particular column in the column-list. The handle to the column descriptor can be passed to OCIAttrGet() to get further information about the column, such as the name and data type (as illustrated by following the left-hand side of the above figure).

No subsequent OCIAttrGet() or OCIParamGet() call requires extra round trips, as all the description is cached on the client side by OCIDescribeAny().

Restrictions

The OCIDescribeAny() call limits information returned to the basic information and stops expanding a node if it amounts to another describe. For example, if a table column is of an object type, then the OCI does not return a subtree describing the type since this information can be obtained by another describe.

Notes on Types and Attributes

When performing describe operations, you should be aware of the following notes.

Note on Datatype Codes

OCI_ATTR_TYPECODE returns typecodes which represent the types supplied by the user when a new type is created using the CREATE TYPE statement. These typecodes are of the enumerated type OCITypeCode, and are represented by OCI_TYPECODE constants. Internal PL/SQL types (boolean, indexed table) are not supported.

OCI_ATTR_DATA_TYPE returns typecodes which represent the datatypes stored in database columns. These are similar to the describe values returned by previous versions of Oracle. These values are represented by SQLT constants (ub2 values). BOOLEAN types return SQLT_BOL.

Note on Describing Types

In order to describe type objects, it is necessary to initialize the OCI process in object mode:

/* Initialize the OCI Process */
  if (OCIInitialize((ub4) OCI_OBJECT, (dvoid *)0, 
                    (dvoid * (*)(dvoid *, size_t)) 0,
                    (dvoid * (*)(dvoid *, dvoid *, size_t))0, 
                    (void (*)(dvoid *, dvoid *)) 0 ))
  { (void) printf("FAILED: OCIInitialize()\n");
    return OCI_ERROR; }

Note on Implicit and Explicit Describes

The column attribute OCI_ATTR_PRECISION can be returned using an implicit describe with OCIStmtExecute() and an explicit describe with OCIDescribeAny(). When using an implicit describe, the precision should be set to sb2. When using an explicit describe, the precision should be set to ub1 for a placeholder. This is necessary to match the datatype of precision in the dictionary.

Note on OCI_ATTR_LIST_ARGUMENTS

The OCI_ATTR_LIST_ARGUMENTS attribute for type methods represents second-level arguments for the method.

For example, given the following record my_type and the procedure my_proc which takes an argument of type my_type:

my_type record(a number, b char)
my_proc (my_input my_type)

the OCI_ATTR_LIST_ARGUMENTS attribute would apply to arguments a and b of the my_type record.

Parameter Attributes

A parameter is returned by OCIParamGet(). Parameters can describe different types of objects or information. Parameters have attributes depending on the type of description they contain and these are the type-specific attributes. This section describes the attributes and handles that belong to different parameters.

The following table lists the attributes that belong to all parameters:

Table 6-1 Attributes Belonging to All Parameters  

Attribute	Description	Attribute Datatype
OCI_ATTR_NUM_PARAMS	The number of parameters	ub2
OCI_ATTR_OBJ_ID	Object or schema Id	ub4
OCI_ATTR_OBJ_NAME	Database name or object name in a schema	text*
OCI_ATTR_OBJ_SCHEMA	Schema name where the object is located	text*
OCI_ATTR_PTYPE	Type of information described by the parameter. Possible values are: OCI_PTYPE_TABLE - table OCI_PTYPE_VIEW - view OCI_PTYPE_PROC - procedure OCI_PTYPE_FUNC - function OCI_PTYPE_PKG - package OCI_PTYPE_TYPE - type OCI_PTYPE_TYPE_ATTR - attribute of a type OCI_PTYPE_TYPE_COLL - collection type information OCI_PTYPE_TYPE_METHOD - a method of a type OCI_PTYPE_SYN - synonym OCI_PTYPE_SEQ - sequence OCI_PTYPE_COL - column of a table or view OCI_PTYPE_ARG - argument of a function or procedure OCI_PTYPE_TYPE_ARG - argument of a type method OCI_PTYPE_TYPE_RESULT - the results of a method OCI_PTYPE_LIST - column list for tables and views, argument list for functions and procedures, or subprogram list for packages. OCI_PTYPE_SCHEMA - schema OCI_PTYPE_DATABASE- database	ub1
OCI_ATTR_TIMESTAMP	The timestamp of the object this description is based on (in Oracle date format)	ub1 *

The subsections that follow list the attributes and handles specific to different types of parameters.

Types OCI_PTYPE_TABLE or OCI_PTYPE_VIEW

When a parameter is for a table or view (type OCI_PTYPE_TABLE or OCI_PTYPE_VIEW), it has the following type specific attributes:

Table 6-2 Attributes Belonging to Tables or Views  

Attribute	Description	Attribute Datatype
OCI_ATTR_OBJID	Object id	ub4
OCI_ATTR_NUM_COLS	Number of columns	ub2
OCI_ATTR_LIST_COLUMNS	Column list (type OCI_PTYPE_LIST)	dvoid *
OCI_ATTR_REF_TDO	REF to the TDO of the base type in case of extent tables	OCIRef*
OCI_ATTR_IS_TEMPORARY	Is the table temporary?	ub1
OCI_ATTR_IS_TYPED	Is the table typed?	ub1
OCI_ATTR_DURATION	Duration of a temporary table. Values can be: OCI_DURATION_SESSION - session OCI_DURATION_TRANS - transaction OCI_DURATION_NULL -table not temporary	OCIDuration

The following are additional attributes which belong to tables:

Table 6-3 Attributes Specific to Tables  

Attribute	Description	Attribute Datatype
OCI_ATTR_DBA	Data block address of the segment header	ub4
OCI_ATTR_TABLESPACE	Tablespace the table resides in	word
OCI_ATTR_CLUSTERED	Is the table clustered?	ub1
OCI_ATTR_PARTITIONED	Is the table partitioned?	ub1
OCI_ATTR_INDEX_ONLY	Is the table index-only?	ub1

Procedure/Function/Subprogram Attributes

When a parameter is for a procedure or function (type OCI_PTYPE_PROC or OCI_PTYPE_FUNC), it has the following type specific attributes:

Table 6-4 Attribute Belonging to Procedures or Functions  

Attribute	Description	Attribute Datatype
OCI_ATTR_LIST_ARGUMENTS	Argument list. See "List Attributes".	dvoid *
OCI_ATTR_IS_INVOKER_RIGHTS	Is the procedure or function invoker-rights?	ub1

The following attributes are defined only for package subprograms:

Table 6-5 Attributes Specific to Package Subprograms  

Attribute	Description	Attribute Datatype
OCI_ATTR_NAME	Name of the procedure or function	text *
OCI_ATTR_OVERLOAD_ID	Overloading ID number (relevant in case the procedure or function is part of a package and is overloaded). Values returned may be different from direct query of a PL/SQL function or procedure.	ub2

Package Attributes

When a parameter is for a package (type OCI_PTYPE_PKG), it has the following type specific attributes:

Table 6-6 Attributes Belonging to Packages  

Attribute	Description	Attribute Datatype
OCI_ATTR_LIST_SUBPROGRAMS	Subprogram list. See "List Attributes".	dvoid *
OCI_ATTR_IS_INVOKER_RIGHTS	Is the package invoker-rights?	ub1

Type Attributes

When a parameter is for a type (type OCI_PTYPE_TYPE), it has the attributes listed in Table 6-7. These attributes are only valid if the application initialized the OCI process in OCI_OBJECT mode in a call to OCIInitialize().

Table 6-7 Attributes Belonging to Types  

Attribute	Description	Attribute Datatype
OCI_ATTR_REF_TDO	Returns the in-memory REF of the type descriptor object for the type, if the column type is an object type. If space has not been reserved for the OCIRef, then it is allocated implicitly in the cache. The caller can then pin the TDO with OCIObjectPin().	OCIRef *
OCI_ATTR_TYPECODE	Typecode. See "Note on Datatype Codes". Currently can be only OCI_TYPECODE_OBJECT or OCI_TYPECODE_NAMEDCOLLECTION.	OCITypeCode
OCI_ATTR_COLLECTION_TYPECODE	Typecode of collection if type is collection; invalid otherwise. See "Note on Datatype Codes". Currently can be only OCI_TYPECODE_VARRAY or OCI_TYPECODE_TABLE. Error is returned if this attribute is queried for non-collection type.	OCITypeCode
OCI_ATTR_IS_INCOMPLETE_TYPE	Is this an incomplete type?	ub1
OCI_ATTR_IS_SYSTEM_TYPE	Is this a system type?	ub1
OCI_ATTR_IS_PREDEFINED_TYPE	Is this a predefined type?	ub1
OCI_ATTR_IS_TRANSIENT_TYPE	Is this a transient type?	ub1
OCI_ATTR_IS_SYSTEM_ GENERATED_TYPE	Is this a system-generated type?	ub1
OCI_ATTR_HAS_NESTED_TABLE	Does this type contain a nested table attribute?	ub1
OCI_ATTR_HAS_LOB	Does this type contain a LOB attribute?	ub1
OCI_ATTR_HAS_FILE	Does this type contain a FILE attribute?	ub1
OCI_ATTR_COLLECTION_ELEMENT	Handle to collection element. See "Collection Attributes".	dvoid *
OCI_ATTR_NUM_TYPE_ATTRS	Number of type attributes	ub2
OCI_ATTR_LIST_TYPE_ATTRS	List of type attributes. See "List Attributes".	dvoid *
OCI_ATTR_NUM_TYPE_METHODS	Number of type methods	ub2
OCI_ATTR_LIST_TYPE_METHODS	List of type methods. See "List Attributes".	dvoid *
OCI_ATTR_MAP_METHOD	Map method of type. See "Type Method Attributes".	dvoid *
OCI_ATTR_ORDER_METHOD	Order method of type. See "Type Method Attributes".	dvoid *
OCI_ATTR_IS_INVOKER_RIGHTS	Is the type invoker-rights?	ub1
OCI_ATTR_NAME	A pointer to a string which is the type attribute name	text *
OCI_ATTR_SCHEMA_NAME	A string with the schema name under which the type has been created	text *
OCI_ATTR_IS_FINAL_TYPE	Is this a final type?	ub1
OCI_ATTR_IS_INSTANTIABLE_TYPE	Is this an instantiable type?	ub1
OCI_ATTR_IS_SUBTYPE	Is this a subtype?	ub1
OCI_ATTR_SUPERTYPE_SCHEMA_NAME	Name of the schema containing the supertype	text *
OCI_ATTR_SUPERTYPE_NAME	Name of the supertype	text *

Type Attribute Attributes

When a parameter is for an attribute of a type (type OCI_PTYPE_TYPE_ATTR), it has the attributes listed in Table 6-8.

Table 6-8 Attributes Belonging to Type Attributes  

Attribute	Description	Attribute Datatype
OCI_ATTR_DATA_SIZE	The maximum size of the type attribute. This length is returned in bytes and not characters for strings and raws. It returns 22 for NUMBERs.	ub4
OCI_ATTR_TYPECODE	Typecode. See "Note on Datatype Codes".	OCITypeCode
OCI_ATTR_DATA_TYPE	The data type of the type attribute. See "Note on Datatype Codes".	ub2
OCI_ATTR_NAME	A pointer to a string which is the type attribute name	text *
OCI_ATTR_PRECISION	The precision of numeric type attributes. If the precision is nonzero and scale is -127, then it is a FLOAT, else it is a NUMBER(precision, scale). For the case when precision is 0, NUMBER(precision, scale) can be represented simply as NUMBER.	b1 for explicit describe sb2 for implicit describe
OCI_ATTR_SCALE	The scale of numeric type attributes. If the precision is nonzero and scale is -127, then it is a FLOAT, else it is a NUMBER(precision, scale). For the case when precision is 0, NUMBER(precision, scale) can be represented simply as NUMBER.	sb1
OCI_ATTR_TYPE_NAME	A string which is the type name. The returned value will contain the type name if the data type is SQLT_NTY or SQLT_REF. If the data type is SQLT_NTY, the name of the named data type's type is returned. If the data type is SQLT_REF, the type name of the named data type pointed to by the REF is returned	text *
OCI_ATTR_SCHEMA_NAME	A string with the schema name under which the type has been created	text *
OCI_ATTR_REF_TDO	Returns the in-memory REF of the TDO for the type, if the column type is an object type. If space has not been reserved for the OCIRef, then it is allocated implicitly in the cache. The caller can then pin the TDO with OCIObjectPin().	OCIRef *
OCI_ATTR_CHARSET_ID	The character set id, if the type attribute is of a string/character type	ub2
OCI_ATTR_CHARSET_FORM	The character set form, if the type attribute is of a string/character type	ub1
OCI_ATTR_FSPRECISION	The fractional seconds precision of a datetime or interval.	ub1
OCI_ATTR_LFPRECISION	The leading field precision of an interval.	ub1

Type Method Attributes

When a parameter is for a method of a type (type OCI_PTYPE_TYPE_METHOD), it has the attributes listed in Table 6-9.

Table 6-9 Attributes Belonging to Type Methods  

Attribute	Description	Attribute Datatype
OCI_ATTR_NAME	Name of method (procedure or function)	text *
OCI_ATTR_ENCAPSULATION	Encapsulation level of the method (either OCI_TYPEENCAP_PRIVATE or OCI_TYPEENCAP_PUBLIC)	OCITypeEncap
OCI_ATTR_LIST_ARGUMENTS	Argument list. See "Note on OCI_ATTR_LIST_ARGUMENTS", and "List Attributes".	dvoid *
OCI_ATTR_IS_CONSTRUCTOR	Is method a constructor?	ub1
OCI_ATTR_IS_DESTRUCTOR	Is method a destructor?	ub1
OCI_ATTR_IS_OPERATOR	Is method an operator?	ub1
OCI_ATTR_IS_SELFISH	Is method selfish?	ub1
OCI_ATTR_IS_MAP	Is method a map method?	ub1
OCI_ATTR_IS_ORDER	Is method an order method?	ub1
OCI_ATTR_IS_RNDS	Is "Read No Data State" set for method?	ub1
OCI_ATTR_IS_RNPS	Is "Read No Process State" set for method?	ub1
OCI_ATTR_IS_WNDS	Is "Write No Data State" set for method?	ub1
OCI_ATTR_IS_WNPS	Is "Write No Process State" set for method?	ub1
OCI_ATTR_IS_FINAL_METHOD	Is this a final method?	ub1
OCI_ATTR_IS_INSTANTIABLE_METHOD	Is this an instantiable method?	ub1
OCI_ATTR_IS_OVERRIDING_METHOD	Is this an overriding method?	ub1

Collection Attributes

When a parameter is for a collection type (type OCI_PTYPE_COLL), it has the attributes listed in Table 6-10.

Table 6-10 Attributes Belonging to Collection Types  

Attribute	Description	Attribute Datatype
OCI_ATTR_DATA_SIZE	The maximum size of the type attribute. This length is returned in bytes and not characters for strings and raws. It returns 22 for NUMBERs.	ub2
OCI_ATTR_TYPECODE	Typecode. See "Note on Datatype Codes".	OCITypeCode
OCI_ATTR_DATA_TYPE	The data type of the type attribute. See "Note on Datatype Codes".	ub2
OCI_ATTR_NUM_ELEMENTS	The number of elements in an array. It is only valid for collections that are arrays	ub4
OCI_ATTR_NAME	A pointer to a string which is the type attribute name	text *
OCI_ATTR_PRECISION	The precision of numeric type attributes. If the precision is nonzero and scale is -127, then it is a FLOAT, else it is a NUMBER(precision, scale). For the case when precision is 0, NUMBER(precision, scale) can be represented simply as NUMBER.	b1 for explicit describe sb2 for implicit describe
OCI_ATTR_SCALE	The scale of numeric type attributes. If the precision is nonzero and scale is -127, then it is a FLOAT, else it is a NUMBER(precision, scale). For the case when precision is 0, NUMBER(precision, scale) can be represented simply as NUMBER.	sb1
OCI_ATTR_TYPE_NAME	A string which is the type name. The returned value will contain the type name if the data type is SQLT_NTY or SQLT_REF. If the data type is SQLT_NTY, the name of the named data type's type is returned. If the data type is SQLT_REF, the type name of the named data type pointed to by the REF is returned	text *
OCI_ATTR_SCHEMA_NAME	A string with the schema name under which the type has been created	text *
OCI_ATTR_REF_TDO	Returns the in-memory REF of the TDO for the type, if the column type is an object type. If space has not been reserved for the OCIRef, then it is allocated implicitly in the cache. The caller can then pin the TDO with OCIObjectPin().	OCIRef *
OCI_ATTR_CHARSET_ID	The character set id, if the type attribute is of a string/character type	ub2
OCI_ATTR_CHARSET_FORM	The character set form, if the type attribute is of a string/character type	ub1

Synonym Attributes

When a parameter is for a synonym (type OCI_PTYPE_SYN), it has the attributes listed in Table 6-11.

Table 6-11 Attributes Belonging to Synonyms  

Attribute	Description	Attribute Datatype
OCI_ATTR_OBJID	Object id	ub4
OCI_ATTR_SCHEMA_NAME	A string containing the schema name of the synonym translation	text *
OCI_ATTR_NAME	A null-terminated string containing the object name of the synonym translation	text *
OCI_ATTR_LINK	A null-terminated string containing the database link name of the synonym translation	text *

Sequence Attributes

When a parameter is for a sequence (type OCI_PTYPE_SEQ), it has the attributes listed in Table 6-12.

Table 6-12 Attributes Belonging to Sequences  

Attribute	Description	Attribute Datatype
OCI_ATTR_OBJID	Object id	ub4
OCI_ATTR_MIN	Minimum value (in Oracle NUMBER format)	ub1 *
OCI_ATTR_MAX	Maximum value (in Oracle NUMBER format)	ub1 *
OCI_ATTR_INCR	Increment (in Oracle NUMBER format)	ub1 *
OCI_ATTR_CACHE	Number of sequence numbers cached; zero if the sequence is not a cached sequence (in Oracle NUMBER format)	ub1 *
OCI_ATTR_ORDER	Whether the sequence is ordered	ub1
OCI_ATTR_HW_MARK	High-water mark (in Oracle NUMBER format)	ub1 *

Column Attributes

When a parameter is for a column of a table or view (type OCI_PTYPE_COL), it has the attributes listed in Table 6-13.

Table 6-13 Attributes Belonging to Columns of Tables or Views  

Attribute	Description	Attribute Datatype
OCI_ATTR_CHAR_USED	Returns the type of length semantics of the column. 0 means byte-length semantics and 1 means character-length semantics. See "Character Length Semantics Support in Describing" .	ub4
OCI_ATTR_CHAR_SIZE	Returns the column character length which is the number of characters allowed in the column. It is the counterpart of OCI_ATTR_DATA_SIZE which gets the byte length. See "Character Length Semantics Support in Describing" .	ub2
OCI_ATTR_DATA_SIZE	The maximum size of the column. This length is returned in bytes and not characters for strings and raws. It returns 22 for NUMBERs.	ub2
OCI_ATTR_DATA_TYPE	The data type of the column. See "Note on Datatype Codes".	ub2
OCI_ATTR_NAME	A pointer to a string which is the column name	text *
OCI_ATTR_PRECISION	The precision of numeric columns. If the precision is nonzero and scale is -127, then it is a FLOAT, else it is a NUMBER(precision, scale). For the case when precision is 0, NUMBER(precision, scale) can be represented simply as NUMBER.	ub1 for explicit describe sb2 for implicit describe
OCI_ATTR_SCALE	The scale of numeric columns. If the precision is nonzero and scale is -127, then it is a FLOAT, else it is a NUMBER(precision, scale). For the case when precision is 0, NUMBER(precision, scale) can be represented simply as NUMBER.	sb1
OCI_ATTR_IS_NULL	Returns 0 if null values are not permitted for the column	ub1
OCI_ATTR_TYPE_NAME	Returns a string which is the type name. The returned value will contain the type name if the data type is SQLT_NTY or SQLT_REF. If the data type is SQLT_NTY, the name of the named data type's type is returned. If the data type is SQLT_REF, the type name of the named data type pointed to by the REF is returned	text *
OCI_ATTR_SCHEMA_NAME	Returns a string with the schema name under which the type has been created	text *
OCI_ATTR_REF_TDO	The REF of the TDO for the type, if the column type is an object type	OCIRef *
OCI_ATTR_CHARSET_ID	The character set id, if the column is of a string/character type	ub2
OCI_ATTR_CHARSET_FORM	The character set form, if the column is of a string/character type	ub1

Argument/Result Attributes

When a parameter is for an argument of a procedure/function (type OCI_PTYPE_ARG), for a type method argument (type OCI_PTYPE_TYPE_ARG) or for method results (type OCI_PTYPE_TYPE_RESULT), it has the attributes listed in Table 6-14.

Table 6-14 Attributes Belonging to Arguments/Results  

Attribute	Description	Attribute Datatype
OCI_ATTR_NAME	Returns a pointer to a string which is the argument name	text *
OCI_ATTR_POSITION	The position of the argument in the argument list. Always returns zero.	ub2
OCI_ATTR_TYPECODE	Typecode. See "Note on Datatype Codes".	OCITypeCode
OCI_ATTR_DATA_TYPE	The data type of the argument. See "Note on Datatype Codes".	ub2
OCI_ATTR_DATA_SIZE	The size of the data type of the argument. This length is returned in bytes and not characters for strings and raws. It returns 22 for NUMBERs.	ub2
OCI_ATTR_PRECISION	The precision of numeric arguments. If the precision is nonzero and scale is -127, then it is a FLOAT, else it is a NUMBER(precision, scale). For the case when precision is 0, NUMBER(precision, scale) can be represented simply as NUMBER.	b1 for explicit describe sb2 for implicit describe
OCI_ATTR_SCALE	The scale of numeric arguments. If the precision is nonzero and scale is -127, then it is a FLOAT, else it is a NUMBER(precision, scale). For the case when precision is 0, NUMBER(precision, scale) can be represented simply as NUMBER.	sb1
OCI_ATTR_LEVEL	The data type levels. This attribute always returns zero.	ub2
OCI_ATTR_HAS_DEFAULT	Indicates whether an argument has a default	ub1
OCI_ATTR_LIST_ARGUMENTS	The list of arguments at the next level (when the argument is of a record or table type).	dvoid *
OCI_ATTR_IOMODE	Indicates the argument mode: 0 is IN (OCI_TYPEPARAM_IN), 1 is OUT (OCI_TYPEPARAM_OUT), 2 is IN/OUT (OCI_TYPEPARAM_INOUT)	OCITypeParamMode
OCI_ATTR_RADIX	Returns a radix (if number type)	ub1
OCI_ATTR_IS_NULL	Returns 0 if null values are not permitted for the column	ub1
OCI_ATTR_TYPE_NAME	Returns a string which is the type name, or the package name in the case of package local types. The returned value will contain the type name if the data type is SQLT_NTY or SQLT_REF. If the data type is SQLT_NTY, the name of the named data type's type is returned. If the data type is SQLT_REF, the type name of the named datatype pointed to by the REF is returned.	text *
OCI_ATTR_SCHEMA_NAME	For SQLT_NTY or SQLT_REF, returns a string with the schema name under which the type was created, or under which the package was created in the case of package local types	text *
OCI_ATTR_SUB_NAME	For SQLT_NTY or SQLT_REF, returns a string with the type name, in the case of package local types	text *
OCI_ATTR_LINK	For SQLT_NTY or SQLT_REF, returns a string with the database link name of the database on which the type exists. This can happen only in the case of package local types, when the package is remote.	text *
OCI_ATTR_REF_TDO	Returns the REF of the TDO for the type, if the argument type is an object	OCIRef *
OCI_ATTR_CHARSET_ID	Returns the character set ID if the argument is of a string/character type	ub2
OCI_ATTR_CHARSET_FORM	Returns the character set form if the argument is of a string/character type	ub1

List Attributes

When a parameter is for a list of columns, arguments, or subprograms (type OCI_PTYPE_LIST), it has the following type specific attributes and handles (parameters):

The list has an OCI_ATTR_LIST_TYPE attribute which designates the list type. The possible values and their lower bounds when traversing the list are:

List

Table 6-15 List Attributes  

List Attribute	Description	Lower Bound
OCI_LTYPE_COLUMN	Column list	1
OCI_LTYPE_ARG_PROC	Procedure argument list	1
OCI_LTYPE_ARG_FUNC	Function argument list	0
OCI_LTYPE_SUBPRG	Subprogram list	0
OCI_LTYPE_TYPE_ATTR	Type attribute list	1
OCI_LTYPE_TYPE_METHOD	Type method list	1
OCI_LTYPE_TYPE_ARG_PROC	Type method without result argument list	0
OCI_LTYPE_TYPE_ARG_FUNC	Type method without result argument list	1
OCI_LTYPE_SCH_OBJ	Object list within a schema	0
OCI_LTYPE_DB_SCH	Schema list within a database	0

• The list has an OCI_ATTR_NUM_PARAMS attribute, which tells the number of elements in the list.
• Each list has LowerBound .. OCI_ATTR_NUM_PARAMS parameters. LowerBound is the value in the Lower Bound column of Table 6-15, "List Attributes". In the case of a function argument list, position 0 has a parameter for the return value (type OCI_PTYPE_ARG).

Schema Attributes

When a parameter is for a schema type (type OCI_PTYPE_SCHEMA), it has the attributes listed in Table 6-16:

Table 6-16 Attributes Specific to Schemas  

Attribute	Description	Attribute Datatype
OCI_ATTR_LIST_OBJECTS	List of objects in the schema	text*

Database Attributes

When a parameter is for a database type (type OCI_PTYPE_DATABASE), it has the attributes listed in Table 6-17:

Table 6-17 Attributes Specific to Databases  

Attribute	Description	Attribute Datatype
OCI_ATTR_VERSION	Database version	text*
OCI_ATTR_CHARSET_ID	Database character set Id from the server handle	ub2
OCI_ATTR_NCHARSET_ID	Database character set Id from the server handle	ub2
OCI_ATTR_LIST_SCHEMAS	List of schemas (type OCI_PTYPE_SCHEMA) in the database	OCI_PTYPE_LIST
OCI_ATTR_MAX_PROC_LEN	Maximum length of a procedure name	ub4
OCI_ATTR_MAX_COLUMN_LEN	Maximum length of a column name	ub4
OCI_ATTR_CURSOR_COMMIT_BEHAVIOR	How a COMMIT operation affects cursors and prepared statements in the database. Values are: OCI_CURSOR_OPEN - preserve cursor state as before the commit operation OCI_CURSOR_CLOSED - cursors are closed on COMMIT, but the application can still reexecute the statement without re-preparing it	ub1
OCI_ATTR_MAX_CATALOG_NAMELEN	Maximum length of a catalog (database) name	ub1
OCI_ATTR_CATALOG_LOCATION	Position of the catalog in a qualified table. Values are OCI_CL_START and OCI_CL_END	ub1
OCI_ATTR_SAVEPOINT_SUPPORT	Does database support savepoints? Values are OCI_SP_SUPPORTED and OCI_SP_UNSUPPORTED	ub1
OCI_ATTR_NOWAIT_SUPPORT	Does database support the nowait clause? Values are OCI_NW_SUPPORTED and OCI_NW_UNSUPPORTED	ub1
OCI_ATTR_AUTOCOMMIT_DDL	Is autocommit mode required for DDL statements? Values are OCI_AC_DDL and OCI_NO_AC_DDL	ub1
OCI_ATTR_LOCKING_MODE	Locking mode for the database. Values are OCI_LOCK_IMMEDIATE and OCI_LOCK_DELAYED	ub1

Character Length Semantics Support in Describing

Starting with release Oracle9i, query and column information are supported with character length semantics.

These attributes of describe handles support character length semantics:

OCI_ATTR_CHAR_SIZE, gets the column character length which is the number of characters allowed in the column. It is the counterpart of OCI_ATTR_DATA_SIZE which gets the byte length;

OCI_ATTR_CHAR_USED, gets the type of length semantics of the column. 0 means byte-length semantics and 1 means character length semantics.

An application may describe a select-list (query) either implicitly or explicitly through OCIStmtExecute(). Other schema elements must be described explicitly through OCIDescribeAny().

Implicit Describing

If the database column was created using character length semantics, then the implicit describe information will contain both the character length and the byte length and a flag saying how the database column was created. That is, OCI_ATTR_CHAR_SIZE is the character length of the column or expression. The OCI_ATTR_CHAR_USED flag is a 1 in this case, indicating that the column or expression was created with the character length semantics.

The OCI_ATTR_DATA_SIZE value will be always large enough to hold all the data, as many as OCI_ATTR_CHAR_SIZE number of characters. The OCI_ATTR_DATA_SIZE will be usually set to the OCI_ATTR_CHAR_SIZE times the client's max bytes for each character value.

If the database column was created with byte length semantics, then the implicit describe will behave exactly as it does before release 9.0. That is, the OCI_ATTR_DATA_SIZE value returned will be the column's byte length times the maximum conversion ratio between the client and server's character set, that is, column byte length divided by the server's max bytes for each character multiplied by the client's max bytes for each character. The OCI_ATTR_CHAR_USED value is 0 and the OCI_ATTR_CHAR_SIZE value will be set to the same value as OCI_ATTR_DATA_SIZE.

Explicit Describing

Explicit describes of tables will have an attribute OCI_ATTR_DATA_SIZE that gets the column's size in bytes (as it appears in the server), the length in characters in OCI_ATTR_CHAR_SIZE, and a flag OCI_ATTR_CHAR_USED that indicates how the column was created.

When inserting, if the OCI_ATTR_CHAR_USED flag is set you may set the OCI_ATTR_MAXCHAR_SIZE (to be explained in binds) in the bind handle to the value returned by OCI_ATTR_CHAR_SIZE in the parameter handle from the explicit describe. This will assure you that you never violate the size constraint for the column.

Another way to do explicit describe is through function OCIDescribeAny(), which is a generic describe call for existing schema objects: tables, views, synonyms, procedures, functions, packages, sequences, types, schemas, and databases. This call also describes subschema objects, such as a column in a table. This call populates the describe handle with the object-specific attributes which can be obtained through an OCIAttrGet() call.

Calling OCIAttrGet() on the parameter descriptor returns the specific attributes of a stored procedure or function parameter, or a table column descriptor. These subsequent calls do not need an extra round trip to the server because the entire schema object description is cached on the client side by OCIDescribeAny().

When an application describes a table, it can then retrieve information about that table's columns. Additionally, OCIDescribeAny() can directly describe subschema objects such as columns of a table, packages of a function, or fields of a type if the user knows the name of the subschema object. In all cases, the specific information about columns and datatypes is retrieved by reading handle attributes.

After a SQL statement is executed, information about the select-list is available as an attribute of the statement handle. No explicit describe call is needed. To retrieve information about select-list items from the statement handle, the application must call OCIParamGet() once for each position in the select-list to allocate a parameter descriptor for that position.

Once a parameter descriptor has been allocated for a position in the select-list, the application can retrieve specific information by calling OCIAttrGet() on the parameter descriptor. Information available from the parameter descriptor includes the datatype and maximum size of the parameter.

Client and Server Compatibility Issues for Describing

When an Oracle9i or later client talks to an Oracle8i or earlier server, it will behave as if the database is only using byte length semantics;

When an Oracle8i or earlier client talks to a Oracle9i or later server, the attributes OCI_ATTR_CHAR_SIZE and OCI_ATTR_CHAR_USED are not available on the client side.

In either case, the character length semantics cannot be described when either the server or client has an Oracle8i or earlier software release.

Examples Using OCIDescribeAny()

The following examples demonstrate the use of OCIDescribeAny() for describing different types of schema objects. For a more detailed code sample, see the demonstration program cdemodsa.c included with your Oracle installation.

Retrieving Column Data Types For a Table

This example illustrates the use of an explicit describe. Let us take an example application, which needs to retrieve the column datatypes for a table. The following code fragment shows how an application would be able to use the describe interface:

...
text objptr[] = "tablename";
OCIParam     *mypard;
ub4          counter;
ub2          dtype;
text         *col_name;
ub4          counter, col_name_len, char_semantics, col_width;
ub4 objp_len = strlen("tablename");
OCIParam *parmh;         /* parameter handle */
OCIParam *collsthd;      /* handle to list of columns */
OCIParam *colhd;         /* column handle */
OCIDescribe *dschp;      /* describe handle */

...
/* get the describe handle for the table */
if (OCIDescribeAny(svch, errh, objptr, objp_len, OCI_OTYPE_NAME, 0, 
 OCI_PTYPE_TABLE, dschp))
 return error;
/* get the parameter handle */
if (OCIAttrGet(dschp, OCI_HTYPE_DESCRIBE, &parmh, 0, OCI_ATTR_PARAM,
     errh))
    return error;
/* The type information of the object, in this case, OCI_PTYPE_TABLE,
is obtained from the parameter descriptor returned by the OCIAttrGet(). */
/* get the number of columns in the table */
if (OCIAttrGet(parmh, OCI_DTYPE_PARAM, &numcols, 0, 
     OCI_ATTR_NUM_COLS, errh))
    return error;
/* get the handle to the column list of the table */
if (OCIAttrGet(parmh, OCI_DTYPE_PARAM, &collsthd, 0,
 OCI_ATTR_LIST_COLUMNS, errh)==OCI_NO_DATA)
 return error;
/* go through the column list and retrieve the data-type of each column,
and then recursively describe column types. */

for (i = 1; i <= numcols; i++)
{
    /* get parameter for column i */
 if (OCIParamGet(collsthd, OCI_DTYPE_PARAM, errh, &colhd, i))
   return error;
    /* for example, get data type for ith column */
    if (OCIAttrGet(colhd, OCI_DTYPE_PARAM, &datatype[i-1], 0,
       OCI_ATTR_DATA_TYPE, errh))
        return error;
/* Retrieve the length semantics for the column */
OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
                 (dvoid*) &char_semantics,(ub4 *) 0, (ub4) OCI_ATTR_CHAR_USED, 
                (OCIError *) errhp  );
if (char_semantics)
    /* Retrieve the column width in characters */
    OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
                 (dvoid*) &col_width, (ub4 *) 0, (ub4) OCI_ATTR_CHAR_SIZE, 
                (OCIError *) errhp  );
else
    /* Retrieve the column width in bytes */
    OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
                 (dvoid*) &col_width,(ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE, 
                (OCIError *) errhp  );
}
...

Describing the Stored Procedure

Let us consider a stored procedure or a function.The difference between a procedure and a function is that the latter has a return type at position 0 in the argument list, while the former has no argument associated with position 0 in the argument list. The steps required to describe type methods (also divided into functions and procedures) are identical to that of regular PL/SQL functions and procedures. Note that procedures and functions can take default types of objects as arguments. Let us consider the following procedure:

P1 (arg1 emp.sal%type, arg2 emp%rowtype)

Furthermore, let us assume that each row in emp table has two columns: name(VARCHAR2(20)), and sal(NUMBER). Thus, in the argument list for P1, we have two arguments, arg1 and arg2, at positions 1 and 2 respectively at level 0, and arguments name and sal at positions 1and 2 respectively at level 1. Description of P1 returns the number of arguments as two while returning the higher level (> 0) arguments as attributes of the 0 zero level arguments.

The following code fragment explains the description of P1.

...
text objptr[] = "P1";      /* procedure name */
ub4 objp_len = strlen("P1");
OCIParam *parmh;           /* parameter handle */
OCIParam *arglst;          /* list of args */
OCIParam *arg;             /* argument handle */
OCIDescribe *dschp;      /* describe handle */

ub2 numargs, pos, level; 
text *name;
ub4 namelen;

...
/* get the describe handle for the table */
if (OCIDescribeAny(svch, errh, objptr, objp_len, OCI_OTYPE_NAME, 0, 
 OCI_PTYPE_PROC, dschp))
 return error;

/* get the parameter handle */
if (OCIAttrGet(dschp, OCI_HTYPE_DESCRIBE, &parmh, 0, OCI_ATTR_PARAM, 
    errh))
    return error;

/* Get the number of arguments and the arg list */
if (OCIAttrGet (parmh, OCI_DTYPE_PARAM, &arglst, 
0, OCI_ATTR_LIST_ARGUMENTS, errh))
    return error;
if (OCIAttrGet (parmh, OCI_DTYPE_PARAM, &numargs, 0, 
    OCI_ATTR_NUM_PARAMS, errh))
    return error;

/* For a procedure, we begin with i = 1; for a 
function, we begin with i = 0. */

for (i = 1; i < numargs; i++) {
OCIParamGet (arglst, OCI_DTYPE_PARAM, errh, &arg, i);
OCIAttrGet (arg, OCI_DTYPE_PARAM, &name, &namelen, OCI_ATTR_NAME, 
        errh);
...
/* to print the attributes of the argument of type record 
(arguments at the next level), traverse the argument list */

OCIAttrGet (arg, OCI_DTYPE_PARAM, &arglst1, 0,
OCI_ATTR_LIST_ARGUMENTS, erh);

/* check if the current argument is a record. For arg1 in P1
arglst1 is NULL. */

  if (arglst1) {
    OCIAttrGet (arg, OCI_DTYPE_PARAM, &numargs1,0, OCI_ATTR_NUM_PARAMS, 
        errh);

/* Note that for both functions and procedures,the next higher level 
arguments start from index 1. For arg2 in P1, the number of arguments at 
the level 1 would be 2 */

    for (i = 1; i < numargs1, i++) {
      OCIParamGet (arglst1, OCI_DTYPE_PARAM, errh, &arg1, i);
    OCIAttrGet (arg1, OCI_DTYPE_PARAM, &name1, &namelen1, 
        OCI_ATTR_NAME, errh);
    ...
    }
  }
}
...

Retrieving Attributes of an Object Type

This example illustrates the use of an explicit describe on a named object type. We illustrate how you can describe an object by its name or by its object reference (OCIRef). The following code fragment attempts to retrieve the datatype value of each of the object type's attributes.

...
text type_name[] = "typename";
ub4 type_name_len = strlen("typename");
OCIRef *type_ref = ...;
un4 numattrs;
OCIDescribe *dschp;      /* describe handle */
OCIParam *parmh;         /* parameter handle */
OCIParam *attrlsthd;     /* handle to list of attrs */
OCIParam *attrhd;        /* attribute handle */
...
/* allocate describe handle */
if (OCIHandleAlloc((dvoid *)envh, (dvoid **)&dschp,
             (ub4)OCI_HTYPE_DESCRIBE, (size_t)0, (dvoid **)0))
 return error;

/* get the describe handle for the type */
if (describe_by_name)
 if (OCIDescribeAny(svch, errh, (dvoid*)type_name, type_name_len, 
  OCI_OTYPE_NAME, 0, OCI_PTYPE_TYPE, dschp))
  return error;
else
 if (OCIDescribeAny(svch, errh, (dvoid*)type_ref, 0, OCI_OTYPE_REF, 
   0, OCI_PTYPE_TYPE, dschp))
   return error;

/* get the parameter handle */
if (OCIAttrGet(dschp, OCI_HTYPE_DESCRIBE, &parmh, 0, OCI_ATTR_PARAM, 
    errh))
    return error;

/* The type information of the object, in this case, OCI_PTYPE_TYPE, is 
obtained from the parameter descriptor returned by the OCIAttrGet */

/* get the number of attributes in the type */
if (OCIAttrGet(parmh, OCI_DTYPE_PARAM, &numattrs, 0, 
    OCI_ATTR_NUM_TYPE_ATTRS, errh))
    return error;

/* get the handle to the attribute list of the type */
if (OCIAttrGet(parmh, OCI_DTYPE_PARAM, (dvoid *)&attrlsthd, 0, 
 OCI_ATTR_LIST_TYPE_ATTRS, errh)==OCI_NO_DATA)
 return error;

/* go through the attribute list and retrieve the data-type of each attribute, 
and then recursively describe attribute types. */

for (i = 1; i <= numattrs; i++)
{
/* get parameter for attribute i */
if (OCIParamGet(attrlsthd, OCI_DTYPE_PARAM, errh, &attrhd, i))
 return error;

/* for example, get data type and typecode for attribute; note that 
OCI_ATTR_DATA_TYPE returns the SQLT code, while OCI_ATTR_TYPECODE returns the 
Oracle Type System typecode. */
if (OCIAttrGet(attrhd, OCI_DTYPE_PARAM,&datatype[i-1], 0, 
      OCI_ATTR_DATA_TYPE,errh))
    return error;
/* for example, get data type for attribute*/
if (OCIAttrGet(attrhd, OCI_DTYPE_PARAM,&typecode[i-1], 0, 
      OCI_ATTR_TYPECODE, errh))
    return error;

/* if attribute is an object type, recursively describe it */
if (typecode[i-1] == OCI_TYPECODE_OBJECT)
{
OCIRef *attr_type_ref;
OCIDescribe *nested_dschp;

/* allocate describe handle */
if (OCIHandleAlloc((dvoid *)envh,(dvoid**)&dschp,
(ub4)OCI_HTYPE_DESCRIBE,(size_t)0, (dvoid **)0))
return error;

if (OCIAttrGet(attrhd, OCI_DTYPE_PARAM,
          &attr_type_ref, 0, OCI_ATTR_REF_TDO,errh))
      return error;
      OCIDescribeAny(svch, errh,(dvoid*)attr_type_ref, 0, 
     OCI_OTYPE_REF, 0, OCI_PTYPE_TYPE, nested_dschp);
/* go on describing the type... */
    }
}

Retrieving the Collection Element's Data Type of a Named Collection Type

This example illustrates the use of an explicit describe on a named collection type. We illustrate how you can describe an object by its name or by its object reference (OCIRef). The following code fragment attempts to retrieve the data type value of each of the object type's attributes.

...
text type_name[] = "typename";
ub4 type_name_len = strlen("typename");
OCIRef *type_ref = ...;
un4 numattrs;
OCIDescribe *dschp;     /* describe handle */
OCIParam *parmh;        /* parameter handle */
OCIParam *attrlsthd;    /* handle to list of attrs */
OCIParam *attrhd;       /* attribute handle */
...
/* allocate describe handle */
if (OCIHandleAlloc((dvoid *)envh, (dvoid **)&dschp,
             (ub4)OCI_HTYPE_DESCRIBE, (size_t)0, (dvoid **)0))
 return error;

/* get the describe handle for the type */
if (describe_by_name)
 if (OCIDescribeAny(svch, errh, (dvoid*)type_name, type_name_len, 
 OCI_OTYPE_NAME, 0, OCI_PTYPE_TYPE, dschp))
 return error;
else
 if (OCIDescribeAny(svch, errh, (dvoid*)type_ref, 0, OCI_OTYPE_REF, 0, 
 OCI_PTYPE_TYPE, dschp))
 return error;

/* get the parameter handle */
if (OCIAttrGet(dschp, OCI_HTYPE_DESCRIBE, &parmh, 0, OCI_ATTR_PARAM, 
    errh))
    return error;

/* get the Oracle Type System type code of the type to determine that this is a 
collection type */
if (OCIAttrGet(attrhd, OCI_DTYPE_PARAM,&typecode, 0, OCI_ATTR_TYPECODE, 
    errh))
    return error;

/* if typecode is OCI_TYPECODE_NAMEDCOLLECTION,
  proceed to describe collection element */
if (typecode == OCI_TYPECODE_NAMEDCOLLECTION)
{
  /* get the collection's type: ie, OCI_TYPECODE_VARRAY or OCI_TYPECODE_TABLE */

if (OCIAttrGet(parmh, OCI_DTYPE_PARAM, (dvoid *)&collection_typecode, 0, 
OCI_ATTR_COLLECTION_TYPECODE, errh))
    return error;

/* get the collection element; you MUST use this to further retrieve information 
about the collection's element */
if (OCIAttrGet(parmh, OCI_DTYPE_PARAM, &collection_element_parmh, 0, 
OCI_ATTR_COLLECTION_ELEMENT, errh))
    return error;

/* get the number of elements if collection is a VARRAY; not valid for nested 
tables */
if (collection_typecode == OCI_TYPECODE_VARRAY)
    if OCIAttrGet(collection_element_parmh, OCI_DTYPE_PARAM,
      (dvoid *)&num_elements, 0, OCI_ATTR_NUM_ELEMENTS, errh))
      return error;

/* now use the collection_element parameter handle to retrieve information about 
the collection element */
if OCIAttrGet(collection_element_parmh, OCI_DTYPE_PARAM, 
    (dvoid *)&element_typecode, 0, OCI_ATTR_TYPECODE, errh))
    return error;

/* do the same to describe additional collection element information; this is 
very similar to describing type attributes */
...

Describing with Character Length Semantics

The following sample code shows a loop that retrieves the column names and data types corresponding to a query following query execution. The query was associated with the statement handle by a prior call to OCIStmtPrepare().

...
OCIParam     *mypard;
ub4          counter;
ub2          dtype;
text         *col_name;
ub4          counter, col_name_len, char_semantics, col_width;
sb4          parm_status;
...
/* Request a parameter descriptor for position 1 in the select-list */
counter = 1;
parm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, &mypard, 
           (ub4) counter);
/* Loop only if a descriptor was successfully retrieved for
   current position, starting at 1 */
while (parm_status == OCI_SUCCESS) {
   /* Retrieve the data type attribute */
   checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
           (dvoid*) &dtype,(ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE, 
           (OCIError *) errhp  ));
   /* Retrieve the column name attribute */
   checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
           (dvoid**) &col_name, (ub4 *) &col_char_len, (ub4) OCI_ATTR_NAME,
           (OCIError *) errhp ));
   /* Retrieve the length semantics for the column */
   checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
           (dvoid*) &char_semantics,(ub4 *) 0, (ub4) OCI_ATTR_CHAR_USED, 
           (OCIError *) errhp  ));
   if (char_semantics)
       /* Retrieve the column width in characters */
       checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
               (dvoid*) &col_width, (ub4 *) 0, (ub4) OCI_ATTR_CHAR_SIZE, 
               (OCIError *) errhp  ));
   else
       /* Retrieve the column width in bytes */
       checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM, 
               (dvoid*) &col_width,(ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE, 
               (OCIError *) errhp  ));
   printf("column=%s  datatype=%d, char semantics=%d, width=%d\n\n", col_name,
               dtype, char_semantics, col_width);
   fflush(stdout);
   /* increment counter and get next descriptor, if there is one */
   counter++;
   parm_status = OCIParamGet(stmthp, OCI_HTYPE_STMT, errhp, &mypard,
                 (ub4) counter);
   }
...