6 Describing Schema Metadata

Limitations on OCIDescribeAny()

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.

The table name is not returned by OCIDescribeAny() or the implicit use of OCIStmtExecute(). Sometimes a column is not associated with a table. In most cases, the table is already known.

Notes on Types and Attributes

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

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

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

Table or View Parameters

Parameters for a table or view (type OCI_PTYPE_TABLE or OCI_PTYPE_VIEW) have the following type-specific attributes:

ub4

ub2

dvoid *

OCIRef*

ub1

ub1

OCIDuration

The following are additional attributes which belong to tables:

ub4

word

ub1

ub1

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:

dvoid *

ub1

The following attributes are defined only for package subprograms:

OraText *

ub2

Package Attributes

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

dvoid *

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().

OCIRef *

OCITypeCode

OCITypeCode

ub1

ub1

ub1

ub1

ub1

ub1

ub1

ub1

dvoid *

ub2

dvoid *

ub2

dvoid *

dvoid *

dvoid *

ub1

OraText *

OraText *

ub1

ub1

ub1

OraText *

OraText *

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.

ub4

OCITypeCode

ub2

OraText *

ub1 for explicit describe
sb2 for implicit describe

sb1

OraText *

OraText *

OCIRef *

ub2

ub1

ub1

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.

OraText *

OCITypeEncap

dvoid *

ub1

ub1

ub1

ub1

ub1

ub1

ub1

ub1

ub1

ub1

ub1

ub1

ub1

Collection Attributes

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

ub4

OCITypeCode

ub2

ub4

OraText *

ub1 for explicit describe
sb2 for implicit describe

sb1

OraText *

OraText *

OCIRef *

ub2

ub1

Synonym Attributes

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

ub4

OraText *

OraText *

OraText *

Sequence Attributes

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

ub1 *

ub1 *

ub1

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.

ub4

ub2

ub4

ub2

OraText *

ub1 for explicit describe
sb2 for implicit describe

sb1

ub1

OraText *

OraText *

OCIRef *

ub2

ub1

Argument and Result Attributes

When a parameter is for an argument of a procedure or 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.

OraText *

ub2

OCITypeCode

ub2

ub4

ub2

ub1

dvoid *

OCITypeParamMode

ub1

ub1

OraText *

OraText *

OraText *

OraText *

OCIRef *

ub2

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:

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

OraText *

Database Attributes

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

OraText *

ub2

ub2

ub1

ub4

ub4

ub1

ub1

ub1

ub1

ub1

ub1

ub1

Rule Attributes

When a parameter is for a rule (type OCI_PTYPE_RULE), it has the attributes listed in Table 6-18:

OraText *

OraText *

OraText *

OraText *

dvoid *

Rule Set Attributes

When a parameter is for a rule set (type OCI_PTYPE_RULE_SET), it has the attributes listed in Table 6-19:

OraText *

OraText *

OraText *

dvoid *

Evaluation Context Attributes

When a parameter is for an evaluation context (type OCI_PTYPE_EVALUATION_CONTEXT), it has the attributes listed in Table 6-20:

OraText *

OraText *

dvoid *

dvoid *

Table Alias Attributes

When a parameter is for a table alias (type OCI_PTYPE_TABLE_ALIAS), it has the attributes listed in Table 6-21:

OraText *

OraText *

Variable Type Attributes

When a parameter is for a variable (type OCI_PTYPE_VARIABLE_TYPE), it has the attributes listed in Table 6-22:

OraText *

OraText *

OraText *

OraText *

Name Value Attributes

When a parameter is for a name value pair (type OCI_PTYPE_NAME_VALUE), it has the attributes listed in Table 6-23:

OraText *

OCIAnyData*

Implicit Describing

