5
Binding and Defining

This chapter revisits the basic concepts of binding and defining that were introduced in Chapter 2, "OCI Programming Basics", and provides more detailed information about the different types of binds and defines you can use in OCI applications. Additionally, this chapter discusses the use of arrays of structures, as well as other issues involved in binding, defining, and character conversions.

This chapter includes the following sections:

• Binding
• Advanced Bind Operations
• Defining
• Advanced Define Operations
• Binding and Defining Arrays of Structures
• DML with RETURNING Clause
• Character Conversion Issues in Binding and Defining
• PL/SQL REF CURSORs and Nested Tables
• Runtime Data Allocation and Piecewise Operations

Binding

Most DML statements, and some queries (such as those with a WHERE clause), require a program to pass data to Oracle as part of a SQL or PL/SQL statement. Such data can be constant or literal data, known when your program is compiled. For example, the following SQL statement, which adds an employee to a database contains several literals, such as 'BESTRY' and 2365:

INSERT INTO emp VALUES
    (2365, 'BESTRY', 'PROGRAMMER', 2000, 20)

Coding a statement like this into an application would severely limit its usefulness. You would need to change the statement and recompile the program each time you add a new employee to the database. To make the program more flexible, you can write the program so that a user can supply input data at runtime.

When you prepare a SQL statement or PL/SQL block that contains input data to be supplied at runtime, placeholders in the SQL statement or PL/SQL block mark where data must be supplied. For example, the following SQL statement contains five placeholders, indicated by the leading colons (for example, :ename), that show where input data must be supplied by the program.

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

You can use placeholders for input variables in any DELETE, INSERT, SELECT, or UPDATE statement, or PL/SQL block, in any position in the statement where you can use an expression or a literal value. In PL/SQL, placeholders can also be used for output variables.

For each placeholder in the SQL statement or PL/SQL block, you must call an OCI routine that binds the address of a variable in your program to the placeholder. When the statement executes, Oracle gets the data that your program placed in the input, or bind, variables and passes it to the server with the SQL statement. Data does not have to be in a bind variable when you perform the bind step. At the bind step, you are only specifying the address, datatype, and length of the variable.

For example, given the INSERT statement

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

and the following variable declarations

text     *ename, *job;
sword    empno, sal, deptno;

the bind step makes an association between the placeholder name and the address of the program variables. The bind also indicates the datatype and length of the program variables, as illustrated in Figure 5-1.

Figure 5-1 Using OCIBindByName() to Associate Placeholders with Program Variables

Text description of the illustration lnoci021.gif

If you change only the value of a bind variable, it is not necessary to rebind in order to execute the statement again. The bind is a bind by reference, so as long as the address of the bind variable and bind handle remain valid, you can reexecute a statement that references the variable without rebinding.

In the Oracle server, new datatypes have been implemented for named datatypes, REFs and LOBs, and they may be bound as placeholders in a SQL statement.

Named Binds and Positional Binds

The SQL statement in the previous section 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 their names. For binding purposes, an association is made between an input value and the position of the placeholder, using the OCIBindByPos() call.

The example from the previous section could also be used for a positional bind:

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

The five placeholders would then each be 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 the case of a duplicate bind, only a single bind call may be necessary. Consider the following SQL statement, which queries the database for those 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, the second placeholder inherits the bind information from the first placeholder.

OCI Array Interface

You can pass data to Oracle in various ways. You can execute a SQL statement repeatedly using the OCIStmtExecute() routine and supply different input values on each iteration. Alternatively, 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 Oracle when you need to update or insert 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 needs to insert 10 rows into the database. Calling OCIStmtExecute() ten times with single values results in ten 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 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 bind binds. You can use OCIBindByName() or OCIBindByPos() to bind 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, 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 using bind calls.

For example, in a PL/SQL block such as

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

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 Binding

Binding placeholders is done in one or more steps. For a simple scalar or array bind, it is only necessary to specify an association between the placeholder and the data. This is done by using OCI bind by name (OCIBindByName()) or OCI bind by position (OCIBindByPos()) call.

Once the bind is complete, the OCI library knows where to find the input data (or where to put PL/SQL output data) when the SQL statement is executed. As mentioned in the section "Binding", 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 shows handle allocation and binding for each of five placeholders 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 for each bind handle. */
checkerr(errhp, OCIBindByName(stmthp, &bnd1p, errhp, (text *) ":ENAME",
    strlen(":ENAME"), (ub1 *) ename, enamelen+1, STRING_TYPE, (dvoid *) 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, STRING_TYPE, (dvoid *)
    &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), INT_TYPE,
    (dvoid *) &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), INT_TYPE, 
    (dvoid *) 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), INT_TYPE,
    (dvoid *) 0, (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0,OCI_DEFAULT))

PL/SQL Example

Perhaps the most common use for PL/SQL blocks in an OCI program is to call stored procedures or stored functions. For example, assume that there is a procedure called RAISE_SALARY stored in the database, and you want to call this procedure from an OCI program. You do this by embedding a call to that procedure in an anonymous PL/SQL block, then processing the PL/SQL block in the OCI program.

The following program fragment shows how to embed a stored procedure call in an OCI application. For the sake of brevity, only the relevant portions of the program are reproduced here.

The program passes an employee number and a salary increase as inputs to a stored procedure called raise_salary, which takes these parameters:

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 which results is returned in the stored procedure's OUT variable new_salary, and the program displays this value.

/* 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;
  dvoid    *tmp;
  OCISession *usrhp = (OCISession *)NULL; 
...
/* attach to database server, and perform necessary initializations
and authorizations */
...
      /* allocate a statement handle */
  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
           OCI_HTYPE_STMT, 100, (dvoid **) &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, (dvoid *) 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, (dvoid *) 0,
             (ub2 *) 0, (ub2) 0, (ub4) 0, (ub4 *) 0, OCI_DEFAULT));

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

checkerr( OCIBindByName(stmthp, &bnd3p, errhp, (text *) ":new_salary",
             -1, (ub1 *) &new_sal,
             (sword) sizeof(new_sal), SQLT_INT, (dvoid *) 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);
}

The following is one possible sample output from this program. Before execution, the salary of employee 7954 is 2000.

