5 Binding and Defining in OCI

Named Binds and Positional Binds

The SQL statement in Figure 5-1 is an example of a named bind. Each placeholder in the statement has a name associated with it, such as 'ename' or 'sal'. When this statement is prepared and the placeholders are associated with values in the application, the association is made by the name of the placeholder using the OCIBindByName() call with the name of the placeholder passed in the placeholder parameter.

A second type of bind is known as a positional bind. In a positional bind, the placeholders are referred to by their position in the statement rather than by their names. For binding purposes, an association is made between an input value and the position of the placeholder, using the OCIBindByPos() call.

To use the previous example for a positional bind:

INSERT INTO emp VALUES
    (:empno, :ename, :job, :sal, :deptno)

The five placeholders are then each bound by calling OCIBindByPos() and passing the position number of the placeholder in the position parameter. For example, the :empno placeholder would be bound by calling OCIBindByPos() with a position of 1, :ename with a position of 2, and so on.

In a duplicate bind, only a single bind call may be necessary. Consider the following SQL statement, which queries the database for employees whose commission and salary are both greater than a given amount:

SELECT empno FROM emp
    WHERE sal > :some_value
    AND comm > :some_value

An OCI application could complete the binds for this statement with a single call to OCIBindByName() to bind the :some_value placeholder by name. In this case, all bind placeholders for :some_value get assigned the same value as provided by the OCIBindByName() call.

Now consider the case where a 6th placeholder is added that is a duplicate. For example, add :ename as the 6th placeholder in the first previous example:

INSERT INTO emp VALUES
    (:empno, :ename, :job, :sal, :deptno, :ename)

If you are using the OCIBindByName() call, just one bind call suffices to bind both occurrences of the :ename placeholder. All occurrences of :ename in the statement will get bound to the same value. Moreover, if new bind placeholders get added as a result of which bind positions for existing bind placeholders change, you do not need to change your existing bind calls in order to update bind positions. This is a distinct advantage in using the OCIBindByName() call if your program evolves to add more bind variables in your statement text.

If you are using the OCIBindByPos() call, however, you have increased flexibility in terms of binding duplicate bind-parameters separately, if you need it. You have the option of binding any of the duplicate occurrences of a bind parameter separately. Any unbound duplicate occurrences of a parameter inherit the value from the first occurrence of the bind parameter with the same name. The first occurrence must be explicitly bound.

In the context of SQL statements, the position n indicates the bind parameter at the nth position. However, in the context of PL/SQL statements, OCIBindByPos() has a different interpretation for the position parameter: the position n in the bind call indicates a binding for the nth unique parameter name in the statement when scanned left to right.

Using the previous example again and the same SQL statement text, if you want to bind the 6th position separately, the :ename placeholder would be bound by calling OCIBindByPos() with a position of 6. Otherwise, if left unbound, :ename would inherit the value from the first occurrence of the bind parameter with the same name, in this case, from :ename in position 2.

OCI Array Interface

You can pass data to the Oracle database in various ways.

You can execute a SQL statement repeatedly using the OCIStmtExecute() routine and supply different input values on each iteration.

You can use the Oracle array interface and input many values with a single statement and a single call to OCIStmtExecute(). In this case, you bind an array to an input placeholder, and the entire array can be passed at the same time, under the control of the iters parameter.

The array interface significantly reduces round-trips to the database when you are updating or inserting a large volume of data. This reduction can lead to considerable performance gains in a busy client/server environment. For example, consider an application that inserts 10 rows into the database. Calling OCIStmtExecute() 10 times with single values results in 10 network round-trips to insert all the data. The same result is possible with a single call to OCIStmtExecute() using an input array, which involves only one network round-trip.

Binding Placeholders in PL/SQL

You process a PL/SQL block by placing the block in a string variable, binding any variables, and then executing the statement containing the block, just as you would with a single SQL statement.

When you bind placeholders in a PL/SQL block to program variables, you must use OCIBindByName() or OCIBindByPos() to perform the basic binds for host variables that are either scalars or arrays.

The following short PL/SQL block contains two placeholders, which represent IN parameters to a procedure that updates an employee's salary, when given the employee number and the new salary amount:

char plsql_statement[] = "BEGIN\
                          RAISE_SALARY(:emp_number, :new_sal);\
                          END;" ;

These placeholders can be bound to input variables in the same way as placeholders in a SQL statement.

When processing PL/SQL statements, output variables are also associated with program variables by using bind calls.

For example, consider the following PL/SQL block:

BEGIN
    SELECT ename,sal,comm INTO :emp_name, :salary, :commission
    FROM emp
    WHERE empno = :emp_number;
END;

In this block, you would use OCIBindByName() to bind variables in place of the :emp_name, :salary, and :commission output placeholders, and in place of the input placeholder :emp_number.

Steps Used in OCI Binding

Placeholders are bound in several steps. For a simple scalar or array bind, it is only necessary to specify an association between the placeholder and the data, by using OCIBindByName() or OCIBindByPos().

Once the bind is complete, the OCI library detects where to find the input data or where to put the PL/SQL output data when the SQL statement is executed. Program input data does not need to be in the program variable when it is bound to the placeholder, but the data must be there when the statement is executed.

The following code example in Example 5-1 shows handle allocation and binding for each placeholder in a SQL statement.

...
/* The SQL statement, associated with stmthp (the statement handle)
by calling OCIStmtPrepare() */
text *insert = (text *) "INSERT INTO emp(empno, ename, job, sal, deptno)\
    VALUES (:empno, :ename, :job, :sal, :deptno)";
...

/*  Bind the placeholders in the SQL statement, one per bind handle. */
checkerr(errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":ENAME",
    strlen(":ENAME"), (ub1 *) ename, enamelen+1, SQLT_STR, (void *) 0,
    (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));
checkerr(errhp, OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":JOB",
    strlen(":JOB"), (ub1 *) job, joblen+1, SQLT_STR, (void *)
    &job_ind, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));
checkerr(errhp, OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":SAL",
    strlen(":SAL"), (ub1 *) &sal, (sword) sizeof(sal), SQLT_INT,
    (void *) &sal_ind, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,
    OCI_DEFAULT));