If the database column was created using character length semantics, then the implicit describe information will contain the character length, the byte length, and a flag indicating how the database column was created. OCI_ATTR_CHAR_SIZE is the character length of the column or expression. The OCI_ATTR_CHAR_USED flag is 1 in this case, indicating that the column or expression was created with 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 (OCI_ATTR_CHAR_SIZE)*(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 (column's byte length)*(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 the following attributes:

• 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

• 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 can set the OCI_ATTR_MAXCHAR_SIZE in the bind handle to the value returned by OCI_ATTR_CHAR_SIZE in the parameter handle. This will prevent you from violating the size constraint for the column.

Retrieving Column Datatypes for a Table

This example illustrates the use of an explicit describe that retrieves the column datatypes for a table.

...
int i=0;
text objptr[] = "EMPLOYEES"; /* the name of a table to be described */
ub2          numcols, col_width;
ub1          char_semantics;
ub2  coltyp;
ub4 objp_len = (ub4) strlen((char *)objptr);
OCIParam *parmh = (OCIParam *) 0;         /* parameter handle */
OCIParam *collsthd = (OCIParam *) 0;      /* handle to list of columns */
OCIParam *colhd = (OCIParam *) 0;         /* column handle */
OCIDescribe *dschp = (OCIDescribe *)0;      /* describe handle */

OCIHandleAlloc((dvoid *)envhp, (dvoid **)&dschp,
        (ub4)OCI_HTYPE_DESCRIBE, (size_t)0, (dvoid **)0);

/* get the describe handle for the table */
if (OCIDescribeAny(svch, errh, (dvoid *)objptr, objp_len, OCI_OTYPE_NAME, 0,
     OCI_PTYPE_TABLE, dschp))
   return OCI_ERROR;

/* get the parameter handle */
if (OCIAttrGet((dvoid *)dschp, OCI_HTYPE_DESCRIBE, (dvoid *)&parmh, (ub4 *)0,
                OCI_ATTR_PARAM, errh))
    return OCI_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 */
numcols = 0;
if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&numcols, (ub4 *)0,
     OCI_ATTR_NUM_COLS, errh))
    return OCI_ERROR;

/* get the handle to the column list of the table */
if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&collsthd, (ub4 *)0,
     OCI_ATTR_LIST_COLUMNS, errh)==OCI_NO_DATA)
   return OCI_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((dvoid *)collsthd, OCI_DTYPE_PARAM, errh, (dvoid **)&colhd, (ub4)i))
        return OCI_ERROR;

    /* for example, get datatype for ith column */
    coltyp = 0;
    if (OCIAttrGet((dvoid *)colhd, OCI_DTYPE_PARAM, (dvoid *)&coltyp, (ub4 *)0,
       OCI_ATTR_DATA_TYPE, errh))
        return OCI_ERROR;

    /* Retrieve the length semantics for the column */
    char_semantics = 0;
    OCIAttrGet((dvoid*) colhd, (ub4) OCI_DTYPE_PARAM,
                 (dvoid*) &char_semantics,(ub4 *) 0, (ub4) OCI_ATTR_CHAR_USED,
                 (OCIError *) errh);

    col_width = 0;
    if (char_semantics)
        /* Retrieve the column width in characters */
        OCIAttrGet((dvoid*) colhd, (ub4) OCI_DTYPE_PARAM,
                 (dvoid*) &col_width, (ub4 *) 0, (ub4) OCI_ATTR_CHAR_SIZE,
                 (OCIError *) errh);
    else
        /* Retrieve the column width in bytes */
        OCIAttrGet((dvoid*) colhd, (ub4) OCI_DTYPE_PARAM,
                 (dvoid*) &col_width,(ub4 *) 0, (ub4) OCI_ATTR_DATA_SIZE,
                 (OCIError *) errh);
}

if (dschp)
    OCIHandleFree((dvoid *) dschp, OCI_HTYPE_DESCRIBE);
...

Describing the Stored Procedure

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. Consider the following procedure:

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

Assume that each row in emp table has two columns: name(VARCHAR2(20)), and sal(NUMBER). In the argument list for P1, there are 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.

...
int i = 0, j = 0;
text objptr[] = "add_job_history"; /* the name of a procedure to be described */
ub4 objp_len = (ub4)strlen((char *)objptr);
ub2 numargs = 0, numargs1, pos, level;
text *name, *name1;
ub4 namelen, namelen1;
OCIParam *parmh = (OCIParam *) 0;         /* parameter handle */
OCIParam *arglst = (OCIParam *) 0;          /* list of args */
OCIParam *arg = (OCIParam *) 0;             /* argument handle */
OCIParam *arglst1 = (OCIParam *) 0;          /* list of args */
OCIParam *arg1 = (OCIParam *) 0;             /* argument handle */
OCIDescribe *dschp = (OCIDescribe *)0;      /* describe handle */

OCIHandleAlloc((dvoid *)envhp, (dvoid **)&dschp,
        (ub4)OCI_HTYPE_DESCRIBE, (size_t)0, (dvoid **)0);

/* get the describe handle for the procedure */
if (OCIDescribeAny(svch, errh, (dvoid *)objptr, objp_len, OCI_OTYPE_NAME, 0,
     OCI_PTYPE_PROC, dschp))
   return OCI_ERROR;

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

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

if (OCIAttrGet((dvoid *)arglst, OCI_DTYPE_PARAM, (dvoid *)&numargs, (ub4 *)0,
    OCI_ATTR_NUM_PARAMS, errh))
    return OCI_ERROR;

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

