This operation appends one LOB instance to another.

Preconditions

Before you can append one LOB to another, the following conditions must be met:

• Two LOB instances must exist.

• Both instances must be of the same type, for example both BLOB or both CLOB types.

• You can pass any combination of persistent or temporary LOB instances to this operation.

Usage Notes

Persistent LOBs: You must lock the row you are selecting the LOB from prior to updating a LOB value if you are using the PL/SQL DBMS_LOB Package or OCI. While the SQL INSERT and UPDATE statements implicitly lock the row, locking the row can be done explicitly using the SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.

Syntax

See the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — APPEND

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobAppend()

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB Statements, and LOB APPEND executable embedded SQL extension

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guidefor information on embedded SQL statements and directives — LOB APPEND

• Java (JDBC):Oracle Database JDBC Developer’s Guidefor information on creating and populating LOB columns in Java.

Examples

To run the following examples, you must create two LOB instances and pass them when you call the given append operation.

Examples for this use case are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): lappend.sql

• OCI: lappend.c

• Java (JDBC): lappend.java

This section describes how to get the character set form of a LOB instance.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this operation.

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobCharSetForm()

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL): There is no applicable syntax reference for this operation

• C/C++ (Pro*C/C++): There is no applicable syntax reference for this operation.

• Java (JDBC): There is no applicable syntax reference for this operation.

Example

The example demonstrates how to determine the character set form of the foreign language text (ad_fltextn).

This functionality is currently available only in OCI:

• OCI: lgetchfm.c

This section describes how to determine the character set ID.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this operation.

• C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" — LOB Functions, OCILobCharSetId()

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL): There is no applicable syntax reference for this operation.

• C/C++ (Pro*C/C++): There is no applicable syntax reference for this operation

• Java (JDBC): There is no applicable syntax reference for this operation.

Example

This functionality is currently available only in OCI:

• OCI: lgetchar.c

This operation loads a LOB with data from a BFILE. This procedure can be used to load data into any persistent or temporary LOB instance of any LOB data type.

Preconditions

Before you can load a LOB with data from a BFILE, the following conditions must be met:

• The BFILE must exist.

• The target LOB instance must exist.

Usage Notes

Note the following issues regarding this operation.

Use LOADCLOBFROMFILE When Loading Character Data

When you use the DBMS_LOB.LOADFROMFILE procedure to load a CLOB or NCLOB instance, you are loading the LOB with binary data from the BFILE and no implicit character set conversion is performed. For this reason, using the DBMS_LOB.LOADCLOBFROMFILE procedure is recommended when loading character data.

Specifying Amount of BFILE Data to Load

The value you pass for the amount parameter to functions listed in Table 12-2 must be one of the following:

• An amount less than or equal to the actual size (in bytes) of the BFILE you are loading.

• The maximum allowable LOB size (in bytes). Passing this value, loads the entire BFILE. You can use this technique to load the entire BFILE without determining the size of the BFILE before loading. To get the maximum allowable LOB size, use the technique described in Table 12-2.

Syntax

See the following syntax references for details on using this operation in each programmatic environment:

• PL/SQL (DBMS_LOB Package):Oracle Database PL/SQL Packages and Types Reference"DBMS_LOB" — LOADFROMFILE.

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobLoadFromFile()

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB statements, and LOB LOAD, LOB OPEN, and LOB CLOSE executable embedded SQL extension

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guide, for more information on LOB LOAD executable embedded SQL extension

• Java (JDBC): Oracle Database JDBC Developer's Guide Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): lloaddat.sql

• OCI: lloaddat.c

• Java (JDBC): lloaddat.java

This procedure loads a BLOB with data from a BFILE. This procedure can be used to load data into any persistent or temporary BLOB instance.

Preconditions

The following conditions must be met before calling this procedure:

• The target BLOB instance must exist.

• The source BFILE must exist.

• You must open the BFILE. (After calling this procedure, you must close the BFILE at some point.)