checkerr(errhp, OCIBindByName(stmthp, &bnd4p, errhp, (text *) ":DEPTNO",
    strlen(":DEPTNO"), (ub1 *) &deptno,(sword) sizeof(deptno), SQLT_INT,
    (void *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));
checkerr(errhp, OCIBindByName(stmthp, &bnd5p, errhp, (text *) ":EMPNO",
    strlen(":EMPNO"), (ub1 *) &empno, (sword) sizeof(empno), SQLT_INT,
    (void *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT));

PL/SQL Block in an OCI Program

Perhaps the most common use for PL/SQL blocks in OCI is to call stored procedures or stored functions. Assume that there is a procedure named RAISE_SALARY stored in the database, and you embed a call to that procedure in an anonymous PL/SQL block, and then process the PL/SQL block.

The following program fragment shows how to embed a stored procedure call in an OCI application. The program passes an employee number and a salary increase as inputs to a stored procedure called raise_salary:

raise_salary (employee_num IN, sal_increase IN, new_salary OUT);

This procedure raises a given employee's salary by a given amount. The increased salary that results is returned in the stored procedure's variable, new_salary, and the program displays this value.

Note that the PL/SQL procedure argument, new_salary, although a PL/SQL OUT variable, must be bound, not defined. This is explained in Defining PL/SQL Output Variables and in Information for Named Data Type and REF Defines, and PL/SQL OUT Binds.

Example 5-2 demonstrates how to perform a simple scalar bind where only a single bind call is necessary. In some cases, additional bind calls are needed to define attributes for specific bind data types or execution modes.

/* Define PL/SQL statement to be used in program. */
text *give_raise = (text *) "BEGIN\
                  RAISE_SALARY(:emp_number,:sal_increase, :new_salary);\
                     END;";
OCIBind  *bnd1p = NULL;                      /* the first bind handle */
OCIBind  *bnd2p = NULL;                     /* the second bind handle */
OCIBind  *bnd3p = NULL;                      /* the third bind handle */

static void checkerr();
sb4 status;

main()
{
  sword    empno, raise, new_sal;
  void     *tmp;
  OCISession *usrhp = (OCISession *)NULL; 
...
/* attach to Oracle database, and perform necessary initializations
and authorizations */
...
      /* allocate a statement handle */
  checkerr(errhp, OCIHandleAlloc( (void *) envhp, (void **) &stmthp,
           OCI_HTYPE_STMT, 100, (void **) &tmp));

      /* prepare the statement request, passing the PL/SQL text
        block as the statement to be prepared */
checkerr(errhp, OCIStmtPrepare(stmthp, errhp, (text *) give_raise, (ub4) 
      strlen(give_raise), OCI_NTV_SYNTAX, OCI_DEFAULT));

      /* bind each of the placeholders to a program variable */
 checkerr( errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":emp_number",
             -1, (ub1 *) &empno,
            (sword) sizeof(empno), SQLT_INT, (void *) 0,
             (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));

 checkerr( errhp, OCIBindByName(stmthp, &bnd2p, errhp, (text *) ":sal_increase",
             -1, (ub1 *) &raise,
             (sword) sizeof(raise), SQLT_INT, (void *) 0,
             (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));

      /* remember that PL/SQL OUT variables are bound, not defined */

checkerr( errhp, OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":new_salary",
             -1, (ub1 *) &new_sal,
             (sword) sizeof(new_sal), SQLT_INT, (void *) 0,
             (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));

      /* prompt the user for input values */
printf("Enter the employee number: ");
scanf("%d", &empno); 
      /* flush the input buffer */
myfflush();

printf("Enter employee's raise: ");
scanf("%d", &raise);
      /* flush the input buffer */
myfflush();

  /* execute PL/SQL block*/
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, 
      (OCISnapshot *) NULL, (OCISnapshot *) NULL, OCI_DEFAULT));

  /* display the new salary, following the raise */
printf("The new salary is %d\n", new_sal);
}

Binding LOBs

There are two ways of binding LOBs:

• Bind the LOB locator, rather than the actual LOB values. In this case the LOB value is written or read by passing a LOB locator to the OCI LOB functions.

• Bind the LOB value directly, without using the LOB locator.

INSERT INTO some_table VALUES (:one_lob)

OCILobLocator * one_lob;

/* initialize single locator */
one_lob = OCIDescriptorAlloc(...OCI_DTYPE_LOB...);
...
/* pass the address of the locator */
OCIBindByName(...,(void *) &one_lob,... SQLT_CLOB, ...);
OCIStmtExecute(...,1,...)                /* 1 is the iters parameter */

OCILobLocator * lob_array[10];
...
for (i=0; i<10, i++)
    lob_array[i] = OCIDescriptorAlloc(...OCI_DTYPE_LOB...);
                                     /* initialize array of locators */
...
OCIBindByName(...,(void *) lob_array,...);
OCIBindArrayOfStruct(...);
OCIStmtExecute(...,10,...);               /* 10 is the iters parameter */

CREATE TABLESPACE ... AUTOEXTEND ON ... TEMPORARY ...;

CREATE TABLE foo (a INTEGER );
CREATE TYPE lob_typ AS OBJECT (A1 CLOB );
CREATE TABLE lob_long_tab (C1 CLOB, C2 CLOB, CT3 lob_typ, L LONG);

void insert()                 /* A function in an OCI program */
{
   /* The following is allowed */
   ub1 buffer[8000];
   text *insert_sql = (text *) "INSERT INTO lob_long_tab (C1, C2, L) \
                       VALUES (:1, :2, :3)";
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (void *)buffer, 8000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (void *)buffer, 8000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (void *)buffer, 2000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                              (OCISnapshot *) NULL, OCI_DEFAULT);
}

void insert()
{
   /* The following is allowed */
   ub1 buffer[8000];
   text *insert_sql = (text *) "INSERT INTO lob_long_tab (C1, L) \
                      VALUES (:1, :2)";
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (void *)buffer, 2000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (void *)buffer, 8000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                              (OCISnapshot *) NULL, OCI_DEFAULT);
}