for (i = 1; i <= numargs; i++) {
  OCIParamGet ((dvoid *)arglst, OCI_DTYPE_PARAM, errh, (dvoid **)&arg, (ub4)i);
  namelen = 0;
  OCIAttrGet((dvoid *)arg, OCI_DTYPE_PARAM, (dvoid *)&name, (ub4 *)&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((dvoid *)arg, OCI_DTYPE_PARAM, (dvoid *)&arglst1, (ub4 *)0,
        OCI_ATTR_LIST_ARGUMENTS, errh);

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

  if (arglst1) {
    numargs1 = 0;
    OCIAttrGet((dvoid *)arglst1, OCI_DTYPE_PARAM, (dvoid *)&numargs1, (ub4 *)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 the procedure, the number of
    arguments at the level 1 would be 2 */

    for (j = 1; j <= numargs1; j++) {
      OCIParamGet((dvoid *)arglst1, OCI_DTYPE_PARAM, errh, (dvoid **)&arg1,
          (ub4)j);
      namelen1 = 0;
      OCIAttrGet((dvoid *)arg1, OCI_DTYPE_PARAM, (dvoid *)&name1, (ub4 *)&namelen1,
        OCI_ATTR_NAME, errh);
    }
  }
}

if (dschp)
    OCIHandleFree((dvoid *) dschp, OCI_HTYPE_DESCRIBE);
...

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.

...
int i = 0;
text type_name[] = "inventory_typ";
ub4 type_name_len = (ub4)strlen((char *)type_name);
OCIRef *type_ref = (OCIRef *) 0;
ub2 numattrs = 0, describe_by_name = 1;
ub2 datatype = 0;
OCITypeCode typecode = 0;
OCIDescribe *dschp = (OCIDescribe *) 0;      /* describe handle */
OCIParam *parmh = (OCIParam *) 0;         /* parameter handle */
OCIParam *attrlsthd = (OCIParam *) 0;     /* handle to list of attrs */
OCIParam *attrhd = (OCIParam *) 0;        /* attribute handle */

/* allocate describe handle */
if (OCIHandleAlloc((dvoid *)envh, (dvoid **)&dschp,
                  (ub4)OCI_HTYPE_DESCRIBE, (size_t)0, (dvoid **)0))
   return OCI_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 OCI_ERROR;
}
else {
  /* get ref to type using OCIAttrGet */

  /* get the describe handle for the type */
  if (OCIDescribeAny(svch, errh, (dvoid*)type_ref, 0, OCI_OTYPE_REF,
        0, OCI_PTYPE_TYPE, dschp))
       return OCI_ERROR;
}

/* get the parameter handle */
if (OCIAttrGet((dvoid *)dschp, OCI_HTYPE_DESCRIBE, (dvoid *)&parmh, (ub4 *)0,
        OCI_ATTR_PARAM, errh))
    return OCI_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((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&numattrs, (ub4 *)0,
    OCI_ATTR_NUM_TYPE_ATTRS, errh))
    return OCI_ERROR;

/* get the handle to the attribute list of the type */
if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&attrlsthd, (ub4 *)0,
      OCI_ATTR_LIST_TYPE_ATTRS, errh))
  return OCI_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((dvoid *)attrlsthd, OCI_DTYPE_PARAM, errh, (dvoid **)&attrhd, i))
      return OCI_ERROR;

/* for example, get datatype and typecode for attribute; note that
OCI_ATTR_DATA_TYPE returns the SQLT code, while OCI_ATTR_TYPECODE returns the
Oracle Type System typecode. */

datatype = 0;
if (OCIAttrGet((dvoid *)attrhd, OCI_DTYPE_PARAM, (dvoid *)&datatype, (ub4 *)0,
               OCI_ATTR_DATA_TYPE,errh))
    return OCI_ERROR;

typecode = 0;
if (OCIAttrGet((dvoid *)attrhd, OCI_DTYPE_PARAM,(dvoid *)&typecode, (ub4 *)0,
               OCI_ATTR_TYPECODE, errh))
    return OCI_ERROR;

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

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

  if (OCIAttrGet((dvoid *)attrhd, OCI_DTYPE_PARAM,
          (dvoid *)&attr_type_ref, (ub4 *)0, OCI_ATTR_REF_TDO,errh))
    return OCI_ERROR;

   OCIDescribeAny(svch, errh,(dvoid*)attr_type_ref, 0,
          OCI_OTYPE_REF, 0, OCI_PTYPE_TYPE, nested_dschp);
    /* go on describing the attribute type... */
}
}