Usage Notes

Note the following with respect to this operation:

New Offsets Returned

Using DBMS_LOB.LOADBLOBFROMFILE to load binary data into a BLOB achieves the same result as using DBMS_LOB.LOADFROMFILE, but also returns the new offsets of BLOB.

Specifying Amount of BFILE Data to Load

The value you pass for the amount parameter to the DBMS_LOB.LOADBLOBFROMFILE function must be one of the following:

• An amount less than or equal to the actual size (in bytes) of the BFILE you are loading.

• The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE. Passing this value causes the function to load the entire BFILE. This is a useful technique for loading the entire BFILE without introspecting the size of the BFILE.

  See Also:

  Table 12-2

Syntax

See Oracle Database PL/SQL Packages and Types Reference, "DBMS_LOB" — LOADBLOBFROMFILE procedure for syntax details on this procedure.

Examples

This example is available in PL/SQL only. This API is not provided in other programmatic environments. The online file is lldblobf.sql. This example illustrates:

• How to use LOADBLOBFROMFILE to load the entire BFILE without getting its length first.

• How to use the return value of the offsets to calculate the actual amount loaded.

This procedure loads a CLOB or NCLOB with character data from a BFILE. This procedure can be used to load data into a persistent or temporary CLOB or NCLOB instance.

Preconditions

The following conditions must be met before calling this procedure:

• The target CLOB or NCLOB instance must exist.

• The source BFILE must exist.

• You must open the BFILE. (After calling this procedure, you must close the BFILE at some point.)

Usage Notes

You can specify the character set id of the BFILE when calling this procedure. Doing so, ensures that the character set is properly converted from the BFILE data character set to the destination CLOB or NCLOB character set.

Specifying Amount of BFILE Data to Load

The value you pass for the amount parameter to the DBMS_LOB.LOADCLOBFROMFILE function must be one of the following:

• An amount less than or equal to the actual size (in characters) of the BFILE data you are loading.

• The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE

  Passing this value causes the function to load the entire BFILE. This is a useful technique for loading the entire BFILE without introspecting the size of the BFILE.

Syntax

See Oracle Database PL/SQL Packages and Types Reference, "DBMS_LOB" — LOADCLOBFROMFILE procedure for syntax details on this procedure.

Examples

The following examples illustrate different techniques for using this API:

• "About PL/SQL: Loading Character Data from a BFILE into a LOB"

• "About PL/SQL: Loading Segments of Character Data into Different LOBs"

This section describes APIs that allow you to read LOB data. You can use this operation to read LOB data into a buffer. This is useful if your application requires displaying large amounts of LOB data or streaming data operations.

Usage Notes

Note the following when using these APIs.

Streaming Mechanism

The most efficient way to read large amounts of LOB data is to use OCILobRead2() with the streaming mechanism enabled.

Amount Parameter

The value you pass for the amount parameter is restricted for the APIs described in Table 12-3.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — OPEN, READ, CLOSE.

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" —, OCILobOpen(), OCILobRead2(), OCILobClose().

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB statements, and LOB READ executable embedded SQL extension.

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guidefor information on LOB READ executable embedded SQL extension

• Java (JDBC): Oracle Database JDBC Developer's Guide, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): ldisplay.sql

• OCI: ldisplay.c

• C++ (OCCI): No example is provided in this release.

• Java (JDBC): ldisplay.java

This section describes how to read data from LOBs using OCILobRead2().

Usage Notes

Note the following when using this operation.

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.

Chunk Size

A chunk is one or more Oracle blocks. You can specify the chunk size for the BasicFiles LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The API you are using has a function that returns the amount of space used in the LOB chunk to store the LOB value. In PL/SQL use DBMS_LOB.GETCHUNKSIZE. In OCI, use OCILobGetChunkSize(). For SecureFiles, CHUNK is an advisory size and is provided for backward compatibility purposes.