void update()
{
   /* The following is allowed, no matter how many rows it updates */
   ub1 buffer[8000];
   text *update_sql = (text *)"UPDATE lob_long_tab SET \
                              C1 = :1, C2=:2, L=:3";
   OCIStmtPrepare(stmthp, errhp, update_sql, strlen((char*)update_sql),
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (void *)buffer, 8000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (void *)buffer, 8000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (void *)buffer, 2000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                              (OCISnapshot *) NULL, OCI_DEFAULT);
}

void update()
{
   /* The following is allowed, no matter how many rows it updates */
   ub1 buffer[8000];
   text *update_sql = (text *)"UPDATE lob_long_tab SET \
                               C1 = :1, C2=:2, L=:3";
   OCIStmtPrepare(stmthp, errhp, update_sql, strlen((char*)update_sql),
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (void *)buffer, 2000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (void *)buffer, 2000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (void *)buffer, 8000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                               (OCISnapshot *) NULL, OCI_DEFAULT);
}

void insert()
{
   /* Piecewise, callback and array insert/update operations similar to 
    * the allowed regular insert/update operations are also allowed */
}

void insert()
{
   /* The following is NOT allowed because you cannot insert >4000 bytes
    * into both LOB and LONG columns */
   ub1 buffer[8000];
   text *insert_sql = (text *)"INSERT INTO lob_long_tab (C1, L) \
                               VALUES (:1, :2)";
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (void *)buffer, 8000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (void *)buffer, 8000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                              (OCISnapshot *) NULL, OCI_DEFAULT);
}

void insert()
{
   /* Insert of data into LOB attributes is allowed */
   ub1 buffer[8000];
   text *insert_sql = (text *)"INSERT INTO lob_long_tab (CT3) \
                               VALUES (lob_typ(:1))";
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (void *)buffer, 2000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                              (OCISnapshot *) NULL, OCI_DEFAULT);
}

void insert()
{
   /* The following is NOT allowed because you cannot do insert as
    * select character data into LOB column */
   ub1 buffer[8000];
   text *insert_sql = (text *)"INSERT INTO lob_long_tab (C1) SELECT \
                               :1 from FOO";
   OCIStmtPrepare(stmthp, errhp, insert_sql, strlen((char*)insert_sql),
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[0], errhp, 1, (void *)buffer, 8000,
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *) NULL,
                              (OCISnapshot *) NULL, OCI_DEFAULT);
}

Binding in OCI_DATA_AT_EXEC Mode

If the mode parameter in a call to OCIBindByName() or OCIBindByPos() is set to OCI_DATA_AT_EXEC, an additional call to OCIBindDynamic() is necessary if the application uses the callback method for providing data at run time. The call to OCIBindDynamic() sets up the callback routines, if necessary, for indicating the data or piece provided. If the OCI_DATA_AT_EXEC mode is chosen, but the standard OCI piecewise polling method is used instead of callbacks, the call to OCIBindDynamic() is not necessary.

When binding RETURN clause variables, an application must use OCI_DATA_AT_EXEC mode, and it must provide callbacks.

Binding REF CURSOR Variables

REF CURSORs are bound to a statement handle with a bind data type of SQLT_RSET.

Steps Used in OCI Defining

A basic define is done with a position call, OCIDefineByPos(). This step creates an association between a select-list item and an output variable. Additional define calls may be necessary for certain data types or fetch modes. Once the define step is complete, the OCI library determines where to put retrieved data. You can make your define calls again to redefine the output variables without having to reprepare or reexecute the SQL statement.

Example 5-14 shows a scalar output variable being defined following an execute and describe operation.

SELECT department_name FROM departments WHERE department_id = :dept_input

   /* The input placeholder was bound earlier, and the data comes from the
   user input below */

  printf("Enter employee dept: ");
  scanf("%d", &deptno);

 /* Execute the statement. If OCIStmtExecute() returns OCI_NO_DATA, meaning that
    no data matches the query, then the department number is invalid. */

  if ((status = OCIStmtExecute(svchp, stmthp, errhp, 0, 0, (OCISnapshot *) 0, (OCISnapshot *) 0,
       OCI_DEFAULT))
      && (status != OCI_NO_DATA))
  {
    checkerr(errhp, status);
    return OCI_ERROR;
  }
  if (status == OCI_NO_DATA) {
    printf("The dept you entered does not exist.\n");
    return 0;
  }

   /* The next two statements describe the select-list item, dname, and
   return its length */
  checkerr(errhp, OCIParamGet((void *)stmthp, (ub4) OCI_HTYPE_STMT, errhp, (void **)&parmdp, (ub4) 1));
  checkerr(errhp, OCIAttrGet((void*) parmdp, (ub4) OCI_DTYPE_PARAM,
        (void*) &deptlen, (ub4 *) &sizelen, (ub4) OCI_ATTR_DATA_SIZE,
        (OCIError *) errhp  ));

  /* Use the retrieved length of dname to allocate an output buffer, and
   then define the output variable. If the define call returns an error,
   exit the application */
  dept = (text *) malloc((int) deptlen + 1);
  if (status = OCIDefineByPos(stmthp, &defnp, errhp,
             1, (void *) dept, (sb4) deptlen+1,
             SQLT_STR, (void *) 0, (ub2 *) 0,
             (ub2 *) 0, OCI_DEFAULT))
  {
    checkerr(errhp, status);
    return OCI_ERROR;
  }

Defining LOB Output Variables

There are two ways of defining LOBs:

• Define a LOB locator, rather than the actual LOB values. In this case, the LOB value is written or read by passing a LOB locator to the OCI LOB functions.

• Define a LOB value directly, without using the LOB locator.

SELECT lob1 FROM some_table;

OCILobLocator * one_lob;

/* initialize single locator */
OCIDescriptorAlloc(...&one_lob, OCI_DTYPE_LOB...);
...
/* pass the address of the locator */
OCIBindByName(...,(void *) &one_lob,... SQLT_CLOB, ...);
OCIStmtExecute(...,1,...);                /* 1 is the iters parameter */

OCILobLocator * lob_array[10];
...
for (i=0; i<10, i++)
   OCIDescriptorAlloc(...&lob_array[i], OCI_DTYPE_LOB...);
                                         /* initialize array of locators */
