Oracle Call Interface Programmer's Guide, Volumes 1 & 2
Release 8.0

A58234-01

Library

Product

Contents

Index

Prev Next

D
Code Examples

This Appendix contains code examples illustrating the use of OCI calls. These programs are provided for demonstration purposes, and are not guaranteed to run on all platforms. When a specific header or SQL file is required by the application, its listing is included after the application code.

These and other demonstration programs may be available in the demo directory of your Oracle installation.

Example 1, SQL Processing

/* 
 *      -- cdemo81.c --
 *  An example program which adds new employee
 *  records to the personnel data base.  Checking
 *  is done to insure the integrity of the data base.
 *  The employee numbers are automatically selected using
 *  the current maximum employee number as the start.
 *  
 *  The program queries the user for data as follows:
 *   
 *  Enter employee name:
 *  Enter employee job:
 *  Enter employee salary:
 *  Enter employee dept:
 *   
 *  The program terminates if return key (CR) is entered
 *  when the employee name is requested.
 *   
 *  If the record is successfully inserted, the following is printed:
 *   
 *  "ename" added to department "dname" as employee # "empno"
 */

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

static text *username = (text *) "SCOTT";
static text *password = (text *) "TIGER";

/* Define SQL statements to be used in program. */
static text *insert = (text *) "INSERT INTO emp(empno, ename, job, sal, \
          deptno)VALUES (:empno, :ename, :job, :sal, :deptno)";
static text *seldept = (text *) "SELECT dname FROM dept WHERE deptno = :1";
static text *maxemp = (text *) "SELECT NVL(MAX(empno), 0) FROM emp";
static text *selemp = (text *) "SELECT ename, job FROM emp";

static OCIEnv *envhp;
static OCIServer *srvhp;
static OCIError *errhp;
static OCISvcCtx *svchp;
static OCIStmt *stmthp, *stmthp1;
static OCIDefine *defnp = (OCIDefine *) 0;

static OCIBind  *bnd1p = (OCIBind *) 0;          /* the first bind handle */
static OCIBind  *bnd2p = (OCIBind *) 0;         /* the second bind handle */
static OCIBind  *bnd3p = (OCIBind *) 0;          /* the third bind handle */
static OCIBind  *bnd4p = (OCIBind *) 0;         /* the fourth bind handle */
static OCIBind  *bnd5p = (OCIBind *) 0;          /* the fifth bind handle */
static OCIBind  *bnd6p = (OCIBind *) 0;          /* the sixth bind handle */

static void checkerr(/*_ OCIError *errhp, sword status _*/);
static void cleanup(/*_ void _*/);
static void myfflush(/*_ void _*/);
int main(/*_ int argc, char *argv[] _*/);

static sword status;

int main(argc, argv)
int argc;
char *argv[];
{

  sword    empno, sal, deptno;
  sword    len, len2, rv, dsize, dsize2;
  sb4      enamelen = 10;
  sb4      joblen = 9;		
  sb4      deptlen = 14;	
  sb2      sal_ind, job_ind;
  sb2      db_type, db2_type;
  sb1      name_buf[20], name2_buf[20];
  text     *cp, *ename, *job, *dept;

  sb2      ind[2]; 
  ub2      alen[2];
  ub2      rlen[2];
  OCIDescribe  *dschndl1 = (OCIDescribe *) 0,
               *dschndl2 = (OCIDescribe *) 0,
               *dschndl3 = (OCIDescribe *) 0;
  OCISession *authp = (OCISession *) 0;
  
  (void) OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
                       (dvoid * (*)(dvoid *, size_t)) 0,
                       (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                       (void (*)(dvoid *, dvoid *)) 0 );

  (void) OCIEnvInit( (OCIEnv **) &envhp, OCI_DEFAULT, (size_t) 0, 
                     (dvoid **) 0 );

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, 
                   (size_t) 0, (dvoid **) 0);

  /* server contexts */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                   (size_t) 0, (dvoid **) 0);

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                   (size_t) 0, (dvoid **) 0);

  (void) OCIServerAttach( srvhp, errhp, (text *)"inst1_alias", 
                   strlen("inst1_alias"), 0);

  /* set attribute server context in the service context */
  (void) OCIAttrSet( (dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, 
                 (ub4) 0, OCI_ATTR_SERVER, (OCIError *) errhp);

  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)&authp,
                 (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0);
 
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) username, (ub4) strlen((char *)username),
                 (ub4) OCI_ATTR_USERNAME, errhp);
 
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) password, (ub4) strlen((char *)password),
                 (ub4) OCI_ATTR_PASSWORD, errhp);

  checkerr(errhp, OCISessionBegin ( svchp,  errhp, authp, OCI_CRED_RDBMS, 
                  (ub4) OCI_DEFAULT));
 
  (void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                   (dvoid *) authp, (ub4) 0,
                   (ub4) OCI_ATTR_SESSION, errhp);

  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp1,
           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

  /* Retrieve the current maximum employee number. */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, maxemp, 
                                (ub4) strlen((char *) maxemp),
                                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  /* bind the input variable */
  checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, 1, (dvoid *) &empno,
                   (sword) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, OCI_DEFAULT));

  /* execute and fetch */
  if (status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
  {
    if (status == OCI_NO_DATA)
      empno = 10;
    else
    {
      checkerr(errhp, status);
      cleanup();
      return OCI_ERROR;
    }
  }


  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, insert, 
                                (ub4) strlen((char *) insert),
                                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));


  checkerr(errhp, OCIStmtPrepare(stmthp1, errhp, seldept, 
                                (ub4) strlen((char *) seldept),
                                (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));

  /*  Allocate output buffers. Allow for \n and '\0'. */
  ename = (text *) malloc((size_t) enamelen + 2);
  job   = (text *) malloc((size_t) joblen + 2);

  /*  Bind the placeholders in the INSERT statement. */
  if ((status = OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":ENAME",
             -1, (dvoid *) ename,
             enamelen+1, SQLT_STR, (dvoid *) 0,
             (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) ||
      (status = OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":JOB",
             -1, (dvoid *) job,
             joblen+1, SQLT_STR, (dvoid *) &job_ind,
             (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) ||
      (status = OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":SAL",
             -1, (dvoid *) &sal,
             (sword) sizeof(sal), SQLT_INT, (dvoid *) &sal_ind,
             (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) ||
      (status = OCIBindByName(stmthp, &bnd4p, errhp, (text *) ":DEPTNO",
             -1, (dvoid *) &deptno,
             (sword) sizeof(deptno), SQLT_INT, (dvoid *) 0,
             (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)) ||
      (status = OCIBindByName(stmthp, &bnd5p, errhp, (text *) ":EMPNO",
             -1, (dvoid *) &empno,
             (sword) sizeof(empno), SQLT_INT, (dvoid *) 0,
             (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT)))
  {
    checkerr(errhp, status);
    cleanup();
    return OCI_ERROR;
  }

  /*  Bind the placeholder in the "seldept" statement. */
  if (status = OCIBindByPos(stmthp1, &bnd6p, errhp, 1,
           (dvoid *) &deptno, (sword) sizeof(deptno),SQLT_INT,
           (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT))
  {
    checkerr(errhp, status);
    cleanup();
    return OCI_ERROR;
  }  

  /*  Allocate the dept buffer now that you have length. */
  /* the deptlen should eventually get from dschndl3.    */
  deptlen = 14;
  dept = (text *) malloc((size_t) deptlen + 1);

  /*  Define the output variable for the select-list. */
  if (status = OCIDefineByPos(stmthp1, &defnp, errhp, 1,
                             (dvoid *) dept, deptlen+1, SQLT_STR,
                             (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, OCI_DEFAULT))
  {
    checkerr(errhp, status);
    cleanup();
    return OCI_ERROR;
  }

  for (;;)
  {
    /* Prompt for employee name.  Break on no name. */
    printf("\nEnter employee name (or CR to EXIT): ");
    fgets((char *) ename, (int) enamelen+1, stdin);
    cp = (text *) strchr((char *) ename, '\n');
    if (cp == ename)
    {
      printf("Exiting... ");
      cleanup();
      return OCI_SUCCESS;
    }
    if (cp)
      *cp = '\0';
    else
    {
      printf("Employee name may be truncated.\n");
      myfflush();
    }
    /*  Prompt for the employee's job and salary. */
    printf("Enter employee job: ");
    job_ind = 0;
    fgets((char *) job, (int) joblen + 1, stdin);
    cp = (text *) strchr((char *) job, '\n');
    if (cp == job)
    {
      job_ind = -1;            /* make it NULL in table */    
      printf("Job is NULL.\n");/* using indicator variable */
    }
    else if (cp == 0)
    {
      printf("Job description may be truncated.\n");
      myfflush();
    }
    else
      *cp = '\0';

    printf("Enter employee salary: ");
    scanf("%d", &sal);
    myfflush();
    sal_ind = (sal <= 0) ? -2 : 0;  /* set indicator variable */

    /*
     *  Prompt for the employee's department number, and verify
     *  that the entered department number is valid
     *  by executing and fetching.
     */
    do
    {
      printf("Enter employee dept: ");
      scanf("%d", &deptno);
      myfflush();
      if ((status = OCIStmtExecute(svchp, stmthp1, errhp, (ub4) 1, (ub4) 0,
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT))
          && (status != OCI_NO_DATA))
      {
        checkerr(errhp, status);
        cleanup();
        return OCI_ERROR;
      }  
      if (status == OCI_NO_DATA)
        printf("The dept you entered doesn't exist.\n");
      } while (status == OCI_NO_DATA);

      /*
       *  Increment empno by 10, and execute the INSERT
       *  statement. If the return code is 1 (duplicate
       *  value in index), then generate the next
       *  employee number.
       */
      empno += 10;
      if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)) 
               && status != 1)
      {
        checkerr(errhp, status);
        cleanup();
        return OCI_ERROR;
      }
      while (status == 1)
      {
        empno += 10;
        if ((status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
               (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT)) 
                && status != 1)
        {
          checkerr(errhp, status);
          cleanup();
          return OCI_ERROR;
        }
      }  /* end for (;;) */

      /* Commit the change. */
      if (status = OCITransCommit(svchp, errhp, 0))
      {
        checkerr(errhp, status);
        cleanup();
        return OCI_ERROR;
      }
      printf("\n\n%s added to the %s department as employee number %d\n",
                 ename, dept, empno);
  }
}


void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}


/*
 *  Exit program with an exit code.
 */
void cleanup()
{
  if (stmthp)
    checkerr(errhp, OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
  if (stmthp1)
    checkerr(errhp, OCIHandleFree((dvoid *) stmthp1, OCI_HTYPE_STMT));

  if (errhp)
    (void) OCIServerDetach( srvhp, errhp, OCI_DEFAULT );
  if (srvhp)
    checkerr(errhp, OCIHandleFree((dvoid *) srvhp, OCI_HTYPE_SERVER));
  if (svchp)
    (void) OCIHandleFree((dvoid *) svchp, OCI_HTYPE_SVCCTX);
  if (errhp)
    (void) OCIHandleFree((dvoid *) errhp, OCI_HTYPE_ERROR);
  return;
}


void myfflush()
{
  eb1 buf[50];

  fgets((char *) buf, 50, stdin);
}





Example 2, Object Retrieval

/* NAME
     cdemo82.c - oci object sample program ; run cdemo82.sql  */
 
#ifndef CDEMO82_ORACLE
#include <cdemo82.h>
#endif

#define SCHEMA "CDEMO82"


/*******************************************************************/ 
static void pin_display_addr(envhp, errhp, addrref)
OCIEnv *envhp;
OCIError *errhp;
OCIRef *addrref;
{
  sword status;
  address *addr = (address *)0;

  checkerr(errhp, OCIObjectPin(envhp, errhp, addrref, (OCIComplexObject *)0,
                   OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE,
                   (dvoid **)&addr));
  
  if (addr)
  {
    printf("address.state = %.2s address.zip = %.10s\n",
            OCIStringPtr(envhp, addr->state), OCIStringPtr(envhp, addr->zip));
  }
  else
  {
    printf("Pinned address pointer is null\n");
  }
  
  checkerr(errhp, OCIObjectUnpin(envhp, errhp, (dvoid *) addr));
}

/*****************************************************************/ 
static void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  ub4 buflen;
  ub4 errcode;
  
  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    printf("Error - OCI_NO_DATA\n");
    break;
  case OCI_ERROR:
    OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                  errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
    printf("Error - %s\n", errbuf);
    break;
  case OCI_INVALID_HANDLE:
    printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}

/****************************************************************/ 
/* 
 ** execute "selvalstmt" statement -- selects from a table with an object.
 **
 */
static void selectval(envhp, svchp, stmthp, errhp)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
{
  OCIType *addr_tdo = (OCIType *) 0;
  OCIDefine *defn1p = (OCIDefine *) 0, *defn2p = (OCIDefine *) 0;
  address *addr = (address *)NULL;
  sword custno =0;
  int i = 0;
  OCIRef *addrref = (OCIRef *) 0;
  OCIRef *type_ref = (OCIRef *) 0;
  sb4 status;
  OCIDescribe *dschp = (OCIDescribe *) 0;
  OCIParam *parmp;

  /* allocate describe handle for OCIDescribeAny */
  checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &dschp,
                        (ub4) OCI_HTYPE_DESCRIBE,
                        (size_t) 0, (dvoid **) 0));
  

  /* define the application request  */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) selvalstmt,
               (ub4) strlen(selvalstmt),
               (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
  
  /* bind the input variable */
  checkerr(errhp, OCIDefineByPos(stmthp, &defn1p, errhp, (ub4) 1, (dvoid *) 
               &custno,
               (sb4) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0,
               (ub2 *)0, (ub4) OCI_DEFAULT));
  
  checkerr(errhp, OCIDefineByPos(stmthp, &defn2p, errhp, (ub4) 2, (dvoid *) 0,
               (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0,
               (ub2 *)0, (ub4) OCI_DEFAULT));
  
 /*  checkerr(errhp, OCITypeByName(envhp, errhp, svchp, (const text *) 0,
                   (ub4) 0, (const text *) "ADDRESS_VALUE",
                   (ub4) strlen((const char *) "ADDRESS_VALUE"),
                   (CONST text *) 0, (ub4) 0,
                   OCI_DURATION_SESSION,  OCI_TYPEGET_HEADER,
                   &addr_tdo)); */

  checkerr(errhp, OCIDescribeAny(svchp, errhp, (text *)"ADDRESS_VALUE",
                  (ub4) strlen((char *)"ADDRESS_VALUE"), OCI_OTYPE_NAME, 
                  (ub1)1,
                  (ub1) OCI_PTYPE_TYPE, dschp));

  checkerr(errhp, OCIAttrGet((dvoid *) dschp, (ub4) OCI_HTYPE_DESCRIBE,
                  (dvoid *)&parmp, (ub4 *)0, (ub4)OCI_ATTR_PARAM, errhp));

  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &type_ref, (ub4 *) 0,
                    (ub4) OCI_ATTR_REF_TDO, (OCIError *) errhp));

  checkerr(errhp, OCIObjectPin(envhp, errhp, type_ref, (OCIComplexObject *) 0,
               OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE,
               (dvoid **)&addr_tdo));
 
  if(!addr_tdo)
  {
    printf("NULL tdo returned\n");
    goto done_selectval;
  }
  
  
  checkerr(errhp, OCIDefineObject(defn2p, errhp, addr_tdo, (dvoid **) &addr,
                (ub4 *) 0, (dvoid **) 0, (ub4 *) 0));
  
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
               (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) 
               OCI_DEFAULT));
  
  /* execute and fetch */
  do
  {
    if (addr)
      printf("custno = %d address.state = %.2s address.zip = %.10s\n", custno,
            OCIStringPtr(envhp, addr->state), OCIStringPtr(envhp, addr->zip));
    else
      printf("custno = %d fetched address is NULL\n", custno);
    
    addr = (address *)NULL;
  }
  while ((status = OCIStmtFetch(stmthp, errhp, (ub4) 1, (ub4) OCI_FETCH_NEXT,
                               (ub4) OCI_DEFAULT)) == OCI_SUCCESS ||
                                status == OCI_SUCCESS_WITH_INFO);
  
  
  if ( status!= OCI_NO_DATA )
    checkerr(errhp, status);
  
  printf("\n\n");
  
 done_selectval:
  
  checkerr(errhp, OCIHandleFree((dvoid *) defn1p, (ub4) OCI_HTYPE_DEFINE));
  checkerr(errhp, OCIHandleFree((dvoid *) defn2p, (ub4) OCI_HTYPE_DEFINE));
  
}

/****************************************************************
 ** execute "selobjstmt" -- selects records from a table with a REF. 
 */
static void selectobj(envhp, svchp, stmthp, errhp)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
{
  OCIType *addr_tdo = (OCIType *) 0;
  OCIDefine *defn1p = (OCIDefine *) 0, *defn2p = (OCIDefine *) 0;
  sword status;
  OCIRef *addrref = (OCIRef *) 0, *addrref1 = (OCIRef *) 0;
  sword custno =0;
  int i = 0;
  address *addr;
  ub4     ref_len;
  
  
  /* define the application request  */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) selobjstmt,
               (ub4) strlen(selobjstmt),
               (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
  
  checkerr(errhp, OCIDefineByPos(stmthp, &defn1p, errhp, (ub4) 1, (dvoid *) 
               &custno, (sb4) sizeof(sword), SQLT_INT, (dvoid *) 0, (ub2 *)0,
               (ub2 *)0, (ub4) OCI_DEFAULT));
  
  addrref = (OCIRef *)NULL;

  checkerr(errhp, OCIDefineByPos(stmthp, &defn2p, errhp, (ub4) 2, (dvoid *) 
               NULL, (sb4) 0, SQLT_REF, (dvoid *) 0, (ub2 *)0,
               (ub2 *)0, (ub4) OCI_DEFAULT));
  
  checkerr(errhp, OCIDefineObject(defn2p, errhp, (OCIType *)NULL,
                     (dvoid **)&addrref, &ref_len, (dvoid **)0, (ub4 *)0));
  
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
               (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
  
  do
  {
    printf("custno = %d fetched address\n", custno);
    
    if ( addrref )
    {
      pin_display_addr(envhp, errhp, addrref);
    }
    else
      printf("Address ref is NULL\n");
    
  }
  while ((status = OCIStmtFetch(stmthp, errhp, (ub4) 1,  (ub4) OCI_FETCH_NEXT,
                               (ub4) OCI_DEFAULT)) == OCI_SUCCESS ||
                                status == OCI_SUCCESS_WITH_INFO);
  
  
  if ( status != OCI_NO_DATA )
    checkerr(errhp, status);
  
  printf("\n\n");
  checkerr(errhp, OCIHandleFree((dvoid *) defn1p, (ub4) OCI_HTYPE_DEFINE));
  checkerr(errhp, OCIHandleFree((dvoid *) defn2p, (ub4) OCI_HTYPE_DEFINE));
  
}


/*******************************************************************/ 
/*******************************************************************/ 
/*
 ** execute "insstmt"
 **
 */
static void insert(envhp, svchp, stmthp, errhp, insstmt, nrows)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
text *insstmt;
ub2   nrows;
{
  OCIType *addr_tdo = (OCIType *) 0;
  address  addrs;
  null_address naddrs;
  address *addr = &addrs;
  null_address *naddr = &naddrs;
  sword custno =300;
  OCIBind *bnd1p = (OCIBind *) 0, *bnd2p = (OCIBind *) 0;
  char buf[20];
  ub2 i;
  OCIRef *type_ref = (OCIRef *) 0;
  OCIDescribe *dschp = (OCIDescribe *) 0;
  OCIParam *parmp;

  /* allocate describe handle for OCIDescribeAny */
  checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &dschp,
                        (ub4) OCI_HTYPE_DESCRIBE,
                        (size_t) 0, (dvoid **) 0));

  /* define the application request  */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insstmt,
               (ub4) strlen(insstmt),
               (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
  
  /* bind the input variable */
  checkerr(errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":custno", 
               (sb4) -1, (dvoid *) &custno, 
               (sb4) sizeof(sword), SQLT_INT,
               (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, 
               (ub4) OCI_DEFAULT));
  
  checkerr(errhp, OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":addr", 
               (sb4) -1, (dvoid *) 0, 
               (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0, (ub2 *)0,
               (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT));
  
  /* checkerr(errhp, OCITypeByName(envhp, errhp, svchp, (const text *) 0,
                   (ub4) 0, (const text *) "ADDRESS_VALUE",
                   (ub4) strlen((const char *) "ADDRESS_VALUE"),
                   (CONST text *) 0, (ub4) 0,
                   OCI_DURATION_SESSION,  OCI_TYPEGET_HEADER,
                   &addr_tdo)); */

  checkerr(errhp, OCIDescribeAny(svchp, errhp, (text *)"ADDRESS_VALUE",
                  (ub4) strlen((char *)"ADDRESS_VALUE"), OCI_OTYPE_NAME,   
                  (ub1)1, (ub1) OCI_PTYPE_TYPE, dschp));

  checkerr(errhp, OCIAttrGet((dvoid *) dschp, (ub4) OCI_HTYPE_DESCRIBE,
                  (dvoid *)&parmp, (ub4 *)0, (ub4)OCI_ATTR_PARAM, errhp));

  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &type_ref, (ub4 *) 0,
                    (ub4) OCI_ATTR_REF_TDO, (OCIError *) errhp));

  checkerr(errhp, OCIObjectPin(envhp, errhp, type_ref, (OCIComplexObject *) 0,
               OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE,
               (dvoid **)&addr_tdo));

  if(!addr_tdo)
  {
    printf("Null tdo returned\n");
    goto done_insert;
  }
  
  checkerr(errhp, OCIBindObject(bnd2p, errhp, addr_tdo, (dvoid **) &addr,
                  (ub4 *) 0, (dvoid **) &naddr, (ub4 *) 0));
  
  for(i = 0; i <= nrows; i++)
  {
    addr->state = (OCIString *) 0;
    sprintf(buf, "%cA", 65+i%27);
    checkerr(errhp, OCIStringAssignText(envhp, errhp, (CONST text*) buf, 
                          2, &addr->state));
    addr->zip = (OCIString *) 0;
    sprintf(buf, "94%d    ", i+455);
    checkerr(errhp, OCIStringAssignText(envhp, errhp, (CONST text*) buf, 10,
                          &addr->zip));
 
    naddr->null_object   = 0;
    naddr->null_state = 0;
    naddr->null_zip = 0;
    
    checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
             (OCISnapshot *) NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
  }
  checkerr(errhp, OCITransCommit(svchp, errhp, (ub4) 0));
  
 done_insert:
  
  checkerr(errhp, OCIHandleFree((dvoid *) bnd1p, (ub4) OCI_HTYPE_BIND));
  checkerr(errhp, OCIHandleFree((dvoid *) bnd2p, (ub4) OCI_HTYPE_BIND));
  
}