To improve performance, you may run write requests using a multiple of the value returned by one of these functions. The reason for this is that you are using the same unit that the Oracle database uses when reading data from disk. If it is appropriate for your application, then you should batch reads until you have enough for an entire chunk instead of issuing several LOB read calls that operate on the same LOB chunk.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — OPEN, GETCHUNKSIZE, READ, CLOSE

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobOpen(), OCILobRead2(), OCILobClose().

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB statements, and LOB READ executable embedded SQL extension

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guidefor information about LOB READ executable embedded SQL extension

• Java (JDBC): Oracle Database JDBC Developer's Guide Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): lread.sql

• OCI: lread.c

• Java (JDBC): lread.java

This section describes how to read LOB data for multiple locators in one round trip, using OCILobArrayRead().

Usage Notes

This function improves performance in reading LOBs in the size range less than about 512 Kilobytes. For an OCI application example, assume that the program has a prepared SQL statement such as:

SELECT lob1 FROM lob_table for UPDATE;

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 */
...

Streaming Support

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

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

Polling LOB Array Read

The next example is polling LOB data in OCILobArrayRead() with variable amtp, bufl, and offset.

/* 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. */
     }
 
     /* For 3rd locator read data in 500 bytes piece from offset 101. Amount
      * is 2000, that is, total number of pieces is 2000/500 = 4.
      */
     offset[2] = 101; bufl[2] = 500; char_amtp[2] = 2000;
     
     /* For 6th locator read data in 100 bytes piece from offset 51. Amount
      * is 0 indicating pure polling, that is, data is read till the end of
      * the LOB is reached.
      */
     offset[5] = 51;  bufl[5] = 100; char_amtp[5] = 0;
 
     /* For 8th locator read 100 bytes of data in one piece. Note amount 
      * is less than buffer length indicating single piece read.
      */ 
     offset[7] = 61;  bufl[7] = 200; char_amtp[7] = 100; 
 
     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 */
...
     }

Syntax

Use the following syntax references for the OCI programmatic environment:

C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobArrayRead().

Example

An example is provided in the following programmatic environment:

OCI: lreadarr.c

This section describes how to read a portion of a LOB using SUBSTR.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — SUBSTR, OPEN, CLOSE

• C (OCI): There is no applicable syntax reference for this use case

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and ALLOCATE, LOB OPEN, LOB READ, LOB CLOSE executable embedded SQL extensions

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's GuideLOB READ executable embedded SQL extension

• Java (JDBC): Oracle Database JDBC Developer's Guide, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): lsubstr.sql

• OCI: No example is provided with this release.

• C++ (OCCI): No example is provided with this release.

• Java (JDBC): lsubstr.java

This section describes how to compare all or part of two LOBs.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — COMPARE.

• C (OCI): There is no applicable syntax reference for this use case.

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guideor information on LOBs, usage notes on LOB Statements, and EXECUTE executed embedded SQL

  C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guidefor more information on EXECUTE executed embedded SQL

• Java (JDBC): Oracle Database JDBC Developer's Guide, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): lcompare.sql

• C (OCI): No example is provided with this release.

• C++ (OCCI): No example is provided with this release.

• Java (JDBC): lcompare.java

This section describes how to see if a pattern exists in a LOB using INSTR.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — INSTR

• C (OCI): There is no applicable syntax reference for this use case.

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB Statements, and EXECUTE executed embedded SQL

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guidefor more information on EXECUTE executed embedded SQL

• Java (JDBC): Oracle Database JDBC Developer's Guide Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): linstr.sql

• C (OCI): No example is provided with this release.

• C++ (OCCI): No example is provided with this release.

• Java (JDBC): linstr.java

This section describes how to determine the length of a LOB.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — GETLENGTH

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobGetLength2()

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB Statements, and LOB DESCRIBE executable embedded SQL extension

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guidefor more information on LOB DESCRIBE executable embedded SQL extension

• Java (JDBC): Oracle Database JDBC Developer's Guide, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package) llength.sql

