Using LONG-to-LOB API with OCI

Prior to this release, OCI provided interface calls for performing piecewise INSERTS, UPDATES, and fetches of LONG data. These APIs also allow you to provide data dynamically in case of array INSERTs or UPDATEs, instead of providing a static array of bind values. These piecewise operations can be performed by polling or by providing a callback.

The following functions are now supported for LOBs for you to directly INSERT, UPDATE, and fetch LOB data without your having to deal with the LOB locator:

• OCIBindByName() or OCIBindByPos(). These functions create an association between a program variable and a placeholder in the SQL statement or a PL/SQL block for INSERT/UPDATE.

• OCIBindDynamic(). This call is used to register user callbacks for dynamic data allocation for INSERT/UPDATE.

• OCIDefineByPos(). This call associates an item in a SELECT-list with the type and output data buffer.

• OCIDefineDynamic(). This call registers user callbacks for SELECTs if the OCI_DYNAMIC_FETCH mode was selected in OCIDefineByPos().

• OCIStmtGetPieceInfo() and OCIStmtSetPieceInfo(). These calls are used to get or set piece information for piecewise operations.

  See Also: "Runtime data allocation and piecewise operations" in the Oracle Call Interface Programmer's Guide, for details on the LONG API.

Guidelines for Using LONG-to-LOB API for LOBs with OCI

The aforementioned OCI functions work in this release for LOBs in exactly the same way as they do for LONGs. Using these, you can perform INSERTs, UPDATEs, and fetches of data as described here.

Note: When you use the aforementioned functions for CLOBs, BLOBs, LONGs, and LONG RAWs, specify the datatype (dty) as: SQLT_LNG and SQLT_CHR for CLOBs and LONGs SQLT_LBI and SQLT_BIN for BLOBs and LONG RAWs

Using OCI Functions to Perform INSERT or UPDATE on LOBs

There are various ways to perform INSERT or UPDATE of LOB data.

Note: These are in addition to the ways to insert LOB locators, which are documented in Chapter 10, "Internal Persistent LOBs".

In all the ways described in the following, it is assumed that you have initialized the OCI environment and allocated all necessary handles.

Simple INSERTs or UPDATEs in One Piece

To perform simple INSERTs and UPDATEs in one piece, the steps are:

1. OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.

2. OCIBindByName() or OCIBindbyPos() to bind a placeholder in OCI_DEFAULT mode to bind a LOB as CHAR or BIN.

3. OCIStmtExecute() to do the actual INSERT/UPDATE.

Using Piecewise INSERTs and UPDATEs with Polling

To perform piecewise INSERTs and UPDATEs with polling, the steps are:

1. OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.

2. OCIBindByName() or OCIBindbyPos() to bind a placeholder in OCI_DATA_AT_EXEC mode to bind a LOB as CHAR or BIN.

3. OCIStmtExecute() in default mode. This should return OCI_NEED_DATA.

4. While (returned value is OCI_NEED_DATA), do the following:
   • OCIStmtGetPieceInfo() to retrieve information about piece to be inserted

   • OCIStmtSetPieceInfo() to set information about piece to be inserted

   • OCIStmtExecute. You are done when the return value is OCI_SUCCESS.

Piecewise INSERTs and UPDATEs with Callback

To perform piecewise INSERTs and UPDATEs with callback, the steps are:

1. OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.

2. OCIBindByName() or OCIBindbyPos() to bind a placeholder in OCI_DATA_AT_EXEC mode to bind a LOB as CHAR or BIN.

3. OCIBindDynamic() to specify the callback.

4. OCIStmtExecute() in default mode.

Array INSERTs and UPDATEs

Use any of the above modes in conjunction with OCIBindArrayOfStruct(), or by specifying the number of iterations (iter) value > 1 in the OCIStmtExecute() call.

Using OCI Functions to Perform FETCH on LOBs

There are three ways to fetch the LOB data.

Note: These are in addition to the ways to fetch the LOB locator, which are documented in Chapter 10, "Internal Persistent LOBs".

• Simple fetch in one piece

• Piecewise fetch

• Array fetch

Simple Fetch in One Piece

To perform a simple fetch on LOBs in one piece, the steps involved are:

1. OCIStmtPrepare() to prepare the SELECT statement in OCI_DEFAULT mode.

2. OCIDefineByPos() to define a select list position in OCI_DEFAULT mode to define a LOB as CHAR or BIN.

3. OCIStmtExecute() to execute the SELECT statement.

4. OCIStmtFetch() to do the actual fetch.

Piecewise Fetch with Polling

To perform a piecewise fetch on LOBs with polling, the steps are:

1. OCIStmtPrepare() to prepare the SELECT statement in OCI_DEFAULT mode.

2. OCIDefinebyPos() to define a select list position in OCI_DYNAMIC_FETCH mode to define a LOB as CHAR or BIN.

3. OCIStmtExecute() to execute the SELECT statement.

4. OCIStmtFetch() in default mode. This should return OCI_NEED_DATA.

5. While (returned value is OCI_NEED_DATA), do the following:
   • OCIStmtGetPieceInfo() to retrieve information about piece to be fetched.

   • OCIStmtSetPieceInfo() to set information about piece to be fetched.

   • OCIStmtFetch. You are done when the return value is OCI_SUCCESS.

Piecewise with Callback

To perform a piecewise fetch on LOBs with callback, the steps are:

1. OCIStmtPrepare() to prepare the statement in OCI_DEFAULT mode.

2. OCIDefinebyPos() to define a select list position in OCI_DYNAMIC_FETCH mode to define a LOB as CHAR or BIN.

3. OCIStmtExecute() to execute the SELECT statement.

4. OCIDefineDynamic() to specify the callback.

5. OCIStmtFetch() in default mode.

Array Fetch

Use any of the above modes in conjunction with OCIDefineArrayOfStruct(), or by specifying the number of iterations (iter) value >1 in the OCIStmtExecute() call.