/****************************************************************/ 
int main()
{
  OCIEnv *envhp;
  OCIServer *srvhp;
  OCIError *errhp;
  OCISvcCtx *svchp;
  OCIStmt *stmthp;
  OCISession *usrhp;
  
  OCIInitialize((ub4) OCI_THREADED | OCI_OBJECT, (dvoid *)0,  (dvoid * (*)()) 
                   0,(dvoid * (*)()) 0,  (void (*)()) 0 );
  
  OCIHandleAlloc( (dvoid *) NULL, (dvoid **) &envhp, (ub4) OCI_HTYPE_ENV,
                   52, (dvoid **) &tmp);
  
  OCIEnvInit( &envhp, (ub4) OCI_DEFAULT, 21, (dvoid **) &tmp  ); 

  OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, (ub4) OCI_HTYPE_ERROR,
                   52, (dvoid **) &tmp);
  OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, (ub4) OCI_HTYPE_SERVER,
                   52, (dvoid **) &tmp);
  
  OCIServerAttach( srvhp, errhp, (text *) 0, (sb4) 0, (ub4) OCI_DEFAULT);
  
  OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, (ub4) OCI_HTYPE_SVCCTX,
                 52, (dvoid **) &tmp);
  
  /* set attribute server context in the service context */
  OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
            (dvoid *) srvhp, (ub4) 0,
            (ub4) OCI_ATTR_SERVER, (OCIError *) errhp);
  
  /* allocate a user context handle */
  OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp, (ub4) OCI_HTYPE_SESSION,
            (size_t) 0, (dvoid **) 0);
  
  OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
            (dvoid *)"cdemo82", (ub4)strlen("cdemo82"),
            OCI_ATTR_USERNAME, errhp);
  
  OCIAttrSet((dvoid *)usrhp, (ub4)OCI_HTYPE_SESSION,
            (dvoid *)"cdemo82", (ub4)strlen("cdemo82"),
            OCI_ATTR_PASSWORD, errhp);
  
  checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS, 
                              OCI_DEFAULT));
  
  OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
            (dvoid *)usrhp, (ub4)0,
            OCI_ATTR_SESSION, errhp);
  
  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
            (ub4) OCI_HTYPE_STMT, 50, (dvoid **) &tmp));
  
  /* execute "insstmt" */
  printf("--- Test insertion into extent table.\n");
  insert(envhp, svchp, stmthp, errhp, insstmt, 26);
  
  /* execute "selstmt" */
  printf("--- Test selection of a table with one object column.\n");
  selectval(envhp, svchp, stmthp, errhp);
  
  
  /* execute "selobjstmt" */
  printf("--- Test selection of a table with one object REF.\n");
  selectobj(envhp, svchp, stmthp, errhp);
  
  
  checkerr(errhp, OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT));
  
  OCISessionEnd(svchp, errhp, usrhp, (ub4)OCI_DEFAULT);
  OCIServerDetach( srvhp, errhp, (ub4) OCI_DEFAULT );
  checkerr(errhp, OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER));
  checkerr(errhp, OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX));
  checkerr(errhp, OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR));

}

cdemo82.h

/* 
   NAME 
     cdemo82.h - header file for oci object sample program
*/

 #ifndef CDEMO82_ORACLE
# define CDEMO82_ORACLE

#ifndef OCI_ORACLE
#include <oci.h>
#endif

/*-------------------------------------------------------------------
                     PRIVATE TYPES AND CONSTANTS
  ------------------------------------------------------------------*/
#define SERVER "ORACLE"
#define ADDRESS_TYPE_NAME "ADDRESS_OBJECT"
#define EMB_ADDRESS_TYPE_NAME "EMBEDDED_ADDRESS"
#define ADDREXT "ADDREXT"
#define EMBADDREXT "EMBADDREXT"
#define RETURN_ON_ERROR(error) if (error) return (error)
#define BIG_RECORD_SIZE 1000

struct address
{
  OCIString   *state;
  OCIString   *zip;
};
typedef struct address address;

struct null_address
{
  sb4    null_object;
  sb4    null_state;
  sb4    null_zip;
};
typedef struct null_address null_address;

struct embaddress
{
  OCIString   *state;
  OCIString   *zip;
  OCIRef  *preaddrref;
};
typedef struct embaddress embaddress;


struct null_embaddress
{
  sb4     null_state;
  sb4     null_zip;
  sb4     null_preaddrref;
};
typedef struct null_embaddress null_embaddress;

struct person
{
  OCIString        *name;
  OCINumber           age;
  address          addr;
};
typedef struct person person;

struct null_person
{
  sb4              null_name;
  sb4              null_age;
  null_address     null_addr;
};

typedef struct null_person null_person;

static const text *const  names[] =
{(text *) "CUSTOMERVAL", (text *) "ADDRESS", (text *) "STATE"};

static const text *const  selvalstmt = (text *)
                     "SELECT custno, addr FROM customerval";
 
static const text *const  selobjstmt = (text *)
                     "SELECT custno, addr FROM customerobj";
 
static const text *const  selref = (text *)
                     "SELECT REF(extaddr) from extaddr";

static const text *const  deleteref = (text *)
                     "DELETE extaddr";

static const text *const  insertref = (text *)
"insert into extaddr values(address_object('CA', '98765'))";

static const text *const  modifyref = (text *)
"update extaddr set object_column = address_object('TX', '61111')";

static const text *const  selembref = (text *)
                     "SELECT REF(exbextaddr) from embextaddr";

static const text *const  bndref = (text *)
"update extaddr set object_column.state = 'GA' where object_column = :addrref";

static const text *const  insstmt = 
(text *)"INSERT INTO customerval (custno, addr) values (:custno, :addr)";

dvoid *tmp;

/*--------------------------------------------------------------------
                           PUBLIC FUNCTIONS
  -----------------------------------------------------------------*/
OCIRef *cbfunc(/*_ dvoid *context _*/);

/*-----------------------------------------------------------------
                          PRIVATE FUNCTIONS
  -----------------------------------------------------------------*/
static void checkerr(/*_ OCIError *errhp, sword status _*/);
static void selectval(/*_ OCIEnv *envhp, OCISvcCtx *svchp, 
                           OCIStmt *stmthp, OCIError *errhp _*/);
static void selectobj(/*_ OCIEnv *envhp, OCISvcCtx *svchp, 
                           OCIStmt *stmthp, OCIError *errhp  _*/);
static void insert(/*_ OCIEnv *envhp, OCISvcCtx *svchp,
                        OCIStmt *stmthp, OCIError *errhp,
                        text *insstmt, ub2 nrows _*/);

static void pin_display_addr(/*_ OCIEnv *envhp, OCIError *errhp,
                                  OCIRef *addrref _*/);

int main(/*_ void _*/);

cdemo82.sql

Rem cdemo82.sql
Rem
Rem    NAME
Rem      cdemo82.sql - sql to be executed before cdemo82
Rem

set echo on;
connect internal;
drop user cdemo82 cascade;
create user cdemo82 identified by cdemo82;
grant connect, resource to cdemo82;
connect cdemo82/cdemo82;
drop table customerval;
drop table customerobj;
drop table extaddr;
drop table embextaddr;
drop type embedded_address;
drop type address_object;
drop type person;
drop table emp;
create type address_object as object (state char(2), zip char(10));
create type embedded_address as object (state char(2), zip char(10), 
         preaddr REF address_object);
drop type address_value;
create type address_value as object (state char(2), zip char(10));
create table customerval (custno number, addr address_value);
insert into customerval values(100, address_value('CA', '94065'));
create table extaddr of address_object;
create table customerobj (custno number, addr REF address_object);
insert into extaddr values (address_object('CA', '94065'));
insert into customerobj values(1000, null);
update customerobj set addr = (select ref(extaddr) from extaddr where 
         zip='94065');
insert into extaddr values (address_object('CA', '98765'));
insert into extaddr values (address_object('CA', '95117'));
select REFTOHEX(ref(extaddr)) from extaddr;
create table embextaddr of embedded_address;
insert into embextaddr values (embedded_address('CA', '95117', NULL));
select objectTOHEX(p) from embextaddr p;
drop table extper;
drop table empref;
drop table emp;
drop type person;
create type person as object ( name char(20), age number, address 
       address_object );
create table emp (emp_id number, emp_info person);
create table empref (emp_id number, emp_info REF person);
create table extper of person;
create or replace procedure upd_addr(addr IN OUT address_object) is
begin
   addr.state := 'CA';
   addr.zip := '95117';
end;
/
commit;
set echo off;

Example 3, DML with RETURNING Clause

/*   NAME
     cdemord1.c - C DEMO program for DML with RETURNING clause -  #1.

   DESCRIPTION
     This Demo program demonstrates the use of  INSERT/UPDATE/DELETE 
     statements with a RETURNING clause in it.
*/

#include <cdemodr1.h>

/*------------------------ Global Variables -------------------------------*/

static boolean logged_on = FALSE;

/* TAB1 columns */
static int   in1[MAXITER];                    /* for INTEGER      */
static text  in2[MAXITER][40];                /* for CHAR(40)     */
static text  in3[MAXITER][40];                /* for VARCHAR2(40) */
static float in4[MAXITER];                    /* for FLOAT        */
static int   in5[MAXITER];                    /* for DECIMAL      */
static float in6[MAXITER];                    /* for DECIMAL(8,3) */
static int   in7[MAXITER];                    /* for NUMERIC      */
static float in8[MAXITER];                    /* for NUMERIC(7,2) */
static ub1   in9[MAXITER][7];                 /* for DATE         */
static ub1   in10[MAXITER][40];               /* for RAW(40)      */

/* output buffers */
static int   *p1[MAXITER];                     /* for INTEGER      */
static text  *p2[MAXITER];                     /* for CHAR(40)     */
static text  *p3[MAXITER];                     /* for VARCHAR2(40) */
static float *p4[MAXITER];                     /* for FLOAT        */
static int   *p5[MAXITER];                     /* for DECIMAL      */
static float *p6[MAXITER];                     /* for DECIMAL(8,3) */
static int   *p7[MAXITER];                     /* for NUMERIC      */
static float *p8[MAXITER];                     /* for NUMERIC(7,2) */
static ub1   *p9[MAXITER];                     /* for DATE         */
static ub1   *p10[MAXITER];                    /* for RAW(40)      */

static short *ind[MAXCOLS][MAXITER];           /* indicators */
static ub2   *rc[MAXCOLS][MAXITER];            /* return codes */
static ub4   *rl[MAXCOLS][MAXITER];            /* return lengths */


/* skip values for binding TAB1 */ 
static ub4   s1 = (ub4) sizeof(in1[0]);
static ub4   s2 = (ub4) sizeof(in2[0]);
static ub4   s3 = (ub4) sizeof(in3[0]);
static ub4   s4 = (ub4) sizeof(in4[0]);
static ub4   s5 = (ub4) sizeof(in5[0]);
static ub4   s6 = (ub4) sizeof(in6[0]);
static ub4   s7 = (ub4) sizeof(in7[0]);
static ub4   s8 = (ub4) sizeof(in8[0]);
static ub4   s9 = (ub4) sizeof(in9[0]);
static ub4   s10= (ub4) sizeof(in10[0]);

/* Rows returned in each iteration */
static ub2 rowsret[MAXITER];

/*  indicator skips */
static ub4   indsk[MAXCOLS] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
/*  return length skips */
static ub4   rlsk[MAXCOLS] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };
/*  return code skips */
static ub4   rcsk[MAXCOLS] = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 };

static int   lowc1[MAXITER], highc1[MAXITER];

static ub4   pos[MAXCOLS];

static OCIError *errhp;

/*------------------------end of Global variables--------------------*/

/*========================== UTILITY FUNCTIONS ======================*/
/* 
 * These functions are generic functions that can be used in any 
 * OCI program.
 */

/* ----------------------------------------------------------------- */
/* Initialize environment, allocate handles                          */ 
/* ----------------------------------------------------------------- */
sword init_handles(envhp, svchp, errhp, srvhp, authp, init_mode)
OCIEnv **envhp; 
OCISvcCtx **svchp; 
OCIError **errhp; 
OCIServer **srvhp; 
OCISession **authp; 
ub4 init_mode;
{
  (void) printf("Environment setup ....\n");

  /* Initialize the OCI Process */
  if (OCIInitialize(init_mode, (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;
  }

  /* Inititialize the OCI Environment */
  if (OCIEnvInit((OCIEnv **) envhp, (ub4) OCI_DEFAULT,
                 (size_t) 0, (dvoid **) 0 ))
  {
    (void) printf("FAILED: OCIEnvInit()\n");
    return OCI_ERROR;
  }

  /* Allocate a service handle */
  if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) svchp,
                     (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc() on svchp\n");
    return OCI_ERROR;
  }
 
  /* Allocate an error handle */
  if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) errhp,
                     (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc() on errhp\n");
    return OCI_ERROR;
  }
 
  /* Allocate a server handle */
  if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) srvhp,
                     (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc() on srvhp\n");
    return OCI_ERROR;
  }

  /* Allocate a authentication handle */
  if (OCIHandleAlloc((dvoid *) *envhp, (dvoid **) authp,
                     (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc() on authp\n");
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* Attach to server with a given mode.                               */ 
/* ----------------------------------------------------------------- */
sword attach_server(mode, srvhp, errhp, svchp)
ub4 mode; 
OCIServer *srvhp;
OCIError *errhp; 
OCISvcCtx *svchp;
{
  text *cstring = (text *)"";

  if (OCIServerAttach(srvhp, errhp, (text *) cstring,
                     (sb4) strlen((char *)cstring), (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIServerAttach()\n");
    return OCI_ERROR;
  }

  /* Set the server handle in the service handle */
  if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                 (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, errhp))
  {
    (void) printf("FAILED: OCIAttrSet() server attribute\n");
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}
/* ----------------------------------------------------------------- */
/* Logon to the database using given username, password & credentials*/
/* ----------------------------------------------------------------- */
sword log_on(authp, errhp, svchp, uid, pwd, credt, mode)
OCISession *authp; 
OCIError *errhp; 
OCISvcCtx *svchp; 
text *uid; 
text *pwd; 
ub4 credt; 
ub4 mode;
{
  /* Set attributes in the authentication handle */
  if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) uid, (ub4) strlen((char *) uid),
                 (ub4) OCI_ATTR_USERNAME, errhp))
  {
    (void) printf("FAILED: OCIAttrSet() userid\n");
    return OCI_ERROR;
  }
  if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) pwd, (ub4) strlen((char *) pwd),
                 (ub4) OCI_ATTR_PASSWORD, errhp))
  {
    (void) printf("FAILED: OCIAttrSet() passwd\n");
    return OCI_ERROR;
  }

  (void) printf("Logging on as %s  ....\n", uid);

  if (OCISessionBegin(svchp, errhp, authp, credt, mode))
  {
    (void) printf("FAILED: OCIAttrSet() passwd\n");
    return OCI_ERROR;
  }

  (void) printf("%s logged on.\n", uid);

  /* Set the authentication handle in the Service handle */
  if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                 (dvoid *) authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp))
  {
    (void) printf("FAILED: OCIAttrSet() session\n");
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}

/*---------------------------------------------------------------------*/
/* Allocate all required bind handles                                  */
/*---------------------------------------------------------------------*/

sword init_bind_handle(stmthp, bndhp, nbinds)
OCIStmt *stmthp; 
OCIBind *bndhp[]; 
int  nbinds;
{
  int  i;
  /* 
   * This function init the specified number of bind handles 
   * from the given statement handle.
   */
  for (i = 0; i < nbinds; i++)
    bndhp[i] = (OCIBind *) 0;

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* Print the returned raw data.                                      */
/* ----------------------------------------------------------------- */
void print_raw(raw, rawlen)
ub1 *raw; 
ub4 rawlen;
{
  ub4 i;
  ub4 lim;
  ub4 clen = 0;
 
  if (rawlen > 120)
  {
    ub4 llen = rawlen;
 
    while (llen > 120)
    {
      lim = clen + 120;
      for(i = clen; i < lim; ++i)
          (void) printf("%02.2x", (ub4) raw[i] & 0xFF);
 
      (void) printf("\n");
      llen -= 120;
      clen += 120;
    }
    lim = clen + llen;
  }
  else
    lim = rawlen;
 
  for(i = clen; i < lim; ++i)
    (void) printf("%02.2x", (ub4) raw[i] & 0xFF);
 
  (void) printf("\n");
 
  return;
}

/* ----------------------------------------------------------------- */
/*  Free the specified handles                                       */
/* ----------------------------------------------------------------- */
void free_handles(envhp, svchp, srvhp, errhp, authp, stmthp)
OCIEnv *envhp; 
OCISvcCtx *svchp; 
OCIServer *srvhp; 
OCIError *errhp; 
OCISession *authp; 
OCIStmt *stmthp;
{
  (void) printf("Freeing handles ...\n");
 
  if (srvhp)
    (void) OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER);
  if (svchp)
    (void) OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX);
  if (errhp)
    (void) OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR);
  if (authp)
    (void) OCIHandleFree((dvoid *) authp, (ub4) OCI_HTYPE_SESSION);
  if (stmthp)
    (void) OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT);
  if (envhp)
    (void) OCIHandleFree((dvoid *) envhp, (ub4) OCI_HTYPE_ENV);

  return;
}

/* ----------------------------------------------------------------- */
/* Print the error message                                           */
/* ----------------------------------------------------------------- */
void report_error(errhp)
OCIError *errhp;
{
  text  msgbuf[512];
  sb4   errcode = 0;
 
  (void) OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                       msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
  (void) printf("ERROR CODE = %d\n", errcode);
  (void) printf("%.*s\n", 512, msgbuf);
  return;
}

/*-------------------------------------------------------------------*/ 
/* Logout and detach from the server                                 */
/*-------------------------------------------------------------------*/ 
void logout_detach_server(svchp, srvhp, errhp, authp, userid)
OCISvcCtx *svchp; 
OCIServer *srvhp; 
OCIError *errhp;
OCISession *authp; 
text *userid;
{
  if (OCISessionEnd(svchp, errhp, authp, (ub4) 0))
  {
    (void) printf("FAILED: OCISessionEnd()\n");
    report_error(errhp);
  }

  (void) printf("%s Logged off.\n", userid);

  if (OCIServerDetach(srvhp, errhp, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCISessionEnd()\n");
    report_error(errhp);
  }
 
  (void) printf("Detached from server.\n");

  return;
}

/*---------------------------------------------------------------------*/
/* Finish demo and clean up                                            */
/*---------------------------------------------------------------------*/
sword finish_demo(loggedon, envhp, svchp, srvhp, errhp, authp, stmthp, userid)
boolean loggedon; 
OCIEnv *envhp; 
OCISvcCtx *svchp; 
OCIServer *srvhp; 
OCIError *errhp; 
OCISession *authp; 
OCIStmt *stmthp; 
text *userid;
{

  if (loggedon)
    logout_detach_server(svchp, srvhp, errhp, authp, userid);

  free_handles(envhp, svchp, srvhp, errhp, authp, stmthp);

  return OCI_SUCCESS;
}

/*===================== END OF UTILITY FUNCTIONS ======================*/

/*========================= MAIN ======================================*/
int main(argc, argv)
int argc; 
char *argv[];
{
  text *username = (text *)"scott";
  text *password = (text *)"tiger";

  OCIEnv *envhp;
  OCIServer *srvhp;
  OCISvcCtx *svchp;
  OCISession *authp;
  OCIStmt *stmthp;
  OCIBind *bndhp[MAXBINDS];
  int i;


  /* Initialize the Environment and allocate handles */
  if (init_handles(&envhp, &svchp, &errhp, &srvhp, &authp, (ub4)OCI_DEFAULT))
  {
    (void) printf("FAILED: init_handles()\n");
    return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp, 
                       stmthp, username);
  }

  /* Attach to the database server */
  if (attach_server((ub4) OCI_DEFAULT, srvhp, errhp, svchp))
  {
    (void) printf("FAILED: attach_server()\n");
    return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp, 
                       stmthp, username);
  }

  /* Logon to the server and begin a session */
  if (log_on(authp, errhp, svchp, username, password, 
             (ub4) OCI_CRED_RDBMS, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: log_on()\n");
    return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp, 
                       stmthp, username);
  }
  logged_on = TRUE;

  /* Allocate a statement handle */
  if (OCIHandleAlloc((dvoid *)envhp, (dvoid **) &stmthp,
                     (ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: alloc statement handle\n");
    return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp, 
                       stmthp, username);
  }

  /* bind handles will be implicitly allocated in the bind calls */
  /* need to initialize them to null prior to first usage in bind calls */

  for (i = 0; i < MAXBINDS; i++)
    bndhp[i] = (OCIBind *) 0;

  /* Demonstrate INSERT with RETURNING clause */
  if (demo_insert(svchp, stmthp, bndhp, errhp))
    (void) printf("FAILED: demo_insert()\n");
  else
    (void) printf("SUCCESS: demo_insert()\n");

  /* Demonstrate UPDATE with RETURNING clause */
  if (demo_update(svchp, stmthp, bndhp, errhp))
    (void) printf("FAILED: demo_update()\n");
  else
    (void) printf("SUCCESS: demo_update()\n");

  /* Demonstrate DELETE with RETURNING clause */
  if (demo_delete(svchp, stmthp, bndhp, errhp))
    (void) printf("FAILED: demo_delete()\n");
  else
    (void) printf("SUCCESS: demo_delete()\n");

  /* clean up */
  return finish_demo(logged_on, envhp, svchp, srvhp, errhp, authp, 
                     stmthp, username);
}

/* =================== End Main =====================================*/