• OCI: llength.c

• C++ (OCCI): No example is provided with this release.

• Java (JDBC): llength.java

This section describes how to copy all or part of a LOB to another LOB. These APIs copy an amount of data you specify from a source LOB to a destination LOB.

Usage Notes

Note the following issues when using this API.

Specifying Amount of Data to Copy

The value you pass for the amount parameter to the DBMS_LOB.COPY function must be one of the following:

• An amount less than or equal to the actual size of the data you are loading.

• The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE.Passing this value causes the function to read the entire LOB. This is a useful technique for reading the entire LOB without introspecting the size of the LOB.

Note that for character data, the amount is specified in characters, while for binary data, the amount is specified in bytes.

Locking the Row Prior to Updating

If you plan to update a LOB value, then you must lock the row containing the LOB prior to updating. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.

Syntax

See the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — COPY

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobCopy2

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB Statements, and LOB COPY executable embedded SQL extension

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guidefor information on LOB COPY executable embedded SQL extension

• Java (JDBC): Oracle Database JDBC Developer's Guide, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): lcopy.sql

• OCI: lcopy.c

• Java (JDBC): lcopy.java

This section describes how to copy a LOB locator. Note that different locators may point to the same or different data, or to current or outdated data.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Refer to "Read-Consistent Locators" for information on assigning one lob locator to another

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobAssign(), OCILobIsEqual()

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB Statements, and ALLOCATE and LOB ASSIGN executable embedded SQL extensions

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's GuideSELECT, LOB ASSIGN executable embedded SQL extensions

• Java (JDBC): Oracle Database JDBC Developer's Guide Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): lcopyloc.sql

• OCI: lcopyloc.c

• C++ (OCCI): No example is provided with this release.

• Java (JDBC): lcopyloc.java

This section describes how to determine whether one LOB locator is equal to another. If two locators are equal, then this means that they refer to the same version of the LOB data.

Syntax

Use the following syntax references for each programmatic environment:

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobAssign(), OCILobIsEqual()

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's GuideLOB ASSIGN executable embedded SQL extension

• Java (JDBC): Oracle Database JDBC Developer's Guide, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL: No example is provided with this release.

• OCI: lequal.c

• C++ (OCCI): No example is provided with this release.

• Java (JDBC): lequal.java

This section describes how to determine whether a LOB locator is initialized.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this use case.

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobLocatorIsInit()

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.

• C/C++ (Pro*C/C++)Pro*C/C++ Programmer's Guide

• Java (JDBC): There is no applicable syntax reference for this use case.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): No example is provided with this release.

• OCI: linit.c

• C (OCCI)): No example is provided with this release.

• Java (JDBC): No example is provided with this release.

This section describes how to write-append the contents of a buffer to a LOB.

Usage Notes

Note the following issues regarding usage of this API.

Writing Singly or Piecewise

The writeappend operation writes a buffer to the end of a LOB.

For OCI, the buffer can be written to the LOB in a single piece with this call; alternatively, it can be rendered piecewise using callbacks or a standard polling method.

Writing Piecewise: When to Use Callbacks or Polling

If the value of the piece parameter is OCI_FIRST_PIECE, then data must be provided through callbacks or polling.

• If a callback function is defined in the cbfp parameter, then this callback function is called to get the next piece after a piece is written to the pipe. Each piece is written from bufp.

• If no callback function is defined, then OCILobWriteAppend2() returns the OCI_NEED_DATA error code. The application must call OCILobWriteAppend2() again to write more pieces of the LOB. In this mode, the buffer pointer and the length can be different in each call if the pieces are of different sizes and from different locations. A piece value of OCI_LAST_PIECE terminates the piecewise write.

Locking the Row Prior to Updating

Prior to updating a LOB value using the PL/SQL DBMS_LOB package or the OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of an SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — WRITEAPPEND

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobWriteAppend2()

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB statements

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guidefor more information on Embedded SQL Statements and Directives