...
OCIBindByName(...,(void *) lob_array,...);
OCIStmtExecute(...,10,...);               /* 10 is the iters parameter */

CREATE TABLE lob_tab (C1 CLOB, C2 CLOB);

void select_define_before_execute()      /* A function in an OCI program */
{
  /* The following is allowed */
   ub1 buffer1[8000];
   ub1 buffer2[8000];
   text *select_sql = (text *)"SELECT c1, c2 FROM lob_tab";

   OCIStmtPrepare(stmthp, errhp, select_sql, (ub4)strlen((char*)select_sql),
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIDefineByPos(stmthp, &defhp[0], errhp, 1, (void *)buffer1, 8000,
                SQLT_LNG, (void *)0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT);
   OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (void *)buffer2, 8000,
                SQLT_LNG, (void *)0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *)0, 
                 (OCISnapshot *)0, OCI_DEFAULT);
}

void select_execute_before_define()
{
  /* The following is allowed */
   ub1 buffer1[8000];
   ub1 buffer2[8000];
   text *select_sql = (text *)"SELECT c1, c2 FROM lob_tab";

   OCIStmtPrepare(stmthp, errhp, select_sql, (ub4)strlen((char*)select_sql),
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 0, 0, (OCISnapshot *)0,
                 (OCISnapshot *)0, OCI_DEFAULT);
   OCIDefineByPos(stmthp, &defhp[0], errhp, 1, (void *)buffer1, 8000,
                 SQLT_LNG, (void *)0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT);
   OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (void *)buffer2, 8000,
                 SQLT_LNG, (void *)0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT);
   OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
}

Defining PL/SQL Output Variables

Do not use the define calls to define output variables for select-list items in a SQL SELECT statement inside a PL/SQL block. Use OCI bind calls instead.

Defining for a Piecewise Fetch

A piecewise fetch requires an initial call to OCIDefineByPos(). An additional call to OCIDefineDynamic() is necessary if the application uses callbacks rather than the standard polling mechanism.

Skip Parameters

When you split column data across an array of structures, it is no longer stored contiguously in the database. The single array of structures stores data as though it were composed of several arrays of scalars. For this reason, you must specify a skip parameter for each field that you are binding or defining. This skip parameter is the number of bytes that must be skipped in the array of structures before the same field is encountered again. In general, this is equivalent to the byte size of one structure.

Figure 5-2 shows how a skip parameter is determined. In this case, the skip parameter is the sum of the sizes of the fields field1 (2 bytes), field2 (4 bytes), and field3 (2 bytes), which is 8 bytes. This equals the size of one structure.

Figure 5-2 Determining Skip Parameters

Description of "Figure 5-2 Determining Skip Parameters"

On some operating systems it may be necessary to set the skip parameter to sizeof(one_array_element) rather than sizeof(struct), because some compilers insert extra bytes into a structure.

Consider an array of C structures consisting of two fields, a ub4 and a ub1:

struct demo {
    ub4 field1;
    ub1 field2;
};
struct demo demo_array[MAXSIZE];

Some compilers insert 3 bytes of padding after the ub1 so that the ub4 that begins the next structure in the array is properly aligned. In this case, the following statement may return an incorrect value:

skip_parameter = sizeof(struct demo);

On some operating systems this produces a proper skip parameter of 8. On other systems, skip_parameter is set to 5 bytes by this statement. In the latter case, use the following statement to get the correct value for the skip parameter:

skip_parameter = sizeof(demo_array[0]);

text emp_names[4][20];

OCI Calls Used with Arrays of Structures

Two OCI calls must be used when you perform operations involving arrays of structures:

• Use OCIBindArrayOfStruct() for binding fields in arrays of structures for input variables

• Use OCIDefineArrayOfStruct() for defining arrays of structures for output variables.

  Note:

  Binding or defining for arrays of structures requires multiple calls. A call to OCIBindByName() or OCIBindByPos() must precede a call to OCIBindArrayOfStruct(), and a call to OCIDefineByPos() must precede a call to OCIDefineArrayOfStruct().

Arrays of Structures and Indicator Variables

The implementation of arrays of structures in addition supports the use of indicator variables and return codes. You can declare parallel arrays of column-level indicator variables and return codes that correspond to the arrays of information being fetched, inserted, or updated. These arrays can have their own skip parameters, which are specified during OCIBindArrayOfStruct() or OCIDefineArrayOfStruct() calls.

You can set up arrays of structures of program values and indicator variables in many ways. Consider an application that fetches data from three database columns into an array of structures containing three fields. You can set up a corresponding array of indicator variable structures of three fields, each of which is a column-level indicator variable for one of the columns being fetched from the database. A one-to-one relationship between the fields in an indicator struct and the number of select-list items is not necessary.

Using DML with a RETURNING Clause to Combine Two SQL Statements

Using the RETURNING clause with a DML statement enables you to combine two SQL statements into one, possibly saving a server round-trip. This is accomplished by adding an extra clause to the traditional UPDATE, INSERT, and DELETE statements. The extra clause effectively adds a query to the DML statement.

In OCI, values are returned to the application as OUT bind variables. In the following examples, the bind variables are indicated by a preceding colon, ":". These examples assume the existence of table1, a table that contains columns col1, col2, and col3.

The following statement inserts new values into the database and then retrieves the column values of the affected row from the database, for manipulating inserted rows.

INSERT INTO table1 VALUES (:1, :2, :3)
     RETURNING col1, col2, col3
     INTO :out1, :out2, :out3

The next example updates the values of all columns where the value of col1 falls within a given range, and then returns the affected rows that were modified.

UPDATE table1 SET col1 = col1 + :1, col2 = :2, col3 = :3
     WHERE col1 >= :low AND col1 <= :high
     RETURNING col1, col2, col3
     INTO :out1, :out2, :out3

The DELETE statement deletes the rows where col1 value falls within a given range, and then returns the data from those rows.

DELETE FROM table1 WHERE col1 >= :low AND col2 <= :high 
     RETURNING col1, col2, col3
     INTO :out1, :out2, :out3

Binding RETURNING...INTO Variables