/* ===================== Local Functions ============================*/ 
/* ----------------------------------------------------------------- */
/* bind all the columns of TAB1 by positions.                        */
/* ----------------------------------------------------------------- */
static sword bind_pos(OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp) 
{

  if (OCIBindByPos(stmthp, &bndhp[0], errhp, (ub4) 1,
                      (dvoid *) &in1[0], (sb4) sizeof(in1[0]), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT) 
   || OCIBindByPos(stmthp, &bndhp[1], errhp, (ub4) 2,
                      (dvoid *) in2[0], (sb4) sizeof(in2[0]), SQLT_AFC,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByPos(stmthp, &bndhp[2], errhp, (ub4) 3,
                      (dvoid *) in3[0], (sb4) sizeof(in3[0]), SQLT_CHR,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByPos(stmthp, &bndhp[3], errhp, (ub4) 4,
                      (dvoid *) &in4[0], (sb4) sizeof(in4[0]), SQLT_FLT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByPos(stmthp, &bndhp[4], errhp, (ub4) 5,
                      (dvoid *) &in5[0], (sb4) sizeof(in5[0]), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByPos(stmthp, &bndhp[5], errhp, (ub4) 6,
                      (dvoid *) &in6[0], (sb4) sizeof(in6[0]), SQLT_FLT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByPos(stmthp, &bndhp[6], errhp, (ub4) 7,
                      (dvoid *) &in7[0], (sb4) sizeof(in7[0]), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByPos(stmthp, &bndhp[7], errhp, (ub4) 8,
                      (dvoid *) &in8[0], (sb4) sizeof(in8[0]), SQLT_FLT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByPos(stmthp, &bndhp[8], errhp, (ub4) 9,
                      (dvoid *) in9[0], (sb4) sizeof(in9[0]), SQLT_DAT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByPos(stmthp, &bndhp[9], errhp, (ub4) 10,
                      (dvoid *) in10[0], (sb4) sizeof(in10[0]), SQLT_BIN,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIBindByPos()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* bind all the columns of TAB1 by name.                             */
/* ----------------------------------------------------------------- */
static sword bind_name(OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp) 
{

  if (OCIBindByName(stmthp, &bndhp[10], errhp, 
                      (text *) ":out1", (sb4) strlen((char *) ":out1"),
                      (dvoid *) 0, (sb4) sizeof(int), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC) 
   || OCIBindByName(stmthp, &bndhp[11], errhp, 
                      (text *) ":out2", (sb4) strlen((char *) ":out2"),
                      (dvoid *) 0, (sb4) MAXCOLLEN, SQLT_AFC,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByName(stmthp, &bndhp[12], errhp,
                      (text *) ":out3", (sb4) strlen((char *) ":out3"),
                      (dvoid *) 0, (sb4) MAXCOLLEN, SQLT_CHR,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByName(stmthp, &bndhp[13], errhp,
                      (text *) ":out4", (sb4) strlen((char *) ":out4"),
                      (dvoid *) 0, (sb4) sizeof(float), SQLT_FLT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByName(stmthp, &bndhp[14], errhp, 
                      (text *) ":out5", (sb4) strlen((char *) ":out5"),
                      (dvoid *) 0, (sb4) sizeof(int), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByName(stmthp, &bndhp[15], errhp,
                      (text *) ":out6", (sb4) strlen((char *) ":out6"),
                      (dvoid *) 0, (sb4) sizeof(float), SQLT_FLT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByName(stmthp, &bndhp[16], errhp,
                      (text *) ":out7", (sb4) strlen((char *) ":out7"),
                      (dvoid *) 0, (sb4) sizeof(int), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByName(stmthp, &bndhp[17], errhp,
                      (text *) ":out8", (sb4) strlen((char *) ":out8"),
                      (dvoid *) 0, (sb4) sizeof(float), SQLT_FLT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByName(stmthp, &bndhp[18], errhp,
                      (text *) ":out9", (sb4) strlen((char *) ":out9"),
                      (dvoid *) 0, (sb4) DATBUFLEN, SQLT_DAT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
   || OCIBindByName(stmthp, &bndhp[19], errhp,
                      (text *) ":out10", (sb4) strlen((char *) ":out10"),
                      (dvoid *) 0, (sb4) MAXCOLLEN, SQLT_BIN,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC))
  {
    (void) printf("FAILED: OCIBindByName()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}


/* ----------------------------------------------------------------- */
/* bind array structs for TAB1 columns.                              */
/* ----------------------------------------------------------------- */
static sword bind_array(OCIBind *bndhp[], OCIError *errhp)
{
  if (OCIBindArrayOfStruct(bndhp[0], errhp, s1, indsk[0], rlsk[0], rcsk[0])
   || OCIBindArrayOfStruct(bndhp[1], errhp, s2, indsk[1], rlsk[1], rcsk[1])
   || OCIBindArrayOfStruct(bndhp[2], errhp, s3, indsk[2], rlsk[2], rcsk[2])
   || OCIBindArrayOfStruct(bndhp[3], errhp, s4, indsk[3], rlsk[3], rcsk[3])
   || OCIBindArrayOfStruct(bndhp[4], errhp, s5, indsk[4], rlsk[4], rcsk[4])
   || OCIBindArrayOfStruct(bndhp[5], errhp, s6, indsk[5], rlsk[5], rcsk[5])
   || OCIBindArrayOfStruct(bndhp[6], errhp, s7, indsk[6], rlsk[6], rcsk[6])
   || OCIBindArrayOfStruct(bndhp[7], errhp, s8, indsk[7], rlsk[7], rcsk[7])
   || OCIBindArrayOfStruct(bndhp[8], errhp, s9, indsk[8], rlsk[8], rcsk[8])
   || OCIBindArrayOfStruct(bndhp[9], errhp, s10, indsk[9], rlsk[9], rcsk[9]))
  {
    (void) printf("FAILED: OCIBindArrayOfStruct()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}


/* ----------------------------------------------------------------- */
/* bind dynamic for returning TAB1 columns.                          */
/* ----------------------------------------------------------------- */
static sword bind_dynamic(OCIBind *bndhp[], OCIError *errhp)
{
  /* 
   * Note here that both IN & OUT BIND callback functions have to be 
   * provided.  However, since the bind variables in the RETURNING
   * clause are pure OUT Binds the IN callback fuctions (cbf_no_data)
   * is essentially a "do-nothing" function.
   *
   * Also note here that although in this demonstration the IN and OUT
   * callback functions are same, in practice you can have a different
   * callback function for each bind handle.
   */

  ub4 i;

  for (i = 0; i < MAXCOLS; i++)
    pos[i] = i;

  if (OCIBindDynamic(bndhp[10], errhp, (dvoid *) &pos[0], cbf_no_data,
                    (dvoid *) &pos[0], cbf_get_data)
  ||  OCIBindDynamic(bndhp[11], errhp, (dvoid *) &pos[1], cbf_no_data,
                    (dvoid *) &pos[1], cbf_get_data)
  ||  OCIBindDynamic(bndhp[12], errhp, (dvoid *) &pos[2], cbf_no_data,
                    (dvoid *) &pos[2], cbf_get_data)
  ||  OCIBindDynamic(bndhp[13], errhp, (dvoid *) &pos[3], cbf_no_data,
                    (dvoid *) &pos[3], cbf_get_data)
  ||  OCIBindDynamic(bndhp[14], errhp, (dvoid *) &pos[4], cbf_no_data,
                    (dvoid *) &pos[4], cbf_get_data)
  ||  OCIBindDynamic(bndhp[15], errhp, (dvoid *) &pos[5], cbf_no_data,
                    (dvoid *) &pos[5], cbf_get_data)
  ||  OCIBindDynamic(bndhp[16], errhp, (dvoid *) &pos[6], cbf_no_data,
                    (dvoid *) &pos[6], cbf_get_data)
  ||  OCIBindDynamic(bndhp[17], errhp, (dvoid *) &pos[7], cbf_no_data,
                    (dvoid *) &pos[7], cbf_get_data)
  ||  OCIBindDynamic(bndhp[18], errhp, (dvoid *) &pos[8], cbf_no_data,
                    (dvoid *) &pos[8], cbf_get_data)
  ||  OCIBindDynamic(bndhp[19], errhp, (dvoid *) &pos[9], cbf_no_data,
                    (dvoid *) &pos[9], cbf_get_data))
  {
    (void) printf("FAILED: OCIBindDynamic()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* bind input variables.                                             */
/* ----------------------------------------------------------------- */
static sword bind_input(OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp)
{
  /* bind the input data by positions */
  if (bind_pos(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* bind input array attributes*/
  return (bind_array(bndhp, errhp));
}



/* ----------------------------------------------------------------- */
/* bind output variables.                                            */
/* ----------------------------------------------------------------- */
static sword bind_output(OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp)
{

  /* bind the returning bind buffers by names */
  if (bind_name(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* bind the returning bind buffers dynamically */
  return (bind_dynamic(bndhp, errhp));
}

/* ----------------------------------------------------------------- */
/* bind row indicator variables.                                     */
/* ----------------------------------------------------------------- */
static sword bind_low_high(OCIStmt *stmthp, OCIBind *bndhp[], OCIError *errhp)
{
  if (OCIBindByName(stmthp, &bndhp[23], errhp,
                      (text *) ":low", (sb4) strlen((char *) ":low"),
                      (dvoid *) &lowc1[0], (sb4) sizeof(lowc1[0]), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)
   || OCIBindByName(stmthp, &bndhp[24], errhp,
                      (text *) ":high", (sb4) strlen((char *) ":high"),
                      (dvoid *) &highc1[0], (sb4) sizeof(highc1[0]), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIBindByName()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  if (OCIBindArrayOfStruct(bndhp[23], errhp, s1, indsk[0], rlsk[0], rcsk[0])
   || OCIBindArrayOfStruct(bndhp[24], errhp, s1, indsk[0], rlsk[0], rcsk[0]))
  {
    (void) printf("FAILED: OCIBindArrayOfStruct()\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* Demontrate  INSERT with RETURNING clause.                         */
/* ----------------------------------------------------------------- */
static sword demo_insert(OCISvcCtx *svchp, OCIStmt *stmthp,
                            OCIBind *bndhp[], OCIError *errhp)
{
  int   i, j;

  /* 
   * This function inserts values for 10 columns in table TAB1 and 
   * uses the RETURN clause to get back the inserted column values.
   * It inserts  MAXITER (10) such rows.  Thus it expects MAXITER values
   * for each column to be returned.
   */  
  /* The Insert Statement with RETURNING clause */
  text *sqlstmt = (text *)
        "INSERT INTO TAB1 VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10) \
                     RETURNING C1, C2, C3, C4, C5, C6, C7, C8, C9, C10 \
                     INTO :out1, :out2, :out3, :out4, :out5, :out6, \
                          :out7, :out8, :out9, :out10";

  /* Prepare the statement */
  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() insert\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  
  /* Initialise the buffers for update */
  for (i = 0; i < MAXITER; i++)
  {
    in1[i] = i + 1;
    memset((void *)in2[i], (int) 'A'+i%26, (size_t) 40);
    memset((void *)in3[i], (int) 'a'+i%26, (size_t) 40);
    in4[i] = 400.555 + (float) i;
    in5[i] = 500 + i;
    in6[i] = 600.250 + (float) i;
    in7[i] = 700 + i;
    in8[i] = 800.350 + (float) i;
    in9[i][0] = 119;
    in9[i][1] = 185 + (ub1)i%10;
    in9[i][2] = (ub1)i%12 + 1;
    in9[i][3] = (ub1)i%25 + 1;
    in9[i][4] = 0;
    in9[i][5] = 0;
    in9[i][6] = 0;
    for (j = 0; j < 40; j++)
      in10[i][j] = (ub1) (i%0x10);

    rowsret[i] = 0;  
  }

  /* Bind all the input buffers to place holders (:1, :2. :3, etc ) */
  if (bind_input(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* Bind all the output buffers to place holders (:out1, :out2 etc */
  if (bind_output(stmthp, bndhp, errhp))
    return OCI_ERROR;
 
  /* Execute the Insert statement */
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) MAXITER, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() insert\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  /* Commit the changes */
  (void) OCITransCommit(svchp, errhp, (ub4) 0);

  /* Print out the values in the return rows */
  (void) printf("\n\n DEMONSTRATING INSERT....RETURNING \n");
  (void) print_return_data((int)MAXITER);

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* Demonstrate  UPDATE with RETURNING clause.                        */
/* ----------------------------------------------------------------- */
static sword demo_update(OCISvcCtx *svchp, OCIStmt *stmthp, 
                          OCIBind *bndhp[], OCIError *errhp)
{
  int   i, j;
  int   range_size = 3;                                       /* iterations */


  /* 
   * This function updates columns in table TAB1, for certain rows
   * depending on the values of the :low and :high values in 
   * in the WHERE clause. It executes this statement 3 times, (3 iterations)
   * each time with a different set of values for :low and :high
   * Thus for each iteration, multiple rows are returned depending
   * on the number of rows that matched the WHERE clause.
   *
   * The rows it updates here are the rows that were inserted by the
   * cdemodr1.sql script.
   */

  /* The Update Statement with RETURNING clause */
  text *sqlstmt = (text *)
                   "UPDATE TAB1 SET C1 = C1 + :1, C2 = :2, C3 = :3, \
                           C4 = C4 + :4, C5 = C5 + :5, C6 = C6 + :6, \
                           C7 = C7 + :7, C8 = C8 + :8, C9 = :9, C10 = :10 \
                           WHERE C1 >= :low AND C1 <= :high \
                           RETURNING C1, C2, C3, C4, C5, C6, C7, C8, C9, C10 \
                           INTO :out1, :out2, :out3, :out4, :out5, :out6, \
                           :out7, :out8, :out9, :out10";

  /* Prepare the statement */
  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() update\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  /* Initialise the buffers for insertion */
  for (i = 0; i < MAXITER; i++)
  {
    in1[i] = 300 + i;
    memset((void *)in2[i], (int) 'a'+i%26, (size_t) 40);
    memset((void *)in3[i], (int) 'A'+i%26, (size_t) 40);
    in4[i] = 400.555 + (float)i;
    in5[i] = 500 + i;
    in6[i] = 600.280 + (float)i;
    in7[i] = 700 + i;
    in8[i] = 800.620 + (float)i;
    in9[i][0] = 119;
    in9[i][1] = 185 - (ub1)i%10;
    in9[i][2] = (ub1)i%12 + 1;
    in9[i][3] = (ub1)i%25 + 1;
    in9[i][4] = 0;
    in9[i][5] = 0;
    in9[i][6] = 0;
    for (j = 0; j < 40; j++)
      in10[i][j] = (ub1) (i%0x08); 

    rowsret[i] =0;
  }

  /* Bind all the input buffers to place holders (:1, :2. :3, etc ) */
  if (bind_input(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* Bind all the output buffers to place holders (:out1, :out2 etc */
  if (bind_output(stmthp, bndhp, errhp))
    return OCI_ERROR;
 
  /* bind row indicator low, high */
  if (bind_low_high(stmthp, bndhp, errhp))
    return OCI_ERROR;
 
  /* update rows 
         between 101 and 103;  --  expecting 3 rows returned (update 3 rows)
         between 105 and 106;  --  expecting 2 rows returned (update 2 rows)
         between 109 and 113;  --  expecting 5 rows returned (update 5 rows)
  */
  lowc1[0] = 101;
  highc1[0] = 103;

  lowc1[1] = 105;
  highc1[1] = 106;

  lowc1[2] = 109;
  highc1[2] = 113;

  (void) printf("\n\n DEMONSTRATING UPDATE....RETURNING \n");
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) range_size, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() update\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  /* Commit the changes */
  (void) OCITransCommit(svchp, errhp, (ub4) 0);

  /* Print out the values in the return rows */
  (void) print_return_data(range_size);

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* Demonstrate  DELETE with RETURNING clause.                        */
/* ----------------------------------------------------------------- */
static sword demo_delete(OCISvcCtx *svchp, OCIStmt *stmthp,
                          OCIBind *bndhp[], OCIError *errhp)
{
  int   i, range_size = 3;                                    /* iterations */
  sword  retval;

  /*
   * This function deletes certain rows from table TAB1
   * depending on the values of the :low and :high values in
   * the WHERE clause. It executes this statement 3 times, (3 iterations)
   * each time with a different set of values for :low and :high
   * Thus for each iteration, multiples rows are returned depending
   * on the number of rows that matched the WHERE clause.
   *
   * The rows it deletes here are the rows that were inserted by the
   * cdemodr1.sql script.
   */

  /* The Delete Statement with RETURNING clause */
  text *sqlstmt = (text *)
          "DELETE FROM TAB1 WHERE C1 >= :low AND C1 <= :high \
                     RETURNING C1, C2, C3, C4, C5, C6, C7, C8, C9, C10 \
                     INTO :out1, :out2, :out3, :out4, :out5, :out6, \
                          :out7, :out8, :out9, :out10";

  /* Prepare the statement */
  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() delete\n");
    report_error(errhp);
    return OCI_ERROR;
  }

  /* Bind all the output buffers to place holders (:out1, :out2 etc */
  if (bind_output(stmthp, bndhp, errhp))
    return OCI_ERROR;
 
  /* bind row indicator low, high */
  if (bind_low_high(stmthp, bndhp, errhp))
    return OCI_ERROR;

  /* delete rows 
         between 201 and 203;  --  expecting 3 rows returned (3 rows deleted)
         between 205 and 209;  --  expecting 5 rows returned (2 rows deleted)
         between 211 and 213;  --  expecting 3 rows returned (5 rows deleted)
  */
  lowc1[0] = 201;
  highc1[0] = 203;

  lowc1[1] = 205;
  highc1[1] = 209;

  lowc1[2] = 211;
  highc1[2] = 213;


  for (i=0; i<MAXITER; i++)
    rowsret[i] = 0;

  (void) printf("\n\n Demonstrating DETELE....RETURNING \n");
  if ((retval = OCIStmtExecute(svchp, stmthp, errhp, (ub4) range_size, (ub4) 0,
                             (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                             (ub4) OCI_DEFAULT)) != OCI_SUCCESS  &&
                              retval != OCI_SUCCESS_WITH_INFO)
  {
    (void) printf("FAILED: OCIStmtExecute() delete, retval = %d\n", retval);
    report_error(errhp);
  }

  /* Commit the changes */
  (void) OCITransCommit(svchp, errhp, (ub4) 0);

  /* Print out the values in the return rows */
  (void) print_return_data(range_size);

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* IN bind callback that does not do any data input.                 */
/* ----------------------------------------------------------------- */
static sb4 cbf_no_data(dvoid *ctxp, OCIBind *bindp, ub4 iter, ub4 index,
                      dvoid **bufpp, ub4 *alenpp, ub1 *piecep, dvoid **indpp)
{
  /* 
   * This is a dummy input callback function that provides input data
   * for the bind variables in the RETURNING clause.
   */
  *bufpp = (dvoid *) 0;
  *alenpp = 0;
  *indpp = (dvoid *) 0;
  *piecep = OCI_ONE_PIECE;

  return OCI_CONTINUE;
}

/* ----------------------------------------------------------------- */
/* Outbind callback for returning data.                              */
/* ----------------------------------------------------------------- */
static sb4 cbf_get_data(dvoid *ctxp, OCIBind *bindp, ub4 iter, ub4 index,
                         dvoid **bufpp, ub4 **alenp, ub1 *piecep,
                         dvoid **indpp, ub2 **rcodepp)
{
  /* 
   * This is the callback function that is called to receive the OUT
   * bind values for the bind variables in the RETURNING clause 
   */

  static ub4  rows = 0;
  ub4    pos = *((ub4 *)ctxp);

  /* For each iteration the OCI_ATTR_ROWS_RETURNED tells us the number
   * of rows returned in that iteration.  So we can use this information
   * to dynamically allocate storage for all the returned rows for that
   * bind.
   */
  if (index == 0)
  {
    (void) OCIAttrGet((CONST dvoid *)bindp, OCI_HTYPE_BIND, (dvoid *)&rows, 
                       (ub4 *) sizeof(ub4), OCI_ATTR_ROWS_RETURNED, errhp);
    rowsret[iter] = (ub2)rows;

    /* Dynamically allocate storage */
    if (alloc_buffer(pos, iter, rows))
      return OCI_ERROR;
  }

  /* Provide the address of the storage where the data is to be returned */
  switch(pos)
  {
  case 0:
    rl[pos][iter][index] = sizeof(int);
    *bufpp =  (dvoid *) (p1[iter]+ index);
    break;
  case 1:
    rl[pos][iter][index] = (ub4) MAXCOLLEN;
    *bufpp =  (dvoid *) (p2[iter]+(index * MAXCOLLEN));
    break;
  case 2:
    rl[pos][iter][index] = (ub4) MAXCOLLEN;
    *bufpp =  (dvoid *) (p3[iter]+(index * MAXCOLLEN));
    break;
  case 3:
    rl[pos][iter][index] = sizeof(float);
    *bufpp =  (dvoid *) (p4[iter]+ index);
    break;
  case 4:
    rl[pos][iter][index] = sizeof(int);
    *bufpp =  (dvoid *) (p5[iter]+index);
    break;
  case 5:
    rl[pos][iter][index] = sizeof(float);
    *bufpp =  (dvoid *) (p6[iter]+index );
    break;
  case 6:
    rl[pos][iter][index] = sizeof(int);
    *bufpp =  (dvoid *) (p7[iter]+ index);
    break;
  case 7:
    rl[pos][iter][index] = sizeof(float);
    *bufpp =  (dvoid *) (p8[iter]+index);
    break;
  case 8:
    rl[pos][iter][index] = DATBUFLEN;
    *bufpp =  (dvoid *) (p9[iter]+(index * DATBUFLEN));
    break;
  case 9:
    rl[pos][iter][index] = (ub4) MAXCOLLEN;
    *bufpp =  (dvoid *) (p10[iter]+(index * MAXCOLLEN));
    break;
  default:
    *bufpp =  (dvoid *) 0;
    *alenp =  (ub4 *) 0;
    (void) printf("ERROR: invalid position number: %d\n", *((ub2 *)ctxp));
  }

  *piecep = OCI_ONE_PIECE;

  /* provide address of the storage where the indicator will be returned */
  ind[pos][iter][index] = 0; 
  *indpp = (dvoid *) &ind[pos][iter][index];


  /* provide address of the storage where the return code  will be returned */
  rc[pos][iter][index] = 0; 
  *rcodepp = &rc[pos][iter][index];

  /* 
   * provide address of the storage where the actual length  will be 
   * returned 
   */
  *alenp = &rl[pos][iter][index];

  return OCI_CONTINUE;
}

/* ----------------------------------------------------------------- */
/* allocate buffers for callback.                                    */
/* ----------------------------------------------------------------- */
static sword alloc_buffer(ub4 pos, ub4 iter, ub4 rows)
{
  switch(pos)
  {
  case 0:
    p1[iter] = (int *) malloc(sizeof(int) * rows);
    break;
  case 1:
    p2[iter] = (text *) malloc(rows * MAXCOLLEN);
    break;
  case 2:
    p3[iter] = (text *) malloc(rows * MAXCOLLEN);
    break;
  case 3:
    p4[iter] = (float *) malloc(sizeof(float) * rows);
    break;
  case 4:
    p5[iter] = (int *) malloc(sizeof(int) * rows);
    break;
  case 5:
    p6[iter] = (float *) malloc(sizeof(float) * rows);
    break;
  case 6:
    p7[iter] = (int *) malloc(sizeof(int) * rows);
    break;
  case 7:
    p8[iter] = (float *) malloc(sizeof(float) * rows);
    break;
  case 8:
    p9[iter] = (ub1 *) malloc(rows * DATBUFLEN);
    break;
  case 9:
    p10[iter] = (ub1 *) malloc(rows * MAXCOLLEN);
    break;
  default:
    (void) printf("ERROR: invalid position number: %d\n", pos);
    return OCI_ERROR;
  }

  ind[pos][iter] = (short *) malloc(rows * sizeof(short));
  rc[pos][iter] = (ub2 *) malloc(rows * sizeof(ub2));
  rl[pos][iter] = (ub4 *) malloc(rows * sizeof(ub4));

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* print the returned data.                                          */
/* ----------------------------------------------------------------- */
static sword print_return_data(iters)
int iters;
{
  int i, j;

  for (i = 0; i < iters; i++)
  {
    (void) printf("\n*** ITERATION *** : %d\n", i); 
    (void) printf("(...returning %d rows)\n",  rowsret[i]);

    for (j = 0; j < rowsret[i]  ; j++)
    {
      /* Column 1 */
      (void) printf("COL1 [%d]: ind = %d, rc = %d, retl = %d\n", 
                            j, ind[0][i][j], rc[0][i][j], rl[0][i][j]); 
      if (ind[0][i][j] == -1)
        (void) printf("COL1 [%d]: NULL\n", j); 
      else
        (void) printf("COL1 [%d]: %d\n", j, *(p1[i]+j) ); 

      /* Column 2 */
      (void) printf("COL2 [%d]: ind = %d, rc = %d, retl = %d\n", 
                            j, ind[1][i][j], rc[1][i][j], rl[1][i][j]); 
      if (ind[1][i][j] == -1)
        (void) printf("COL2 [%d]: NULL\n", j); 
      else
        (void) printf("COL2 [%d]: %.*s\n", j, rl[1][i][j],p2[i]+(j*MAXCOLLEN) ); 

      /* Column 3 */
      (void) printf("COL3 [%d]: ind = %d, rc = %d, retl = %d\n", 
                            j, ind[2][i][j], rc[2][i][j], rl[2][i][j]); 
      if (ind[2][i][j] == -1)
        (void) printf("COL3 [%d]: NULL\n", j); 
      else
        (void) printf("COL3 [%d]: %.*s\n", j, rl[2][i][j],p3[i]+(j*MAXCOLLEN) ); 
      /* Column 4 */
      (void) printf("COL4 [%d]: ind = %d, rc = %d, retl = %d\n", 
                            j, ind[3][i][j], rc[3][i][j], rl[3][i][j]); 
      if (ind[3][i][j] == -1)
        (void) printf("COL4 [%d]: NULL\n", j); 
      else
        (void) printf("COL4 [%d]: %8.3f\n", j, *(p4[i]+j) ); 

      /* Column 5 */
      (void) printf("COL5 [%d]: ind = %d, rc = %d, retl = %d\n", 
                            j, ind[4][i][j], rc[4][i][j], rl[4][i][j]); 
      if (ind[4][i][j] == -1)
        (void) printf("COL5 [%d]: NULL\n", j); 
      else
        (void) printf("COL5 [%d]: %d\n", j, *(p5[i]+j) ); 

      /* Column 6 */
      (void) printf("COL6 [%d]: ind = %d, rc = %d, retl = %d\n", 
                            j, ind[5][i][j], rc[5][i][j], rl[5][i][j]); 
      if (ind[5][i][j] == -1)
        (void) printf("COL6 [%d]: NULL\n", j); 
      else
        (void) printf("COL6 [%d]: %8.3f\n", j, *(p6[i]+j) ); 

      /* Column 7 */
      (void) printf("COL7 [%d]: ind = %d, rc = %d, retl = %d\n", 
                            j, ind[6][i][j], rc[6][i][j], rl[6][i][j]); 
      if (ind[6][i][j] == -1)
        (void) printf("COL7 [%d]: NULL\n", j); 
      else
        (void) printf("COL7 [%d]: %d\n", j, *(p7[i]+j) ); 

      /* Column 8 */
      (void) printf("COL8 [%d]: ind = %d, rc = %d, retl = %d\n", 
                            j, ind[7][i][j], rc[7][i][j], rl[7][i][j]); 
      if (ind[7][i][j] == -1)
        (void) printf("COL8 [%d]: NULL\n", j); 
      else
        (void) printf("COL8 [%d]: %8.3f\n", j, *(p8[i]+j) ); 

      /* Column 9 */
      (void) printf("COL9 [%d]: ind = %d, rc = %d, retl = %d\n", 
                            j, ind[8][i][j], rc[8][i][j], rl[8][i][j]); 
      if (ind[8][i][j] == -1)
        (void) printf("COL9 [%d]: NULL\n", j); 
      else
        (void) printf("COL9 [%d]: %u-%u-%u%u\n", j, 
                                     *(p9[i]+(j*DATBUFLEN+3)),
                                     *(p9[i]+(j*DATBUFLEN+2)),
                                     *(p9[i]+(j*DATBUFLEN+0)) - 100,
                                     *(p9[i]+(j*DATBUFLEN+1)) - 100 );

      /* Column 10 */
      (void) printf("COL10 [%d]: ind = %d, rc = %d, retl = %d\n", 
                            j, ind[9][i][j], rc[9][i][j], rl[9][i][j]); 
      if (ind[9][i][j] == -1)
        (void) printf("COL10 [%d]: NULL\n", j); 
      else
      {
        (void) printf("COL10 [%d]: ", j); 
        print_raw(p10[i]+(j*MAXCOLLEN), rl[9][i][j]);
      }
      (void) printf("\n");
    }
  }

  return OCI_SUCCESS;
}

cdemodr1.h


/*----------------------------------------------------------
 * Include Files
 */
#include <stdio.h>
#include <string.h>
#include <oci.h>

/*------------------------------------------------------------------
 * Define Constants
 */

#define MAXBINDS       25 
#define MAXROWS         5           /* max no of rows returned per iter */ 
#define MAXCOLS        10
#define MAXITER        10           /* max no of iters in execute */
#define MAXCOLLEN      40           /* if changed, update cdemodr1.sql */
#define DATBUFLEN       7

int main(/*_ int argc, char *argv[] _*/);
static sword init_handles(/*_ OCIEnv **envhp, OCISvcCtx **svchp, 
                               OCIError **errhp, OCIServer **svrhp, 
                               OCISession **authp, ub4 mode _*/);

static sword attach_server(/*_ ub4 mode, OCIServer *srvhp,
                        OCIError *errhp, OCISvcCtx *svchp _*/);
static sword log_on(/*_ OCISession *authp, OCIError *errhp, OCISvcCtx *svchp,
                 text *uid, text *pwd, ub4 credt, ub4 mode _*/);
static sword alloc_bind_handle(/*_ OCIStmt *stmthp, OCIBind *bndhp[],
                               int nbinds _*/);
static void print_raw(/*_ ub1 *raw, ub4 rawlen _*/);

static void free_handles(/*_ OCIEnv *envhp, OCISvcCtx *svchp, OCIServer      
         *srvhp, OCIError *errhp, OCISession *authp, OCIStmt *stmthp _*/);
void report_error(/*_ OCIError *errhp _*/);
void logout_detach_server(/*_ OCISvcCtx *svchp, OCIServer *srvhp,
                              OCIError *errhp, OCISession *authp,
                              text *userid _*/);
sword finish_demo(/*_ boolean loggedon, OCIEnv *envhp, OCISvcCtx *svchp,
                      OCIServer *srvhp, OCIError *errhp, OCISession *authp,
                      OCIStmt *stmthp, text *userid _*/);
static sword demo_insert(/*_ OCISvcCtx *svchp, OCIStmt *stmthp, 
                              OCIBind *bndhp[], OCIError *errhp _*/);
static sword demo_update(/*_ OCISvcCtx *svchp, OCIStmt *stmthp, 
                              OCIBind *bndhp[], OCIError *errhp _*/);
static sword demo_delete(/*_ OCISvcCtx *svchp, OCIStmt *stmthp, 
                              OCIBind *bndhp[], OCIError *errhp _*/);
static sword bind_name(/*_ OCIStmt *stmthp, OCIBind *bndhp[], 
                            OCIError *errhp _*/);
static sword bind_pos(/*_ OCIStmt *stmthp, OCIBind *bndhp[], 
                           OCIError *errhp _*/);
static sword bind_input(/*_ OCIStmt *stmthp, OCIBind *bndhp[], 
                             OCIError *errhp _*/);
static sword bind_output(/*_ OCIStmt *stmthp, OCIBind *bndhp[], 
                              OCIError *errhp _*/);
static sword bind_array(/*_ OCIBind *bndhp[], OCIError *errhp _*/);
static sword bind_dynamic(/*_ OCIBind *bndhp[], OCIError *errhp _*/);
static sb4 cbf_no_data(/*_ dvoid *ctxp, OCIBind *bindp, ub4 iter, ub4 index,
                 dvoid **bufpp, ub4 *alenpp, ub1 *piecep, dvoid **indpp _*/);
static sb4 cbf_get_data(/*_ dvoid *ctxp, OCIBind *bindp, ub4 iter, ub4 index,
                             dvoid **bufpp, ub4 **alenpp, ub1 *piecep,
                             dvoid **indpp, ub2 **rcodepp _*/);
static sword alloc_buffer(/*_ ub4 pos, ub4 iter, ub4 rows _*/);
static sword print_return_data(/*_ int iter _*/);

Example 4, Describing an Object

/*
   NAME
     cdemodsc.c

   DESCRIPTION
     Tests OCIDescribeAny() on an object.

     cdemodsc takes the user name and password and a type name
     (created in the database) as command line arguments and 
     dumps all the information about the type -- 
     its attribute types, methods, 
     method parameters, etc.

*/

#ifndef CDEMODSC_ORACLE
#include "cdemodsc.h"
#endif

/*******************************************************************/ 
static void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512]; 
  sb4 errcode; 

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    break;
  case OCI_NEED_DATA:
    break;
  case OCI_NO_DATA:
    break;
  case OCI_ERROR:
    DISCARD OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                    errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
    DISCARD printf("Error - %s\n", errbuf);
    exit(1);
    break;
  case OCI_INVALID_HANDLE:
    break;
  case OCI_STILL_EXECUTING:
    break;
  case OCI_CONTINUE:
    break;
  default:
    break;
  }
}

/*----------------------------------------------------------------*/

static void chk_methodlst(envhp, errhp, svchp, parmp, count, comment)
OCIEnv *envhp;
OCIError *errhp;
OCISvcCtx *svchp;
dvoid   *parmp;
ub4      count;
const text    *comment;
{
  sword  retval;
  ub4    pos;
  dvoid *parmdp;

  for (pos = 1; pos <= count; pos++)
  {
    checkerr(errhp, OCIParamGet((dvoid *)parmp, (ub4) OCI_DTYPE_PARAM, errhp,
                       (dvoid *)&parmdp, (ub4) pos));
    chk_method(envhp, errhp, svchp, parmdp, comment);
  }
}

/*----------------------------------------------------------------*/

static void chk_method(envhp, errhp, svchp, parmp, comment)
OCIEnv *envhp;
OCIError *errhp;
OCISvcCtx *svchp;
dvoid   *parmp;
const text    *comment;
{
  sword  retval;
  text   method[MAXNAME],
        *namep;
  ub4    size;
  ub4    num_arg;
  ub1    has_result,
         is_selfish,
         is_virtual,
         is_inline,
         is_constructor,
         is_destructor,
         is_constant,
         is_operator,
         is_map,
         is_order,
         is_rnds,
         is_rnps,
         is_wnds,
         is_wnps;
  OCITypeEncap encap;
  dvoid *list_arg;

  /* get name of the method */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &namep, (ub4 *) &size,
                    (ub4) OCI_ATTR_NAME, (OCIError *) errhp));

  (void) strncpy((char *)method, (char *)namep, (size_t) size);
  method[size] = '\0';

  /* get the number of arguments */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &num_arg, (ub4 *) 0,
                    (ub4) OCI_ATTR_NUM_ARGS, (OCIError *) errhp));

  /* encapsulation (public?) */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &encap, (ub4 *) 0,
                    (ub4) OCI_ATTR_ENCAPSULATION, (OCIError *) errhp));

  /* has result */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&has_result, (ub4 *)0,
                    (ub4)OCI_ATTR_HAS_RESULT, (OCIError *) errhp));

  /* map method */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, 
                    (dvoid *)&is_map, (ub4 *)0, 
                    (ub4)OCI_ATTR_IS_MAP, (OCIError *) errhp));

  /* order method */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&is_order, (ub4 *)0,
                    (ub4)OCI_ATTR_IS_ORDER, (OCIError *) errhp));

  /* selfish method */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&is_selfish, (ub4 *)0,
                    (ub4)OCI_ATTR_IS_SELFISH, (OCIError *) errhp));

  /* virtual method */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&is_virtual, (ub4 *)0,
                    (ub4)OCI_ATTR_IS_VIRTUAL, (OCIError *) errhp));

  /* inline method */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&is_inline, (ub4 *)0,
                    (ub4)OCI_ATTR_IS_INLINE, (OCIError *) errhp));

  /* constant method */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&is_constant, (ub4 *)0,
                    (ub4)OCI_ATTR_IS_CONSTANT, (OCIError *) errhp));

  /* operator */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&is_operator, (ub4 *)0,
                    (ub4)OCI_ATTR_IS_OPERATOR, (OCIError *) errhp));

  /* constructor method */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&is_constructor, (ub4 *)0,
                    (ub4)OCI_ATTR_IS_CONSTRUCTOR, (OCIError *) errhp));

  /* destructor method */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&is_destructor, (ub4 *)0,
                    (ub4)OCI_ATTR_IS_DESTRUCTOR, (OCIError *) errhp));

  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, 
                    (dvoid *)&is_rnds, (ub4 *)0, 
                    (ub4)OCI_ATTR_IS_RNDS, (OCIError *) errhp));
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, 
                    (dvoid *)&is_rnps, (ub4 *)0, 
                    (ub4)OCI_ATTR_IS_RNPS, (OCIError *) errhp));
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, 
                    (dvoid *)&is_wnds, (ub4 *)0, 
                    (ub4)OCI_ATTR_IS_WNDS, (OCIError *) errhp));
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, 
                    (dvoid *)&is_wnps, (ub4 *)0, 
                    (ub4)OCI_ATTR_IS_WNPS, (OCIError *) errhp));
  
  /* get list of arguments */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&list_arg, (ub4 *)0,
                    (ub4)OCI_ATTR_LIST_ARGUMENTS, (OCIError *) errhp));

  SPACING;
  printf ( "\n%s\n", comment);
  SPACING; 
  printf ( "Name:              %s\n", method);
  SPACING; 
  printf ( "Number of args:    %d\n", num_arg);
  SPACING; 
  printf ( "Encapsulation:     %s\n",
           (encap==OCI_TYPEENCAP_PUBLIC) ? "public" : "private");
  SPACING; 
  printf ( "Has result:        %d\n", has_result);
  SPACING; 
  printf ( "Is selfish:        %d\n", is_selfish);
  SPACING; 
  printf ( "Is virtual:        %d\n", is_virtual);
  SPACING; 
  printf ( "Is inline:         %d\n", is_inline);
  SPACING; 
  printf ( "Is constructor:    %d\n", is_constructor);
  SPACING; 
  printf ( "Is desctructor:    %d\n", is_destructor);
  SPACING; 
  printf ( "Is constant:       %d\n", is_constant);
  SPACING; 
  printf ( "Is operator:       %d\n", is_operator);
  SPACING; 
  printf ( "Is map:            %d\n", is_map);
  SPACING; 
  printf ( "Is order:          %d\n", is_order);
  SPACING; 
  printf ( "Is RNDS:           %d\n", is_rnds);
  SPACING; 
  printf ( "Is RNPS:           %d\n", is_rnps);
  SPACING; 
  printf ( "Is WNPS:           %d\n", is_wnps);
  printf("\n");

  if (has_result)
    chk_arg(envhp, errhp, svchp, list_arg, OCI_PTYPE_TYPE_RESULT, 0, 1);
  if (num_arg > 0)
    chk_arg(envhp, errhp, svchp, list_arg, OCI_PTYPE_TYPE_ARG, 1, num_arg + 1);
}

/*------------------------------------------------------------------*/

static void chk_arglst(envhp, errhp, svchp, parmp)
OCIEnv *envhp;
OCIError *errhp;
OCISvcCtx *svchp;
dvoid   *parmp;
{
  dvoid *arglst;
  ub4   numargs;
  ub1   ptype;
  sword retval;

  /* get list of arguments */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, 
               (dvoid*) &arglst, (ub4 *) 0, 
               (ub4) OCI_ATTR_LIST_ARGUMENTS, (OCIError *) errhp));

  /* get number of parameters */
  checkerr(errhp, OCIAttrGet((dvoid*) arglst, (ub4) OCI_DTYPE_PARAM, 
               (dvoid*) &numargs, (ub4 *) 0, 
               (ub4) OCI_ATTR_NUM_PARAMS, (OCIError *) errhp));
  
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM, 
               (dvoid*) &ptype, (ub4 *) 0, 
               (ub4) OCI_ATTR_PTYPE, (OCIError *) errhp));
  
  switch (ptype) 
  {
  case OCI_PTYPE_FUNC:
    chk_arg (envhp, errhp, svchp, arglst, OCI_PTYPE_ARG, 0, numargs);
    break;
  case OCI_PTYPE_PROC:
    chk_arg (envhp, errhp, svchp, arglst, OCI_PTYPE_ARG, 1, numargs);
  }
}

/*-----------------------------------------------------------------*/

static void chk_arg (envhp, errhp, svchp, parmp, type, start, end)
OCIEnv *envhp;
OCIError *errhp;
OCISvcCtx *svchp;
dvoid *parmp;
ub1    type;
ub4    start;
ub4    end;
{
  text  argname[NPOS][30];
  text  *namep;
  ub4   sizep;         
  ub2   collen[NPOS];         
  ub2   coldesr[NPOS];
  dvoid *parmdp;
  ub4   i, pos;
  sword retval;
  ub2   level[NPOS];
  ub1   radix[NPOS], def[NPOS]; 
  ub4   iomode[NPOS];
  ub1   precision[NPOS], scale[NPOS], isnull[NPOS]; 
  

  for (pos = start; pos < end; pos++)
  {

    checkerr(errhp, OCIParamGet((dvoid *)parmp, (ub4) OCI_DTYPE_PARAM, errhp, 
                       (dvoid *)&parmdp, (ub4) pos));
    
    /* get data type */
    checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                 (dvoid*) &coldesr[pos], (ub4 *) 0, 
                 (ub4) OCI_ATTR_DATA_TYPE, 
                 (OCIError *) errhp));

    /* method's result has no name */
    iomode[pos] = 0;
    def[pos] = 0;
    sizep = 0;
    if (type != OCI_PTYPE_TYPE_RESULT)
    {
      /* has default */
      checkerr(errhp, OCIAttrGet((dvoid *)parmdp, (ub4) OCI_DTYPE_PARAM,
                        (dvoid *)&def[pos], (ub4 *)0,
                        (ub4)OCI_ATTR_HAS_DEFAULT, (OCIError *) errhp));

      /* get iomode */
      checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                        (dvoid*) &iomode[pos], (ub4 *) 0,
                        (ub4) OCI_ATTR_IOMODE, (OCIError *) errhp));

      /* get argument name */
      checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                        (dvoid*) &namep, (ub4 *) &sizep,
                        (ub4) OCI_ATTR_NAME, (OCIError *) errhp));

      (void) strncpy((char *)argname[pos], (char *)namep, 
                    (size_t) sizep);
    }
    argname[pos][sizep] = '\0';

    /* the following are not for type arguments and results */
    precision[pos] = 0;
    scale[pos] = 0;
    collen[pos] = 0;
    level[pos] = 0;
    radix[pos] = 0;
    isnull[pos] = FALSE;
    if (type != OCI_PTYPE_TYPE_ARG && type != OCI_PTYPE_TYPE_RESULT)
    {
      /* get the data size */
      checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                        (dvoid*) &collen[pos], (ub4 *) 0,
                        (ub4) OCI_ATTR_DATA_SIZE, (OCIError *) errhp));

      /* get the precision of the attribute */
      checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                        (dvoid*) &precision, (ub4 *) 0,
                        (ub4) OCI_ATTR_PRECISION, (OCIError *) errhp));

      /* get the scale of the attribute */
      checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                        (dvoid*) &scale, (ub4 *) 0,
                        (ub4) OCI_ATTR_SCALE, (OCIError *) errhp));

      /* get the level of the attribute */
      checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                        (dvoid*) &level[pos], (ub4 *) 0,
                        (ub4) OCI_ATTR_LEVEL, (OCIError *) errhp));

      /* get the radix of the attribute */
      checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                        (dvoid*) &radix[pos], (ub4 *) 0,
                        (ub4) OCI_ATTR_RADIX, (OCIError *) errhp));

      /* is null */
      checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                        (dvoid*) &isnull, (ub4 *) 0,
                        (ub4) OCI_ATTR_IS_NULL, (OCIError *) errhp));

      /* should get error 24328 */
      if (OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                     (dvoid*) &isnull, (ub4 *) 0,
                     (ub4) OCI_ATTR_INDEX_ONLY, (OCIError *) errhp)
          != OCI_ERROR)
        printf("ERROR: should get error here\n");
    }
  }

  SPACING; 
  (void) 
   printf("Argument Name  Length  Datatype  Level Radix Default Iomode Prec Scl 
Null\n");
  SPACING; 
  (void)
   printf
   ("_____________________________________________________________\n"); 
  for (i = start; i < end; i++)
  {
    SPACING; 
    (void) printf( "%15s%6d%8d%6d%6d     %c%6d%9d%4d%4d\n",  argname[i],
                        collen[i], coldesr[i], level[i], radix[i],
                        (def[i])?'y':'n', iomode[i], precision[i], scale[i],
                        isnull[i]);
  }
  printf("\n");
      
}

