Skip Headers
Oracle® Database SecureFiles and Large Objects Developer's Guide
11g Release 1 (11.1)
Part Number B28393-01
Go to Documentation Home
Home		 Go to Book List
Book List		 Go to Table of Contents
Contents		 Go to Index
Index		 Go to Master Index
Master Index		 Go to Feedback page
Contact Us
Go to previous page
Previous		 Go to next page
Next
View PDF

15 Using LOB APIs

This chapter describes APIs that perform operations on BLOB, CLOB, and NCLOB datatypes. The operations given in this chapter can be used with either persistent or temporary LOB instances. Note that operations in this chapter do not apply to BFILEs. APIs covered in this chapter are listed in Table 15-1.

See Also:

The following information is given for each operation described in this chapter:

This chapter contains these topics:

Supported Environments

Table 15-1, "Environments Supported for LOB APIs" indicates which programmatic environments are supported for the APIs discussed in this chapter. The first column describes the operation that the API performs. The remaining columns indicate with "Yes" or "No" whether the API is supported in PL/SQL, OCI, OCCI, COBOL, Pro*C/C++, COM, and JDBC.

Table 15-1 Environments Supported for LOB APIs

Operation PL/SQL OCI OCCI COBOL Pro*C/C++ COM JDBC

Appending One LOB to Another

Yes

Yes

No

Yes

Yes

Yes

Yes

Determining Character Set Form

No

Yes

No

No

No

No

No

Determining Character Set ID

No

Yes

No

No

No

No

No

Determining Chunk Size, See: Writing Data to a LOB

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Comparing All or Part of Two LOBs

Yes

No

No

Yes

Yes

Yes

Yes

Converting a BLOB to a CLOB

Yes

No

No

No

No

No

No

Converting a CLOB to a BLOB

Yes

No

No

No

No

No

No

Copying a LOB Locator

Yes

Yes

No

Yes

Yes

Yes

Yes

Copying All or Part of One LOB to Another LOB

Yes

Yes

No

Yes

Yes

Yes

Yes

Disabling LOB Buffering

No

Yes

No

Yes

Yes

Yes

No

Displaying LOB Data

Yes

Yes

No

Yes

Yes

Yes

Yes

Enabling LOB Buffering

No

No

No

Yes

Yes

Yes

No

Equality: Checking If One LOB Locator Is Equal to Another

No

Yes

No

No

Yes

No

Yes

Erasing Part of a LOB

Yes

Yes

No

Yes

Yes

Yes

Yes

Flushing the Buffer

No

Yes

No

Yes

Yes

No

No

Determining Whether LOB Locator Is Initialized

No

Yes

No

No

Yes

No

No

Length: Determining the Length of a LOB

Yes

Yes

No

Yes

Yes

Yes

Yes

Loading a LOB with Data from a BFILE

Yes

Yes

No

Yes

Yes

Yes

Yes

Loading a BLOB with Data from a BFILE

Yes

No

No

No

No

No

No

Loading a CLOB or NCLOB with Data from a BFILE

Yes

No

No

No

No

No

No

LOB Array Read

No

Yes

No

No

No

No

No

LOB Array Write

No

Yes

No

No

No

No

No

Opening Persistent LOBs with the OPEN and CLOSE Interfaces

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Open: Determining Whether a LOB is Open

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Patterns: Checking for Patterns in a LOB Using INSTR

Yes

No

No

Yes

Yes

No

Yes

Reading a Portion of a LOB (SUBSTR)

Yes

No

No

Yes

Yes

Yes

Yes

Reading Data from a LOB

Yes

Yes

No

Yes

Yes

Yes

Yes

Storage Limit, Determining: Maximum Storage Limit for Terabyte-Size LOBs

Yes

No

No

No

No

No

No

Trimming LOB Data

Yes

Yes

No

Yes

Yes

Yes

Yes

WriteNoAppend, see Appending to a LOB .

No

No

No

No

No

No

No

Writing Data to a LOB

Yes

Yes

Yes

Yes

Yes

Yes

Yes

Appending One LOB to Another

This operation appends one LOB instance to another.

Preconditions

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

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. For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".

Syntax

See the following syntax references for each programmatic environment:

Examples

To run the following examples, you must create two LOB instances and pass them when you call the given append operation. Creating a LOB instance is described in Chapter 13, "Operations Specific to Persistent and Temporary LOBs".

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

Determining Character Set Form

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

Syntax

Use the following syntax references for each programmatic environment:

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:

Determining Character Set ID

This section describes how to determine the character set ID.

Syntax

Use the following syntax references for each programmatic environment:

Example

This functionality is currently available only in OCI:

Loading a LOB with Data from a BFILE

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 datatype.