Because both the UPDATE and DELETE statements can affect multiple rows in the table, and a DML statement can be executed multiple times in a single OCIStmtExecute() call, how much data is returned may not be known at run time. As a result, the variables corresponding to the RETURNING...INTO placeholders must be bound in OCI_DATA_AT_EXEC mode. An application must define its own dynamic data handling callbacks rather than using a polling mechanism.

The returning clause can be particularly useful when working with LOBs. Normally, an application must insert an empty LOB locator into the database, and then select it back out again to operate on it. By using the RETURNING clause, the application can combine these two steps into a single statement:

INSERT INTO some_table VALUES (:in_locator)
    RETURNING lob_column
    INTO :out_locator

An OCI application implements the placeholders in the RETURNING clause as pure OUT bind variables. However, all binds in the RETURNING clause are initially IN and must be properly initialized. To provide a valid value, you can provide a NULL indicator and set that indicator to -1.

An application must adhere to the following rules when working with bind variables in a RETURNING clause:

• Bind RETURNING clause placeholders in OCI_DATA_AT_EXEC mode using OCIBindByName() or OCIBindByPos(), followed by a call to OCIBindDynamic() for each placeholder.

• When binding RETURNING clause placeholders, supply a valid OUT bind function as the ocbfp parameter of the OCIBindDynamic() call. This function must provide storage to hold the returned data.

• The icbfp parameter of OCIBindDynamic() call should provide a default function that returns NULL values when called.

• The piecep parameter of OCIBindDynamic() must be set to OCI_ONE_PIECE.

No duplicate binds are allowed in a DML statement with a RETURNING clause, and no duplication is allowed between bind variables in the DML section and the RETURNING section of the statement.

OCI Error Handling

The OUT bind function provided to OCIBindDynamic() must be prepared to receive partial results of a statement if there is an error. If the application has issued a DML statement that is executed 10 times, and an error occurs during the fifth iteration, the Oracle database returns the data from iterations 1 through 4. The callback function is still called to receive data for the first four iterations.

DML with RETURNING REF...INTO Clause in OCI

The RETURNING clause can also be used to return a REF to an object that is being inserted into or updated in the database:

UPDATE extaddr e SET e.zip = '12345', e.state ='AZ'
    WHERE e.state = 'CA' AND e.zip = '95117'
    RETURNING REF(e), zip
    INTO :addref, :zip

The preceding statement updates several attributes of an object in an object table and returns a REF to the object (and a scalar postal code (ZIP)) in the RETURNING clause.