static void chk_collection (envhp, errhp, svchp, parmp, is_array)
OCIEnv *envhp;
OCIError *errhp;
OCISvcCtx *svchp;
dvoid   *parmp;
sword    is_array;
{
  text         schema[MAXNAME],
               type[MAXNAME],
              *namep;
  ub4          size;
  ub2          len;
  ub4          num_elems;
  OCITypeCode  typecode;
  sword        retval;

  /* get the data size */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &len, (ub4 *) 0,
                    (ub4) OCI_ATTR_DATA_SIZE, (OCIError *) errhp));

  /* get the name of the collection */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &namep, (ub4 *) &size,
                    (ub4) OCI_ATTR_TYPE_NAME, (OCIError *) errhp));

  (void) strncpy((char *)type, (char *)namep, (size_t) size);
  type[size] = '\0';

  /* get the name of the schema */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &namep, (ub4 *) &size,
                    (ub4) OCI_ATTR_SCHEMA_NAME, (OCIError *) errhp));

  (void) strncpy((char *)schema, (char *)namep, (size_t) size);
  schema[size] = '\0';

  /* get the data type */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &typecode, (ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE,
                    (OCIError *) errhp));
 
  num_elems = 0;
  if (is_array)
    /* get the number of elements */
    checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                      (dvoid*) &num_elems, (ub4 *) 0,
                      (ub4) OCI_ATTR_NUM_ELEMS, (OCIError *) errhp));

  SPACING;
  (void)
    printf ( "Schema    Type            Length   Datatype Elements\n");
  SPACING;
  (void)
    printf ( "____________________________________________________\n");
  SPACING;
  (void) printf( "%10s%16s%6d%11d%9d\n",  schema, type, len, typecode,
                  num_elems);
  printf("\n");
}

/*-----------------------------------------------------------------*/

static void chk_column(envhp, errhp, svchp, parmp, parmcnt)
OCIEnv *envhp;
OCIError *errhp;
OCISvcCtx *svchp;
dvoid *parmp;
ub4 parmcnt;
{
  text  colname1[NPOS][30], colname2[NPOS][30], colname3[NPOS][30];
  text  *namep;
  ub4   sizep;
  ub2   collen[NPOS];
  ub2   coldesr[NPOS];
  dvoid *parmdp;
  ub4   i, pos;
  sword retval;
 
  /* loop through all the attributes in the type and get all information */
  for (pos = 1; pos <= parmcnt; pos++)
  {  
    /* get the parameter list for each attribute */
    checkerr(errhp, OCIParamGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM, errhp,
                       (dvoid *)&parmdp, (ub4) pos));
 
    /* size of the attribute (non object or REF) objects */
    checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                      (dvoid*) &collen[pos-1], (ub4 *) 0,
                      (ub4) OCI_ATTR_DATA_SIZE, (OCIError *) errhp));
    
    /* name of the attribute */
    checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &namep, (ub4 *) &sizep,
                    (ub4) OCI_ATTR_NAME, (OCIError *) errhp));
 
    (void) strncpy((char *)colname1[pos-1], (char *)namep, (size_t) sizep);
    colname1[pos-1][sizep] = '\0';
 
    /* get the schema name */
    checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &namep, (ub4 *) &sizep,
                    (ub4) OCI_ATTR_SCHEMA_NAME, (OCIError *) errhp));
 
    (void) strncpy((char *)colname2[pos-1], (char *)namep, (size_t) sizep);
    colname2[pos-1][sizep] = '\0';
 
    /* name of the attribute */
    checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &namep, (ub4 *) &sizep,
                    (ub4) OCI_ATTR_TYPE_NAME, (OCIError *) errhp));
 
    (void) strncpy((char *)colname3[pos-1], (char *)namep, (size_t) sizep);
    colname3[pos-1][sizep] = '\0';
 
    /* get data type */
    checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                      (dvoid*) &coldesr[pos-1], (ub4 *) 0,
                      (ub4) OCI_ATTR_DATA_TYPE,
                      (OCIError *) errhp));

    if (coldesr[pos-1] == SQLT_NTY || coldesr[pos-1] == SQLT_REF)
    {
      /* call tst_desc_type here if the type is object or REF */
      tab += 5;
      SPACING;
      printf("!!!!ATTRIBUTE IS A TYPE OR REF!!!!\n");
      SPACING;
      printf("ATTRIBUTE NAME IS %s\n", colname3[pos-1]);
      SPACING;
      printf("ATTRIBUTE TYPE IS %d\n", coldesr[pos-1]);
      tst_desc_type(envhp, errhp, svchp, colname3[pos-1]); 
      tab -= 5;
      printf("\n");
    }
    
  }
 
  SPACING;
  (void)
    printf ( "Column Name    Schema    Type            Length   Datatype\n");
  SPACING;
  (void)
    printf ( "__________________________________________________________\n");
  for (i = 1; i <= parmcnt; i++)
  {
    SPACING;
    (void) printf( "%15s%10s%16s%6d%8d\n",  colname1[i-1], colname2[i-1],
                    colname3[i-1], collen[i-1], coldesr[i-1] );
  }
  printf("\n");
}

/*-----------------------------------------------------------------*/