Enter the employee number: 7954
Enter employee's raise: 1000
The new salary is 3000.

The previous section and example demonstrated how to perform a simple scalar bind. In that case, only a single bind call is necessary. In some cases, additional bind calls are necessary to define specific attributes for specific bind datatypes or execution modes. These more sophisticated bind operations are discussed in the following section.

Oracle also provides predefined C datatypes that map object attributes.

Advanced Bind Operations

The section "What is Binding?" discussed how a basic bind operation is performed to create an association between a placeholder in a SQL statement and a program variable using OCIBindByName() or OCIBindByPos().

This section covers more advanced bind operations, including multi-step binds, and binds of named data types and REFs.

In certain cases, additional bind calls are necessary to define specific attributes for certain bind data types or certain execution modes.

The following sections describe these special cases, and the information about binding is summarized in Table 5-1, "Bind Information for Different Bind Types".

Named Data Type Binds

For information on binding named data types (objects),

Binding REFs

For information on this topic,

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 OCILob functions.
• Bind the LOB value directly, without using the LOB locator.

Both of these ways are discussed next.

Binding LOB Locators

Either a single locator or an array of locators can be bound in a single bind call. In each case, the application must pass the address of a LOB locator and not the locator itself. For example, if an application has prepared a SQL statement like

INSERT INTO some_table VALUES (:one_lob)

where one_lob is a bind variable corresponding to a LOB column, and has made the following declaration:

OCILobLocator * one_lob;

Then the following sequence of steps would be used to bind the placeholder, and execute the statement

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

You could also do an array insert using the same SQL INSERT statement. In this case, the application would include the following code:

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

Note that you must allocate descriptors with the OCIDescriptorAlloc() routine before they can be used. In the case of an array of locators, you must initialize each array element using OCIDescriptorAlloc(). Use OCI_DTYPE_LOB as the type parameter when allocating BLOBs, CLOBs, and NCLOBs. Use OCI_DTYPE_FILE when allocating BFILEs

Binding LOB Data

Oracle allows nonzero binds for INSERTs and UPDATEs of any size LOB. So you can bind up to 4 gigabytes of data into a LOB column using OCIBindByPos(), OCIBindByName(), and PL/SQL binds. Because you can have multiple LOBs in a row, you can bind up to 4 gigabytes of data for each one of those LOBs in the same INSERT or UPDATE statement.

The bind of more than 4 kilobytes of data to a LOB column uses space from the temporary tablespace. Users of this features should make sure that their temporary tablespace is big enough to hold at least the amount of data equal to the sum of all the bind lengths for LOBs. If your temporary tablespace is extendable, it will be extended automatically after the existing space is fully consumed. Use the command:

"CREATE TABLESPACE ... AUTOEXTENT ON ... TEMPORARY ...;"  

to create an extendable temporary tablespace.

Restrictions on LOB Binds

• If a table has both LONG and LOB columns, then you can have binds of greater than 4 kilobytes for either the LONG column or the LOB columns, but not both in the same statement.
• You cannot bind data of any size to LOB attributes in object-relational datatypes.For LOB attributes, you need to insert an empty LOB locator and then modify the contents of the LOB using OCILob*() functions.
• In an INSERT AS SELECT operation, Oracle does not allow binding of any length data to LOB columns.
• Oracle doesn't do any implicit conversion such as HEX to RAW or RAW to HEX for data of size more than 4000 bytes. The following PL/SQL code illustrates this:

  create table t (c1 clob, c2 blob);
  declare
    text   varchar(32767);
    binbuf raw(32767);
  begin
    text := lpad ('a', 12000, 'a');
    binbuf := utl_raw.cast_to_raw(text);
  
    -- The following works ...
    insert into t values (text, binbuf);
  
    -- The following won't work because Oracle won't do implicit 
    -- hex to raw conversion.
    insert into t (c2) values (text);  
  
    -- The following won't work because Oracle won't do implicit 
    -- raw to hex conversion.
    insert into t (c1) values (binbuf);  
  
    -- The following won't work because we can't combine the
    -- utl_raw.cast_to_raw() operator with the >4k bind.
    insert into t (c2) values (utl_raw.cast_to_raw(text));
  
  end;
  /
  
  

• If you bind more than 4000 bytes of data to a BLOB or a CLOB, and the data is filtered by a SQL operator, then Oracle will limit the size of the result to at most 4000 bytes.

  For example:

  create table t (c1 clob, c2 blob);
  -- The following command inserts only 4000 bytes because the result of
  -- LPAD is limited to 4000 bytes
  insert into t(c1) values (lpad('a', 5000, 'a')); 
  
  -- The following command inserts only 2000 bytes because the result of
  -- LPAD is limited to 4000 bytes, and the implicit hex to raw conversion
  -- converts it to 2000 bytes of RAW data.
  insert into t(c2) values (lpad('a', 5000, 'a')); 
  

Examples of Binding LOBs

Consider the following SQL statements which will be used in the examples that follow:

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

Example1: Binding LOBs

void insert()                 /* A function in an OCI program */
{
   /* The following is allowed */
   ub1 buffer[8000];
   text *insert_sql = "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, (dvoid *)buffer, 8000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[2], errhp, 3, (dvoid *)buffer, 2000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT);
}

Example2: Binding LOBs

void insert()
{
   /* The following is allowed */
   ub1 buffer[8000];
   text *insert_sql = "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, (dvoid *)buffer, 2000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT);
}

Example3: Binding LOBs

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

Example4: Binding LOBs

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

Example5: Binding LOBs

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

Example6: Binding LOBs

void insert()
{
   /* The following is NOT allowed because we try to insert >4000 bytes
    * to 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, (dvoid *)buffer, 8000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIBindByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer, 8000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT);
}

Example7: Binding LOBs

void insert()
{
   /* The following is NOT allowed because we try to insert data into
    * LOB attributes */
   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, (dvoid *)buffer, 2000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT);
}

Example8: Binding LOBs

