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.

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_PIECE is 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 */
...
     }

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);
}

...