static void tst_desc_type(envhp, errhp, svchp, objname)
OCIEnv *envhp;
OCIError *errhp;
OCISvcCtx *svchp;
text *objname;
{
  OCIDescribe *dschp = (OCIDescribe *) 0;
  sword  retval;
  OCITypeCode typecode,
         collection_typecode;
  text   schema[MAXNAME],
         version[MAXNAME],
        *namep,
        *type_name;
  ub4    size,
         text_len;
  OCIRef *type_ref;
  ub2    num_attr,
         num_method;
  ub1    is_incomplete,
         is_system,
         is_predefined,
         is_transient,
         is_sysgen,
         has_table,
         has_lob,
         has_file;
  dvoid *list_attr,
        *list_method,
        *map_method,
        *order_method,
        *collection_dschp,
        *some_object;
  OCIParam *parmp;
  ub1 objtype;

  /* must allocate describe handle first for OCIDescribeAny */
  checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &dschp,
             (ub4) OCI_HTYPE_DESCRIBE, 
             (size_t) 0, (dvoid **) 0));

  /* call OCIDescribeAny and passing in the type name */
  checkerr(errhp, OCIDescribeAny(svchp, errhp, (text *)objname,
                  (ub4) strlen((char *)objname), OCI_OTYPE_NAME, (ub1)1,
                  (ub1) OCI_PTYPE_TYPE, dschp));

  /* get the parameter list for the requested type */
  checkerr(errhp, OCIAttrGet((dvoid *) dschp, (ub4) OCI_HTYPE_DESCRIBE,
                  (dvoid *)&parmp, (ub4 *)0, (ub4)OCI_ATTR_PARAM, errhp));

  /* get the schema name for the requested type */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp,(ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &namep, (ub4 *) &size,
                    (ub4) OCI_ATTR_SCHEMA_NAME, (OCIError *) errhp));

  (void) strncpy((char *)schema, (char *)namep, (size_t) size);
  schema[size] = '\0';
  
  /* get the type code for the requested type */  
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &typecode, (ub4 *) 0,
                    (ub4) OCI_ATTR_TYPECODE, (OCIError *) errhp));

  /* get other information for collection type */
  if (typecode == OCI_TYPECODE_NAMEDCOLLECTION)
  {
    checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                      (dvoid *)&collection_typecode, (ub4 *)0,
                      (ub4)OCI_ATTR_COLLECTION_TYPECODE, (OCIError *)errhp));
    checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                      (dvoid *)&collection_dschp, (ub4 *)0,
                      (ub4)OCI_ATTR_COLLECTION_ELEMENT, (OCIError *)errhp));
    checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                      (dvoid *)&collection_dschp, (ub4 *)0,
                      (ub4)OCI_ATTR_COLLECTION_ELEMENT, (OCIError *)errhp));
  }

  /* get the ref to the type descriptor */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &type_ref, (ub4 *) 0,
                    (ub4) OCI_ATTR_REF_TDO, (OCIError *) errhp));

  /* get the type version */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &namep, (ub4 *) &size,
                    (ub4) OCI_ATTR_VERSION, (OCIError *) errhp));

  (void) strncpy((char *)version, (char *)namep, (size_t) size);
  version[size] = '\0';

  /* incomplete type */  
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &is_incomplete, (ub4 *) 0,
                    (ub4) OCI_ATTR_IS_INCOMPLETE_TYPE, (OCIError *) errhp));

  /* system type */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &is_system, (ub4 *) 0,
                    (ub4) OCI_ATTR_IS_SYSTEM_TYPE, (OCIError *) errhp));

  /* predefined type */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &is_predefined, (ub4 *) 0,
                    (ub4) OCI_ATTR_IS_PREDEFINED_TYPE, (OCIError *) errhp));

  /* transient type */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &is_transient, (ub4 *) 0,
                    (ub4) OCI_ATTR_IS_TRANSIENT_TYPE, (OCIError *) errhp));

  /* system generated type */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
               (dvoid*) &is_sysgen, (ub4 *) 0,
               (ub4) OCI_ATTR_IS_SYSTEM_GENERATED_TYPE, (OCIError*) errhp));

  /* has nested table */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &has_table, (ub4 *) 0,
                    (ub4) OCI_ATTR_HAS_NESTED_TABLE, (OCIError *) errhp));

  /* has lob */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &has_lob, (ub4 *) 0,
                    (ub4) OCI_ATTR_HAS_LOB, (OCIError *) errhp));

  /* has file */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &has_file, (ub4 *) 0,
                    (ub4) OCI_ATTR_HAS_FILE, (OCIError *) errhp));

  /* get the list of attributes */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&list_attr, (ub4 *)0,
                    (ub4)OCI_ATTR_LIST_TYPE_ATTRS, (OCIError *)errhp));

  /* number of attributes */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &num_attr, (ub4 *) 0,
                    (ub4) OCI_ATTR_NUM_TYPE_ATTRS, (OCIError *) errhp));

  /* get method list */
  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&list_method, (ub4 *)0,
                    (ub4)OCI_ATTR_LIST_TYPE_METHODS, (OCIError *)errhp));

  /* get number of methods */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &num_method, (ub4 *) 0,
                    (ub4) OCI_ATTR_NUM_TYPE_METHODS, (OCIError *) errhp));

  /* get map method list */ 
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &map_method, (ub4 *) 0,
                    (ub4) OCI_ATTR_MAP_METHOD, (OCIError *) errhp));

  /* get order method list*/
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &order_method, (ub4 *) 0,
                    (ub4) OCI_ATTR_ORDER_METHOD, (OCIError *) errhp));

  SPACING;
  printf ( "TYPE     : Attributes : \n");
  SPACING;
  printf ( "Schema:            %s\n", schema);
  SPACING;
  printf ( "Typecode:          %d\n", typecode);
  if (typecode == OCI_TYPECODE_NAMEDCOLLECTION)
  {
    SPACING;
    printf ( "Collection typecode: %d\n", collection_typecode);
  }
  SPACING;
  printf ( "Version:           %s\n", version);
  SPACING;
  printf ( "Number of attrs:   %d\n", num_attr);
  SPACING;
  printf ( "Number of methods: %d\n", num_method);
  SPACING;
  printf ( "Is incomplete:     %d\n", is_incomplete);
  SPACING;
  printf ( "Is system:         %d\n", is_system);
  SPACING;
  printf ( "Is predefined:     %d\n", is_predefined);
  SPACING;
  printf ( "Is sys-gen:        %d\n", is_sysgen);
  SPACING;
  printf ( "Is transient:      %d\n", is_transient);
  SPACING;
  printf ( "Has nested table:  %d\n", has_table);
  SPACING;
  printf ( "Has LOB:           %d\n", has_lob);
  SPACING;
  printf ( "Has file:          %d\n", has_file);
  printf("\n");

  if (num_attr > 0)
    chk_column(envhp, errhp, svchp, list_attr, num_attr);
  else if (typecode == OCI_TYPECODE_NAMEDCOLLECTION)
    chk_collection(envhp, errhp, svchp, collection_dschp,
                   collection_typecode == OCI_TYPECODE_VARRAY);
  if (map_method != (dvoid *)0)
    chk_method(envhp, errhp, svchp, map_method, "TYPE MAP 
METHOD\n---------------");
  if (order_method != (dvoid *)0)
    chk_method(envhp, errhp, svchp, order_method, "TYPE ORDER 
METHOD\n-----------------");
  if (num_method > 0)
    chk_methodlst(envhp, errhp, svchp, list_method, num_method, "TYPE 
METHOD\n-----------");
}


/********************************************************************/ 
int main(int argc, char *argv[])
{
  OCIEnv *envhp = (OCIEnv *) 0;
  OCIServer *srvhp = (OCIServer *) 0;
  OCIError *errhp = (OCIError *) 0;
  OCISvcCtx *svchp = (OCISvcCtx *) 0;
  OCISession *usrhp = (OCISession *) 0;
  dvoid *tmp;
  int i;
  
  tab = 0;
 
  if (argc < 4)
  {
    (void) printf("Usage -- cdemort <username> <password> <upper case 
typename>\n");
    return (0);
  }
 
  (void) OCIInitialize((ub4) OCI_THREADED | OCI_OBJECT,
                (dvoid *)0,  (dvoid * (*)()) 0,
                (dvoid * (*)()) 0,  (void (*)()) 0 );

  (void) OCIHandleAlloc( (dvoid *) NULL, (dvoid **) &envhp,
                 (ub4) OCI_HTYPE_ENV,
                 52, (dvoid **) &tmp);

  (void) OCIEnvInit( &envhp, (ub4) OCI_DEFAULT, 21, (dvoid **) &tmp  );

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp,
                 (ub4) OCI_HTYPE_ERROR,
                 52, (dvoid **) &tmp);

  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp,
                 (ub4) OCI_HTYPE_SERVER,
                 52, (dvoid **) &tmp);

  checkerr(errhp, OCIServerAttach( srvhp, errhp, (text *) "",
                 (sb4) strlen(""), (ub4) OCI_DEFAULT));

  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp,
                  (ub4) OCI_HTYPE_SVCCTX,
                  52, (dvoid **) &tmp));

  checkerr(errhp, OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                  (dvoid *) srvhp, (ub4) 0,
                  (ub4) OCI_ATTR_SERVER, (OCIError *) errhp));

  checkerr(errhp, OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp,
               (ub4)OCI_HTYPE_SESSION, 0, (dvoid **)0));

  checkerr(errhp, OCIAttrSet((dvoid *) usrhp, (ub4)OCI_HTYPE_SESSION,
               (dvoid *)argv[1], (ub4)strlen(argv[1]),
               (ub4)OCI_ATTR_USERNAME, errhp));

  checkerr(errhp, OCIAttrSet((dvoid *) usrhp, (ub4)OCI_HTYPE_SESSION,
               (dvoid *)argv[2], (ub4)strlen(argv[2]),
               (ub4)OCI_ATTR_PASSWORD, errhp));

  checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, 
                                   OCI_CRED_RDBMS, OCI_DEFAULT));

  checkerr(errhp, OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
               (dvoid *)usrhp, (ub4)0,
               (ub4)OCI_ATTR_SESSION, errhp));

  /* dump an object with all the types */
  SPACING;
  (void) printf("%s\n", argv[3]);
  tst_desc_type(envhp, errhp, svchp, argv[3]); 
  printf("\n");

  checkerr(errhp, OCISessionEnd (svchp, errhp, usrhp, OCI_DEFAULT));

  (void) OCIServerDetach( srvhp, errhp, (ub4) OCI_DEFAULT );

  checkerr(errhp, OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX));
  checkerr(errhp, OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR));
  checkerr(errhp, OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER));

  return (0);
}

/* end of file cdemodsc.c */

cdemodsc.h

/*   NAME 
     cdemodsc.h - header file for cdemodsc.c
*/

/*-----------------------------------------------------------------*/
#ifndef CDEMODSC
#define CDEMODSC

#ifndef S
#include <s.h>
#endif
 
#ifndef HSTDEF
#include <hstdef.h>
#endif
 
#ifndef SQLDEF
#include <sqldef.h>
#endif
 
#ifndef OCIDEF
#include <ocidef.h>
#endif
#ifndef OCI_ORACLE
#include <oci.h>
#endif
 
#include <lnx.h>
 
/*---------------------------------------------------------------*/
/*
** #define
*/

#define MAXNAME       30
#define MAXOBJLEN     60
#define MAXOBJS        7
#define NPOS          40
#define SPACING       for (glindex = 0; glindex < tab; glindex++)\
                        printf(" ")

/*------------------------------------------------------------------*/
/*
** Prototypes for functions in cdemodsc.c
*/ 
static void chk_column(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, 
dvoid *dschp, ub4 parmcnt  _*/);
static void chk_method(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, 
dvoid *dschp, const text *comment _*/);
static void chk_methodlst(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, 
dvoid *dschp, ub4 count, const text *comment _*/);
static void chk_arglst(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, 
dvoid *dschp _*/);
static void chk_arg(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, dvoid 
*dschp, ub1 type, ub4 start, ub4 end _*/);
static void chk_collection (/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx 
*svchp, dvoid *dschp, sword is_array _*/);
static void tst_desc_type(/*_ OCIEnv *envhp, OCIError *errhp, OCISvcCtx *svchp, 
text *objname _*/);
static void checkerr(/*_ OCIError *errhp, sword status _*/);

/* Prototype for main function */
int main(/*_ int argc, char *argv[] _*/);
int tab;
int glindex; 

#endif  /* CDEMODSC */

Example 5, CLOB/BLOB Operations


/*   NAME
     cdemolb2.c - Demonstrates writing and reading of CLOB/BLOB columns
               with stream mode and with callback functions.

   DESCRIPTION
     This program takes 2 input files (the first a text file and the 
     second a binary file) and stores the files into CLOB, BLOB columns.

     On output, the program reads the newly populated CLOB/BLOB columns 
     and writes them to the output files (txtfile1.log, binfile1.log,
     txtfile2.log, binfile2.log), where 

     txtfile1.log  --  created for stream reading CLOB contents to it
     binfile1.log  --  created for stream reading BLOB contents to it

     txtfile2.log  --  created for callback reading CLOB contents to it
     binfile2.log  --  created for callback reading BLOB contents to it
 

     Sample usage: cdemolb2 cdemolb.dat giffile.dat

     cdemolb.dat   --  a text file in the demo directory
     giffile.dat   --  a gif file in the demo directory
 
     After successful execution of the program, the files, cdemolb.dat,
     txtfile1.log, and txtfile2.log should be identical.  giffile.dat,
     binfile1.log, and binfile2.log should be identical.

*/

#include <stdio.h>
#include <oci.h>
 
static sb4 init_handles(/*_ void _*/);
static sb4 log_on(/*_ void _*/);
static sb4 setup_table(/*_ void _*/);
static sb4 select_locator(/*_ int rowind _*/);
static ub4 file_length(/*_ FILE *fp _*/);
static sb4 test_file_to_lob(/*_ int rowind, char *tfname, char *bfname _*/);
static void test_lob_to_file(/*_ int rowind _*/);
static void stream_write_lob(/*_ int rowind, OCILobLocator *lobl, 
                                 FILE *fp, ub4 filelen _*/);
static void callback_write_lob(/*_ int rowind, OCILobLocator *lobl, 
                                 FILE *fp, ub4 filelen _*/);
static void stream_read_lob(/*_ int rowind, OCILobLocator *lobl, FILE *fp _*/);
static void callback_read_lob(/*_ int rowind, OCILobLocator *lobl,FILE *fp _*/);
static sb4 cbk_fill_buffer(/*_ dvoid *ctxp, dvoid *bufxp, ub4 *lenp, 
                                ub1 *piece _*/);
static sb4 cbk_write_buffer(/*_ dvoid *ctxp, CONST dvoid *bufxp, ub4 lenp, 
                                 ub1 piece _*/);

static void logout(/*_ void _*/);
static void drop_table(/*_ void _*/);
static void report_error(/*_ void _*/);

int main(/*_ int argc, char *argv[] _*/);

#define TRUE       1
#define FALSE      0

#define MAXBUFLEN  5000

static OCIEnv        *envhp;
static OCIServer     *srvhp;
static OCISvcCtx     *svchp;
static OCIError      *errhp;
static OCISession    *authp;
static OCIStmt       *stmthp;
static OCILobLocator *clob, *blob;
static OCIDefine     *defnp1 = (OCIDefine *) 0, *defnp2 = (OCIDefine *) 0;
static OCIBind       *bndhp = (OCIBind *) 0;

static FILE *fp1, *fp2;

static ub4  txtfilelen = 0;
static ub4  binfilelen = 0;

static boolean  istxtfile;
static boolean  tab_exists = FALSE;

/*------------------------end of Inclusions-----------------------------*/

int main(argc, argv)
int argc; 
char *argv[];
{
  int  rowind;

  if (argc != 3)
  {
    (void) printf("Usage: %s txtfilename binfilename\n", argv[0]);
    return 0;
  }

  if (init_handles())
  {
    (void) printf("FAILED: init_handles()\n");
    return OCI_ERROR;
  }

  if (log_on())
  {
    (void) printf("FAILED: log_on()\n");
    return OCI_ERROR;
  }

  if (setup_table())
  {
    (void) printf("FAILED: setup_table()\n");
    logout();
    return OCI_ERROR;
  }

  tab_exists = TRUE;

  for (rowind = 1; rowind <= 2; rowind++)
  {
    if (select_locator(rowind))
    {
      (void) printf("FAILED: select_locator()\n");
      logout();
      return OCI_ERROR;
    }

    if (test_file_to_lob(rowind, argv[1], argv[2]))
    {
      (void) printf("FAILED: load files to lobs\n");
      logout();
      return OCI_ERROR;
    }

    test_lob_to_file(rowind);
  }

  logout();

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* initialize environment, allocate handles, etc.                    */ 
/* ----------------------------------------------------------------- */

sb4 init_handles()
{
  if (OCIInitialize((ub4) OCI_DEFAULT, (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;
  }
 
  /* initialize environment handle */
  if (OCIEnvInit((OCIEnv **) &envhp, (ub4) OCI_DEFAULT,
                 (size_t) 0, (dvoid **) 0 ))
  {
    (void) printf("FAILED: OCIEnvInit()\n");
    return OCI_ERROR;
  }
 
  if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &svchp,
                     (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc()\n");
    return OCI_ERROR;
  }

  if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp,
                     (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc()\n");
    return OCI_ERROR;
  }
 
  if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &stmthp,
                     (ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc()\n");
    return OCI_ERROR;
  }
 
  if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp,
                     (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc()\n");
    return OCI_ERROR;
  }

  if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &authp,
                     (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc()\n");
    return OCI_ERROR;
  }

  if (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &clob, 
                         (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIDescriptorAlloc()\n");
    return OCI_ERROR;
  }

  /* allocate the lob locator variables */
  if (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &blob, 
                         (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIDescriptorAlloc()\n");
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}

/* ----------------------------------------------------------------- */
/* attach to the server and log on as SCOTT/TIGER                    */
/* ----------------------------------------------------------------- */

sb4 log_on()
{
  text *uid = (text *)"SCOTT";
  text *pwd = (text *)"TIGER";
  text *cstring = (text *) "";

  /* attach to the server */
  if (OCIServerAttach(srvhp, errhp, (text *) cstring,
                     (sb4) strlen((char *)cstring), (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIServerAttach()\n");
    return OCI_ERROR;
  }
 
  if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) uid, (ub4) strlen((char *)uid),
                 (ub4) OCI_ATTR_USERNAME, errhp))
  {
    (void) printf("FAILED: OCIAttrSet()\n");
    return OCI_ERROR;
  }
 
  if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) pwd, (ub4) strlen((char *)pwd),
                 (ub4) OCI_ATTR_PASSWORD, errhp))
  {
    (void) printf("FAILED: OCIAttrSet()\n");
    return OCI_ERROR;
  }

  /* set the server attribute in the service context */
  if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                 (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, errhp))
  {
    (void) printf("FAILED: OCIAttrSet()\n");
    return OCI_ERROR;
  }
 
  /* log on */
  if (OCISessionBegin(svchp, errhp, authp, (ub4) OCI_CRED_RDBMS,
                     (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCISessionBegin()\n");
    return OCI_ERROR;
  }
 
  /* set the session attribute in the service context */
  if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) authp,
                 (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp))
  {
    (void) printf("FAILED: OCIAttrSet()\n");
    return OCI_ERROR;
  }
 
  return OCI_SUCCESS;

}

/* ----------------------------------------------------------------- */
/* Create table FOO with CLOB, BLOB columns and insert one row.      */
/* Both columns are empty lobs, not null lobs.                       */
/* ----------------------------------------------------------------- */

sb4 setup_table()
{
  int colc;

  text  *crtstmt = (text *) "CREATE TABLE FOO (A CLOB, B BLOB, C INTEGER)";
  text  *insstmt = 
          (text *) "INSERT INTO FOO VALUES (EMPTY_CLOB(), EMPTY_BLOB(), :1)";

  if (OCIStmtPrepare(stmthp, errhp, crtstmt, (ub4) strlen((char *) crtstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() crtstmt\n");
    return OCI_ERROR;
  }

  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot *) 0, (OCISnapshot *) 0, 
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() crtstmt\n");
    return OCI_ERROR;
  }

  if (OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) strlen((char *) insstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() insstmt\n");
    return OCI_ERROR;
  }

  if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1,
                      (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIBindByPos()\n");
    return OCI_ERROR;
  }

  for (colc = 1; colc <= 2; colc++)
  {
    if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                      (CONST OCISnapshot *) 0, (OCISnapshot *) 0, 
                      (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtExecute() insstmt\n");
      return OCI_ERROR;
    }
  }

  (void) OCITransCommit(svchp, errhp, (ub4)0);

  return OCI_SUCCESS;
}

/*---------------------------------------------------------------------*/
/* Select lob locators from the CLOB, BLOB columns.                    */
/* We need the 'FOR UPDATE' clause since we need to write to the lobs. */
/*---------------------------------------------------------------------*/

sb4 select_locator(int rowind)
{
  int colc = rowind;
  text  *sqlstmt = (text *)"SELECT A, B FROM FOO WHERE C = :1 FOR UPDATE";

  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n");
    return OCI_ERROR;
  }
  
  if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1,
                      (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIBindByPos()\n");
    return OCI_ERROR;
  }

  if (OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                     (dvoid *) &clob, (sb4) -1, (ub2) SQLT_CLOB,
                     (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)
  ||  OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 2,
                     (dvoid *) &blob, (sb4) -1, (ub2) SQLT_BLOB,
                     (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIDefineByPos()\n");
    return OCI_ERROR;
  }

  /* execute the select and fetch one row */
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() sqlstmt\n");
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}          

/* ----------------------------------------------------------------- */
/* Read operating system files into local buffers and then write the */
/* buffers to lobs.                                                  */
/* ----------------------------------------------------------------- */

sb4 test_file_to_lob(int rowind, char *txtfile, char *binfile)
{
  (void) printf("\n===> Testing loading files into lobs .....\n\n");
  
  fp1 = fopen((const char *)txtfile, (const char *) "r");
  fp2 = fopen((const char *)binfile, (const char *) "rb");

  if ( !(fp1 && fp2))
  {
    (void) printf("ERROR: Failed to open file(s).\n");
    return -1;
  }

  txtfilelen = file_length(fp1);
  binfilelen = file_length(fp2);

  switch (rowind)
  {
    case 1:
       stream_write_lob(rowind, clob, fp1, txtfilelen);
       stream_write_lob(rowind, blob, fp2, binfilelen);
       break;
    case 2:
       istxtfile = TRUE;
       callback_write_lob(rowind, clob, fp1, txtfilelen);
       istxtfile = FALSE;
       callback_write_lob(rowind, blob, fp2, binfilelen);
       break;
    default:
       (void) printf("ERROR: Invalid row indicator.\n");
       break;
  }

  (void) fclose(fp1);
  (void) fclose(fp2);

  return 0;
}
  
/* ----------------------------------------------------------------- */
/* get the length of the input file.                                 */
/* ----------------------------------------------------------------- */

ub4 file_length(FILE *fp)
{
  fseek(fp, 0, SEEK_END);
  return (ub4) (ftell(fp));
}

/* ----------------------------------------------------------------- */
/* Read operating system files into local buffers and then write the */
/* buffers to lobs using stream mode.                                */
/* ----------------------------------------------------------------- */

void stream_write_lob(int rowind, OCILobLocator *lobl, FILE *fp, ub4 filelen)
{
  ub4   offset = 1;
  ub4   loblen = 0;
  ub1   bufp[MAXBUFLEN];
  ub4   amtp = filelen;
  ub1   piece;
  sword retval;
  int   readval;
  ub4   len = 0;
  ub4   nbytes;
  ub4   remainder = filelen;

  (void) printf("--> To do streamed write lob, amount = %d\n", filelen);

  (void) OCILobGetLength(svchp, errhp, lobl, &loblen);
  (void) printf("Before stream write, LOB length = %d\n\n", loblen);

  (void) fseek(fp, 0, 0);

  if (filelen > MAXBUFLEN)
    nbytes = MAXBUFLEN;
  else
    nbytes = filelen;

  if (fread((void *)bufp, (size_t)nbytes, 1, fp) != 1)
  {
    (void) printf("ERROR: read file.\n");
    return;
  }

  remainder -= nbytes; 
  
  if (remainder == 0)       /* exactly one piece in the file */ 
  {
    (void) printf("Only one piece, no need for stream write.\n");
    if (retval = OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp,
                            (ub4) nbytes, OCI_ONE_PIECE, (dvoid *)0, 
                            (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
                            (ub2) 0, (ub1) SQLCS_IMPLICIT) != OCI_SUCCESS)
    {
      (void) printf("ERROR: OCILobWrite(), retval = %d\n", retval);
      return;
    }
  }
  else                     /* more than one piece */
  {
    if (OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp,
                    (ub4) MAXBUFLEN, OCI_FIRST_PIECE, (dvoid *)0, 
                    (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
                    (ub2) 0, (ub1) SQLCS_IMPLICIT) != OCI_NEED_DATA)
    {
      (void) printf("ERROR: OCILobWrite().\n");
      return;
    }

    piece = OCI_NEXT_PIECE;

    do
    {
      if (remainder > MAXBUFLEN)
        nbytes = MAXBUFLEN;
      else
      {
        nbytes = remainder;
        piece = OCI_LAST_PIECE;
      }

      if (fread((void *)bufp, (size_t)nbytes, 1, fp) != 1)
      {
        (void) printf("ERROR: read file.\n");
        piece = OCI_LAST_PIECE; 
      }

      retval = OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp,
                          (ub4) nbytes, piece, (dvoid *)0, 
                          (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
                          (ub2) 0, (ub1) SQLCS_IMPLICIT);
      remainder -= nbytes; 

    } while (retval == OCI_NEED_DATA && !feof(fp));
  }

  if (retval != OCI_SUCCESS)
  {
    (void) printf("Error: stream writing LOB.\n");
    return;
  }

  (void) OCILobGetLength(svchp, errhp, lobl, &loblen);
  (void) printf("After stream write, LOB length = %d\n\n", loblen);

  return;
}


/* ----------------------------------------------------------------- */
/* Read operating system files into local buffers and then write the */
/* buffers to lobs using callback function.                          */
/* ----------------------------------------------------------------- */

void callback_write_lob(int rowind, OCILobLocator *lobl, FILE *fp, ub4 filelen)
{
  ub4   offset = 1;
  ub4   loblen = 0;
  ub1   bufp[MAXBUFLEN];
  ub4   amtp = filelen;
  ub4   nbytes;
  sword retval;

  (void) printf("--> To do callback write lob, amount = %d\n", filelen);

  (void) OCILobGetLength(svchp, errhp, lobl, &loblen);
  (void) printf("Before callback write, LOB length = %d\n\n", loblen);
 
  (void) fseek(fp, 0, 0);

  if (filelen > MAXBUFLEN)
    nbytes = MAXBUFLEN;
  else
    nbytes = filelen;

  if (fread((void *)bufp, (size_t)nbytes, 1, fp) != 1)
  {
    (void) printf("ERROR: read file.\n");
    return;
  }

  if (filelen < MAXBUFLEN)       /* exactly one piece in the file */
  {
    (void) printf("Only one piece, no need for callback write.\n");
    if (retval = OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp,
                            (ub4) nbytes, OCI_ONE_PIECE, (dvoid *)0, 
                            (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
                            (ub2) 0, (ub1) SQLCS_IMPLICIT) != OCI_SUCCESS)
    {
      (void) printf("ERROR: OCILobWrite().\n");
      return;
    }
  }
  else                     /* more than one piece */
  {
    if (retval = OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid *)bufp,
                            (ub4)nbytes, OCI_FIRST_PIECE, (dvoid *)0,
                            cbk_fill_buffer, (ub2) 0, (ub1) SQLCS_IMPLICIT))
    {
      (void) printf("ERROR: OCILobWrite().\n");
      report_error();
      return;
    }
  }

  (void) OCILobGetLength(svchp, errhp, lobl, &loblen);
  (void) printf("After callback write, LOB length = %d\n\n", loblen);
 
  return;
}