void insert()
{
   /* The following is NOT allowed because we try to 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, (dvoid *)buffer, 8000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT);
}

Other Disallowed Operations

Other update operations similar to the disallowed insert operations are also not allowed. Piecewise and callback INSERT or UPDATE operations similar to the disallowed regular INSERT or UPDATE operations are also not allowed.

Binding FILEs

When using a FILE locator as a bind variable for an INSERT or UPDATE statement, you must first initialize the locator with a directory alias and filename (using OCILobFileSetName()) before issuing the INSERT or UPDATE statement.

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 will use the callback method for providing data at runtime. The call to OCIBindDynamic() sets up the callback routines, if necessary, for indicating the data or piece that is being provided.

If the OCI_DATA_AT_EXEC mode is chosen, but the standard OCI piecewise polling method will be 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 datatype of SQLT_RSET.

Summary of Bind Information

The following table summarizes the bind calls necessary for different types of binds. For each type, the table lists the bind datatype (passed in the dty parameter of OCIBindByName() or OCIBindByPos()), and notes about the bind

.

Table 5-1 Bind Information for Different Bind Types  

Type of Bind	Bind Datatype	Notes
Scalar	any scalar datatype	Bind a single scalar using OCIBindByName() or OCIBindByPos().
Array of Scalars	any scalar datatype	Bind an array of scalars using OCIBindByName() or OCIBindByPos().
Named Data Type	SQLT_NTY	Two bind calls are required: OCIBindByName() or OCIBindByPos() OCIBindObject()
REF	SQLT_REF	Two bind calls are required: OCIBindByName() or OCIBindByPos() OCIBindObject()
LOB BFILE	SQLT_BLOB SQLT_CLOB	Allocate the LOB locator using OCIDescriptorAlloc(), and then bind its address (OCILobLocator **) with OCIBindByName() or OCIBindByPos(), using one of the LOB datatypes.
Array of Structures or Static Arrays	varies	Two bind calls are required: OCIBindByName() or OCIBindByPos() OCIBindArrayOfStruct()
Piecewise Insert	varies	OCIBindByName() or OCIBindByPos() is required. The application may also need to call OCIBindDynamic() to register piecewise callbacks.
REF CURSOR variables	SQLT_RSET	Allocate a statement handle, OCIStmt, and then bind its address (OCIStmt **) using the SQLT_RSET datatype.

Defining

Query statements return data from the database to your application. When processing a query, you must define an output variable or an array of output variables for each item in the select-list from which you want to retrieve data. The define step creates an association that determines where returned results are stored, and in what format.

For example, if your OCI statement processes the following statement:

SELECT name, ssn FROM employees
    WHERE empno = :empnum

you would normally need to define two output variables, one to receive the value returned from the name column, and one to receive the value returned from the ssn column.

If the SELECT statement being processed might return more than a single value for a query, the output variables you define may be arrays instead of scalar values.

Depending on the application, the define step can take place before or after the execute. If the datatypes of select-list items are known when the application is coded, the define can take place before the statement is executed. If your application is processing dynamic SQL statements--statements entered by you at runtime-- or statements that do not have a clearly defined select-list, such as

SELECT * FROM employees

the application must execute the statement and retrieve describe information before defining output variables.

The OCI processes the define call locally, on the client side. In addition to indicating the location of buffers where results should be stored, the define step also determines what type of data conversions, if any, will take place when data is returned to the application.

The dty parameter of the OCIDefineByPos() call specifies the datatype of the output variable. The OCI is capable of a wide range of data conversions when data is fetched into the output variable. For example, internal data in Oracle DATE format can be automatically converted to a string datatype on output.

Steps Used in Defining

Defining output variables is done in one or more steps. A basic define is accomplished with the OCI define by 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 datatypes or fetch modes.

Once the define step is complete, the OCI library knows where to put retrieved data after fetching it from the database.

The following example code shows a scalar output variable being defined following an execute and a describe.

/* The following statement was prepared, and associated with statement
   handle stmthp1.

   SELECT dname FROM dept WHERE deptno = :dept_input

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

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

/* 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, stmthp1, errhp, 0, 0, 0, 0, 
           OCI_DEFAULT))
          && (status != OCI_NO_DATA))
      {
        checkerr(errhp, status);
        do_exit(EXIT_FAILURE);
      }  
      if (status == OCI_NO_DATA) {
        printf("The dept you entered doesn't exist.\n");
        return 0;
        }
/* The next two statements describe the select-list item, dname, and
   return its length */
checkerr(errhp, OCIParamGet(stmthp1, errhp, &parmdp, (ub4) 1));
checkerr(errhp, OCIAttrGet((dvoid*) parmdp, (ub4) OCI_DTYPE_PARAM, 
        (dvoid*) &deptlen, (ub4 *) 0, (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(stmthp1, &defnp, errhp,
             1, (ub1 *) dept, deptlen+1,
             SQLT_STRING, (dvoid *) 0,
             (ub2 *) 0, OCI_DEFAULT))
  {
    checkerr(errhp, status);
    do_exit(EXIT_FAILURE);
  }

Advanced Defines

In some cases the define step requires more than just a call to OCIDefineByPos(). There are additional calls that define the attributes of an array fetch (OCIDefineArrayOfStruct()) or a named data type fetch (OCIDefineObject()). For example, to fetch multiple rows with a column of named data types, all three calls must be invoked for the column; but to fetch multiple rows of scalar columns, OCIDefineArrayOfStruct() and OCIDefineByPos() are sufficient.

Oracle also provides pre-defined C datatypes that map object type attributes.

Advanced Define Operations

The section "What is Defining?" discussed how a basic define operation is performed to create an association between a SQL select-list item and an output buffer in an application.

This section covers more advanced defined operations, including multi-step defines, and defines of named data types and REFs.

In some cases the define step requires more than just a call to OCIDefineByPos(). There are additional calls that define the attributes of an array fetch (OCIDefineArrayOfStruct()) or a named data type fetch (OCIDefineObject()). For example, to fetch multiple rows with a column of named data types, all the three calls must be invoked for the column; but to fetch multiple rows of scalar columns only OCIDefineArrayOfStruct() and OCIDefineByPos() are sufficient.

The following sections discuss specific information pertaining to different types of defines.

Defining Named Data Type Output Variables

For information on defining named data type (object) output variables, refer to "Defining Named Datatype Output Variables".

Defining REF Output Variables

For information on defining REF output variables, refer to "Defining REF Output Variables".

Defining LOB Output Variables

There are two ways of defining LOBs:

• Define as 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 OCILob functions.
• Define as a LOB value directly, without using the LOB locator.

Both of these ways are discussed next.

Defining LOB Locators

Either a single locator or an array of locators can be defined in a single define call. In each case, the application must pass the address of a LOB locator and not the locator itself. For example, if an application has prepared a SQL statement like:

SELECT lob1 FROM some_table;

where lob1 is the LOB column and one_lob is a define variable corresponding to a LOB column with the following declaration:

OCILobLocator * one_lob;

Then the following sequence of steps would be used to bind the placeholder, and execute the statement

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

You could also do an array select using the same SQL SELECT statement. In this case, the application would include the following code:

OCILobLocator * lob_array[10];
...
for (i=0; i<10, i++)
   lob_array[i] = OCIDescriptorAlloc(...OCI_DTYPE_LOB...);
                                     /* initialize array of locators */
...
OCIDefineByPos(...,1, (dvoid *) lob_array,... SQLT_CLOB, ...);
OCIDefineArrayOfStruct(...);
OCIStmtExecute(...,10,...);               /* 10 is the iters parameter */

Note that you must allocate descriptors with the OCIDescriptorAlloc() routine before they can be used. In the case of an array of locators, you must initialize each array element using OCIDescriptorAlloc(). Use OCI_DTYPE_LOB as the type parameter when allocating BLOBs, CLOBs, and NCLOBs. Use OCI_DTYPE_FILE when allocating BFILEs

Defining LOB Data

Oracle allows nonzero defines for SELECTs of any size LOB. So you can select up to 4 gigabytes of data from a LOB column using OCIDefineByPos(), and PL/SQL defines. Because you can have multiple LOBs in a row, you can select up to 4 gigabytes of data from each one of those LOBs in the same SELECT statement.

Examples of Defining LOBs

Consider the following SQL statements which will be used in the examples that follow:

CREATE TABLE lob_tab (C1 CLOB, C2 CLOB);

Example1: Defining LOBs

void select_define_before_execute()      /* A function in an OCI program */
{
   /* The following is allowed */
   ub1 buffer1[8000];
   ub1 buffer2[8000];
   text *select_sql = "SELECT c1, c2 FROM lob_tab"; 
   OCIStmtPrepare(stmthp, errhp, select_sql, strlen((char*)select_sql), 
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIDefineByPos(stmthp, &defhp[0], errhp, 1, (dvoid *)buffer1, 8000, 
                SQLT_LNG, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIDefineByPos(stmthp, &defhp[1], errhp, 2, (dvoid *)buffer2, 8000, 
                SQLT_LNG, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 1, 0, OCI_DEFAULT);
}

Example2: Defining LOBs

void select_execute_before_define()
{
   /* The following is allowed */
   ub1 buffer1[8000];
   ub1 buffer2[8000];
   text *select_sql = "SELECT c1, c2 FROM lob_tab"; 
   OCIStmtPrepare(stmthp, errhp, select_sql, strlen((char*)select_sql), 
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
   OCIStmtExecute(svchp, stmthp, errhp, 0, 0, OCI_DEFAULT);
   OCIDefineByPos(stmthp, &bindhp[0], errhp, 1, (dvoid *)buffer1, 8000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIDefineByPos(stmthp, &bindhp[1], errhp, 2, (dvoid *)buffer2, 8000, 
                SQLT_LNG, 0, 0, 0, 0, 0, (ub4) OCI_DEFAULT);
   OCIStmtFetch(stmthp, errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
}

Example3: Defining LOBs

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

Defining PL/SQL Output Variables

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

Defining For a Piecewise Fetch

When performing a piecewise fetch, an initial call to OCIDefineByPos() is required. An additional call to OCIDefineDynamic() is necessary if the application will use callbacks rather than the standard polling mechanism for fetching data.

Binding and Defining Arrays of Structures

When using arrays of structures, an initial call to OCIDefineByPos() is required. An additional call to OCIDefineArrayOfStruct() is necessary to set up additional parameters, including the skip parameter necessary for arrays of structures operations.

The arrays of structures functionality of OCI can simplify the processing of multi-row, multi-column operations. The OCI programmer can create a structure of related scalar data items and then fetch values from the database into an array of these structures or insert values into the database from an array of these structures.

For example, an application may need to fetch multiple rows of data from three columns named NAME, AGE, and SALARY. The OCI application could include the definition of a structure containing separate fields to hold the NAME, AGE and SALARY data from one row in the database table. The application would then fetch data into an array of these structures.

In order to perform a multi-row, multi-column operation using an array of structures, the developer associates each column involved in the operation with a field in a structure. This association, which is part of the OCIDefineArrayOfStruct() and OCIBindArrayOfStruct() calls, specifies where fetched data will be stored, or where inserted or updated data will be found.

Figure 5-2, "Fetching Data Into an Array of Structures" is a graphical representation of this process. In the figure, an application fetches various fields from a database row into a single structure in an array of structures. Each column being fetched corresponds to one of the fields in the structure.

Figure 5-2 Fetching Data Into an Array of Structures

Text description of the illustration lnoci001.gif

Skip Parameters

When you split column data across an array of structures, it is no longer contiguous. The single array of structures stores data as though it were composed of several interleaved arrays of scalars. Because of this fact, developers must specify a "skip parameter" for each field they are binding or defining. This skip parameter specifies the number of bytes that need to be skipped in the array of structures before the same field is encountered again. In general this will be equivalent to the byte size of one structure.

The figure below demonstrates how a skip parameter is determined. In this case the skip parameter is the sum of the sizes of the fields field1, field2, andfield3, which is 8 bytes. This equals the size of one structure.

Figure 5-3 Determining Skip Parameters

Text description of the illustration lnoci002.gif

On some systems it may be necessary to set the skip parameter to be sizeof(one_array_element) rather than sizeof(struct). This is because some compilers may insert padding into a structure. For example, 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 three bytes of padding after the ub1 so that the ub4 which 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 systems this will produce a proper skip parameter of eight. On other systems, skip_parameter will be set to five bytes by this statement. In this case, use the following statement to get the correct value for the skip parameter:

skip_parameter = sizeof(demo_array[0]);

Skip Parameters for Standard Arrays

The ability to work with arrays of structures is an extension of the functionality for binding and defining arrays of program variables. Programmers can also work with standard arrays (as opposed to arrays of structures). When specifying a standard array operation, the related skip will be equal to the size of the datatype of the array under consideration. For example, for an array declared as

text emp_names[4][20];

the skip parameter for the bind or define operation will be 20. Each data element in the array is then recognized as a separate unit, rather than being part of a structure.

OCI Calls Used with Arrays of Structures

Two OCI calls must be used when performing operations involving arrays of structures: OCIBindArrayOfStruct() (for binding fields in arrays of structures for input variables) and OCIDefineArrayOfStruct() (for defining arrays of structures for output variables).

Arrays of Structures and Indicator Variables

The implementation of arrays of structures also supports the use of indicator variables and return codes. OCI application developers can declare parallel arrays of column-level indicator variables and return codes, corresponding to the arrays of information being fetched, inserted, or updated. These arrays can have their own skip parameters, which are specified during a call to OCIBindArrayOfStruct() or OCIDefineArrayOfStruct().

You can set up arrays of structures of program values and indicator variables in many ways. For example, 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.

DML with RETURNING Clause

The OCI supports the use of the RETURNING clause with SQL INSERT, UPDATE, and DELETE statements. This section outlines the rules an OCI application must follow to correctly implement DML statements with the RETURNING clause.

Using DML with RETURNING Clause

Using the RETURNING clause with a DML statement nonzero you to essentially combine two SQL statements into one, possibly saving you 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 the OCI, the values are returned to the application through the use of OUT bind variables. The rules for binding these variables are described in the next section. In the following examples, the bind variables are indicated by the preceding colon, such as :out1. These examples assume the existence of a table called table1, which contains three columns: col1, col2, and col3.

For example, the following statement inserts new values into the database and then retrieves the column values of the affected row from the database, allowing your application to work with inserted rows.

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

The next example uses the UPDATE statement. This statement updates the values of all columns whose col1 value falls within a certain range, and then returns the affected rows to the application, allowing the application to see which rows 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 following DELETE statement deletes the rows whose col1 value falls within a certain range, and then returns the data from those rows so that the application can check them.

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

Note that in both the UPDATE and DELETE examples there is the possibility that the statement will affect multiple rows in the table. Additionally, a DML statement could be executed multiple times in a single OCIExecute() statement. Because of this possibility for multiple returning values, an OCI application may not know how much data will be returned at runtime. As a result, the variables corresponding to the RETURNING...INTO placeholders must be bound in OCI_DATA_AT_EXEC mode. It is an additional requirement that the application must define its own dynamic data handling callbacks rather than using the OCI_DATA_AT_EXEC 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. 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

Binding RETURNING...INTO variables

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

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

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

   Note: The OCI only supports the callback mechanism for RETURNING clause binds. The polling mechanism is not supported.

2. When binding RETURNING clause placeholders, you must supply a valid out bind function as the ocbfp parameter of the OCIBindDynamic() call. This function must provide storage to hold the returned data.
3. The icbfp parameter of OCIBindDynamic() call should provide a "dummy" function which returns NULL values when called.
4. The piecep parameter of OCIBindDynamic() must be set to OCI_ONE_PIECE.
5. No duplicate binds are allowed in a DML statement with a RETURNING clause, such as no duplication between bind variables in the DML section and the RETURNING section of the statement.

Error Handling

The out bind function provided to OCIBindDynamic() must be prepared to receive partial results of a statement in the event of an error. For example, if the application has issued a DML statement which should be executed 10 times, and an error occurs during the fifth iteration, the server will still return the data from iterations 1 through 4. The callback function would still be called to receive data for the first four iterations.

DML with RETURNING REF...INTO clause

The RETURNING clause can also be used to return a REF to an object which is being inserted into or updated in the database. The following SQL statement shows how this could be used.

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

This statement updates several attributes of an object in an object table and then returns a REF to the object (along with the scalar ZIP code) in the RETURNING clause.

Binding the REF output variable in an OCI application requires three steps:

1. The initial bind information is set using OCIBindByName()
2. Additional bind information for the REF (including the TDO) is set with OCIBindObject()
3. A call to OCIBindDynamic()

The following pseudocode shows a function which performs the binds necessary for the above example.

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"), 
                       (dvoid *) 0, (sb4) sizeof(OCIRef *), SQLT_REF, 
                       (dvoid *) 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"), 
                       (dvoid *) 0, (sb4) MAXZIPLEN, SQLT_CHR, 
                       (dvoid *) 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,  
          (dvoid **) &addrref[0], (ub4 *) 0, (dvoid **) 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, (dvoid *) &pos[0], cbf_no_data, 
                    (dvoid *) &pos[0], cbf_get_data) 
  ||  OCIBindDynamic(bndhp[3], errhp, (dvoid *) &pos[1], cbf_no_data, 
                    (dvoid *) &pos[1], cbf_get_data)) 
  { 
    return OCI_ERROR; 
  } 
 
  return OCI_SUCCESS; 
}

Additional Notes About 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. Thus, when the callback is called the first time in a particular iteration (that is, index=0), you can allocate space for all the rows which will be returned for that bind variable. When the callback is called subsequently (with index>0) within the same iteration, you can merely increment the buffer pointer to the correct memory within the allocated space to retrieve the data.

Array Interface for DML RETURNING Statements

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

When the statement executes, 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 successfully completed. At this point the application may choose to roll back the transaction or ignore the warning.

Character Conversion Issues in Binding and Defining

This section discusses issues involving character conversions between the client and the server.

Choosing Character Set

Oracle provides support for character data in the database, and OCI provides support for binding and defining character data. If a database column containing character data is defined to be an NCHAR/NVARCHAR2 column, then a bind or define involving that column must take into account special considerations for dealing with character set specifications.

These considerations are necessary in case the width of the client character set is different from that on the server, and also for proper character conversion between the client and server. During conversion of data between different character sets, the size of the data may grow or shrink as much as fourfold. Care must be taken to insure that buffers provided to hold the data are of sufficient size.

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

Character Set Form and ID

Each OCI bind and define handle has OCI_ATTR_CHARSET_FORM and OCI_ATTR_CHARSET_ID attributes associated with it. An application can set these attributes with the OCIAttrSet() call in order to specify the character form and character set ID of the bind/define buffer.

The form attribute (OCI_ATTR_CHARSET_FORM) indicates the character set the client buffer is in, for binds, and the character set in which to store fetched data, for defines. It has two possible values:

• SQLCS_IMPLICIT - indicates database character set ID
• SQLCS_NCHAR - indicates national character set ID

The default value is SQLCS_IMPLICIT, which implies the database character set for the bind or define buffer and the character data in the buffer is converted to the server database character set. SQLCS_NCHAR implies national character set ID for the bind or define buffer and the client buffer data is converted to the server national character set.

If the character set ID attribute (OCI_ATTR_CHARSET_ID) is not specified, then the default value of the database or national character set ID of the client is used, depending on the value of form. They are the values specified in the NLS_LANG and NLS_NCHAR environment variables.

Implicit Conversion Between CHAR and NCHAR

As the result of implicit conversion between database character sets and national character sets, OCI can support cross binding and cross defining between CHAR and NCHAR. For example, even though the OCI_ATTR_CHARSET_FORM attribute is set to be SQLCS_NCHAR, OCI enables converting data to the database character set if the data is inserted into a CHAR column.

Setting Client Character Sets in OCI

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

Here is a pseudocode example of the use of these functions:

OCIEnv *envhp; 
ub2 ncsid = 2; /* we8dec */ 
ub2 hdlcsid, hdlncsid; 
OraText thename[20]; 
utext *selstmt = UTF16("SELECT ename FROM emp"); /* make a 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, 0, OCI_NLS_CHARSET_ID, 0, 0); 
OCIAttrSet(defnp, ..., &hdlcsid, 0, OCI_ATTR_CHARSET_ID, errhp); 
           /* change charset id to NLS_LANG setting*/ 
...

Using OCI_ATTR_MAXDATA_SIZE Attribute

Every bind handle has a OCI_ATTR_MAXDATA_SIZE attribute. This attribute specifies the number of bytes to be allocated on the server to accommodate the client-side bind data after any necessary character set conversions.

An application will typically set OCI_ATTR_MAXDATA_SIZE to the maximum size of the column or the size of the PL/SQL variable, depending on how it is used. Oracle issues an error if OCI_ATTR_MAXDATA_SIZE is not a large enough value to accommodate the data after conversion, and the operation will fail.

The following scenarios demonstrate some examples of the use of the OCI_ATTR_MAXDATA_SIZE attribute:

• Scenario 1: CHAR (source data) -> non-CHAR (destination column)

  In this case there are implicit bind conversions taking place on the data. The recommended value of OCI_ATTR_MAXDATA_SIZE in this case would be the size of the source buffer multiplied by the worst-case expansion between the client and server character sets.

• Scenario 2: CHAR (source data) -> CHAR (destination column) or non-CHAR (source data) -> CHAR (destination column)

  In either of these cases, the recommended value of OCI_ATTR_MAXDATA_SIZE is the size of the column.

• Scenario 3: CHAR (source data) -> PL/SQL variable

  In this case, the recommended value of OCI_ATTR_MAXDATA_SIZE is the size of the PL/SQL variable.

Using OCI_ATTR_MAXCHAR_SIZE Attribute

Bind and define handles have an attribute, OCI_ATTR_MAXCHAR_SIZE, associated with them. An application can use this attribute to work with data in terms of number of characters, rather than number of bytes.

For binds, the OCI_ATTR_MAXCHAR_SIZE attribute sets the number of characters that an application reserves on the server to store the data being bound. This works together with the OCI_ATTR_MAXDATA_SIZE attribute, and the nonzero minimum of their derived byte length is used.

For example, if OCI_ATTR_MAXDATA_SIZE is set to 100, and OCI_ATTR_MAXCHAR_SIZE is set to 0, then the maximum possible size of the data on the server after conversion is 100 bytes. However, if OCI_ATTR_MAXDATA_SIZE is set to 300, and OCI_ATTR_MAXCHAR_SIZE is set to a nonzero value, such as 100, then if the character set has 2 bytes/character, the maximum possible allocated size is 200 bytes, which is the minimum of 300 bytes and 2*100 bytes.

For defines, the OCI_ATTR_MAXCHAR_SIZE attribute specifies the maximum number of characters that the client application allows in the return buffer. Its derived byte length overrides the maxlength parameter specified in the OCIDefineByPos() call.

Buffer Expansion During Binding

Update or insert operations are done through variable binding. When binding variables, specify OCI_ATTR_MAXCHAR_SIZE and/or OCI_ATTR_MAXDATA_SIZE in the bind handle to specify character and byte constraints to be used while inserting data in the server.

These attributes are defined as:

• OCI_ATTR_MAXCHAR_SIZE sets the maximum number of allowed characters in the buffer on the server side.
• OCI_ATTR_MAXDATA_SIZE sets the maximum number of allowed bytes in the buffer on the server side.

If neither of these two attributes is set, OCI expands the buffer using its best estimates.

Do not set OCI_ATTR_MAXDATA_SIZE for OUT binds or for PL/SQL binds.

Only set OCI_ATTR_MAXDATA_SIZE for INSERT or UPDATE statements.

In Binds

If the underlying column was created using character length semantics, then it is preferable to specify the constraint using OCI_ATTR_MAXCHAR_SIZE. In this case, as long as the actual buffer contains less characters that specified in OCI_ATTR_MAXCHAR_SIZE, no constraints are violated at OCI level.

If the underlying column was created using byte length semantics, then use OCI_ATTR_MAXDATA_SIZE in the bind handle to specify the byte constraint on the server. If you also specify an OCI_ATTR_MAXCHAR_SIZE value, then this constraint is additionally imposed when allocating the receiving buffer on the server side.

Dynamic SQL

For dynamic SQL, you can use the explicit describe to get OCI_ATTR_DATA_SIZE and or OCI_ATTR_CHAR_SIZE in parameter handles as a guide for setting OCI_ATTR_MAXDATA_SIZE and OCI_ATTR_MAXCHAR_SIZE attributes in bind handles. Furthermore, it is always a safer practice to specify OCI_ATTR_MAXDATA_SIZE and OCI_ATTR_MAXCHAR_SIZE to be no more than the actual column width in bytes, and characters.

Buffer Expansion During Inserts

Consider the following scenario where you should avoid unexpected behavior caused by buffer expansion during inserts.

If the database column has character length semantics and the user tries to insert data into that column using OCIBindByPos() or OCIBindByName() and sets only the OCI_ATTR_MAXCHAR_SIZE to, say 3000 bytes. The database character set is UTF8 and the client character set is ASCII. Then, in this case although 3000 characters will fit in a buffer of size 3000 bytes for the client, on the server side it might expand to more than 4000 bytes. In this case, unless the underlying column is a LONG or a LOB type, the server will return an error. You can get around this problem by specifying, additionally, the OCI_ATTR_MAXDATA_SIZE to be 4000, to guarantee that the data will never exceed 4000 bytes.

Constraint Checking During Defining

To select from data columns into client buffers, OCI uses defined variables. You can set an OCI_ATTR_MAXCHAR_SIZE value on the define buffer to impose an additional character length constraint on the define buffer. There is no OCI_ATTR_MAXDATA_SIZE attribute for define handles since the buffer size in bytes serves as the limit on byte length. So, normally, the define buffer size provided in the OCIDefineByPos() call can be used as the byte constraint.

Dynamic SQL Selects

When sizing buffers for dynamic SQL, always use the OCI_ATTR_DATA_SIZE value in the implicit describe to size your buffer to avoid truncation. If the database column is created using character length semantics, which is known through OCI_ATTR_CHAR_USED attribute, then you can use the OCI_ATTR_MAXCHAR_SIZE value to set an additional constraint on the define buffer. In this case no more than the number of OCI_ATTR_MAXCHAR_SIZE characters will be put in the buffer.

Return Lengths

The following length values are always in bytes irrespective of the character length semantics of the database:

• The value returned in the alen, or the actual length field in binds and defines.
• The value that appears in the length prefixed in special datatypes like VARCHAR and LONG VARCHAR
• The value of the indicator variable in case of truncation.

The only exception to this rule is for string buffers in OCI_UTF16ID. If you specify the character set ID as OCI_UTF16ID for a bind/define handle, then the above lengths are in UTF-16 units.

Note that the buffer sizes in the bind and define calls and the piece sizes in the OCIGetPieceInfo() and OCISetPieceInfo() and the callbacks are always in bytes.

General Compatibility Issues for Character Length Semantics

• For a release 9.0 or later client talking to an 8.1 or earlier server, OCI_ATTR_MAXCHAR_SIZE is not understood by the server, so this value will be ignored. If you specify only this value, OCI will derive the corresponding OCI_ATTR_MAXDATA_SIZE value based on the maximum bytes for each character for the client-side character set.
• For an 8.1 or earlier client talking to a 9.0 or later server, the client will never be able to specify an OCI_ATTR_MAXCHAR_SIZE value, so the server will consider the client always expecting byte length semantics. This is similar to the situation when the client specifies only OCI_ATTR_MAXDATA_SIZE.

So in both cases, the server and client can exchange information in an appropriate manner.

Code Examples for Binding and Defining with Character Conversion

The following two examples illustrate concepts discussed above.

Code Example for Inserting and Selecting Using OCI_ATTR_MAXCHAR_SIZE

When a column is created by specifying a number N of characters, the actual allocation in the data base will consider the worst scenario in the following table. The real bytes allocated will be a multiple of N, say M times N. Currently, M is three as the maximum bytes for each character in UTF8.

For example, in the following table EMP, ENAME column is defined as 30 characters and ADDRESS is defined as 80 characters. Then the corresponding byte lengths in database are M*30 or 3*30=90, and M*80 or 3*80=240 respectively.

...
utext ename[31], address[81]; 
/* E' <= 30+ 1, D' <= 80+ 1, considering null-termination */
sb2 ename_max_chars = EC=20, address_max_chars = ED=60;
 /* EC <= (E' - 1), ED <= (D' - 1) */
sb2 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, &bndlp, errhp, (text *)":ENAME", 
    (sb4)strlen((char *)":ENAME",
    (dvoid *)ename, sizeof(ename), SQLT_STR, (dvoid *)&insname_ind,
    (ub2 *)alenp, (ub2 *)rcodep, (ub4)maxarr_len, (ub4 *)curelep, OCI_DEFAULT); 
/* either */
OCIAttrSet((dvoid *)bnd1p, (ub4)OCI_HTYPE_BIND, (dvoid *)&ename_max_bytes, 
    (ub4)0, (ub4)OCI_ATTR_MAXDATA_SIZE, errhp);
/* or */ 
OCIAttrSet((dvoid *)bnd1p, (ub4)OCI_HTYPE_BIND, (dvoid *)&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, (dvoid *)ename, 
   (sb4)sizeof (ename),
   SQLT_STR, (dvoid *)&selname_ind, (ub2 *)alenp, (ub2 *)rcodep, 
   (ub4)OCI_DEFAULT);
/* if not called, byte semantics is by default */
OCIAttrSet((dvoid *)bnd1p, (ub4)OCI_HTYPE_DEFINE, (dvoid *)&ename_max_chars,
   (ub4)0,
   (ub4)OCI_ATTR_MAXCHAR_SIZE, errhp);
...

Code Example for UTF-16 Binding and Defining

The character set ID in bind and define of the CHAR/VARCHAR2 or NCHAR/NVARCHAR variant handles can be set to specify that all data passed by the corresponding bind and define calls is assumed to be in UTF-16 (Unicode) encoding. To specify UTF-16, set OCI_ATTR_CHARSET_ID = OCI_UTF16ID.

OCI provides a typedef called utext to facilitate binding and defining of UTF-16 data. The internal representation of utext is a 16-bit unsigned integer (ub2). Platforms where the encoding scheme of the wchar_t datatype conforms to UTF-16 (unsigned 16-bit value) can easily convert utext to the wchar_t datatype using cast operators.

Even for UTF-16 data, the buffer size in bind and define calls is assumed to be in bytes. Users should use the new utext datatype as the buffer for input/output data.

The following pseudocode illustrates a bind and define for UTF-16 data:

...
OCIStmt  *stmthp1, *stmthp2;
OCIDefine *dfn1p, *dfnp2; 
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;
sb2 ename_col_len = 20;
sb2 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"), 
              (dvoid *) ename, sizeof(ename), SQLT_STR, 
              (dvoid *)&insname_ind, (ub2 *) 0, (ub2 *) 0, (ub4) 0, 
              (ub4 *)0, OCI_DEFAULT);
OCIAttrSet ((dvoid *) bnd1p, (ub4) OCI_HTYPE_BIND, (dvoid *) &csid,
           (ub4) 0, (ub4)OCI_ATTR_CHARSET_ID, errhp); 
OCIAttrSet((dvoid *) bnd1p, (ub4) OCI_HTYPE_BIND, (dvoid *) &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, (dvoid *)ename,
                (sb4)sizeof(ename), SQLT_STR,
                (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT);
OCIAttrSet ((dvoid *) dfn1p, (ub4) OCI_HTYPE_DEFINE, (dvoid *) &csid,
            (ub4) 0, (ub4)OCI_ATTR_CHARSET_ID, errhp);
...

PL/SQL REF CURSORs and Nested Tables

The OCI provides the ability to bind and define PL/SQL REF CURSORs and nested tables. An application can use a statement handle to bind and define these types of variables. As an example, consider this PL/SQL block:

static const text *plsql_block = (text *)
    "begin \
        OPEN :cursor1 FOR SELECT empno, ename, job, mgr, sal, deptno \
                FROM emp_rc WHERE job=:job ORDER BY empno; \
        OPEN :cursor2 FOR SELECT * FROM dept_rc ORDER BY deptno; \
    end;";

An application would allocate a statement handle for binding, by calling OCIHandleAlloc(), and then bind the :cursor1 placeholder to the statement handle, as in the following code, where :cursor1 is bound to stm2p. Note that the handle allocation code is not included here.

err = OCIStmtPrepare (stm1p, errhp, (text *) nst_tab, strlen(nst_tab), 
                         OCI_NTV_SYNTAX, OCI_DEFAULT);
...
err = OCIBindByName (stm1p, (OCIBind **) bndp, errhp,
             (text *)":cursor1", (sb4)strlen((char *)":cursor1"),
             (dvoid *)&stm2p, (sb4) 0,  SQLT_RSET, (dvoid *)0,
               (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0,   (ub4)OCI_DEFAULT);

In this code, stm1p is the statement handle for the PL/SQL block, while stm2p is the statement handle which is bound as a REF CURSOR for later data retrieval. A value of SQLT_RSET is passed for the dty parameter.

As another example, consider the following:

static const text *nst_tab = (text *)
    "SELECT ename, CURSOR(SELECT dname, loc FROM  dept_rc) \
        FROM emp_rc WHERE ename = 'LOCKE'";

In this case the second position is a nested table, which an OCI application can define as a statement handle as follows. Note that the handle allocation code is not included here.

err = OCIStmtPrepare (stm1p, errhp, (text *) nst_tab, strlen(nst_tab), 
                      OCI_NTV_SYNTAX, OCI_DEFAULT);
...
err = OCIDefineByPos (stm1p, (OCIDefine **) dfn2p, errhp, (ub4)2, 
                     (dvoid *)&stm2p, 
                     (sb4)0, SQLT_RSET, (dvoid *)0, (ub2 *)0, 
                     (ub2 *)0, (ub4)OCI_DEFAULT);

After execution, when you fetch a row into stm2p it then becomes a valid statement handle.

Runtime Data Allocation and Piecewise Operations

You can use the OCI to perform piecewise inserts and updates, and fetches of data. You can also use the OCI to provide data dynamically in the case of array inserts or updates, instead of providing a static array of bind values. You can insert or retrieve a very large column as a series of chunks of smaller size, minimizing client-side memory requirements.

The size of individual pieces is determined at runtime by the application. Each piece may be of the same size as other pieces, or it may be of a different size.

The piecewise functionality of OCI can be particularly useful when you are performing operations on extremely large blocks of string or binary data (for example, operations involving database columns that store CLOB, BLOB, LONG, RAW, or LONG RAW data).

Valid Datatypes for Piecewise Operations

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

• VARCHAR2
• STRING
• LONG
• LONG RAW
• RAW
• CLOB
• BLOB

Some LOB/FILE operations also provide piecewise semantics for reading or writing data.

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

Binding and Defining for LOBs

The following functions accept SQLT_CHR (VARCHAR2), SQLT_LNG (LONG), and SQLT_CLOB (CLOB) for CLOB columns. They also accept SQLT_LBI (LONG RAW), and SQLT_BIN (RAW) and SQLT_BLOB (BLOB) datatypes for BLOB columns:

• OCIDefineByPos()
• OCIBindByName()
• OCIBindByPos()

For example, if the datatype is specified as SQLT_CHR for a CLOB column, then OCIStmtFetch() will fetch the CLOB data. However, if the datatype is specified as SQLT_CLOB for a CLOB column, then OCIStmtFetch() works as before release 9.0.1. It will return the CLOB locator and you can call OCILobRead() to read the CLOB data.

Types of Piecewise Operations

Figure 5-4, "Piecewise Insert of a Column" shows a single column being inserted piecewise into a database table through a series of insert operations (i1, i2, i3...in). In this example the inserted pieces are of varying sizes.

Figure 5-4 Piecewise Insert of a Column

Text description of the illustration lnoci006.gif

You can perform piecewise operations in two ways:

• Use calls provided in the OCI library to execute piecewise operations under a polling paradigm, as in release 7.3.
• 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, this indicates that an OCI application will be providing data for an INSERT or UPDATE dynamically at runtime.

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

In each case, you can provide the run-time information for the INSERT, UPDATE, or FETCH 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 run-time 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 runtime. 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 not required any more, they should be freed by the client.

Runtime data is provided in one of the two ways:

• You can define a callback using the OCIBindDynamic() function which when called at runtime returns a piece or the whole data.
• 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. OCIStmtGetPieceInfo() provides information about which bind and which 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().

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 needs to 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 runtime. 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.

The following steps outline the procedure involved in performing a piecewise insert or update. The procedure is illustrated in Figure 5-5, "Steps for Performing Piecewise Insert".