Oracle Call Interface Programmer's Guide, Volumes 1 & 2 Release 8.0 A58234-01 |
Library | Product | Contents | Index

# 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:

- Overview
- Using OCIDescribeAny()
- Examples

## Overview

This chapter deals with the use of the OCIDescribeAny() function to describe schema objects. For information about describing select-list items, refer to the section "Describing Select-List Items" on page 4-8.

For additional information about the OCIDescribeAny() call and its parameters, refer to the function description on page 13 - 57.

## Using OCIDescribeAny()

The OCIDescribeAny() function allows you to perform an explicit describe of one of the following schema objects:

- tables and views
- synonyms
- procedures
- functions
- packages
- sequences
- collections
- types

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. For example, when an application describes a table, it can then retrieve information about that table's columns.

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, the figure shows how description of a certain table might be organized:

The describe handle returned by OCIDescribeAny() points to such a tree of descriptions. 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 (e.g. column list), then we refer to them as parameters. In this document, we will use the terms handle and parameter 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.

For similar reasons, the OCI also does not allow describes on columns, arguments, or fields of tables, views, functions, procedures, or types. Such information can be obtained by describing the top-level object containing it.

### Note on Datatype Codes

For more information about typecodes (e.g., the OCI_TYPCODE values returned in the OCI_ATTR_TYPECODE attribute, and the SQLT typecodes returned in the OCI_ATTR_DATA_TYPE attribute), refer to the section "Typecodes" on page 3-24.

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; }

For more information on this function, refer to the description of OCIInitialize() on page 13-72.

### 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_rec record(a number, b char)
    my_proc (my_input my_rec)

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. Hence, parameters have attributes depending on the type of description they contain - 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_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. | ub1
OCI_ATTR_TIMESTAMP | the timestamp of the object this description is based on (in Oracle date format) | ub1 *
OCI_ATTR_NUM_ATTRS | the number of attributes | ub2
OCI_ATTR_NUM_PARAMS | the number of parameters | ub2

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

### Table/View Attributes

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 *

The following are additional attributes which belong to tables:

##### Table 6-3 Attributes Specific to Tables

Attribute | Description | Attribute Datatype
--- | --- | ---
OCI_ATTR_RDBA | data block address of the segment header | ub4
OCI_ATTR_TABLESPACE | tablespace the table resides in | word
OCI_ATTR_CLUSTERED | whether the table is clustered | ub1
OCI_ATTR_PARTITIONED | whether the table is partitioned | ub1
OCI_ATTR_INDEX_ONLY | whether the table is index only | ub1

### Procedure/Function 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" on page 6-19. | dvoid *

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" on page 6-19. | dvoid *

### 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" on page 6-4. 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" on page 6-4. 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_VERSION | a null terminated string containing the user-assigned version | text *
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" on page 6-13. | dvoid *
OCI_ATTR_NUM_TYPE_ATTRS | number of type attributes | ub4
OCI_ATTR_LIST_TYPE_ATTRS | list of type attributes. See "List Attributes" on page 6-19. | dvoid *
OCI_ATTR_NUM_TYPE_METHODS | number of type methods | ub4
OCI_ATTR_LIST_TYPE_METHODS | list of type methods. See "List Attributes" on page 6-19. | dvoid *
OCI_ATTR_MAP_METHOD | map method of type. See "Type Method Attributes" on page 6-12. | dvoid *
OCI_ATTR_ORDER_METHOD | order method of type. See "Type Method Attributes" on page 6-12. | dvoid *

### 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. | ub2
OCI_ATTR_TYPECODE | typecode. See "Note on Datatype Codes" on page 6-4. | OCITypeCode
OCI_ATTR_DATA_TYPE | the data type of the type attribute. See "Note on Datatype Codes" on page 6-4. | 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 a describe returns a value of zero for precision or -127 for scale, this indicates that the item being described is uninitialized; i.e., it is NULL in the data dictionary. | ub1
OCI_ATTR_SCALE | the scale of numeric type attributes. If a describe returns a value of zero for precision or -127 for scale, this indicates that the item being described is uninitialized; i.e., it is NULL in the data dictionary. | 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

### 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" on page 6-5, and "List Attributes" on page 6-19. | 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