• Java (JDBC): Oracle Database JDBC Developer's Guide Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): lwriteap.sql

• OCI: lwriteap.c

• C++ (OCCI): No example is provided with this release.

• Java (JDBC): lwriteap.java

This section describes how to write the contents of a buffer to a LOB.

Usage Notes

Note the following issues regarding usage of this API.

Stream Write

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.

Chunk Size

A chunk is one or more Oracle blocks. You can specify the chunk size for the LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The API you are using has a function that returns the amount of space used in the LOB chunk to store the LOB value. In PL/SQL use DBMS_LOB.GETCHUNKSIZE. In OCI, use OCILobGetChunkSize().

Use a Multiple of the Returned Value to Improve Write Performance

To improve performance, run write requests using a multiple of the value returned by one of these functions. The reason for this is that the LOB chunk is versioned for every write operation. If all writes are done on a chunk basis, then no extra or excess versioning is incurred or duplicated. If it is appropriate for your application, then you should batch writes until you have enough for an entire chunk instead of issuing several LOB write calls that operate on the same LOB chunk.

Locking the Row Prior to Updating

Prior to updating a LOB value using the PL/SQL DBMS_LOB Package or OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.

Using DBMS_LOB.WRITE to Write Data to a BLOB

When you are passing a hexadecimal string to DBMS_LOB.WRITE() to write data to a BLOB, use the following guidelines:

• The amount parameter should be <= the buffer length parameter

• The length of the buffer should be ((amount*2) - 1). This guideline exists because the two characters of the string are seen as one hexadecimal character (and an implicit hexadecimal-to-raw conversion takes place), that is, every two bytes of the string are converted to one raw byte.

The following example is correct:

declare
   blob_loc  BLOB;
   rawbuf RAW(10);
   an_offset INTEGER := 1;
   an_amount BINARY_INTEGER := 10;
BEGIN
   select blob_col into blob_loc from a_table
where id = 1;
   rawbuf := '1234567890123456789';
   dbms_lob.write(blob_loc, an_amount, an_offset,
rawbuf);
   commit;
END;

Replacing the value for an_amount in the previous example with the following values, yields error message, ora_21560:

    an_amount BINARY_INTEGER := 11;

or

    an_amount BINARY_INTEGER := 19;

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — WRITE

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobWrite2().

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB statements, and LOB WRITE executable embedded SQL extension

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's GuideLOB WRITE executable embedded SQL extension

• Java (JDBC): Oracle Database JDBC Developer's Guide Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): lwrite.sql

• OCI: lwrite.c

• Java (JDBC): lwrite.java

This section describes how to write LOB data for multiple locators in one round trip, using OCILobArrayWrite().

Usage Notes

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

...

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

Polling LOB Data in Array Write

The next example is polling LOB data in OCILobArrayWrite() with variable amtp, bufl, and offset.

/* Fetch the locators */
...
 
ub4    array_iter = 10;
char  *bufp[10];
oraub8 bufl[10];
oraub8 char_amtp[10];
oraub8 offset[10];
sword  st;
int    i, j;
int piece_count; 
 
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 3rd locator write data in 500 bytes piece from offset 101. Amount
      * is 2000, that is, total number of pieces is 2000/500 = 4.
      */
     offset[2] = 101; bufl[2] = 500; char_amtp[2] = 2000;
     
     /* For 6th locator write data in 100 bytes piece from offset 51. Amount
      * is 0 indicating pure polling, that is, data is written 
      * till OCI_LAST_PIECE
      */
     offset[5] = 51;  bufl[5] = 100; char_amtp[5] = 0;
 
     /* For 8th locator write 100 bytes of data in one piece. Note amount 
      * is less than buffer length indicating single piece write.
      */ 
     offset[7] = 61;  bufl[7] = 200; char_amtp[7] = 100;
 
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] */
...
    /* Calculate number of pieces that must be written */
    piece_count = char_amtp[i-1]/bufl[i-1];
 
    /* Single piece case */
    if (char_amtp[i-1] <= bufl[i-1])
      piece_count = 1;
 
    /* Zero amount indicates pure polling. So we can write as many
     * pieces as needed. Let us write 50 pieces.
     */
    if (char_amtp[i-1] == 0)
      piece_count = 50;
 
    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 < piece_count; 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] */