/* ----------------------------------------------------------------- */
/* callback function to read the file into buffer.                   */
/* ----------------------------------------------------------------- */

sb4 cbk_fill_buffer(ctxp, bufxp, lenp, piece)
  dvoid *ctxp;
  dvoid *bufxp;
  ub4 *lenp;
  ub1 *piece;
{
  FILE   *fp = (istxtfile ? fp1 : fp2); 
  ub4    filelen = (istxtfile ? txtfilelen : binfilelen);
  ub4    nbytes;
  static ub4 len = MAXBUFLEN;     /* because 1st piece has been written */

  
  if ((filelen - len) > MAXBUFLEN)
    nbytes = MAXBUFLEN;
  else
    nbytes = filelen - len;

  *lenp = nbytes;

  if (fread((void *)bufxp, (size_t)nbytes, 1, fp) != 1) 
  {
    (void) printf("ERROR: read file. Abort callback fill buffer\n");
    *piece = OCI_LAST_PIECE;
    len = MAXBUFLEN;         /* reset it for the next callback_write_lob() */
    return OCI_CONTINUE;
  }

  len += nbytes;

  if (len == filelen)         /* entire file has been read */
  {
    *piece = OCI_LAST_PIECE;
    len = MAXBUFLEN;          /* reset it for the next callback_write_lob() */
  }
  else
    *piece = OCI_NEXT_PIECE;

  return OCI_CONTINUE;
}

/* ----------------------------------------------------------------- */
/* Read lobs into local buffers and then write them to operating     */
/* system files.                                                     */
/* ----------------------------------------------------------------- */

void test_lob_to_file(int rowind)
{
  ub4   offset = 1;
  ub4   loblen = 0;
  ub1   bufp[MAXBUFLEN];
  ub4   amtp = MAXBUFLEN;
  text  txtfilename[20], binfilename[20];

  (void) sprintf((char *) txtfilename, (char *)"txtfile%d.log", rowind);
  (void) sprintf((char *) binfilename, (char *)"binfile%d.log", rowind);

  (void) printf("\n===> Testing writing lobs to files .....\n\n");
  
  fp1 = fopen((char *)txtfilename, (const char *) "w");
  fp2 = fopen((char *)binfilename, (const char *) "wb");

  if ( !(fp1 && fp2))
  {
    (void) printf("ERROR: Failed to open file(s).\n");
    return;
  }

  switch (rowind)
  {
    case 1:
       stream_read_lob(rowind, clob, fp1);
       stream_read_lob(rowind, blob, fp2);
       break;
    case 2:
       istxtfile = TRUE;
       callback_read_lob(rowind, clob, fp1);

       istxtfile = FALSE;
       callback_read_lob(rowind, blob, fp2);
       break;
    default:
       (void) printf("ERROR: Invalid row indicator.\n");
       break;
  }

  (void) fclose(fp1);
  (void) fclose(fp2);

  return;
}

/* ----------------------------------------------------------------- */
/* Read lobs using stream mode into local buffers and then write     */
/* them to operating system files.                                   */
/* ----------------------------------------------------------------- */

void stream_read_lob(int rowind, OCILobLocator *lobl, FILE *fp)
{
  ub4   offset = 1;
  ub4   loblen = 0;
  ub1   bufp[MAXBUFLEN];
  ub4   amtp = 4096000000;
  sword retval;
  ub4   piece = 0;
  ub4   remainder;            /* the number of bytes for the last piece */

  (void) OCILobGetLength(svchp, errhp, lobl, &loblen);
  /*amtp = loblen;*/

  (void) printf("--> To stream read LOB, loblen = %d.\n", loblen);

  memset(bufp, '\0', MAXBUFLEN);

  retval = OCILobRead(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp,
                     (loblen < MAXBUFLEN ? loblen : MAXBUFLEN), (dvoid *)0, 
                     (sb4 (*)(dvoid *, const dvoid *, ub4, ub1)) 0,
                     (ub2) 0, (ub1) SQLCS_IMPLICIT);

  (void) printf(" amtp passed in was 4gb, amtp returned is %u\n", amtp);

  switch (retval)
  {
    case OCI_SUCCESS:             /* only one piece */
      (void) printf("stream read %d th piece\n", ++piece);
      (void) fwrite((void *)bufp, (size_t)loblen, 1, fp);
      break;
    case OCI_ERROR: 
      report_error();
      break;
    case OCI_NEED_DATA:           /* there are 2 or more pieces */

      remainder = loblen;

      (void) fwrite((void *)bufp, MAXBUFLEN, 1, fp); /* full buffer to write */

      do
      {
        memset(bufp, '\0', MAXBUFLEN);
        /*amtp = 0;*/

        remainder -= MAXBUFLEN;

        retval = OCILobRead(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp,
                           (ub4) MAXBUFLEN, (dvoid *)0, 
                           (sb4 (*)(dvoid *, const dvoid *, ub4, ub1)) 0,
                           (ub2) 0, (ub1) SQLCS_IMPLICIT);

        /* the amount read returned is undefined for FIRST, NEXT pieces */
        (void) printf("stream read %d th piece, amtp = %u\n", ++piece, amtp);

        if (remainder < MAXBUFLEN)     /* last piece not a full buffer piece */
           (void) fwrite((void *)bufp, (size_t)remainder, 1, fp);
        else
           (void) fwrite((void *)bufp, MAXBUFLEN, 1, fp);

      } while (retval == OCI_NEED_DATA);
      break;
    default:
      (void) printf("Unexpected ERROR: OCILobRead() LOB.\n");
      break;
  }
  return;
}

/* ----------------------------------------------------------------- */
/* Read lobs using callback function into local buffers and          */
/* then write them to operating system files.                        */
/* ----------------------------------------------------------------- */

void callback_read_lob(int rowind, OCILobLocator *lobl, FILE *fp)
{
  ub4   offset = 1;
  ub4   loblen = 0;
  ub1   bufp[MAXBUFLEN];
  ub4   amtp = 4096000000;
  sword retval;

  (void) OCILobGetLength(svchp, errhp, lobl, &loblen);

  (void) printf("--> To callback read LOB, loblen = %d.\n", loblen);

  if (retval = OCILobRead(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp,
                         (ub4) MAXBUFLEN, (dvoid *) bufp, cbk_write_buffer,
                         (ub2) 0, (ub1) SQLCS_IMPLICIT))
  {
    (void) printf("ERROR: OCILobRead() LOB.\n");
    report_error();
  }
  return;
}

/* ----------------------------------------------------------------- */
/* callback function to write buffer to the file.                    */
/* ----------------------------------------------------------------- */
 
sb4 cbk_write_buffer(ctxp, bufxp, lenp, piece)
  dvoid *ctxp;
  CONST dvoid *bufxp;
  ub4 lenp;
  ub1 piece;
{
  static ub4 piece_count = 0;
  FILE *fp = (istxtfile ? fp1 : fp2);

  piece_count++;

  switch (piece)
  {
    case OCI_LAST_PIECE:
      (void) fwrite((void *)bufxp, (size_t)lenp, 1, fp);
      (void) printf("callback read the %d th piece\n\n", piece_count);
      piece_count = 0;
      return OCI_CONTINUE;

    case OCI_FIRST_PIECE:
    case OCI_NEXT_PIECE:
      (void) fwrite((void *)bufxp, (size_t)lenp, 1, fp);
      break;
    default:
      (void) printf("callback read error: unkown piece = %d.\n", piece);
      return OCI_ERROR;
  }

  (void) printf("callback read the %d th piece\n", piece_count);

  return OCI_CONTINUE;
}

/*-------------------------------------------------------------------*/ 
/* Drop table FOO before logging off from the server.                */
/*-------------------------------------------------------------------*/ 

void drop_table()
{
  text  *sqlstmt = (text *) "DROP TABLE FOO";
 
  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *) sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n");
    return;
  }
 
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot *) 0, (OCISnapshot *) 0,
                    (ub4) OCI_DEFAULT))
    (void) printf("FAILED: OCIStmtExecute() sqlstmt\n");
  return;
}

/*-------------------------------------------------------------------*/ 
/* Logoff and disconnect from the server.  Free handles.             */
/*-------------------------------------------------------------------*/ 

void logout()
{
  if (tab_exists)
    drop_table();

  (void) OCISessionEnd(svchp, errhp, authp, (ub4) 0);
  (void) OCIServerDetach(srvhp, errhp, (ub4) OCI_DEFAULT);
 
  (void) printf("Logged off and detached from server.\n");
 
  (void) OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER);
  (void) OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX);
  (void) OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR);
  (void) OCIHandleFree((dvoid *) authp, (ub4) OCI_HTYPE_SESSION);
  (void) OCIDescriptorFree((dvoid *) clob, (ub4) OCI_DTYPE_LOB);
  (void) OCIDescriptorFree((dvoid *) blob, (ub4) OCI_DTYPE_LOB);
  (void) OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT);

  (void) printf("All handles freed\n");
  return;
} 

/* ----------------------------------------------------------------- */
/* retrieve error message and print it out.                          */ 
/* ----------------------------------------------------------------- */
void report_error()
{
  text  msgbuf[512];
  sb4   errcode = 0;

  (void) OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                       msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
  (void) printf("ERROR CODE = %d\n", errcode);
  (void) printf("%.*s\n", 512, msgbuf);
  return;
}

Example 6, LOB Buffering

/*   NAME
     cdemolbs.c - Demonstrates reading and writing to LOBs through
                  the LOB Buffering Subsystem.

   DESCRIPTION
     This program reads from an input binary/text file, writing into an 
     initialized B/CLOB column in buffered mode. It then reads in buffered
     mode from the B/CLOB column and populates an output file. After building
     the executable (assume it is called cdemolbs), run program as follows:
         cdemolbs src.txt src.bin dst.txt dst.bin
     where src.txt and src.bin are text and binary files of size <= 512Kbytes.
     IMPORTANT: . This program works only for single-byte CLOBs.
                . Before running this program, ensure that the database is
                  started up and a table FOO does not exist in the SCOTT/
                  TIGER sample account.
*/


#include <stdio.h>
#include <string.h>
#include <oci.h>

/---------- Public Constants and Variables ----------------------*/

/* Constants */
#define TRUE       1
#define FALSE      0
#define MAXBUFLEN  32768
#define MAXLBSLEN  524288

/* OCI Handles */
static OCIEnv        *envhp;
static OCIServer     *srvhp;
static OCISvcCtx     *svchp;
static OCIError      *errhp;
static OCISession    *authp;
static OCIStmt       *stmthp;
static OCILobLocator *clob, *blob;
static OCIDefine     *defnp1 = (OCIDefine *) 0, *defnp2 = (OCIDefine *) 0;
static OCIBind       *bndhp = (OCIBind *) 0;

/* Misellaneous */
static FILE          *fp1, *fp2;
static ub4            txtfilelen = 0;
static ub4            binfilelen = 0;
static boolean        istxtfile;
static boolean        tab_exists = FALSE;

/*---------- Public functions - Specification --------------------*/

int  main           (/*_ int argc, char *argv[] _*/);

static sb4  init_handles   (/*_ void _*/);
static sb4  init_table     (/*_ void _*/);
static sb4  log_on         (/*_ void _*/);
static void log_off        (/*_ void _*/);
static sb4  write_lobs     (/*_ int rowind, char *txtfile, char *binfile _*/);
static sb4  read_lobs      (/*_ int rowind, char *txtfile, char *binfile _*/);

/*----------- Private functions - Specification -------------------*/

static sb4  select_clob   (/*_ int rowind _*/);
static sb4  select_blob   (/*_ int rowind _*/);
static sb4  select_lobs   (/*_ int rowind _*/);
static sb4  buf_write_lob (/*_ int rowind, OCILobLocator *locator, FILE *fp,
                               ub4 filelen _*/);
static sb4  buf_read_lob  (/*_ int rowind, OCILobLocator *locator,
                                FILE *fp _*/);
static void drop_table    (/*_ void _*/);
static void report_error  (/*_ void _*/);
static ub4  file_length   (/*_ FILE *fp _*/);

/*----------------- Public functions ----------------------------*/

/*----------------------- main -----------------------------------*/

/* main driver */
int main(argc, argv)
int argc; 
char *argv[];
{
  int rowind;

  /* validate input arguments */
  if (argc != 5)
  {
    (void) printf("Usage: %s srctxtfile srcbinfile desttxtfile destbinfile\n",
                  argv[0]);
    return 0;
  }
  /* initialize OCI handles */
  if (init_handles())
  {
    (void) printf("FAILED: init_handles()\n");
    return OCI_ERROR;
  }
  /* log on to server */
  if (log_on())
  {
    (void) printf("FAILED: log_on()\n");
    return OCI_ERROR;
  }
  /* init demo table */
  if (init_table())
  {
    (void) printf("FAILED: init_table()\n");
    log_off();
    return OCI_ERROR;
  }
  /* write to LOBs in row 1 through the  buffering subsystem, 
                                      reading from src files*/
  rowind = 1;
  if (write_lobs(rowind, argv[1], argv[2]))
  {
    (void) printf("FAILED: write files to lobs\n");
    log_off();
    return OCI_ERROR;
  }
  /* read from LOBs in row 1 through buffering subsystem,
                                  writing to dest files */
  rowind = 1;
  if (read_lobs(rowind, argv[3], argv[4]))
  {
    (void) printf("FAILED: write lobs to files\n");
    log_off();
    return OCI_ERROR;
  }
  /*  clean up and log off from server */
  log_off();

  return OCI_SUCCESS;
}

/*------------------- init_handles --------------------------------*/

/* initialize environment, and allocate all handles */
sb4 init_handles()
{
  if (OCIInitialize((ub4) OCI_DEFAULT, (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;
  }
  /* initialize environment handle */
  if (OCIEnvInit((OCIEnv **) &envhp, (ub4) OCI_DEFAULT,
                 (size_t) 0, (dvoid **) 0 ))
  {
    (void) printf("FAILED: OCIEnvInit()\n");
    return OCI_ERROR;
  }
  /* initialize service context */
  if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &svchp,
                     (ub4) OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc()\n");
    return OCI_ERROR;
  }
  /* initialize error handle */
  if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp,
                     (ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc()\n");
    return OCI_ERROR;
  }
  /* initialize statement handle */ 
  if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &stmthp,
                     (ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc()\n");
    return OCI_ERROR;
  }
  /* initialize server handle */
  if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp,
                     (ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc()\n");
    return OCI_ERROR;
  }
  /* initialize session/authentication handle */
  if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &authp,
                     (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIHandleAlloc()\n");
    return OCI_ERROR;
  }

  /* allocate the lob locator variables */
  if (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &clob, 
                         (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIDescriptorAlloc()\n");
    return OCI_ERROR;
  }
  if (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &blob, 
                         (ub4)OCI_DTYPE_LOB, (size_t) 0, (dvoid **) 0))
  {
    (void) printf("FAILED: OCIDescriptorAlloc()\n");
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}

/*---------------------- init_table --------------------------------*/

/* create table FOO with initialized CLOB, BLOB columns, and insert two rows */
sb4 init_table()
{
  int   colc;
  text *crtstmt = (text *) "CREATE TABLE FOO (C1 CLOB, C2 BLOB, C3 INTEGER)";
  text *insstmt = 
          (text *) "INSERT INTO FOO VALUES (EMPTY_CLOB(), EMPTY_BLOB(), :1)";


  /* prepare create statement */
  if (OCIStmtPrepare(stmthp, errhp, crtstmt, (ub4) strlen((char *) crtstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() crtstmt\n");
    return OCI_ERROR;
  }
  /* execute create statement */
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot *) 0, (OCISnapshot *) 0, 
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() crtstmt\n");
    return OCI_ERROR;
  }
  /* prepare insert statement */
  if (OCIStmtPrepare(stmthp, errhp, insstmt, (ub4) strlen((char *) insstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() insstmt\n");
    return OCI_ERROR;
  }
  /* associate variable colc with bind placeholder #1 in the SQL statement */
  if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1,
                      (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIBindByPos()\n");
    return OCI_ERROR;
  }
  /* insert two rows */
  for (colc = 1; colc <= 2; colc++)
  {
    if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                       (CONST OCISnapshot *) 0, (OCISnapshot *) 0, 
                       (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtExecute() insstmt\n");
      return OCI_ERROR;
    }
  }

  /* commit the Xn */
  (void) OCITransCommit(svchp, errhp, (ub4)0);

  /* set flag to be used by log_off() to drop the table */
  tab_exists = TRUE;

  return OCI_SUCCESS;
}


/*----------------------- log_on ---------------------------------*/

/* attach to the server and log on as SCOTT/TIGER */
sb4 log_on()
{
  text *uid     = (text *)"SCOTT";
  text *pwd     = (text *)"TIGER";
  text *cstring = (text *)"inst1_alias";

  /* attach to the server */
  if (OCIServerAttach(srvhp, errhp, (text *) cstring,
                     (sb4) strlen((char *)cstring), (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIServerAttach()\n");
    return OCI_ERROR;
  }
 
  /* set username and password attributes of the server handle */
  if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) uid, (ub4) strlen((char *)uid),
                 (ub4) OCI_ATTR_USERNAME, errhp))
  {
    (void) printf("FAILED: OCIAttrSet()\n");
    return OCI_ERROR;
  }
  if (OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                 (dvoid *) pwd, (ub4) strlen((char *)pwd),
                 (ub4) OCI_ATTR_PASSWORD, errhp))
  {
    (void) printf("FAILED: OCIAttrSet()\n");
    return OCI_ERROR;
  }

  /* set the server attribute in the service context */
  if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                 (dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER, errhp))
  {
    (void) printf("FAILED: OCIAttrSet()\n");
    return OCI_ERROR;
  }
 
  /* log on */
  if (OCISessionBegin(svchp, errhp, authp, (ub4) OCI_CRED_RDBMS,
                     (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCISessionBegin()\n");
    return OCI_ERROR;
  }
 
  /* set the session attribute in the service context */
  if (OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) authp,
                 (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp))
  {
    (void) printf("FAILED: OCIAttrSet()\n");
    return OCI_ERROR;
  } 
  return OCI_SUCCESS;
}

/*-------------------------- logoff -------------------------------*/

/* Logoff and disconnect from the server.  Free handles */
void log_off()
{
  if (tab_exists)
    drop_table();

  (void) OCISessionEnd(svchp, errhp, authp, (ub4) 0);
  (void) OCIServerDetach(srvhp, errhp, (ub4) OCI_DEFAULT);
 
  (void) printf("Logged off and detached from server.\n");
 
  (void) OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER);
  (void) OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX);
  (void) OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR);
  (void) OCIHandleFree((dvoid *) authp, (ub4) OCI_HTYPE_SESSION);
  (void) OCIDescriptorFree((dvoid *) clob, (ub4) OCI_DTYPE_LOB);
  (void) OCIDescriptorFree((dvoid *) blob, (ub4) OCI_DTYPE_LOB);
  (void) OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT);

  (void) printf("All handles freed\n");
  return;
} 

/*-------------------- write_lobs -------------------------------*/