See Also:

  • The LOADBLOBFROMFILE and LOADCLOBFROMFILE procedures implement the functionality of this procedure and provide improved features for loading binary data and character data. (These improved procedures are available in the PL/SQL environment only.) When possible, using one of the improved procedures is recommended. See "Loading a BLOB with Data from a BFILE" and "Loading a CLOB or NCLOB with Data from a BFILE" for more information.

  • As an alternative to this operation, you can use SQL*Loader to load persistent LOBs with data directly from a file in the file system. See "Using SQL*Loader to Load LOBs" for more information.

Preconditions

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

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, see Loading a CLOB or NCLOB with Data from a BFILE for more information.

Specifying Amount of BFILE Data to Load

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

Table 15-2 Maximum LOB Size for Load from File Operations

Environment Function To pass maximum LOB size, get value of:

DBMS_LOB

DBMS_LOB.LOADBLOBFROMFILE

DBMS_LOB.LOBMAXSIZE

DBMS_LOB

DBMS_LOB.LOADCLOBFROMFILE

DBMS_LOB.LOBMAXSIZE

OCI

OCILobLoadFromFile2()

(For LOBs of any size.)

UB8MAXVAL

OCI

OCILobLoadFromFile()

(For LOBs less than 4 gigabytes in size.)

UB4MAXVAL

Syntax

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

Examples

Examples are provided in the following programmatic environments:

Loading a BLOB with Data from a BFILE

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.

See Also:

Preconditions

The following conditions must be met before calling this procedure:

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:

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:

Loading a CLOB or NCLOB with Data from a BFILE

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.

See Also:

Preconditions

The following conditions must be met before calling this procedure:

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:

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:

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

The following example illustrates:

  • How to use default csid (0).

  • How to load the entire file without calling getlength for the BFILE.

  • How to find out the actual amount loaded using return offsets.

This example assumes that ad_source is a BFILE in UTF8 character set format and the database character set is UTF8. The online file is lldclobf.sql.

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

The following example illustrates:

  • How to get the character set ID from the character set name using the NLS_CHARSET_ID function.

  • How to load a stream of data from a single BFILE into different LOBs using the returned offset value and the language context lang_ctx.

  • How to read a warning message.

This example assumes that ad_file_ext_01 is a BFILE in JA16TSTSET format and the database national character set is AL16UTF16. The online file is lldclobs.sql.

Determining Whether a LOB is Open

This operation determines whether a LOB is open.

Preconditions

The LOB instance must exist before executing this procedure.

Usage Notes

When a LOB is open, it must be closed at some point later in the session.

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

Java (JDBC): Checking If a LOB Is Open

Here is how to check a BLOB or a CLOB.

Checking If a CLOB Is Open

To see if a CLOB is open, your JDBC application can use the isOpen method defined in oracle.sql.CLOB. The return Boolean value indicates whether the CLOB has been previously opened or not. The isOpen method is defined as follows:

/** 
  * Check whether the CLOB is opened. 
  * @return true if the LOB is opened. 
  */ 
public boolean isOpen () throws SQLException

The usage example is:

CLOB clob = ... 
 // See if the CLOB is opened 
 boolean isOpen = clob.isOpen ();
...

Checking If a BLOB Is Open

To see if a BLOB is open, your JDBC application can use the isOpen method defined in oracle.sql.BLOB. The return Boolean value indicates whether the BLOB has been previously opened or not. The isOpen method is defined as follows:

/** 
 * Check whether the BLOB is opened. 
 * @return true if the LOB is opened. 
 */ 
 public boolean isOpen () throws SQLException

The usage example is:

BLOB blob = ... 
// See if the BLOB is opened 
boolean isOpen = blob.isOpen ();
...

Displaying LOB Data

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 15-3.

Table 15-3 Maximum LOB Size for Amount Parameter

Environment Function Value of amount parameter is limited to:

DBMS_LOB

DBMS_LOB.READ

The size of the buffer, 32Kbytes.

OCI

OCILobRead()

(For LOBs less than 4 gigabytes in size.)

UB4MAXVAL

Specifying this amount reads the entire file.

OCI

OCILobRead2()

(For LOBs of any size.)

UB8MAXVAL

Specifying this amount reads the entire file.

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

Reading Data from a LOB

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, will indicate 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 the Oracle Call Interface Programmer's Guide.)

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

You will improve performance if you 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:

Examples

Examples are provided in the following programmatic environments:

LOB Array Read

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 Kbytes. 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:

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() needs to 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
           needs to 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 will be 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 will be 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

Reading a Portion of a LOB (SUBSTR)

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

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

Comparing All or Part of Two LOBs

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

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

Patterns: Checking for Patterns in a LOB Using INSTR

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:

Examples

Examples are provided in the following programmatic environments:

Length: Determining the Length of a LOB

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

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

Copying All or Part of One LOB to Another LOB

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:

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.

For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".

Syntax

See the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

Copying a LOB Locator

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.