sword bind_output(stmthp, bndhp, errhp)
OCIStmt *stmthp;
OCIBind *bndhp[];
OCIError *errhp;
{
  ub4 i;
                                  /* get TDO for BindObject call */
  if (OCITypeByName(envhp, errhp, svchp, (CONST text *) 0,
                   (ub4) 0, (CONST text *) "ADDRESS_OBJECT",
                   (ub4) strlen((CONST char *) "ADDRESS_OBJECT"),
                   (CONST text *) 0, (ub4) 0,
                    OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, &addrtdo))
  {
    return OCI_ERROR;
  }

                         /* initial bind call for both variables */
  if (OCIBindByName(stmthp, &bndhp[2], errhp,
                       (text *) ":addref", (sb4) strlen((char *) ":addref"),
                       (void *) 0, (sb4) sizeof(OCIRef *), SQLT_REF,
                       (void *) 0, (ub2 *)0, (ub2 *)0,
                       (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC)
  ||  OCIBindByName(stmthp, &bndhp[3], errhp,
                       (text *) ":zip", (sb4) strlen((char *) ":zip"),
                       (void *) 0, (sb4) MAXZIPLEN, SQLT_CHR,
                       (void *) 0, (ub2 *)0, (ub2 *)0,
                       (ub4) 0, (ub4 *) 0, (ub4) OCI_DATA_AT_EXEC))
  {
    return OCI_ERROR;
  }

                                 /* object bind for REF variable */
  if (OCIBindObject(bndhp[2], errhp, (OCIType *) addrtdo,
          (void **) &addrref[0], (ub4 *) 0, (void **) 0, (ub4 *) 0))
  {
    return OCI_ERROR;
  }


  for (i = 0; i < MAXCOLS; i++)
    pos[i] = i;
                    /* dynamic binds for both RETURNING variables */
  if (OCIBindDynamic(bndhp[2], errhp, (void *) &pos[0], cbf_no_data,
                    (void *) &pos[0], cbf_get_data)
  ||  OCIBindDynamic(bndhp[3], errhp, (void *) &pos[1], cbf_no_data,
                    (void *) &pos[1], cbf_get_data))
  {
    return OCI_ERROR;
  }

  return OCI_SUCCESS;
}

Additional Notes About OCI Callbacks

When a callback function is called, the OCI_ATTR_ROWS_RETURNED attribute of the bind handle tells the application the number of rows being returned in that particular iteration. During the first callback of an iteration, you can allocate space for all rows that are returned for that bind variable. During subsequent callbacks of the same iteration, you increment the buffer pointer to the correct memory within the allocated space.

Array Interface for DML RETURNING Statements in OCI

OCI provides additional functionality for single-row DML and array DML operations in which each iteration returns more than one row. To take advantage of this feature, you must specify an OUT buffer in the bind call that is at least as big as the iteration count specified by the OCIStmtExecute() call. This is in addition to the bind buffers provided through callbacks.

If any of the iterations returns more than one row, then the application receives an OCI_SUCCESS_WITH_INFO return code. In this case, the DML operation is successful. At this point, the application may choose to roll back the transaction or ignore the warning.

Choosing a Character Set

If a database column containing character data is defined to be an NCHAR or NVARCHAR2 column, then a bind or define involving that column must make special considerations for dealing with character set specifications.

These considerations are necessary in case the width of the client character set is different from the server character set, and also for proper character conversion. During conversion of data between different character sets, the size of the data may increase or decrease by a factor of four. Ensure that buffers that are provided to hold the data are of sufficient size.

In some cases, it may also be easier for an application to deal with NCHAR or NVARCHAR2 data in terms of numbers of characters, rather than numbers of bytes, which is the usual case.

Setting Client Character Sets in OCI

You can set the client character sets through the OCIEnvNlsCreate() function parameters charset and ncharset. Both of these parameters can be set as OCI_UTF16ID. The charset parameter controls coding of the metadata and CHAR data. The ncharset parameter controls coding of NCHAR data. The function OCINlsEnvironmentVariableGet() returns the character set from NLS_LANG and the national character set from NLS_NCHAR.

Example 5-19 illustrates the use of these functions (OCI provides a typedef called utext to facilitate binding and defining of UTF-16 data):

OCIEnv *envhp; 
ub2 ncsid = 2; /* we8dec */ 
ub2 hdlcsid, hdlncsid; 
OraText thename[20]; 
utext *selstmt = L"SELECT ename FROM emp"; /* UTF16 statement */ 
OCIStmt *stmthp; 
OCIDefine *defhp; 
OCIError *errhp; 
OCIEnvNlsCreate(OCIEnv **envhp, ..., OCI_UTF16ID, ncsid); 
... 
OCIStmtPrepare(stmthp, ..., selstmt, ...); /* prepare UTF16 statement */ 
OCIDefineByPos(stmthp, defnp, ..., 1, thename, sizeof(thename), SQLT_CHR,...); 
OCINlsEnvironmentVariableGet(&hdlcsid, (size_t)0, OCI_NLS_CHARSET_ID, (ub2)0,
     (size_t*)NULL);
OCIAttrSet(defnp, ..., &hdlcsid, 0, OCI_ATTR_CHARSET_ID, errhp); 
           /* change charset ID to NLS_LANG setting*/ 
...

Binding Variables in OCI

Update or insert operations are done through variable binding. When binding variables, specify the OCI_ATTR_MAXDATA_SIZE attribute and OCI_ATTR_MAXCHAR_SIZE attribute in the bind handle to indicate the byte and character constraints used when inserting data in to the Oracle database.

These attributes are defined as:

• The OCI_ATTR_MAXDATA_SIZE attribute sets the maximum number of bytes allowed in the buffer on the server side (see Using the OCI_ATTR_MAXDATA_SIZE Attribute for more information).

• The OCI_ATTR_MAXCHAR_SIZE attribute sets the maximum number of characters allowed in the buffer on the server side (see Using the OCI_ATTR_MAXCHAR_SIZE Attribute for more information).

...
utext ename[31], address[81];
/* E' <= 30+ 1, D' <= 80+ 1, considering null-termination */
sb4 ename_max_chars = EC=20, address_max_chars = ED=60;
 /* EC <= (E' - 1), ED <= (D' - 1) */
sb4 ename_max_bytes = EB=80, address_max_bytes = DB=200;
 /* EB <= M * EC, DB <= M * DC */
text *insstmt = (text *)"INSERT INTO EMP(ENAME, ADDRESS) VALUES (:ENAME, \
:ADDRESS)";
text *selstmt = (text *)"SELECT ENAME, ADDRESS FROM EMP";
...
/* Inserting Column Data */
OCIStmtPrepare(stmthp1, errhp, insstmt, (ub4)strlen((char *)insstmt),
    (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
OCIBindByName(stmthp1, &bnd1p, errhp, (text *)":ENAME",
    (sb4)strlen((char *)":ENAME"),
    (void *)ename, sizeof(ename), SQLT_STR, (void *)&insname_ind,
    (ub2 *)alenp, (ub2 *)rcodep, (ub4)maxarr_len, (ub4 *)curelep, OCI_DEFAULT);
/* either */
OCIAttrSet((void *)bnd1p, (ub4)OCI_HTYPE_BIND, (void *)&ename_max_bytes,
    (ub4)0, (ub4)OCI_ATTR_MAXDATA_SIZE, errhp);
/* or */
OCIAttrSet((void *)bnd1p, (ub4)OCI_HTYPE_BIND, (void *)&ename_max_chars,
    (ub4)0, (ub4)OCI_ATTR_MAXCHAR_SIZE, errhp);
...
/* Retrieving Column Data */
OCIStmtPrepare(stmthp2, errhp, selstmt, strlen((char *)selstmt),
    (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
OCIDefineByPos(stmthp2, &dfn1p, errhp, (ub4)1, (void *)ename,
   (sb4)sizeof (ename),
   SQLT_STR, (void *)&selname_ind, (ub2 *)alenp, (ub2 *)rcodep,
   (ub4)OCI_DEFAULT);
/* if not called, byte semantics is by default */
OCIAttrSet((void *)dfn1p, (ub4)OCI_HTYPE_DEFINE, (void *)&ename_max_chars,
   (ub4)0,
   (ub4)OCI_ATTR_MAXCHAR_SIZE, errhp);
...

...
OCIStmt  *stmthp1, *stmthp2;
OCIDefine *dfn1p, *dfn2p;
OCIBind *bnd1p, *bnd2p;
text *insstmt=
      (text *) "INSERT INTO EMP(ENAME, ADDRESS) VALUES (:ename, :address)"; \
text *selname =
      (text *) "SELECT ENAME, ADDRESS FROM EMP";
utext ename[21];   /* Name -    UTF-16 */
utext address[51]; /* Address - UTF-16 */
ub2 csid = OCI_UTF16ID;
sb4 ename_col_len = 20;
sb4 address_col_len = 50;
...
/* Inserting UTF-16 data */
OCIStmtPrepare (stmthp1, errhp, insstmt, (ub4)strlen ((char *)insstmt),
                (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
OCIBindByName (stmthp1, &bnd1p, errhp, (text*)":ENAME",
              (sb4)strlen((char *)":ENAME"),
              (void *) ename, sizeof(ename), SQLT_STR,
              (void *)&insname_ind, (ub2 *) 0, (ub2 *) 0, (ub4) 0,
              (ub4 *)0, OCI_DEFAULT);
OCIAttrSet ((void *) bnd1p, (ub4) OCI_HTYPE_BIND, (void *) &csid,
           (ub4) 0, (ub4)OCI_ATTR_CHARSET_ID, errhp);
OCIAttrSet((void *) bnd1p, (ub4) OCI_HTYPE_BIND, (void *) &ename_col_len,
           (ub4) 0, (ub4)OCI_ATTR_MAXDATA_SIZE, errhp);
...
/* Retrieving UTF-16 data */
OCIStmtPrepare (stmthp2, errhp, selname, strlen((char *) selname),
                (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
OCIDefineByPos (stmthp2, &dfn1p, errhp, (ub4)1, (void *)ename,
                (sb4)sizeof(ename), SQLT_STR,
                (void *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT);
OCIAttrSet ((void *) dfn1p, (ub4) OCI_HTYPE_DEFINE, (void *) &csid,
            (ub4) 0, (ub4)OCI_ATTR_CHARSET_ID, errhp);
...

Valid Data Types for Piecewise Operations

Only some data types can be manipulated in pieces. OCI applications can perform piecewise fetches, inserts, or updates of all the following data types:

• VARCHAR2

• STRING

• LONG

• LONG RAW

• RAW

• CLOB

• BLOB

Another way of using this feature for all data types is to provide data dynamically for array inserts or updates. The callbacks should always specify OCI_ONE_PIECE for the piecep parameter of the callback for data types that do not support piecewise operations.

Types of Piecewise Operations

You can perform piecewise operations in two ways:

• Use calls provided in the OCI library to execute piecewise operations under a polling paradigm.

• Employ user-defined callback functions to provide the necessary information and data blocks.

When you set the mode parameter of an OCIBindByPos() or OCIBindByName() call to OCI_DATA_AT_EXEC, it indicates that an OCI application is providing data for an INSERT or UPDATE operation dynamically at runtime.

Similarly, when you set the mode parameter of an OCIDefineByPos() call to OCI_DYNAMIC_FETCH, it indicates that an application dynamically provides allocation space for receiving data at the time of the fetch.

In each case, you can provide the runtime information for the INSERT, UPDATE, or FETCH operation in one of two ways: through callback functions, or by using piecewise operations. If callbacks are desired, an additional bind or define call is necessary to register the callbacks.

The following sections give specific information about runtime data allocation and piecewise operations for inserts, updates, and fetches.

Providing INSERT or UPDATE Data at Runtime

When you specify the OCI_DATA_AT_EXEC mode in a call to OCIBindByPos() or OCIBindByName(), the value_sz parameter defines the total size of the data that can be provided at run time. The application must be ready to provide to the OCI library the run time IN data buffers on demand as many times as is necessary to complete the operation. When the allocated buffers are no longer required, they must be freed by the client.

Runtime data is provided in one of two ways:

• You can define a callback using the OCIBindDynamic() function, which when called at run time returns either a piece of the data or all of it.

• If no callbacks are defined, the call to OCIStmtExecute() to process the SQL statement returns the OCI_NEED_DATA error code. The client application then provides the IN/OUT data buffer or piece using the OCIStmtSetPieceInfo() call that specifies which bind and piece are being used.

Performing a Piecewise Insert or Update

Once the OCI environment has been initialized, and a database connection and session have been established, a piecewise insert begins with calls to prepare a SQL or PL/SQL statement and to bind input values. Piecewise operations using standard OCI calls rather than user-defined callbacks do not require a call to OCIBindDynamic().

Note:

Additional bind variables that are not part of piecewise operations may require additional bind calls, depending on their data types.

Following the statement preparation and bind, the application performs a series of calls to OCIStmtExecute(), OCIStmtGetPieceInfo(), and OCIStmtSetPieceInfo() to complete the piecewise operation. Each call to OCIStmtExecute() returns a value that determines what action should be performed next. In general, the application retrieves a value indicating that the next piece must be inserted, populates a buffer with that piece, and then executes an insert. When the last piece has been inserted, the operation is complete.

Keep in mind that the insert buffer can be of arbitrary size and is provided at run time. In addition, each inserted piece does not need to be of the same size. The size of each piece to be inserted is established by each OCIStmtSetPieceInfo() call.

Note:

If the same piece size is used for all inserts, and the size of the data being inserted is not evenly divisible by the piece size, the final inserted piece is expected to be smaller. You must account for this by indicating the smaller size in the final OCIStmtSetPieceInfo() call.

The procedure is illustrated in Figure 5-3 and expanded in the steps following the figure.

Figure 5-3 Performing Piecewise Insert

Description of "Figure 5-3 Performing Piecewise Insert"

1. Initialize the OCI environment, allocate the necessary handles, connect to a server, authorize a user, and prepare a statement request by using OCIStmtPrepare2().

2. Bind a placeholder by using OCIBindByName() or OCIBindByPos(). You do not need to specify the actual size of the pieces you use, but you must provide the total size of the data that can be provided at run time.

3. Call OCIStmtExecute() for the first time. No data is being inserted here, and the OCI_NEED_DATA error code is returned to the application. If any other value is returned, it indicates that an error occurred.

4. Call OCIStmtGetPieceInfo() to retrieve information about the piece that must be inserted. The parameters of OCIStmtGetPieceInfo() include a pointer to a value indicating if the required piece is the first piece, OCI_FIRST_PIECE, or a subsequent piece, OCI_NEXT_PIECE.

5. The application populates a buffer with the piece of data to be inserted and calls OCIStmtSetPieceInfo() with these parameters:

   • A pointer to the piece

   • A pointer to the length of the piece

   • A value indicating whether this is the first piece (OCI_FIRST_PIECE), an intermediate piece (OCI_NEXT_PIECE), or the last piece (OCI_LAST_PIECE)

6. Call OCIStmtExecute() again. If OCI_LAST_PIECE was indicated in Step 5 and OCIStmtExecute() returns OCI_SUCCESS, all pieces were inserted successfully. If OCIStmtExecute() returns OCI_NEED_DATA, go back to Step 3 for the next insert. If OCIStmtExecute() returns any other value, an error occurred.

The piecewise operation is complete when the final piece has been successfully inserted. This is indicated by the OCI_SUCCESS return value from the final OCIStmtExecute() call.

Piecewise updates are performed in a similar manner. In a piecewise update operation the insert buffer is populated with data that is being updated, and OCIStmtExecute() is called to execute the update.

See Also:

"Polling Mode Operations in OCI"

Piecewise Operations with PL/SQL

An OCI application can perform piecewise operations with PL/SQL for IN, OUT, and IN/OUT bind variables in a method similar to that outlined previously. Keep in mind that all placeholders in PL/SQL statements are bound, rather than defined. The call to OCIBindDynamic() specifies the appropriate callbacks for OUT or IN/OUT parameters.

PL/SQL Indexed Table Binding Support

PL/SQL indexed tables can be passed as IN/OUT binds into PL/SQL anonymous blocks using OCI. The procedure for binding PL/SQL indexed tables is quite similar to performing an array bind for SQL statements. The OCI program must bind the location of an array with other metadata for the array as follows, using either OCIBindByName() or OCIBindByPos().The process of binding a C array into a PL/SQL indexed table bind variable must provide the following information during the bind call:

• void *valuep (IN/OUT) - A pointer to a location that specifies the beginning of the array in client memory

• ub2 dty (IN) - The data type of the elements of the array as represented on the client

• sb4 value_sz (IN) - The maximum size (in bytes) of each element of the array as represented on the client

• ub4 maxarr_len (IN) - The maximum number of elements of the data type the array is expected to hold in its lifetime

  If allocating the entire array up front for doing static bindings, the array must be sized sufficiently to contain maxarr_len number of elements, each of size value_sz. This information is also used to constrain the indexed table as seen by PL/SQL. PL/SQL cannot look up the indexed table (either for read or write) beyond this specified limit.

• ub4 *curelep (IN/OUT) - A pointer to the number of elements in the array (from the beginning of the array) that are currently valid.

  This should be less than or equal to the maximum array length. Note that this information is also used to constrain the indexed table as seen by PL/SQL. For IN binds, PL/SQL cannot read from the indexed table beyond this specified limit. For OUT binds, PL/SQL can write to the indexed table beyond this limit, but not beyond the maxarr_len limit.

For IN indexed table binds, before performing OCIStmtExecute(), the user must set up the current array length (*curelep) for that execution. In addition, the user also must set up the actual length and indicator as applicable for each element of the array.

For OUT binds, OCI must return the current array length (*curelep) and the actual length, indicator and return code as applicable for each element of the array.

For best performance, keep the array allocated with maximum array length, and then vary the current array length between executes based on how many elements are actually being passed back and forth. Such an approach does not require repeatedly deallocating and reallocating the array for every execute, thereby helping overall application performance.

It is also possible to bind using OCI piecewise calls for PL/SQL indexed tables. Such an approach does not require preallocating the entire array up front. The OCIStmtSetPieceInfo() and OCIStmtGetPieceInfo() calls can be used to pass in individual elements piecewise.

Providing FETCH Information at Run Time

When a call is made to OCIDefineByPos() with the mode parameter set to OCI_DYNAMIC_FETCH, an application can specify information about the data buffer at the time of fetch. You may also need to call OCIDefineDynamic() to set a callback function that is invoked to get information about your data buffer.

Runtime data is provided in one of two ways:

• You can define a callback using the OCIDefineDynamic() function. The value_sz parameter defines the maximum size of the data that is provided at run time. When the client library needs a buffer to return the fetched data, the callback is invoked to provide a runtime buffer into which either a piece of the data or all of it is returned.

• If no callbacks are defined, the OCI_NEED_DATA error code is returned and the OUT data buffer or piece can then be provided by the client application by using OCIStmtSetPieceInfo(). The OCIStmtGetPieceInfo() call provides information about which define and which piece are involved.

Performing a Piecewise Fetch

The fetch buffer can be of arbitrary size. In addition, each fetched piece does not need to be of the same size. The only requirement is that the size of the final fetch must be exactly the size of the last remaining piece. The size of each piece to be fetched is established by each OCIStmtSetPieceInfo() call. This process is illustrated in Figure 5-4 and explained in the steps following the figure.

Figure 5-4 Performing Piecewise Fetch

Description of "Figure 5-4 Performing Piecewise Fetch"

1. Initialize the OCI environment, allocate necessary handles, connect to a database, authorize a user, prepare a statement, and execute the statement by using OCIStmtExecute().

2. Define an output variable by using OCIDefineByPos(), with mode set to OCI_DYNAMIC_FETCH. At this point you do not need to specify the actual size of the pieces you use, but you must provide the total size of the data that is to be fetched at run time.

3. Call OCIStmtFetch2() for the first time. No data is retrieved, and the OCI_NEED_DATA error code is returned to the application. If any other value is returned, then an error occurred.

4. Call OCIStmtGetPieceInfo() to obtain information about the piece to be fetched. The piecep parameter indicates whether it is the first piece (OCI_FIRST_PIECE), a subsequent piece (OCI_NEXT_PIECE), or the last piece (OCI_LAST_PIECE).

5. Call OCIStmtSetPieceInfo() to specify the fetch buffer.

6. Call OCIStmtFetch2() again to retrieve the actual piece. If OCIStmtFetch2() returns OCI_SUCCESS, all the pieces have been fetched successfully. If OCIStmtFetch2() returns OCI_NEED_DATA, return to Step 4 to process the next piece. If any other value is returned, an error occurred.

See Also:

"Polling Mode Operations in OCI"

Piecewise Binds and Defines for LOBs

There are two ways of doing piecewise binds and defines for LOBs:

• Using the data interface

  You can bind or define character data for CLOB columns using SQLT_CHR (VARCHAR2) or SQLT_LNG (LONG) as the input data type for the following functions. You can also bind or define raw data for BLOB columns using SQLT_LBI (LONG RAW), and SQLT_BIN (RAW) as the input data type for these functions:

  • OCIDefineByPos()

  • OCIBindByName()

  • OCIBindByPos()

    See Also:

    • "Binding LOB Data" for usage and examples for both INSERT and UPDATE statements

    • "Defining LOB Data" for usage and examples of SELECT statements

  All the piecewise operations described later are supported for CLOB and BLOB columns in this case.

• Using the LOB locator

  You can bind or define a LOB locator for CLOB and BLOB columns using SQLT_CLOB (CLOB) or SQLT_BLOB (BLOB) as the input data type for the following functions.

  • OCIDefineByPos()

  • OCIBindByName()

  • OCIBindByPos()

  You must then call OCILob* functions to read and manipulate the data. OCILobRead2() and OCILobWrite2() support piecewise and callback modes.