/* write from files to LOBs */
sb4 write_lobs (rowind, txtfile, binfile)
int   rowind;
char *txtfile;
char *binfile;
{
  ub4   loblen = 0;
  text *svptstmt = (text *)"SAVEPOINT cdemolbs_svpt";
  text *rlbkstmt = (text *)"ROLLBACK TO SAVEPOINT cdemolbs_svpt";
  ub4   txtfilelen = 0;
  ub4   binfilelen = 0;

  /* validate row indicator */
  if (!rowind || (rowind > 2))
  {
    (void) printf("ERROR: Invalid row indicator.\n");
    return OCI_ERROR;
  }
  /* open source files */
  fp1 = fopen((CONST char *)txtfile, (CONST char *) "r");
  fp2 = fopen((CONST char *)binfile, (CONST char *) "r");
  if (!(fp1 && fp2))
  {
    (void) printf("ERROR: Failed to open file(s).\n");
    return -1;
  }
  if ((txtfilelen = file_length(fp1)) > MAXLBSLEN)
  {
    (void) printf("ERROR: %s - length > 512Kbytes", txtfile);
    return -1;
  }
  if ((binfilelen = file_length(fp2)) > MAXLBSLEN)
  {
    (void) printf("ERROR: %s - length > 512Kbytes", binfile);
    return -1;
  }

  /* reset file pointers to start of file */
  (void) fseek(fp1, 0, 0);
  (void) fseek(fp2, 0, 0);

  /* set savepoint for Xn before commencing buffered mode operations */
  if (OCIStmtPrepare(stmthp, errhp, svptstmt, (ub4) strlen((char *)svptstmt),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() svptstmt\n");
    return OCI_ERROR;
  }
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                     (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() svptstmt\n");
    report_error();
    return OCI_ERROR;
  }

  (void) printf("\n===> Writing CLOB from txtfile in buffered mode.....\n\n");

  /* fetch the CLOB's locator from the table for update */
  if (select_clob(rowind))
  {
    (void) printf("FAILED: select_clob()\n");
    log_off();
    return OCI_ERROR;
  }
  /* report LOB length before buffered write begins */
  (void) OCILobGetLength(svchp, errhp, clob, &loblen);
  (void) printf("Before buffered write, CLOB length = %d\n\n", loblen);

  /* enable the CLOB locator for buffering operations */
  if (OCILobEnableBuffering(svchp, errhp, clob))
  {
    (void) printf("FAILED: OCILobEnableBuffering() CLOB\n");
    return OCI_ERROR;
  }
  /* write the text file contents into CLOB through the buffering subsystem */
  if (buf_write_lob(rowind, clob, fp1, txtfilelen) > 0)
  {
    /* if buffered write operation failed, rollback Xn to savepoint & exit */
    if (OCIStmtPrepare(stmthp, errhp, rlbkstmt, 
                    (ub4) strlen((char *)rlbkstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtPrepare() rlbkstmt\n");
      return OCI_ERROR;
    }
    if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                    (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtExecute() rlbkstmt\n");
      report_error();
      return OCI_ERROR;
    }

    (void) printf("FAILED: buf_write_lob() CLOB\n");
    return OCI_ERROR;
  }
  /* commit the Xn if the CLOB's buffer was flushed successfully */
  (void) OCITransCommit(svchp, errhp, (ub4)0);

  /* disable CLOB locator from buffering */
  if (OCILobDisableBuffering(svchp, errhp, clob))
  {
    (void) printf("FAILED: OCILobDisableBuffering() CLOB\n");
    return OCI_ERROR;
  }

  (void) printf("\n===> Writing BLOB from binfile in buffered mode.....\n\n");

  /* fetch the BLOB's locator from the table for update */
  if (select_blob(rowind))
  {
    (void) printf("FAILED: select_blob()\n");
    log_off();
    return OCI_ERROR;
  }
  /* report LOB length before buffered write begins */
  (void) OCILobGetLength(svchp, errhp, blob, &loblen);
  (void) printf("Before buffered write, BLOB length = %d\n\n", loblen);

  /* enable the BLOB locator for buffering operations */
  if (OCILobEnableBuffering(svchp, errhp, blob))
  {
    (void) printf("FAILED: OCILobEnableBuffering() BLOB\n");
    return OCI_ERROR;
  }
  /* write the bin file contents into BLOB through the buffering subsystem */
  if (buf_write_lob(rowind, blob, fp2, binfilelen) > 0)
  {
    /* if buffered write operation failed, rollback Xn to savepoint & exit */
    if (OCIStmtPrepare(stmthp, errhp, rlbkstmt, 
                    (ub4) strlen((char *)rlbkstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtPrepare() rlbkstmt\n");
      return OCI_ERROR;
    }
    if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                    (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtExecute() rlbkstmt\n");
      report_error();
      return OCI_ERROR;
    }

    (void) printf("FAILED: buf_write_lob() BLOB\n");
    return OCI_ERROR;
  }
  /* commit the Xn if the BLOB's buffer was flushed successfully */
  (void) OCITransCommit(svchp, errhp, (ub4)0);

  /* disable BLOB locator from buffering */
  if (OCILobDisableBuffering(svchp, errhp, blob))
  {
    (void) printf("FAILED: OCILobDisableBuffering() BLOB\n");
    return OCI_ERROR;
  }

  /* close input files */
  (void) fclose(fp1);
  (void) fclose(fp2);

  return OCI_SUCCESS;
}

/*--------------------- read_lobs --------------------------------*/

/* read from LOBs into files */
sb4 read_lobs (rowind, txtfile, binfile)
int   rowind;
char *txtfile;
char *binfile;
{
  ub4 loblen = 0;
  text *svptstmt = (text *)"SAVEPOINT cdemolbs_svpt";
  text *rlbkstmt = (text *)"ROLLBACK TO SAVEPOINT cdemolbs_svpt";

  if (!rowind || (rowind > 2))
  {
    (void) printf("ERROR: Invalid row indicator.\n");
    return -1;
  }

  /* open destination files */
  fp1 = fopen((CONST char *)txtfile, (CONST char *) "w");
  fp2 = fopen((CONST char *)binfile, (CONST char *) "w");
  if (!(fp1 && fp2))
  {
    (void) printf("ERROR: Failed to open file(s).\n");
    return -1;
  }

  /* reset file pointers to start of file */
  (void) fseek(fp1, 0, 0);
  (void) fseek(fp2, 0, 0);

  /* fetch the BLOB's locator from the table for reads */
  if (select_lobs(rowind))
  {
    (void) printf("FAILED: select_lobs()\n");
    log_off();
    return OCI_ERROR;
  }
  /* report CLOB length before buffered read begins */
  (void) OCILobGetLength(svchp, errhp, clob, &loblen);
  (void) printf("Before buffered read, CLOB length = %d\n\n", loblen);

  /* report BLOB length before buffered read begins */
  (void) OCILobGetLength(svchp, errhp, blob, &loblen);
  (void) printf("Before buffered read, BLOB length = %d\n\n", loblen);

  /* set savepoint for Xn before commencing buffered mode operations */
  if (OCIStmtPrepare(stmthp, errhp, svptstmt, (ub4) strlen((char *)svptstmt),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() svptstmt\n");
    return OCI_ERROR;
  }
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                     (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                     (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() svptstmt\n");
    report_error();
    return OCI_ERROR;
  }

  /* enable the locators for buffering operations */
  if (OCILobEnableBuffering(svchp, errhp, clob))
  {
    (void) printf("FAILED: OCILobEnableBuffering() CLOB\n");
    return OCI_ERROR;
  }
  if (OCILobEnableBuffering(svchp, errhp, blob))
  {
    (void) printf("FAILED: OCILobEnableBuffering() BLOB\n");
    return OCI_ERROR;
  }

  (void) printf("\n===> Reading CLOB into dst.txt in buffered mode...\n\n");

  /* read the CLOB into buffer and write the contents to a text file */
  if (buf_read_lob(rowind, clob, fp1) > 0)
  {
    /* if buffered read operation failed, rollback Xn to savepoint & exit */
    if (OCIStmtPrepare(stmthp, errhp, rlbkstmt, 
                    (ub4) strlen((char *)rlbkstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtPrepare() rlbkstmt\n");
      return OCI_ERROR;
    }
    if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                    (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtExecute() rlbkstmt\n");
      report_error();
      return OCI_ERROR;
    }

    (void) printf("FAILED: buf_read_lob() CLOB\n");
    return OCI_ERROR;
  }

  (void) printf("\n===> Reading BLOB into dst.bin in buffered mode...\n\n");

  /* read the BLOB into buffer and write the contents to a binary file */
  if (buf_read_lob(rowind, blob, fp2) > 0)
  {
    /* if buffered read operation failed, rollback Xn to savepoint & exit */
    if (OCIStmtPrepare(stmthp, errhp, rlbkstmt, 
                    (ub4) strlen((char *)rlbkstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtPrepare() rlbkstmt\n");
      return OCI_ERROR;
    }
    if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                    (ub4) OCI_DEFAULT))
    {
      (void) printf("FAILED: OCIStmtExecute() rlbkstmt\n");
      report_error();
      return OCI_ERROR;
    }

    (void) printf("FAILED: buf_read_clob()\n");
    return OCI_ERROR;
  }

  /* commit the Xn if buffered reads went off successfully */
  (void) OCITransCommit(svchp, errhp, (ub4)0);

  /* disable locator for buffering */
  if (OCILobDisableBuffering(svchp, errhp, clob))
  {
    (void) printf("FAILED: OCILobDisableBuffering() \n");
    return OCI_ERROR;
  }
  if (OCILobDisableBuffering(svchp, errhp, blob))
  {
    (void) printf("FAILED: OCILobDisableBuffering() \n");
    return OCI_ERROR;
  }

  /* close output files */
  (void) fclose(fp1);
  (void) fclose(fp2);

  return OCI_SUCCESS;
}

/*----------------- Public functions ----------------------------*/

/*------------------ select_clob -------------------------------*/

/* select locator from the CLOB column */
sb4 select_clob(rowind)
int rowind;
{
  int   colc    = rowind;
  text *sqlstmt = (text *)"SELECT C1 FROM FOO WHERE C3 = :1 FOR UPDATE";
  /* we need the 'FOR UPDATE' clause since we need to write to the lobs */

  /* prepare select statement */
  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n");
    return OCI_ERROR;
  }
  /* associate variable colc with bind placeholder #1 in the SQL statement */
  if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1,
                      (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIBindByPos()\n");
    return OCI_ERROR;
  }
  /* associate clob var with its define handle */ 
  if (OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                     (dvoid *) &clob, (sb4) -1, (ub2) SQLT_CLOB,
                     (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIDefineByPos() CLOB\n");
    return OCI_ERROR;
  }
  /* execute the select and fetch one row */
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() sqlstmt\n");
    return OCI_ERROR;
  }
  return OCI_SUCCESS;
}          

/*------------------------------- select_blob ---------------------------*/

/* select locator from the BLOB column */
sb4 select_blob(rowind)
int rowind;
{
  int   colc    = rowind;
  text *sqlstmt = (text *)"SELECT C2 FROM FOO WHERE C3 = :1 FOR UPDATE";
  /* we need the 'FOR UPDATE' clause since we need to write to the lobs */

  /* prepare select statement */
  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n");
    return OCI_ERROR;
  }
  /* associate variable colc with bind placeholder #1 in the SQL statement */
  if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1,
                      (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIBindByPos()\n");
    return OCI_ERROR;
  }
  /* associate blob var with its define handle */ 
  if (OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 1,
                     (dvoid *) &blob, (sb4) -1, (ub2) SQLT_BLOB,
                     (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIDefineByPos()\n");
    return OCI_ERROR;
  }
  /* execute the select and fetch one row */
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() sqlstmt\n");
    return OCI_ERROR;
  }
  return OCI_SUCCESS;
}          


/*------------------- select_lobs -------------------------------*/

/* select lob locators from the CLOB, BLOB columns */
sb4 select_lobs(rowind)
int rowind;
{
  int   colc    = rowind;
  text *sqlstmt = (text *)"SELECT C1, C2 FROM FOO WHERE C3 = :1";
  /* we don't need the 'FOR UPDATE' clause since 
                    we are just reading the LOBs */
 
  /* prepare select statement */
  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n");
    return OCI_ERROR;
  }
  /* associate variable colc with bind placeholder #1 in the SQL statement */
  if (OCIBindByPos(stmthp, &bndhp, errhp, (ub4) 1,
                      (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT,
                      (dvoid *) 0, (ub2 *)0, (ub2 *)0,
                      (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIBindByPos()\n");
    return OCI_ERROR;
  }
  /* associate clob and blob vars with their define handles */
  if (OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                     (dvoid *) &clob, (sb4) -1, (ub2) SQLT_CLOB,
                     (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)
  ||  OCIDefineByPos(stmthp, &defnp2, errhp, (ub4) 2,
                     (dvoid *) &blob, (sb4) -1, (ub2) SQLT_BLOB,
                     (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIDefineByPos()\n");
    return OCI_ERROR;
  }
  /* execute the select and fetch one row */
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot*) 0, (OCISnapshot*) 0,
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() sqlstmt\n");
    return OCI_ERROR;
  }
  return OCI_SUCCESS;
} 

/*-------------------- buf_write_lob -----------------------------*/
/*
 * Read operating system files into local buffers and then write these local
 * buffers to LOBs using buffering system.
 */
sb4 buf_write_lob(rowind, locator, fp, filelen)
int rowind; 
OCILobLocator *locator; 
FILE *fp; 
ub4 filelen;
{
  ub4 offset = 1;
  ub1 bufp[MAXBUFLEN];
  ub4 amtp;
  ub4 nbytes = 0;
  ub4 remainder = filelen;

  /* reset per read/write buffer size and perform the first read */
  amtp = nbytes = (filelen > MAXBUFLEN) ? MAXBUFLEN : filelen;

  /* write into the LOB's client-side buffer 
                  (upto max 16 pages of 32K each) */
  while (remainder > 0)
  {
    if (fread((void *)bufp, (size_t)nbytes, (size_t)1, fp) != 1)
    {
      (void) printf("ERROR: read file.\n");
      return(OCI_ERROR);
    }
    if (feof(fp))
    {
      (void) printf("Exit - End of file reached\n", amtp, offset);
      break;
    }
    (void) printf("Write %d bytes out of remaining %d bytes at off %d\n",
                  amtp, remainder, offset);
    if (OCILobWrite(svchp, errhp, locator, &amtp, (ub4) offset,
                    (dvoid *) bufp,
                    (ub4) nbytes, OCI_ONE_PIECE, (dvoid *)0,
                    (sb4 (*)(dvoid *, dvoid *, ub4 *, ub1 *)) 0,
                    (ub2) 0, (ub1) SQLCS_IMPLICIT))
    {
      (void) printf("FAILED: OCILobWrite() \n");
      return(OCI_ERROR);
    }
    if (amtp < nbytes)
    {
      (void) printf("FAILED: Full file not written \n");
      return(OCI_ERROR);
    }
    amtp = nbytes;
    offset += nbytes;
    remainder -= nbytes;
  }

  /* flush the buffers back to the server */
  (void) printf("Flush LOB's buffer to server\n");
  if (OCILobFlushBuffer(svchp, errhp, locator, OCI_LOB_BUFFER_NOFREE))
  {
    (void) printf("FAILED: OCILobFlushBuffer() \n");
    return OCI_ERROR;
  }
  return OCI_SUCCESS;
}

/*--------------------------------- buf_read_lob ----------------------------*/

/*
 * Read LOBs using buffered mode into local buffers and writes them into
 * operating system files.
 */
sb4 buf_read_lob(rowind, locator, fp)
int rowind; 
OCILobLocator *locator; 
FILE *fp;
{
  ub4 offset = 1;
  ub1 bufp[MAXBUFLEN];
  ub4 amtp = 0;
  ub4 nbytes = 0;

  /* set amount to be read per iteration */
  amtp = nbytes = MAXBUFLEN;

  /*
   * read from CLOB and write to text file (in the process, populating upto
   * 16 pages of 32K each in the buffering subsystem).
   */
  while (amtp)
  {
    (void) printf("Reading %d bytes from offset %d\n", amtp, offset);
    if (OCILobRead(svchp, errhp, locator, &amtp, (ub4) offset, (dvoid *) bufp,
                   (ub4) nbytes, (dvoid *)0,
                   (sb4 (*)(dvoid *, CONST dvoid *, ub4, ub1)) 0,
                   (ub2) 0, (ub1) SQLCS_IMPLICIT))
    {
      (void) printf("FAILED: OCILobRead() \n");
      return OCI_ERROR;
    }
    (void) fwrite((void *)bufp, (size_t)amtp, (size_t)1, fp);       /* write 
buffer to file */
    offset += nbytes;
  }
  return OCI_SUCCESS;
}

/*---------------------- drop_table ------------------------------*/
 
/* Drop table FOO before logging off from the server */
void drop_table()
{
  text *dropstmt = (text *) "DROP TABLE FOO";

  /* prepare drop statement */ 
  if (OCIStmtPrepare(stmthp, errhp, dropstmt, (ub4) strlen((char *) dropstmt),
                    (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtPrepare() dropstmt\n");
    return;
  }
  /* execute drop statement */  
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                    (CONST OCISnapshot *) 0, (OCISnapshot *) 0,
                    (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() dropstmt\n");
    return;
  }
  return;
}

/*---------------------- report_error -----------------------------*/

/* retrieve error message and print it out */ 
void report_error()
{
  text msgbuf[512];
  sb4  errcode = 0;

  (void) OCIErrorGet((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                       msgbuf, (ub4) sizeof(msgbuf), (ub4) OCI_HTYPE_ERROR);
  (void) printf("ERROR CODE = %d\n", errcode);
  (void) printf("%.*s\n", 512, msgbuf);
  return;
}

/*---------------------- file_length ------------------------------*/

/* get the length of the input file */
ub4 file_length(fp)
FILE *fp;
{
  fseek(fp, 0, SEEK_END);
  return (ub4) (ftell(fp));
}


Example 7, REF Pinning and Navigation

/*
   NAME
     cdemobj.c

   DESCRIPTION
     Demo of selection of a REF and display the pinned object through
     navigational interface.
*/

#ifndef CDEMO_OBJ_ORACLE
#include "cdemobj.h"
#endif

/* statement to select a ref from an extent table customer_tab */
static const text *const selref = (text *)
          "SELECT REF(customer_tab) from customer_tab";

/* statement to create the type address */
static const text *const create_type_address = (text *)
"CREATE TYPE address AS OBJECT (\
  no       NUMBER,\
  street   VARCHAR(60),\
  state    CHAR(2),\
  zip      CHAR(10)\
)";

/* statement to create the typed table address_tab */
static const text *const create_type_addr_tab = (text *)
"create type addr_tab is table of address";

/* statement to create the type person */
static const text *const create_type_person = (text *)
"CREATE TYPE person AS OBJECT (\
  firstname         CHAR(20),\
  lastname          varchar(20),\
  age               int,\
  salary            float,\
  bonus             double precision,\
  retirement_fund   int,\
  number_of_kids    smallint,\
  years_of_school   numeric(10, 2),\
  preaddr           addr_tab,\
  birthday          date,\
  number_of_pets    real,\
  comment1          raw(200),\
  comment2          clob,\
  comment3          varchar2(200),\
  addr              ADDRESS\
)";

/* statement to create the type customer */
static const text *const create_type_customer = (text *)
"CREATE TYPE customer AS OBJECT (\
  account       char(20),\
  aperson       REF person\
)";

/* statement to create the typed table person */
static const text *const create_table_person = (text *)
"create table person_tab of person nested table preaddr store as 
person_preaddr_table";

/* statement to create the typed table customer_tab */
static const text *const create_table_customer = (text *)
"create table customer_tab of customer";

/* statement to insert data into table customer_tab */
static const text *const insert_customer = (text *)
"insert into customer_tab values('00001', null)";

/* statement to insert data into table person_tab */
static const text *const insert_person = (text *)
"insert into person_tab values('Sandy', 'Wood', 25, 32000, 10000, 20000, 3,\
                          15, addr_tab(),\
                          to_date('1961 08 23', 'YYYY MM DD'), 2,\
                          '1234567890', 'This is a test', 'This is a test',\
                          ADDRESS(8888, 'Fenley Road', 'CA', '91406'))";

/* statement to insert data into the nested table in person_tab */
static const text *const insert_address1 = (text *)
"insert into the (select preaddr from person_tab where\
                firstname='Sandy') values\
                (715, 'South Henry', 'ca', '95117')";
static const text *const insert_address2 = (text *)
"insert into the (select preaddr from person_tab where\
                firstname='Sandy') values\
                (6830, 'Woodley Ave', 'ca', '90416')";

/* statement to update the ref in the table customer_tab */
static const text *const update_customer = (text *)
"update customer_tab set aperson = (select ref(person_tab)\
                              from person_tab where\
                              firstname = 'Sandy')";

/***************************************************************************
*  Check the error and display the error message                           *
****************************************************************************/
static void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512]; 
  sb4 errcode; 

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    break;
  case OCI_NEED_DATA:
    break;
  case OCI_NO_DATA:
    break;
  case OCI_ERROR:  /* get the error back and display on the screen */
    (void) OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
                    errbuf, (ub4) sizeof(errbuf), (ub4) OCI_HTYPE_ERROR);
    (void) printf("Error - %s\n", errbuf);
    break;
  case OCI_INVALID_HANDLE:
    break;
  case OCI_STILL_EXECUTING:
    break;
  case OCI_CONTINUE:
    break;
  default:
    break;
  }
}
/****************************************************************************
*  Display attribute value of an object                                *
****************************************************************************/
static void display_attr_val(envhp, errhp, names, typecode, attr_value)
OCIEnv *envhp;            /* environment handle        */
OCIError *errhp;            /* error handle              */
text    *names;            /* the name of the attribute */
OCITypeCode   typecode;          /* the type code             */
dvoid   *attr_value;       /* the value pointer         */
{
  text           str_buf[200];
  double         dnum;
  ub4            text_len, str_len;
  OCIRaw         *raw = (OCIRaw *) 0;
  OCIString        *vs = (OCIString *) 0;
 
  /* display the data based on the type code */
  switch (typecode)
  {
     case OCI_TYPECODE_DATE :                    /* fixed length string */
         str_len = 200;
         (void) OCIDateToText(errhp, (CONST OCIDate *) attr_value, 
                              (CONST text*)
            "Month dd, SYYYY, HH:MI A.M.",
            (ub1) 27, (CONST text*) "American", (ub4) 8,
             (ub4 *)&str_len, str_buf);
         str_buf[str_len+1] = '\0';
         (void) printf("attr %s = %s\n", names, (text *) str_buf);
         break; 
     case OCI_TYPECODE_RAW :                                /* RAW */
         raw = *(OCIRaw **) attr_value;
       (void) printf("attr %s = %s\n", names, (text *) OCIRawPtr(envhp, raw));
         break; 
     case OCI_TYPECODE_CHAR :                      /* fixed length string */
     case OCI_TYPECODE_VARCHAR :                              /* varchar  */
     case OCI_TYPECODE_VARCHAR2 :                             /* varchar2 */
         vs = *(OCIString **) attr_value;
         (void) printf("attr %s = %s\n", names, (text *) 
                     OCIStringPtr(envhp, vs));
         break; 
     case OCI_TYPECODE_SIGNED8:                            /* BYTE - sb1  */
         (void) printf("attr %s = %d\n", names, *(sb1 *) attr_value);
         break; 
     case OCI_TYPECODE_UNSIGNED8:                 /* UNSIGNED BYTE - ub1  */
         (void) printf("attr %s = %d\n", names, *(ub1 *) attr_value);
         break; 
     case OCI_TYPECODE_OCTET:                                /* OCT  */
         (void) printf("attr %s = %d\n", names, *(ub1 *) attr_value);
         break; 
     case OCI_TYPECODE_UNSIGNED16:                     /* UNSIGNED SHORT  */
     case OCI_TYPECODE_UNSIGNED32:                      /* UNSIGNED LONG  */
     case OCI_TYPECODE_REAL:                                   /* REAL    */
     case OCI_TYPECODE_DOUBLE:                                 /* DOUBLE  */
     case OCI_TYPECODE_INTEGER:                                   /* INT  */
     case OCI_TYPECODE_SIGNED16:                                /* SHORT  */
     case OCI_TYPECODE_SIGNED32:                                 /* LONG  */
     case OCI_TYPECODE_DECIMAL:                               /* DECIMAL  */
     case OCI_TYPECODE_FLOAT:                                 /* FLOAT    */
     case OCI_TYPECODE_NUMBER:                                /* NUMBER   */
     case OCI_TYPECODE_SMALLINT:                              /* SMALLINT */
         (void) OCINumberToReal(errhp, (CONST OCINumber *) attr_value, 
                             (uword)sizeof(dnum),
                      (dvoid *) &dnum);
         (void) printf("attr %s = %f\n", names, dnum);
         break;
     default: 
         (void) printf("attr %s - typecode %d\n", names, typecode);
         break;
    }
}


/****************************************************************************
*  Dump the info of any object                                      *
****************************************************************************/
static void dump_object(envhp, errhp, svchp, tdo, obj, null_obj)
OCIEnv *envhp;             /* environment handle           */
OCIError *errhp;           /* error handle                 */
OCISvcCtx *svchp;          /* service handle               */
OCIType  *tdo;             /* type descriptor             */
dvoid   *obj;              /* object pointer               */
dvoid   *null_obj;         /* parallel null struct pointer */
{
  text           *names[50];
  text           *lengths[50];
  text           *indexes[50];
  ub2             count, pos;
  OCITypeElem         *ado;
  ub4             text_len, str_len;
  ub4             i;
  OCITypeCode           typecode;
  OCIInd          attr_null_status;
  dvoid          *attr_null_struct;
  dvoid          *attr_value;
  OCIType         *attr_tdo, *element_type;
  dvoid          *object;
  dvoid          *null_object;
  OCIType         *object_tdo;
  ub1             status;
  OCIRef         *type_ref;
  text           str_buf[200];
  double         dnum;
  dvoid          *element = (dvoid *) 0, *null_element = (dvoid *) 0;
  boolean        exist, eoc, boc;
  sb4            index;
  OCIDescribe    *dschp = (OCIDescribe *) 0, *dschp1 = (OCIDescribe *) 0;
  text           *namep, *typenamep;
  dvoid          *list_attr;
  OCIIter        *itr = (OCIIter *) 0;
  dvoid     *parmp = (dvoid *) 0, *parmdp = (dvoid *) 0, *parmp1 = (dvoid *) 0,
                 *parmp2 = (dvoid *) 0;  
  OCIRef         *elem_ref = (OCIRef *) 0;

  checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &dschp,
                        (ub4) OCI_HTYPE_DESCRIBE,
                        (size_t) 0, (dvoid **) 0));
 
  checkerr(errhp, OCIDescribeAny(svchp, errhp, (dvoid *) tdo,
                  (ub4) 0, OCI_OTYPE_PTR, (ub1)1,
                  (ub1) OCI_PTYPE_TYPE, dschp));

  checkerr(errhp, OCIAttrGet((dvoid *) dschp, (ub4) OCI_HTYPE_DESCRIBE,
                  (dvoid *)&parmp, (ub4 *)0, (ub4)OCI_ATTR_PARAM, errhp));
 
  checkerr(errhp, OCIAttrGet((dvoid*) parmp,(ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &typenamep, (ub4 *) &str_len,
                    (ub4) OCI_ATTR_NAME, (OCIError *) errhp));
  typenamep[str_len] = '\0';

  printf("starting displaying instance of type '%s'\n", typenamep);

  /* loop through all attributes in the type */
  checkerr(errhp, OCIAttrGet((dvoid*) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &count, (ub4 *) 0,
                    (ub4) OCI_ATTR_NUM_TYPE_ATTRS, (OCIError *) errhp));

  checkerr(errhp, OCIAttrGet((dvoid *) parmp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid *)&list_attr, (ub4 *)0,
                    (ub4)OCI_ATTR_LIST_TYPE_ATTRS, (OCIError *)errhp));

  /* loop through all attributes in the type */
  for (pos = 1; pos <= count; pos++)
  {

    checkerr(errhp, OCIParamGet((dvoid *) list_attr,
                       (ub4) OCI_DTYPE_PARAM, errhp,
                       (dvoid *)&parmdp, (ub4) pos));
 
    checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                    (dvoid*) &namep, (ub4 *) &str_len,
                    (ub4) OCI_ATTR_NAME, (OCIError *) errhp));
    namep[str_len] = '\0';
 
    /* get the attribute */
    if (OCIObjectGetAttr(envhp, errhp, obj, null_obj, tdo,
                  &namep, &str_len, 1, 
                  (ub4 *)0, 0, &attr_null_status, &attr_null_struct, 
                  &attr_value, &attr_tdo) != OCI_SUCCESS)
      (void) printf("BUG -- OCIObjectGetAttr, expect OCI_SUCCESS.\n");

    /* get the type code of the attribute */
    checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM,
                      (dvoid*) &typecode, (ub4 *) 0,
                      (ub4) OCI_ATTR_TYPECODE,
                      (OCIError *) errhp));

    /* support only fixed length string, ref and embedded object */
    switch (typecode)
    {
       case OCI_TYPECODE_OBJECT :                     /* embedded object */
           printf("attribute %s is an embedded object.
                      Display instance ....\n",
                    namep);
           /* recursive call to dump nested object data */
           dump_object(envhp, errhp, svchp, attr_tdo, attr_value, 
                            attr_null_struct);
           break; 
       case OCI_TYPECODE_REF :                        /* embedded object */
           printf("attribute %s is a ref. Pin and display instance ...\n",
                     namep);
           /* pin the object */
           if (OCIObjectPin(envhp, errhp, *(OCIRef **)attr_value, 
                     (OCIComplexObject *)0, OCI_PIN_ANY, 
                     OCI_DURATION_SESSION,   OCI_LOCK_NONE, 
                     (dvoid **)&object) != OCI_SUCCESS)
             (void) printf("BUG -- OCIObjectPin, expect OCI_SUCCESS.\n");
           /* allocate the ref */
           if (( status = OCIObjectNew(envhp, errhp, svchp, OCI_TYPECODE_REF, 
                    (OCIType *)0, 
                    (dvoid *)0, OCI_DURATION_DEFAULT, TRUE, (dvoid **) 
                    &type_ref)) 
                    != OCI_SUCCESS)
             (void) printf("BUG -- OCIObjectNew, expect OCI_SUCCESS.\n");
           /* get the ref of the type from the object */
           if (( status = OCIObjectGetTypeRef(envhp, errhp, object, type_ref)) 
                    != OCI_SUCCESS)
             (void) printf("BUG -- ORIOGTR, expect OCI_SUCCESS.\n");
           /* pin the type ref to get the type object */
           if (OCIObjectPin(envhp, errhp, type_ref,  (OCIComplexObject *)0, 
                     OCI_PIN_ANY, OCI_DURATION_SESSION, OCI_LOCK_NONE,
                    (dvoid **)&object_tdo) !=
                     OCI_SUCCESS)
             (void) printf("BUG -- OCIObjectPin, expect OCI_SUCCESS.\n");
           /* get null struct of the object */
           if (( status = OCIObjectGetInd(envhp, errhp, object, 
                               &null_object)) != OCI_SUCCESS)
             (void) printf("BUG -- ORIOGNS, expect OCI_SUCCESS.\n");
           /* call the function recursively to dump the pinned object */
           dump_object(envhp, errhp, svchp, object_tdo, object, 
                    null_object);
       case OCI_TYPECODE_NAMEDCOLLECTION:
           checkerr(errhp, OCIHandleAlloc((dvoid *) envhp, (dvoid **) &dschp1,
                        (ub4) OCI_HTYPE_DESCRIBE,
                        (size_t) 0, (dvoid **) 0));
 
           checkerr(errhp, OCIDescribeAny(svchp, errhp, (dvoid *) attr_tdo,
                  (ub4) 0, OCI_OTYPE_PTR, (ub1)1,
                  (ub1) OCI_PTYPE_TYPE, dschp1));

           checkerr(errhp, OCIAttrGet((dvoid *) dschp1, (ub4) 
                  OCI_HTYPE_DESCRIBE,
                  (dvoid *)&parmp1, (ub4 *)0, (ub4)OCI_ATTR_PARAM, errhp));
 
           /* get the collection type code of the attribute */
           checkerr(errhp, OCIAttrGet((dvoid*) parmp1, (ub4) OCI_DTYPE_PARAM,
                      (dvoid*) &typecode, (ub4 *) 0,
                      (ub4) OCI_ATTR_COLLECTION_TYPECODE,
                      (OCIError *) errhp));
           switch (typecode)
           {
             case OCI_TYPECODE_VARRAY:                /* variable array */
               (void) printf
               ("\n---> Dump the table from the top to the bottom.\n");
               checkerr(errhp, OCIAttrGet((dvoid*) parmp1, (ub4) 
                      OCI_DTYPE_PARAM,
                      (dvoid*) &parmp2, (ub4 *) 0,
                      (ub4) OCI_ATTR_COLLECTION_ELEMENT,
                      (OCIError *) errhp));
               checkerr(errhp, OCIAttrGet((dvoid*) parmp2,
                       (ub4) OCI_DTYPE_PARAM,
                       (dvoid*) &elem_ref, (ub4 *) 0,
                       (ub4) OCI_ATTR_REF_TDO,
                       (OCIError *) errhp));
               checkerr(OCITypeByRef(envhp, errhp, elem_ref, OCI_PIN_DEFAULT, 
                       0, &element_type));
               /* initialize the iterator */
               checkerr(errhp, OCIIterCreate(envhp, errhp, (CONST OCIColl*) 
                       attr_value, &itr));
               /* loop through the iterator */
               for(eoc = FALSE;!OCIIterNext(envhp, errhp, itr, (dvoid **) 
                                 &element,
                                   (dvoid **)&null_element, &eoc) && !eoc;)
               {
             /* if type is named type, call the same function recursively */
                 if (typecode == OCI_TYPECODE_OBJECT)
                   dump_object(envhp, errhp, svchp, element_type, element,
                 null_element);
                 else  /* else, display the scaler type attribute */
                   display_attr_val(envhp, errhp, namep, typecode, element);
               }
               break;

             case OCI_TYPECODE_TABLE:                    /* nested table */
               (void) printf
               ("\n---> Dump the table from the top to the bottom.\n");
               /* go to the first element and print out the index */
               checkerr(errhp, OCIAttrGet((dvoid*) parmp1, (ub4) 
                      OCI_DTYPE_PARAM,
                      (dvoid*) &parmp2, (ub4 *) 0,
                      (ub4) OCI_ATTR_COLLECTION_ELEMENT,
                      (OCIError *) errhp));
               checkerr(errhp, OCIAttrGet((dvoid*) parmp2,
                       (ub4) OCI_DTYPE_PARAM,
                       (dvoid*) &elem_ref, (ub4 *) 0,
                       (ub4) OCI_ATTR_REF_TDO,
                       (OCIError *) errhp));
               checkerr(errhp, OCITypeByRef(envhp, errhp, elem_ref, 
                       OCI_DURATION_SESSION,
                       OCI_TYPEGET_HEADER, &element_type));
               attr_value = *(dvoid **)attr_value;
               /* move to the first element in the nested table */
               checkerr(errhp, OCITableFirst(envhp, errhp, (CONST OCITable*) 
                            attr_value, &index));
               (void) printf
               ("     The index of the first element is : %d.\n", index);
               /* print out the element */
               checkerr(errhp, OCICollGetElem(envhp, errhp,
                                       (CONST OCIColl *) attr_value, index,
                                       &exist, (dvoid **) &element,
                                       (dvoid **) &null_element));
               /* if it is named type, recursively call the same function */
               checkerr(errhp, OCIAttrGet((dvoid*) parmp2,
                       (ub4) OCI_DTYPE_PARAM,
                       (dvoid*) &typecode, (ub4 *) 0,
                       (ub4) OCI_ATTR_TYPECODE,
                       (OCIError *) errhp));
               if (typecode == OCI_TYPECODE_OBJECT)
                 dump_object(envhp, errhp, svchp, element_type, 
                        (dvoid *)element, (dvoid *)null_element);
               else
                 display_attr_val(envhp, errhp, namep, typecode, element);

               for(;!OCITableNext(envhp, errhp, index, (CONST OCITable *) 
                     attr_value,
                     &index, &exist) && exist;)
               {
                checkerr(errhp, OCICollGetElem(envhp, errhp, (CONST OCIColl *)
                                         attr_value, index,
                                         &exist, (dvoid **) &element, 
                                         (dvoid **) &null_element));
                  if (typecode == OCI_TYPECODE_OBJECT)
                     dump_object(envhp, errhp, svchp, element_type, 
                           (dvoid *)element, (dvoid *)null_element);
                  else
                     display_attr_val(envhp, errhp, namep, typecode, element);
               }
               break;
                 default:
               break;
             }
           checkerr(errhp, OCIHandleFree((dvoid *) dschp1, (ub4) 
                              OCI_HTYPE_DESCRIBE));
           break;
       default:   /* scaler type, display the attribute value */
           if (attr_null_status == OCI_IND_NOTNULL)
             {
              display_attr_val(envhp, errhp, namep, typecode, attr_value);
           }
           else
              printf("attr %s is null\n", namep);
           break;
    }
  }

  checkerr(errhp, OCIHandleFree((dvoid *) dschp, (ub4) OCI_HTYPE_DESCRIBE));
  printf("finishing displaying instance of type '%s'\n", typenamep);
}


/****************************************************************************
*  Setup the schema and insert the data                                     *
*****************************************************************************/
void setup(envhp, svchp, stmthp, errhp)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
{
  /* create the schema and populate the data */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_type_address,
                          (ub4) strlen((const char *) create_type_address),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                         (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_type_addr_tab,
                          (ub4) strlen((const char *) create_type_addr_tab),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                         (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_type_person,
                          (ub4) strlen((const char *) create_type_person),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                          (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_type_customer,
                          (ub4) strlen((const char *) create_type_customer),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                         (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) create_table_person,
                          (ub4) strlen((const char *) create_table_person),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                         (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) 
                           create_table_customer,
                          (ub4) strlen((const char *) create_table_customer),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                          (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insert_customer,
                          (ub4) strlen((const char *) insert_customer),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                         (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insert_person,
                          (ub4) strlen((const char *) insert_person),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                         (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insert_address1,
                          (ub4) strlen((const char *) insert_address1),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                         (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) insert_address2,
                          (ub4) strlen((const char *) insert_address2),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                         (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) update_customer,
                          (ub4) strlen((const char *) update_customer),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
                         (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCITransCommit(svchp, errhp, (ub4) 0));
}


/
*******************************************************************************/ 
void select_pin_display(envhp, svchp, stmthp, errhp)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
{
  sword status = OCI_SUCCESS;
  OCIDefine *defnp;
  OCIRef *custref = (OCIRef *) 0, *per_type_ref = (OCIRef *) 0;
  OCIRef *cust_type_ref = (OCIRef *) 0;
  ub4    custsize;
  customer *cust = (customer *) 0, *custnew = (customer *) 0;
  null_customer *null_cust = (null_customer *) 0, 
                *null_custnew = (null_customer *) 0; 
  person *per = (person *) 0;
  null_person *null_per = (null_person *) 0;
  null_address *nt_null = (null_address *) 0;
  OCIType *pertdo = (OCIType *) 0, *custtdo = (OCIType *) 0;
  address *addr = (address *) 0;
  sb4 index;
  boolean exist;
  dvoid *tabobj = (dvoid *) 0;

  (void) printf("\n=============================================\n");

  /* allocate ref */
  if (( status = OCIObjectNew(envhp, errhp, svchp, OCI_TYPECODE_REF, 
                         (OCIType *)0,
                         (dvoid *)0, OCI_DURATION_DEFAULT, TRUE, 
                         (dvoid **) &per_type_ref))
                  != OCI_SUCCESS)
     (void) printf("BUG -- OCIObjectNew, expect OCI_SUCCESS.\n");
 
  /* allocate ref */
  if (( status = OCIObjectNew(envhp, errhp, svchp, OCI_TYPECODE_REF, 
                         (OCIType *)0,
                         (dvoid *)0, OCI_DURATION_DEFAULT, TRUE, 
                         (dvoid **) &cust_type_ref))
                  != OCI_SUCCESS)
     (void) printf("BUG -- OCIObjectNew, expect OCI_SUCCESS.\n");
 
  /* define the application request  */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) selref,
                          (ub4) strlen((const char *) selref),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIHandleAlloc( (dvoid *) stmthp, (dvoid **) &defnp,
                           (ub4) OCI_HTYPE_DEFINE,
                           0, (dvoid **) 0));
 
  checkerr(errhp, OCIDefineByPos(stmthp, &defnp, errhp, (ub4) 1, (dvoid *) 0,
                   (sb4) 0, SQLT_REF, (dvoid *) 0, (ub2 *)0,
                   (ub2 *)0, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIDefineObject(defnp, errhp, (OCIType *) 0,
                            (dvoid **) &custref,
                            &custsize, (dvoid **) 0, (ub4 *) 0));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 0,
                         (ub4) 0, (OCISnapshot *)
                         NULL, (OCISnapshot *) NULL,
                         (ub4) OCI_DEFAULT));
 
  while ((status = OCIStmtFetch(stmthp, errhp, (ub4) 1,  (ub4) OCI_FETCH_NEXT,
                         (ub4) OCI_DEFAULT)) == 0)
  {
 
    (void) printf("\n-----------------------------------------\n");

    /* pin the ref and get the typed table to get to person */
    checkerr(errhp, OCIObjectPin(envhp, errhp, custref,
                          (OCIComplexObject *)0,
                          OCI_PIN_ANY, OCI_DURATION_SESSION,
                          OCI_LOCK_NONE, (dvoid **) &cust));
    (void) printf("The customer account number is %s\n",
                   OCIStringPtr(envhp, cust->account));
    if (( status = OCIObjectGetInd(envhp, errhp, (dvoid *) cust, 
                               (dvoid **) &null_cust)) != OCI_SUCCESS)
    {
      (void) printf("BUG -- ORIOGNS, expect OCI_SUCCESS.\n");
    }
    else
    {
      (void) printf("null_cus = %d, null_account = %d, null_aperson = %d\n",
                      null_cust->null_cus, null_cust->null_account,
                      null_cust->null_aperson);
    } 
    
    checkerr(errhp, OCIObjectPin(envhp, errhp, cust->aperson,
                          (OCIComplexObject *)0,
                          OCI_PIN_ANY, OCI_DURATION_SESSION,
                          OCI_LOCK_NONE, (dvoid **) &per));

    if (( status = OCIObjectGetInd(envhp, errhp, (dvoid *) per, 
                               (dvoid **) &null_per)) != OCI_SUCCESS)
    {
      (void) printf("BUG -- ORIOGNS, expect OCI_SUCCESS.\n");
    }
    else
    {      
      checkerr(errhp, OCIObjectGetTypeRef(envhp, errhp, (dvoid *)per,
                          per_type_ref));
      checkerr(errhp, OCIObjectPin(envhp, errhp, per_type_ref,
                          (OCIComplexObject *)0, OCI_PIN_ANY,
                          OCI_DURATION_SESSION, OCI_LOCK_NONE,
                          (dvoid **) &pertdo));
      dump_object(envhp, errhp, svchp, pertdo, (dvoid *) per,
                          (dvoid *) null_per);     
    }
  }

  if ( status != OCI_NO_DATA )
    checkerr(errhp, status);

  (void) printf("\n\n");
}

 
/****************************************************************************
*  Clean up the schema and the data                                         *
*****************************************************************************/
void cleanup(envhp, svchp, stmthp, errhp)
OCIEnv *envhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIError *errhp;
{
  /* clean up the schema */
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop table 
customer_tab",
                          (ub4) strlen((const char *)"drop table customer_tab" 
),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1,
                          (ub4) 0, (OCISnapshot *)
                          NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop table 
                         person_tab",
                         (ub4) strlen((const char *)"drop table person_tab" ),
                         (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1,
                          (ub4) 0, (OCISnapshot *)
                          NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop type customer",
                          (ub4) strlen((const char *)"drop table customer" ),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1,
                          (ub4) 0, (OCISnapshot *)
                          NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));

  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop type person",
                          (ub4) strlen((const char *)"drop table person" ),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1,
                          (ub4) 0, (OCISnapshot *)
                          NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));

  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop type addr_tab",
                          (ub4) strlen((const char *)"drop table addr_tab" ),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1,
                          (ub4) 0, (OCISnapshot *)
                          NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));

  checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) "drop type address",
                          (ub4) strlen((const char *) "drop type address"),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1,
                          (ub4) 0, (OCISnapshot *)
                          NULL, (OCISnapshot *) NULL, (ub4) OCI_DEFAULT));
 
  checkerr(errhp, OCITransCommit(svchp, errhp, (ub4) 0));
}