...
 
/* If piece_count is 1 it is a single piece write. */
if (piece_count[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);
}
 
...

Syntax

Use the following syntax references for the OCI programmatic environment:

C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobArrayWrite().

Example

An example is provided in the following programmatic environment:

OCI: lwritearr.c

This section describes how to trim a LOB to the size you specify.

Usage Notes

Note the following issues regarding usage of this API.

Locking the Row Prior to Updating

Prior to updating a LOB value using the PL/SQL DBMS_LOB Package, or OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of:

• A SELECT FOR UPDATE statement in SQL and PL/SQL programs.

• An OCI pin or lock function in OCI programs.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — TRIM

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobTrim2().

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB statements, and LOB TRIM executed embedded SQL extension

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guide for more information on LOB TRIM executed embedded SQL extension

• Java (JDBC): Oracle Database JDBC Developer's Guide Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): ltrim.sql

• OCI: ltrim.c

• C++ (OCCI): No example is provided with this release.

• Java (JDBC): ltrim.java

This section describes how to erase part of a LOB.

Usage Notes

Note the following issues regarding usage of this API.

Locking the Row Prior to Updating

Prior to updating a LOB value using the PL/SQL DBMS_LOB Package or OCI, you must lock the row containing the LOB. While INSERT and UPDATE statements implicitly lock the row, locking is done explicitly by means of a SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using the OCI pin or lock function in OCI programs.

Syntax

Use the following syntax references for each programmatic environment:

• PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — ERASE

• C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobErase2().

• C++ (OCCI): Oracle C++ Call Interface Programmer's Guide

• COBOL (Pro*COBOL)Pro*COBOL Programmer's Guidefor information on LOBs, usage notes on LOB statements, and LOB ERASE executable embedded SQL extension.

• C/C++ (Pro*C/C++):Pro*C/C++ Programmer's Guidefor more information on LOB ERASE executable embedded SQL extension

• Java (JDBC): Oracle Database JDBC Developer's Guide, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column

Examples

Examples are provided in the following programmatic environments:

• PL/SQL (DBMS_LOB Package): lerase.sql

• OCI: lerase.c

• C++ (OCCI): No example is provided with this release.

• Java (JDBC): lerase.java

This technique is convenient if you have character data stored in binary format that you want to store in a CLOB. You specify the character set of the binary data when calling this procedure.

You can convert a CLOB instance to a BLOB instance using the PL/SQL procedure DBMS_LOB.CONVERTTOBLOB. This technique is a convenient way to convert character data to binary data using LOB APIs. See

This script can be used to ensure that hot backups can be taken of tables that have NOLOGGING or FILESYSTEM_LIKE_LOGGING LOBs and have a known recovery point with no read inconsistencies:

ALTER DATABASE FORCE LOGGING;
SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;  --Start SCN

SCN (System Change Number) is a stamp that defines a version of the database at the time that a transaction is committed.

Perform the backup.

Run the next script:

ALTER SYSTEM CHECKPOINT GLOBAL;
SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;  --End SCN
ALTER DATABASE NO FORCE LOGGING;

Back up the archive logs generated by the database. At the minimum, archive logs between start SCN and end SCN (including both SCN points) must be backed up.

To restore to a point with no read inconsistency, restore to end SCN as your incomplete recovery point. If recovery is done to an SCN after end SCN, there can be read inconsistency in the NOLOGGING LOBs.

For SecureFiles, if a read inconsistency is found during media recovery, the database treats the inconsistent blocks as holes and fills BLOBs with 0's and CLOBs with fill characters.