if (dschp)
    OCIHandleFree((dvoid *) dschp, OCI_HTYPE_DESCRIBE);
...

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

This example illustrates the use of an explicit describe on a named collection type:

text type_name[] = "phone_list_typ";
ub4 type_name_len = (ub4) strlen((char *)type_name);
OCIRef *type_ref = (OCIRef *) 0;
ub2 describe_by_name = 1;
ub4 num_elements = 0;
OCITypeCode typecode = 0, collection_typecode = 0, element_typecode = 0;
dvoid *collection_element_parmh = (dvoid *) 0;
OCIDescribe *dschp = (OCIDescribe *) 0;      /* describe handle */
OCIParam *parmh = (OCIParam *) 0;         /* parameter handle */

/* allocate describe handle */
if (OCIHandleAlloc((dvoid *)envh, (dvoid **)&dschp,
                  (ub4)OCI_HTYPE_DESCRIBE, (size_t)0, (dvoid **)0))
   return OCI_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 OCI_ERROR;
}
else {
  /* get ref to type using OCIAttrGet */

  /* get the describe handle for the type */
  if (OCIDescribeAny(svch, errh, (dvoid*)type_ref, 0, OCI_OTYPE_REF,
        0, OCI_PTYPE_TYPE, dschp))
       return OCI_ERROR;
}

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

/* get the Oracle Type System type code of the type to determine that this is a
collection type */
typecode = 0;
if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM,(dvoid *)&typecode, (ub4 *)0,
      OCI_ATTR_TYPECODE, errh))
    return OCI_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 */
  collection_typecode = 0;
  if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, (dvoid *)&collection_typecode,
 (ub4 *)0,
       OCI_ATTR_COLLECTION_TYPECODE, errh))
    return OCI_ERROR;

  /* get the collection element; you MUST use this to further retrieve information
     about the collection's element */
  if (OCIAttrGet((dvoid *)parmh, OCI_DTYPE_PARAM, &collection_element_parmh,
                 (ub4 *)0,
        OCI_ATTR_COLLECTION_ELEMENT, errh))
    return OCI_ERROR;
  /* get the number of elements if collection is a VARRAY; not valid for nested
     tables */
  if (collection_typecode == OCI_TYPECODE_VARRAY) {
    if (OCIAttrGet((dvoid *)collection_element_parmh, OCI_DTYPE_PARAM,
          (dvoid *)&num_elements, (ub4 *)0, OCI_ATTR_NUM_ELEMS, errh))
      return OCI_ERROR;
  }
  /* now use the collection_element parameter handle to retrieve information about
     the collection element */
  element_typecode = 0;
  if (OCIAttrGet((dvoid *)collection_element_parmh, OCI_DTYPE_PARAM,
        (dvoid *)&element_typecode, (ub4 *)0, OCI_ATTR_TYPECODE, errh))
    return OCI_ERROR;

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

if (dschp)
    OCIHandleFree((dvoid *) dschp, OCI_HTYPE_DESCRIBE);
...

Describing with Character Length Semantics

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

...
OCIParam     *mypard = (OCIParam *) 0;
ub2          dtype;
text         *col_name;
ub4          counter, col_name_len, char_semantics;
ub2          col_width;
sb4          parm_status;

text *sqlstmt = (text *)"SELECT * FROM employees WHERE employee_id = 100";

checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (OraText *)sqlstmt,
                    (ub4)strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, 0, 0, (OCISnapshot *)0, (OCISnapshot *)0, OCI_DEFAULT));

/* Request a parameter descriptor for position 1 in the select-list */
counter = 1;
parm_status = OCIParamGet((dvoid *)stmthp, OCI_HTYPE_STMT, errhp,
               (dvoid **)&mypard, (ub4) counter);
/* Loop only if a descriptor was successfully retrieved for
   current position, starting at 1 */
while (parm_status == OCI_SUCCESS) {
   /* Retrieve the datatype 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 */
   col_name_len = 0;
   checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
           (dvoid**) &col_name, (ub4 *) &col_name_len, (ub4) OCI_ATTR_NAME,
           (OCIError *) errhp ));
   /* Retrieve the length semantics for the column */
   char_semantics = 0;
   checkerr(errhp, OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
           (dvoid*) &char_semantics,(ub4 *) 0, (ub4) OCI_ATTR_CHAR_USED,
           (OCIError *) errhp  ));
   col_width = 0;
   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  ));
   /* increment counter and get next descriptor, if there is one */
   counter++;
   parm_status = OCIParamGet((dvoid *)stmthp, OCI_HTYPE_STMT, errhp,
          (dvoid **)&mypard, (ub4) counter);
} /* while */
...