8.4 OCI API for LOBs
Oracle Call Interface (OCI) LOB functions enable you to access and make changes to LOBs in C.
See Also:
LOB and BFILE OperationsPrefetching LOB Data in OCI
When using the OCI client, the number of server round trips can be reduced by
prefetching part of the data and metadata (length and chunk size) along with the LOB
locator during the fetch. This applies to persistent LOBs, temporary LOBs, and
BFILEs. For small to medium sized LOBs, Oracle recommends
setting the prefetch length such that about majority of your LOBs are smaller than
the prefetch size.
LOB prefetch size can be set at the session level, and can be overwritten at the statement or the column level.
OCIAttrSet() function to set the prefetch size
for the session. The default session prefetch size is
0.default_lobprefetch_size = 32000;
OCIAttrSet(authp, OCI_HTYPE_SESSION, &default_lobprefetch_size , 0,
OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE, errhp));You can overwrite the session level default prefetch size at the column
level. For this, you should first set the column level attribute
OCI_ATTR_LOBPREFETCH_LENGTH to TRUE and then set the
column level prefetch size attribute OCI_ATTR_LOBPREFETCH_SIZE in
the define handle to override the session level default lob prefetch size. The
following code snippet demonstrates how to set the prefetch size at session level:
prefetch_length = TRUE;
status = OCIAttrSet(defhp, OCI_HTYPE_DEFINE, &prefetch_length, 0, OCI_ATTR_LOBPREFETCH_LENGTH, errhp);
lpf_size = 32000;
OCIAttrSet(defhp, OCI_HTYPE_DEFINE, &lpf_size, sizeof(ub4), OCI_ATTR_LOBPREFETCH_SIZE, errhp);ub4 get_lpf_size = 0;
OCIAttrGet(defhp, OCI_HTYPE_DEFINE,&get_lpf_size, 0,OCI_ATTR_LOBPREFETCH_SIZE, errhp);See Also:
User Session Handle AttributesFixed-width and Varying-width Character Set Rules for OCI
In OCI, for fixed-width client-side character sets, the following rules apply:
-
CLOBs andNCLOBs:offsetandamountparameters are always in characters -
BLOBs andBFILEs:offsetandamountparameters are always in bytes
The following rules apply only to varying-width client-side character sets:
-
Offset parameter:
Regardless of whether the client-side character set is varying-width, the offset parameter is always as follows:
-
CLOBs andNCLOBs: in characters -
BLOBs andBFILEs: in bytes
-
-
Amount parameter:
The amount parameter is always as follows:
-
When referring to a server-side LOB: in characters
-
When referring to a client-side buffer: in bytes
-
-
OCILobGetLength2():
Regardless of whether the client-side character set is varying-width, the output length is as follows:
-
CLOBs andNCLOBs: in characters -
BLOBs andBFILEs: in bytes
-
-
OCILobRead2():
With client-side character set of varying-width,
CLOBs andNCLOBs:-
Input amount is in characters. Input amount refers to the number of characters to read from the server-side
CLOBorNCLOB. -
Output amount is in bytes. Output amount indicates how many bytes were read into the buffer
bufp.
-
-
OCILobWrite2(): With client-side character set of varying-width,
CLOBs andNCLOBs:-
Input amount is in bytes. The input amount refers to the number of bytes of data in the input buffer
bufp. -
Output amount is in characters. The output amount refers to the number of characters written into the server-side
CLOBorNCLOB.
-
- Amount Operation for OCILob Operations: For operations such as
OCILobCopy2(),OCILobErase2(),OCILobLoadFromFile2(), andOCILobTrim2(), theamountparameter is in characters forCLOBs andNCLOBs irrespective of the client-side character set because all these operations refer to the amount of LOB data on the server.
See Also:
Overview of Globalization SupportAmount Parameter
When using the OCILobRead2() and
OCILobWrite2() functions, in order to read or write the entire
LOB. you can set the input amount parameter as follows:
Table 8-4 Special Amount Parameter Setting to Read/Write the entire LOB
| OCILobRead2 | OCILobWrite2 | |
|---|---|---|
piece = OCI_ONE_PIECE |
Set amount to UB8MAXVAL to read the entire LOB
|
|
| Streaming with Polling | Set amount to 0 to read entire data in a loop | Set amount to 0 to continue writing buffer size amount until
OCI_LAST_PIECE |
| Streaming with Callback | Set amount 0 to ensure that the callback is called until the entire data is read | Set amount to 0 to ensure that the callback is called until
OCI_LAST_PIECE is returned by the
callback
|
Table 8-5 OCI Attributes on the OCILobLocator
| ATTRIBUTE | OCIAttrSet | OCIAttrGet |
|---|---|---|
OCI_ATTR_LOBEMPTY |
Sets the descriptor to be empty LOB | N/A |
OCI_ATTR_LOB_REMOTE |
N/A | set to TRUE if the lob locator is from a
remote database, set to FALSE otherwise
|
OCI_ATTR_LOB_TYPE |
N/A | holds the LOB type (CLOB / BLOB /
BFILE)
|
OCI_ATTR_LOB_IS_VALUE |
N/A | set to TRUE if it is from a value LOB,
otherwiseFALSE |
OCI_ATTR_LOB_IS_READONLY |
N/A | set to TRUE if it is a read-only LOB,
otherwise FALSE |
OCI_ATTR_LOBPREFETCH_LENGTH |
When set to TRUE the attribute will
enable prefetching and will prefetch the LOB length and the chunk size
while performing select operation of LOB locator
|
set to TRUE if prefetching is turned on
for the locator.
|
OCI_ATTR_LOBPREFETCH_SIZE |
Overrides the default prefetch size for LOBs. Has a
prerequisite of the OCI_ATTR_LOBPREFETCH_LENGTH
attribute to be set to TRUE.
|
Returns the prefetch size of the locator. |
Table 8-6 OCI Functions for LOBs
| Category | Function/Procedure | Description |
|---|---|---|
| Sanity Checking | OCILobLocatorIsInit() |
Checks whether a LOB locator is initialized. |
| Open/Close | OCILobOpen() |
Open a LOB |
OCILobIsOpen() |
Check if a LOB is open | |
OCILobClose() |
Close the LOB | |
| Read Operations | OCILobGetLength2() |
Get the length of the LOB |
OCILobGetStorageLimit() |
Get the LOB storage limit for the database configuration | |
OCILobGetChunkSize() |
Get the optimum read / write size | |
OCILobRead2() |
Read data from the LOB starting at the specified offset | |
OCILobArrayRead() |
Reads data using multiple locators in one round trip. | |
OCILobCharSetId() |
Returns the character set ID of a LOB. | |
OCILobCharSetForm() |
Returns the character set form of a LOB. | |
| Modify Operations | OCILobWrite2() |
Write data to the LOB at a specified offset |
OCILobArrayWrite() |
Writes data using multiple locators in one round trip. | |
OCILobWriteAppend2() |
Write data to the end of the LOB | |
OCILobErase2() |
Erase part of a LOB, starting at a specified offset | |
OCILobTrim2() |
Trim the LOB value to the specified shorter length | |
| Operations involving multiple locators | OCILobIsEqual() |
Checks whether two LOB locators refer to the same LOB. |
OCILobAppend() |
Append a LOB value to another LOB | |
OCILobCopy2() |
Copy all or part of a LOB to another LOB | |
OCILobLocatorAssign() |
Assign one LOB to another | |
OCILobLoadFromFile2() |
Load BFILE data into a LOB
|
|
| Operations specific to SecureFiles | OCILObGetOptions() |
Returns options (deduplication, compression, encryption) for SecureFiles. |
OCILObSetOptions() |
Sets LOB features (deduplication and compression) for SecureFiles | |
OCILobGetContentType() |
Gets the content string for a SecureFiles | |
OCILobSetContentType() |
Sets a content string in a SecureFiles |
Example 8-4 OCI API for LOBs
/* Define SQL statements to be used in program. */
#define LOB_NUM_QUERIES 2
static text *selstmt[LOB_NUM_QUERIES] = {
(text *) "select ad_sourcetext from print_media where product_id = 1", /* 0 */
(text *) "select ad_sourcetext from print_media where product_id = 2 for update",
};
sword run_query(ub4 index, ub2 dty)
{
OCILobLocator *c1 = (OCILobLocator *)0;
OCILobLocator *c2 = (OCILobLocator *)0;
OCIStmt *stmthp;
OCIDefine *defn1p = (OCIDefine *) 0;
OCIDefine *defn2p = (OCIDefine *) 0;
OCIBind *bndp1 = (OCIBind *) 0;
OCIBind *bndp2 = (OCIBind *) 0;
ub8 loblen;
ub1 lbuf[128];
ub1 inbuf[9] = "modified";
ub1 inbuf_len = 8;
ub8 amt = 15;
ub8 bamt = 0;
ub4 csize = 0;
ub8 slimit = 0;
boolean flag = FALSE;
boolean boolval = TRUE;
ub4 id = 10;
CHECK_ERROR (OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
/************** Allocate descriptors ***********************/
CHECK_ERROR (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &c1,
(ub4)OCI_DTYPE_FILE, (size_t) 0,
(dvoid **) 0));
CHECK_ERROR (OCIDescriptorAlloc((dvoid *) envhp, (dvoid **) &c2,
(ub4)OCI_DTYPE_FILE, (size_t) 0,
/********** Execute selstmt[0] to get c1 ***********************/
CHECK_ERROR (OCIStmtPrepare(stmthp, errhp, selstmt[0],
(ub4) strlen((char *) selstmt[0]),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
CHECK_ERROR (OCIDefineByPos(stmthp, &defn1p, errhp, (ub4) 1, (dvoid *) &c1,
(sb4) -1, SQLT_CLOB, (dvoid *) 0, (ub2 *) 0,
(ub2 *)0, (ub4) OCI_DEFAULT));
CHECK_ERROR (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
OCI_DEFAULT));
/********** Execute selstmt[1] to get c2 **********************/
CHECK_ERROR (OCIStmtPrepare(stmthp, errhp, selstmt[1],
(ub4) strlen((char *) selstmt[1]),
(ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
CHECK_ERROR (OCIDefineByPos(stmthp, &defn1p, errhp, (ub4) 1, (dvoid *) &c2,
(sb4) -1, SQLT_CLOB, (dvoid *) 0, (ub2 *) 0,
(ub2 *)0, (ub4) OCI_DEFAULT));
CHECK_ERROR (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
(CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
OCI_DEFAULT));
/*------------------------------------------------------------------*/
/*---------------------- Sanity Checking ---------------------------*/
/*------------------------------------------------------------------*/
CHECK_ERROR (OCILobLocatorIsInit(envhp, errhp, (OCILobLocator *) c1,
&boolval));
if (boolval)
printf("LOB locator is initialized! \n");
else
printf("LOB locator is NOT initialized \n");
/*------------------------------------------------------------------*/
/*----------------------- Open/Close -------------------------------*/
/*------------------------------------------------------------------*/
/*----------------------- Opening a CLOB ---------------------------*/
CHECK_ERROR (OCILobOpen(svchp, errhp, c1, (ub1)OCI_LOB_READONLY));
printf("OCILobOpen: Works\n");
/*-------------- Determining Whether a CLOB Is Open ---------------*/
CHECK_ERROR (OCILobIsOpen(svchp, errhp, c1, &boolval));
printf("OCILobIsOpen: %s\n", (boolval)?"TRUE":"FALSE");
/*----------------------- Closing a LOB ---------------------------*/
CHECK_ERROR (OCILobClose(svchp, errhp, c1));
printf("OCILobClose: Works\n");
/*------------------------------------------------------------------*/
/*-------------------- LOB Read Operations -------------------------*/
/*------------------------------------------------------------------*/
printf("OCILobFileOpen: Works\n");
/*----------------- Getting the Length of a LOB --------------------*/
CHECK_ERROR (OCILobGetLength2(svchp, errhp, c1, &loblen));
printf("OCILobGetLength2: loblen: %d \n", loblen);
/*----------------- Getting the Storage Limit of a LOB -------------*/
CHECK_ERROR (OCILobGetStorageLimit(svchp, errhp, c1, &slimit));
printf("OCILobGetStorageLimit: storage limit: %ld \n", slimit);
/*----------------- Getting the Chunk Size of a LOB -----------------*/
CHECK_ERROR (OCILobGetChunkSize(svchp, errhp, c1, &csize));
printf("OCILobGetChunkSize: storage limit: %d \n", csize);
/*------------------------ Reading LOB Data ------------------------*/
CHECK_ERROR (OCILobRead2(svchp, errhp, c1, &amt,
NULL, (oraub8)1, lbuf,
(oraub8)sizeof(lbuf), OCI_ONE_PIECE ,(dvoid*)0,
NULL, (ub2)0, (ub1)SQLCS_IMPLICIT));
printf("OCILobRead2: buf: %.*s amt: %lu\n", amt, lbuf, amt);
/*------------------------------------------------------------------*/
/*-------------------- Modifying a LOB -----------------------------*/
/*------------------------------------------------------------------*/
/*---------------------- Writing Data to LOB -----------------------*/
amt = 8;
CHECK_ERROR (OCILobWrite2(svchp, errhp, c2, &bamt, &amt, 1,
(dvoid *) inbuf, (ub8)inbuf_len, OCI_ONE_PIECE, (dvoid *)0,
(OCICallbackLobWrite2)0,
(ub2) 0, (ub1) SQLCS_IMPLICIT));
/*---------------------- Write Append to a LOB ---------------------*/
/* Append 8 characters */
amt = 8;
CHECK_ERROR (OCILobWriteAppend2(svchp, errhp, c2, &bamt, &amt,
(dvoid *) inbuf, (ub8)inbuf_len, OCI_ONE_PIECE, (dvoid *)0,
(OCICallbackLobWrite2)0,
(ub2) 0, (ub1) SQLCS_IMPLICIT));
/*---------------------- Erase part of LOB contents ----------------*/
/* Erase 5 characters */
amt = 5;
CHECK_ERROR (OCILobErase2(svchp, errhp, c2, &amt, 2));
/*------------------------- Trim a LOB -----------------------------*/
amt = 1000;
CHECK_ERROR (OCILobTrim2(svchp, errhp, c2, amt));
printf("OCILobTrim2 Works! \n");
/*------------------------------------------------------------------*/
/*------------- Operations involving 2 locators --------------------*/
/*------------------------------------------------------------------*/
/*--------------- Check Equality of LOB locators -------------------*/
CHECK_ERROR ( OCILobIsEqual(envhp, c1, c2, &boolval))
printf("OCILobIsEqual %s\n", (boolval)?"TRUE":"FALSE");
/*--------------- Append contents of a LOB to another LOB ----------*/
CHECK_ERROR(OCILobAppend(svchp, errhp, c2, c1));
printf("OCILobAppend: Works! \n");
/*------------------------ LOB Copy --------------------------------*/
/* Copy 10 characters from offset 1 of source to offset 2 of destination*/
CHECK_ERROR (OCILobCopy2(svchp, errhp, c2, c1, 10, 2, 1));
printf("OCILobCopy2: Works! \n");
/*------------------- LOB Locator Assign ---------------------------*/
CHECK_ERROR (OCILobLocatorAssign(svchp, errhp, c1, &c2));
printf("OCILobLocatorAssign: Works! \n");
/* Free the LOB descriptors which were allocated */
OCIDescriptorFree((dvoid *) c1, (ub4) SQLT_CLOB);
OCIDescriptorFree((dvoid *) c2, (ub4) SQLT_CLOB);
CHECK_ERROR (OCIHandleFree((dvoid *) stmthp, OCI_HTYPE_STMT));
}8.4.1 Efficiently Reading LOB Data in OCI
This section describes how to read the contents of a LOB into a buffer.
Streaming Read in OCI
The most efficient way to read large amounts of LOB data is to use
OCILobRead2() with the streaming mechanism enabled using
polling or callback. To do so, specify the starting point of the read using the
offset parameter as follows:
ub8 char_amt = 0;
ub8 byte_amt = 0;
ub4 offset = 1000;
OCILobRead2(svchp, errhp, locp, &byte_amt, &char_amt, offset, bufp, bufl,
OCI_ONE_PIECE, 0, 0, 0, 0);
When using polling mode, be sure to look at the value of the
byte_amt parameter after each OCILobRead2()
call to see how many bytes were read into the buffer because the buffer may not be
entirely full.
When using callbacks, the lenp parameter, which
is input to the callback, indicates how many bytes are filled in the buffer. Be sure
to check the lenp parameter during your callback processing because
the entire buffer may not be filled with data.
See Also:
LOB Array Read
This section describes how to read LOB data for multiple locators in one
round trip, using OCILobArrayRead().
For an OCI application example, assume that the program has a prepared SQL statement such as:
SELECT lob1 FROM lob_table;
where lob1 is the LOB column and
lob_array is an array of define variables corresponding to a
LOB column:
OCILobLocator * lob_array[10];
...
for (i=0; i<10, i++) /* initialize array of locators */
lob_array[i] = OCIDescriptorAlloc(..., OCI_DTYPE_LOB, ...);
...
OCIDefineByPos(..., 1, (dvoid *) lob_array, ... SQLT_CLOB, ...);
/* Execute the statement with iters = 10 to do an array fetch of 10 locators. */
OCIStmtExecute ( <service context>, <statement handle>, <error handle>,
10, /* iters */
0, /* row offset */
NULL, /* snapshot IN */
NULL, /* snapshot out */
OCI_DEFAULT /* mode */);
...
ub4 array_iter = 10;
char *bufp[10];
oraub8 bufl[10];
oraub8 char_amtp[10];
oraub8 offset[10];
for (i=0; i<10; i++)
{
bufp[i] = (char *)malloc(1000);
bufl[i] = 1000;
offset[i] = 1;
char_amtp[i] = 1000; /* Single byte fixed width char set. */
}
/* Read the 1st 1000 characters for all 10 locators in one
* round trip. Note that offset and amount need not be
* same for all the locators. */
OCILobArrayRead(<service context>, <error handle>,
&array_iter, /* array size */
lob_array, /* array of locators */
NULL, /* array of byte amounts */
char_amtp, /* array of char amounts */
offset, /* array of offsets */
(void **)bufp, /* array of read buffers */
bufl, /* array of buffer lengths */
OCI_ONE_PIECE, /* piece information */
NULL, /* callback context */
NULL, /* callback function */
0, /* character set ID - default */
SQLCS_IMPLICIT);/* character set form */
...
for (i=0; i<10; i++)
{
/* Fill bufp[i] buffers with data to be written */
strncpy (bufp[i], "Test Data------", 15);
bufl[i] = 1000;
offset[i] = 50;
char_amtp[i] = 15; /* Single byte fixed width char set. */
}
/* Write the 15 characters from offset 50 to all 10
* locators in one round trip. Note that offset and
* amount need not be same for all the locators. */
*/
OCILobArrayWrite(<service context>, <error handle>,
&array_iter, /* array size */
lob_array, /* array of locators */
NULL, /* array of byte amounts */
char_amtp, /* array of char amounts */
offset, /* array of offsets */
(void **)bufp, /* array of read buffers */
bufl, /* array of buffer lengths */
OCI_ONE_PIECE, /* piece information */
NULL, /* callback context */
NULL, /* callback function */
0, /* character set ID - default */
SQLCS_IMPLICIT);/* character set form */
...
LOB Array Read with Streaming
LOB array APIs can be used to read/write LOB data in multiple pieces.
This can be done by using polling method or a callback function.Here data is
read/written in multiple pieces sequentially for the array of locators. For polling,
the API would return to the application after reading/writing each piece with the
array_iter parameter (OUT) indicating the index of the locator
for which data is read/written. With a callback, the function is called after
reading/writing each piece with array_iter as IN parameter.
Note that:
-
It is possible to read/write data for a few of the locators in one piece and read/write data for other locators in multiple pieces. Data is read/written in one piece for locators which have sufficient buffer lengths to accommodate the whole data to be read/written.
-
Your application can use different amount value and buffer lengths for each locator.
-
Your application can pass zero as the amount value for one or more locators indicating pure streaming for those locators. In the case of reading, LOB data is read to the end for those locators. For writing, data is written until
OCI_LAST_PIECEis specified for those locators.
LOB Array Read with Callback
The following example reads 10Kbytes of data for each of 10 locators with 1Kbyte buffer size. Each locator needs 10 pieces to read all the data. The callback function is called 100 (10*10) times to return the pieces sequentially.
/* Fetch the locators */
...
ub4 array_iter = 10;
char *bufp[10];
oraub8 bufl[10];
oraub8 char_amtp[10];
oraub8 offset[10];
sword st;
for (i=0; i<10; i++)
{
bufp[i] = (char *)malloc(1000);
bufl[i] = 1000;
offset[i] = 1;
char_amtp[i] = 10000; /* Single byte fixed width char set. */
}
st = OCILobArrayRead(<service context>, <error handle>,
&array_iter, /* array size */
lob_array, /* array of locators */
NULL, /* array of byte amounts */
char_amtp, /* array of char amounts */
offset, /* array of offsets */
(void **)bufp, /* array of read buffers */
bufl, /* array of buffer lengths */
OCI_FIRST_PIECE, /* piece information */
ctx, /* callback context */
cbk_read_lob, /* callback function */
0, /* character set ID - default */
SQLCS_IMPLICIT);
...
/* Callback function for LOB array read. */
sb4 cbk_read_lob(dvoid *ctxp, ub4 array_iter, CONST dvoid *bufxp, oraub8 len,
ub1 piece, dvoid **changed_bufpp, oraub8 *changed_lenp)
{
static ub4 piece_count = 0;
piece_count++;
switch (piece)
{
case OCI_LAST_PIECE:
/*--- buffer processing code goes here ---*/
(void) printf("callback read the %d th piece(last piece) for %dth locator \n\n",
piece_count, array_iter );
piece_count = 0;
break;
case OCI_FIRST_PIECE:
/*--- buffer processing code goes here ---*/
(void) printf("callback read the 1st piece for %dth locator\n",
array_iter);
/* --Optional code to set changed_bufpp and changed_lenp if the buffer needs
to be changed dynamically --*/
break;
case OCI_NEXT_PIECE:
/*--- buffer processing code goes here ---*/
(void) printf("callback read the %d th piece for %dth locator\n",
piece_count, array_iter);
/* --Optional code to set changed_bufpp and changed_lenp if the buffer
must be changed dynamically --*/
break;
default:
(void) printf("callback read error: unkown piece = %d.\n", piece);
return OCI_ERROR;
}
return OCI_CONTINUE;
}
...
LOB Array Read in Polling Mode
The following example reads 10Kbytes of data for each of 10 locators
with 1Kbyte buffer size. Each locator needs 10 pieces to read the complete data.
OCILobArrayRead() must be called 100 (10*10) times to fetch all
the data.First we call OCILobArrayRead() with
OCI_FIRST_PIECE as piece parameter. This call
returns the first 1K piece for the first locator.Next
OCILobArrayRead() is called in a loop until the application
finishes reading all the pieces for the locators and returns
OCI_SUCCESS. In this example it loops 99 times returning the
pieces for the locators sequentially.
/* Fetch the locators */
...
/* array_iter parameter indicates the number of locators in the array read.
* It is an IN parameter for the 1st call in polling and is ignored as IN
* parameter for subsequent calls. As OUT parameter it indicates the locator
* index for which the piece is read.
*/
ub4 array_iter = 10;
char *bufp[10];
oraub8 bufl[10];
oraub8 char_amtp[10];
oraub8 offset[10];
sword st;
for (i=0; i<10; i++)
{
bufp[i] = (char *)malloc(1000);
bufl[i] = 1000;
offset[i] = 1;
char_amtp[i] = 10000; /* Single byte fixed width char set. */
}
st = OCILobArrayRead(<service context>, <error handle>,
&array_iter, /* array size */
lob_array, /* array of locators */
NULL, /* array of byte amounts */
char_amtp, /* array of char amounts */
offset, /* array of offsets */
(void **)bufp, /* array of read buffers */
bufl, /* array of buffer lengths */
OCI_FIRST_PIECE, /* piece information */
NULL, /* callback context */
NULL, /* callback function */
0, /* character set ID - default */
SQLCS_IMPLICIT); /* character set form */
/* First piece for the first locator is read here.
* bufp[0] => Buffer pointer into which data is read.
* char_amtp[0 ] => Number of characters read in current buffer
*
*/
While ( st == OCI_NEED_DATA)
{
st = OCILobArrayRead(<service context>, <error handle>,
&array_iter, /* array size */
lob_array, /* array of locators */
NULL, /* array of byte amounts */
char_amtp, /* array of char amounts */
offset, /* array of offsets */
(void **)bufp, /* array of read buffers */
bufl, /* array of buffer lengths */
OCI_NEXT_PIECE, /* piece information */
NULL, /* callback context */
NULL, /* callback function */
0, /* character set ID - default */
SQLCS_IMPLICIT);
/* array_iter returns the index of the current array element for which
* data is read. for example, aray_iter = 1 implies first locator,
* array_iter = 2 implies second locator and so on.
*
* lob_array[ array_iter - 1]=> Lob locator for which data is read.
* bufp[array_iter - 1] => Buffer pointer into which data is read.
* char_amtp[array_iter - 1] => Number of characters read in current buffer
*/
...
/* Consume the data here */
...
}
8.4.2 Efficiently Writing LOB Data in OCI
This section describes how to write the contents of a buffer to a LOB.
Streaming Write in OCI
The most efficient way to write large amounts of LOB data is to use
OCILobWrite2() with the streaming mechanism enabled, and using
polling or a callback. If you know how much data is written to the LOB, then specify
that amount when calling OCILobWrite2(). This ensures that LOB data
on the disk is contiguous. Apart from being spatially efficient, the contiguous
structure of the LOB data makes reads and writes in subsequent operations
faster.
LOB Array Write with Callback
The following example writes 10Kbytes of data for each of 10 locators
with a 1K buffer size. A total of 100 pieces must be written (10 pieces for each
locator). The first piece is provided by the OCILobArrayWrite()
call. The callback function is called 99 times to get the data for subsequent pieces
to be written.
/* Fetch the locators */
...
ub4 array_iter = 10;
char *bufp[10];
oraub8 bufl[10];
oraub8 char_amtp[10];
oraub8 offset[10];
sword st;
for (i=0; i<10; i++)
{
bufp[i] = (char *)malloc(1000);
bufl[i] = 1000;
offset[i] = 1;
char_amtp[i] = 10000; /* Single byte fixed width char set. */
}
st = OCILobArrayWrite(<service context>, <error handle>,
&array_iter, /* array size */
lob_array, /* array of locators */
NULL, /* array of byte amounts */
char_amtp, /* array of char amounts */
offset, /* array of offsets */
(void **)bufp, /* array of write buffers */
bufl, /* array of buffer lengths */
OCI_FIRST_PIECE, /* piece information */
ctx, /* callback context */
cbk_write_lob /* callback function */
0, /* character set ID - default */
SQLCS_IMPLICIT);
...
/* Callback function for LOB array write. */
sb4 cbk_write_lob(dvoid *ctxp, ub4 array_iter, dvoid *bufxp, oraub8 *lenp,
ub1 *piecep, ub1 *changed_bufpp, oraub8 *changed_lenp)
{
static ub4 piece_count = 0;
piece_count++;
printf (" %dth piece written for %dth locator \n\n", piece_count, array_iter);
/*-- code to fill bufxp with data goes here. *lenp should reflect the size and
* should be less than or equal to MAXBUFLEN -- */
/* --Optional code to set changed_bufpp and changed_lenp if the buffer must
* be changed dynamically --*/
if (this is the last data buffer for current locator)
*piecep = OCI_LAST_PIECE;
else if (this is the first data buffer for the next locator)
*piecep = OCI_FIRST_PIECE;
piece_count = 0;
else
*piecep = OCI_NEXT_PIECE;
return OCI_CONTINUE;
}
...
LOB Array Write in Polling Mode
The following example writes 10Kbytes of data for each of
10 locators with a 1K buffer size.
OCILobArrayWrite() has to be called 100
(10 times 10) times to write all the data. The
function is used in a similar manner to OCILobWrite2().
/* Fetch the locators */
...
/* array_iter parameter indicates the number of locators in the array read.
* It is an IN parameter for the 1st call in polling and is ignored as IN
* parameter for subsequent calls. As an OUT parameter it indicates the locator
* index for which the piece is written.
*/
ub4 array_iter = 10;
char *bufp[10];
oraub8 bufl[10];
oraub8 char_amtp[10];
oraub8 offset[10];
sword st;
int i, j;
for (i=0; i<10; i++)
{
bufp[i] = (char *)malloc(1000);
bufl[i] = 1000;
/* Fill bufp here. */
...
offset[i] = 1;
char_amtp[i] = 10000; /* Single byte fixed width char set. */
}
for (i = 1; i <= 10; i++)
{
/* Fill up bufp[i-1] here. The first piece for ith locator would be written from
bufp[i-1] */
...
st = OCILobArrayWrite(<service context>, <error handle>,
&array_iter, /* array size */
lob_array, /* array of locators */
NULL, /* array of byte amounts */
char_amtp, /* array of char amounts */
offset, /* array of offsets */
(void **)bufp, /* array of write buffers */
bufl, /* array of buffer lengths */
OCI_FIRST_PIECE, /* piece information */
NULL, /* callback context */
NULL, /* callback function */
0, /* character set ID - default */
SQLCS_IMPLICIT); /* character set form */
for ( j = 2; j < 10; j++)
{
/* Fill up bufp[i-1] here. The jth piece for ith locator would be written from
bufp[i-1] */
...
st = OCILobArrayWrite(<service context>, <error handle>,
&array_iter, /* array size */
lob_array, /* array of locators */
NULL, /* array of byte amounts */
char_amtp, /* array of char amounts */
offset, /* array of offsets */
(void **)bufp, /* array of write buffers */
bufl, /* array of buffer lengths */
OCI_NEXT_PIECE, /* piece information */
NULL, /* callback context */
NULL, /* callback function */
0, /* character set ID - default */
SQLCS_IMPLICIT);
/* array_iter returns the index of the current array element for which
* data is being written. for example, aray_iter = 1 implies first locator,
* array_iter = 2 implies second locator and so on. Here i = array_iter.
*
* lob_array[ array_iter - 1] => Lob locator for which data is written.
* bufp[array_iter - 1] => Buffer pointer from which data is written.
* char_amtp[ array_iter - 1] => Number of characters written in
* the piece just written
*/
}
/* Fill up bufp[i-1] here. The last piece for ith locator would be written from
bufp[i -1] */
...
st = OCILobArrayWrite(<service context>, <error handle>,
&array_iter, /* array size */
lob_array, /* array of locators */
NULL, /* array of byte amounts */
char_amtp, /* array of char amounts */
offset, /* array of offsets */
(void **)bufp, /* array of write buffers */
bufl, /* array of buffer lengths */
OCI_LAST_PIECE, /* piece information */
NULL, /* callback context */
NULL, /* callback function */
0, /* character set ID - default */
SQLCS_IMPLICIT);
}
...