/**********************************************************************/ 
int main()
{
  OCIEnv *envhp;
  OCIServer *srvhp;
  OCIError *errhp;
  OCISvcCtx *svchp;
  OCISession *usrhp;
  OCIStmt *stmthp;
  dvoid *tmp;

  /* initialize the process */
  (void) OCIInitialize((ub4) OCI_THREADED | OCI_OBJECT,
                (dvoid *)0,  (dvoid * (*)()) 0,
                (dvoid * (*)()) 0,  (void (*)()) 0 );

  /* initialize the environmental handle */
  (void) OCIEnvInit( &envhp, (ub4) OCI_DEFAULT, 21, (dvoid **) &tmp  );

  /* get the error handle */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp,
                   (ub4) OCI_HTYPE_ERROR,
                   52, (dvoid **) &tmp);

  /* two server contexts */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp,
                   (ub4) OCI_HTYPE_SERVER,
                   52, (dvoid **) &tmp);
  /* attach the server */
  (void) OCIServerAttach( srvhp, errhp, (text *) "", (sb4) 0, (ub4) 
                           OCI_DEFAULT);

  /* get the service handle */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp,
                   (ub4) OCI_HTYPE_SVCCTX,
                   52, (dvoid **) &tmp);

  /* set attribute server context in the service context */
  (void) OCIAttrSet( (dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                    (dvoid *) srvhp, (ub4) 0,
                    (ub4) OCI_ATTR_SERVER, (OCIError *) errhp);

  /* get the user handle */
  (void) OCIHandleAlloc((dvoid *)envhp, (dvoid **)&usrhp,
               (ub4)OCI_HTYPE_SESSION, 0, (dvoid **)0);

  /* set the attribute user name */
  (void) OCIAttrSet((dvoid *) usrhp, (ub4)OCI_HTYPE_SESSION,
              (dvoid *)"scott", (ub4)strlen("scott"),
              (ub4)OCI_ATTR_USERNAME, errhp);

  /* set the attribute password */
  (void) OCIAttrSet((dvoid *) usrhp, (ub4)OCI_HTYPE_SESSION,
              (dvoid *)"tiger", (ub4)strlen("tiger"),
              (ub4)OCI_ATTR_PASSWORD, errhp);

  /* authenticate */
  checkerr(errhp, OCISessionBegin (svchp, errhp, usrhp, OCI_CRED_RDBMS, 
                              OCI_DEFAULT));

  /* set the attribute user context of the service handle */
  (void) OCIAttrSet((dvoid *)svchp, (ub4)OCI_HTYPE_SVCCTX,
              (dvoid *)usrhp, (ub4)0,
              (ub4)OCI_ATTR_SESSION, errhp);

  /* get the statement handle */
  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
           (ub4) OCI_HTYPE_STMT, 50, (dvoid **) &tmp));

  (void) printf("\n*********************************************\n");
  (void) printf("--- Setup the schema and insert the data.\n");
  setup(envhp, svchp, stmthp, errhp); 

  (void) printf("\n*********************************************\n");
  (void) printf("--- Select a REF, pin the REF, then display the object.\n");
  select_pin_display(envhp, svchp, stmthp, errhp); 

  (void) printf("\n*********************************************\n");
  (void) printf("--- Clean up the schema and the data.\n");
  cleanup(envhp, svchp, stmthp, errhp); 

  checkerr(errhp, OCISessionEnd (svchp, errhp, usrhp, OCI_DEFAULT));

  /* dettach */
  (void) OCIServerDetach( srvhp, errhp, (ub4) OCI_DEFAULT );
  checkerr(errhp, OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT));
  checkerr(errhp, OCIHandleFree((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX));
  checkerr(errhp, OCIHandleFree((dvoid *) errhp, (ub4) OCI_HTYPE_ERROR));
  checkerr(errhp, OCIHandleFree((dvoid *) srvhp, (ub4) OCI_HTYPE_SERVER));

  return (0);
}




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index