Note:

Assigning one LOB to another using PL/SQL entails using the ":=" sign. This is an advanced topic that is discussed in more detail in "Read-Consistent Locators".

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

Equality: Checking If One LOB Locator Is Equal to Another

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.

See Also:

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

Determining Whether LOB Locator Is Initialized

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

See Also:

Table 15-1, "Environments Supported for LOB APIs"

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

Appending to a LOB

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

See Also:

Table 15-1, "Environments Supported for LOB APIs"

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.

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.

For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".

Syntax

Use the following syntax references for each programmatic environment:

Examples

Examples are provided in the following programmatic environments:

Writing Data to a LOB

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

See Also:

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 will be 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 will make for faster reads and writes in subsequent operations.

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

You will improve performance if you 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.

For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".

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 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:




Examples


Examples are provided in the following programmatic environments:









LOB Array Write


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



Usage Notes




See Also:

"LOB Array Read" for examples of array read/write.



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*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 need to 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 needs to
  *   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 will be 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 will be 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 need to 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








Trimming LOB Data


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




See Also:

Table 15-1, "Environments Supported for LOB APIs"



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:



For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".



Syntax


Use the following syntax references for each programmatic environment:




Examples


Examples are provided in the following programmatic environments:









Erasing Part of a LOB


This section describes how to erase part of a LOB.




See Also:

Table 15-1, "Environments Supported for LOB APIs"



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.


For more details on the state of the locator after an update, refer to"Example of Updating LOBs Through Updated Locators".



Syntax


Use the following syntax references for each programmatic environment:




Examples


Examples are provided in the following programmatic environments:









Enabling LOB Buffering


This section describes how to enable LOB buffering.




See Also:

Table 15-1, "Environments Supported for LOB APIs"



Usage Notes


Enable LOB buffering when you are performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.




Note:


    

  • 

    You must flush the buffer in order to make your modifications persistent.
    

    • 

  • 

    Do not enable buffering for the stream read and write involved in checkin and checkout.
    

    • 





For more information, refer to "LOB Buffering Subsystem".



Syntax


Use the following syntax references for each programmatic environment:




Examples


Examples are provided in the following programmatic environments:









Flushing the Buffer


This section describes how to flush the LOB buffer.




See Also:

Table 15-1, "Environments Supported for LOB APIs"



Usage Notes


Enable buffering when performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.




Notes:


    

  • 

    You must flush the buffer in order to make your modifications persistent.
    

    • 

  • 

    Do not enable buffering for the stream read and write involved in checkin and checkout.
    

    • 





For more information, refer to "LOB Buffering Subsystem".



Syntax


Use the following syntax references for each programmatic environment:




Examples


Examples are provided in the following programmatic environments:









Disabling LOB Buffering


This section describes how to disable LOB buffering.




See Also:

Table 15-1, "Environments Supported for LOB APIs"



Usage Notes


Enable buffering when performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.




Note:


    

  • 

    You must flush the buffer in order to make your modifications persistent.
    

    • 

  • 

    Do not enable buffering for the stream read and write involved in checkin and checkout.
    

    • 





For more information, refer to "LOB Buffering Subsystem"



Syntax


Use the following syntax references for each programmatic environment:




Examples


Examples are provided in the following programmatic environments:









Determining Whether a LOB instance Is Temporary


This section describes how to determine whether a LOB instance is temporary.




See Also:

Table 15-1, "Environments Supported for LOB APIs"



Syntax


Use the following syntax references for each programmatic environment:




Examples


Examples are provided in the following programmatic environments:





Java (JDBC): Determining Whether a BLOB Is Temporary


To see if a BLOB is temporary, the JDBC application can either use the isTemporary instance method to determine whether the current BLOB object is temporary, or pass the BLOB object to the static isTemporary method to determine whether the specified BLOB object is temporary. These two methods are defined inlistempb.java.


This JDBC API replaces previous workarounds that use DBMS_LOB.isTemporary().


To determine whether a CLOB is temporary, the JDBC application can either use the isTemporary instance method to determine whether the current CLOB object is temporary, or pass the CLOB object to the static isTemporary method. These two methods are defined in listempc.java.










Converting a BLOB to a CLOB


You can convert a BLOB instance to a CLOB using the PL/SQL procedure DBMS_LOB.CONVERTTOCLOB. 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. See Oracle Database PL/SQL Packages and Types Reference for details on syntax and usage of this procedure.








Converting a CLOB to a BLOB


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 Oracle Database PL/SQL Packages and Types Reference for details on syntax and usage of this procedure.



















Go to previous page

Previous		
Go to next page

Next		











Go to Documentation Home

Home		
Go to Book List

Book List		
Go to Table of Contents

Contents		
Go to Index

Index		
Go to Master Index

Master Index		
Go to Feedback page

Contact Us