As a reference, the following code shows the possible method flags which are used when determining the corresponding procedure/function attributes:

    OCITypeMethodFlag
    { OCI_TYPEMETHOD_INLINE = 0x0001,                               /* inline */
      OCI_TYPEMETHOD_CONSTANT = 0x0002,                           /* constant */
      OCI_TYPEMETHOD_VIRTUAL = 0x0004,                             /* virtual */
      OCI_TYPEMETHOD_CONSTRUCTOR = 0x0008,                     /* constructor */
      OCI_TYPEMETHOD_DESTRUCTOR = 0x0010,                       /* destructor */
      OCI_TYPEMETHOD_OPERATOR  = 0x0020,                          /* operator */
      OCI_TYPEMETHOD_SELFISH = 0x0040,  /* selfish method (generic otherwise) */

      OCI_TYPEMETHOD_MAP = 0x0080,                 /* map (relative ordering) */
      OCI_TYPEMETHOD_ORDER  = 0x0100,            /* order (relative ordering) */
        /* OCI_TYPEMETHOD_MAP and OCI_TYPEMETHOD_ORDER are mutually exclusive */

      OCI_TYPEMETHOD_RNDS= 0x0200,            /* Read no Data State (default) */
      OCI_TYPEMETHOD_WNDS= 0x0400,                     /* Write no Data State */
      OCI_TYPEMETHOD_RNPS= 0x0800,                   /* Read no Process State */
      OCI_TYPEMETHOD_WNPS= 0x1000                   /* Write no Process State */ }

### 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" on page 6-4. | OCITypeCode
OCI_ATTR_DATA_TYPE | the data type of the type attribute. See "Note on Datatype Codes" on page 6-4. | 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 a describe returns a value of zero for precision or -127 for scale, this indicates that the item being described is uninitialized; i.e., it is NULL in the data dictionary. | ub1
OCI_ATTR_SCALE | the scale of numeric type attributes. If a describe returns a value of zero for precision or -127 for scale, this indicates that the item being described is uninitialized; i.e., it is NULL in the data dictionary. | 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 | a null-terminated 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 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 *

Table 6-12.

### 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 Attributes Belonging to Columns of Tables or Views

Attribute | Description | Attribute Datatype
--- | --- | ---
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" on page 6-4. | ub2
OCI_ATTR_NAME | a pointer to a string which is the column name | text *
OCI_ATTR_PRECISION | the precision of numeric columns. If a describe returns a value of zero for precision or -127 for scale, this indicates that the item being described is uninitialized; i.e., it is NULL in the data dictionary. | ub1
OCI_ATTR_SCALE | the scale of numeric columns. If a describe returns a value of zero for precision or -127 for scale, this indicates that the item being described is uninitialized; i.e., it is NULL in the data dictionary. | 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

Table 6-13.

### 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 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" on page 6-4. | OCITypeCode
OCI_ATTR_DATA_TYPE | the data type of the argument. See "Note on Datatype Codes" on page 6-4. | 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 a describe returns a value of zero for precision or -127 for scale, this indicates that the item being described is uninitialized; i.e., it is NULL in the data dictionary. | ub1
OCI_ATTR_SCALE | the scale of numeric arguments. If a describe returns a value of zero for precision or -127 for scale, this indicates that the item being described is uninitialized; i.e., it is NULL in the data dictionary. | 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 data type 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

Table 6-14.

### 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 are:
  - OCI_LTYPE_COL - column list
  - OCI_LTYPE_ARG_PROC - procedure argument list
  - OCI_LTYPE_ARG_FUNC - function argument list
  - OCI_LTYPE_SUBPRG - subprogram list
  - OCI_LTYPE_TYPE_ATTR - type attribute list
  - OCI_LTYPE_TYPE_METHOD - type method list
  - OCI_LTYPE_TYPE_ARG_PROC - type method without result argument list
  - OCI_LTYPE_TYPE_ARG_FUNC - type method without result argument list
- The list has an OCI_ATTR_NUM_PARAMS attribute, which tells the number of elements in the list.
- The list has 1..OCI_ATTR_NUM_PARAMS parameters for each of the columns, arguments, or subprograms in the list (type OCI_PTYPE_COL, OCI_PTYPE_ARG, OCI_PTYPE_PROC, or OCI_PTYPE_FUNC). In the case of a function argument list, position 0 has a parameter for the return value (type OCI_PTYPE_ARG).

## Examples

The following examples demonstrate the use of OCIDescribeAny() for describing different types of schema objects. For a more detailed code sample, refer to "Example 4, Describing an Object" on page D-55.

### 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 pseudo-code shows how an application would be able to use the describe interface:

    text objptr[] = <table-name>;
    ub4 objp_len = strlen(<table_name>);
    OCIParam *parmh;         /* parameter handle */
    OCIParam *collsthd;      /* handle to list of columns */
    OCIParam *colhd;         /* column 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;
    }

### 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/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 pseudocode elucidates 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 */
    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 pseudo-code attempts to retrieve the data type value each of the object type's attribute. It is very similar to the first example on page 6 - 20.

    text type_name[] = <type_name>;
    ub4 type_name_len = strlen(<type_name>);
    OCIRef *type_ref = <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 pseudo-code attempts to retrieve the data type value each of the object type's attribute. It is very similar to the first example on page 6 - 20.

    text type_name[] = <type_name>;
    ub4 type_name_len = strlen(<type_name>);
    OCIRef *type_ref = <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 */
    }

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index