7 LOB and BFILE Operations

This chapter contains these topics:

Using OCI Functions for LOBs

The OCI includes a set of functions for performing operations on large objects (LOBs) in a database. Persistent LOBs (BLOBs, CLOBs, NCLOBs) are stored in the database tablespaces in a way that optimizes space and provides efficient access. These LOBs have the full transactional support of the database server. BFILEs are large data objects stored in the server's operating system files outside the database tablespaces.

The OCI also provides support for temporary LOBs, which can be used like local variables for operating on LOB data.

BFILEs are read-only. Oracle supports only binary BFILEs.

See Also:

Creating and Modifying Persistent LOBs

LOB instances can be either persistent (stored in the database) or temporary (existing only in the scope of your application). Do not confuse the concept of a persistent LOB with a persistent object.

There are two ways of creating and modifying persistent LOBs:

  1. Using the data interface

    You can create a LOB by inserting character data into a CLOB column or RAW data into a BLOB column directly. You can also modify LOBs by using a SQL UPDATE statement, binding character data into a CLOB column or RAW data into a BLOB column.

    Insert, update, and select of remote LOBs (over a dblink) is supported as long as neither the remote server or the local server is of a release less than Oracle Database 10g Release 2. The data interface only supports data size up to 2 GB -1, the maximum size of an sb4.

    See Also:

    Oracle Database SecureFiles and Large Objects Developer's Guide chapter "Data Interface for Persistent LOBs", for more information and examples.
  2. Using the LOB locator

    You create a new internal LOB by initializing a new LOB locator using OCIDescriptorAlloc(), calling OCIAttrSet() to set it to empty (using the OCI_ATTR_LOBEMPTY attribute), and then binding the locator to a placeholder in an INSERT statement. Doing so inserts the empty locator into a table with a LOB column or attribute. You can then SELECT...FOR UPDATE this row to get the locator, and write to it using one of the OCI LOB functions.

    Note:

    To modify a LOB column or attribute (write, copy, trim, and so forth), you must lock the row containing the LOB. One way to do this is to use a SELECT...FOR UPDATE statement to select the locator before performing the operation.

See Also:

"Binding LOB Data" for usage and examples for both INSERT and UPDATE

For any LOB write command to be successful, a transaction must be open. If you commit a transaction before writing the data, you must lock the row again (by reissuing the SELECT...FOR UPDATE, for example), because the commit closes the transaction.

Associating a BFILE in a Table with an Operating System File

The BFILENAME() function can be used in an INSERT statement to associate an external server-side (operating system) file with a BFILE column or attribute in a table. Using BFILENAME() in an UPDATE statement associates the BFILE column or attribute with a different operating system file. OCILobFileSetName() can also be used to associate a BFILE in a table with an operating system file. BFILENAME() is usually used in an INSERT or UPDATE without bind variables and OCILobFileSetName() is used for bind variables.

See Also:

LOB Attributes of an Object

An OCI application can use OCIObjectNew() to create a persistent or transient object with a LOB attribute.

Writing to a LOB Attribute of an Object

It is possible to use the OCI to create a new persistent object with a LOB attribute and write to that LOB attribute. The application would follow these steps when using a LOB locator:

  1. Call OCIObjectNew() to create a persistent object with a LOB attribute.

  2. Mark the object as "dirty."

  3. Flush the object, thereby inserting a row into the table

  4. Re-pin the latest version of the object (or refresh the object), thereby retrieving the object from the database and acquiring a valid locator for the LOB

  5. Call OCILobWrite() using the LOB locator in the object to write the data.

    See Also:

    Chapter 11, "OCI Object-Relational Programming" and the chapters that follow it, for more information about objects

There is a second way of writing to a LOB attribute: when using the data interface, you can bind or define character data for a CLOB attribute or RAW data for a BLOB attribute.

See Also:

Transient Objects with LOB Attributes

An application can call OCIObjectNew() and create a transient object with an internal LOB (BLOB, CLOB, NCLOB) attribute. However, you cannot perform any operations, such as read or write, on the LOB attribute because transient objects with LOB attributes are not supported. Calling OCIObjectNew() to create a transient internal LOB type will not fail, but the application cannot use any LOB operations with the transient LOB.

An application can, however, create a transient object with a BFILE attribute and use the BFILE attribute to read data from a file stored in the server's file system. The application can also call OCIObjectNew() to create a transient BFILE.

Array Interface for LOBs

You can use the OCI array interface with LOBs, just as with any other datatype. There are two ways of using the array interface.

  1. Using the data interface

    You can bind or define arrays of character data for a CLOB column or RAW data for a BLOB column. You can use array bind and define interfaces to insert and select multiple rows with LOBs in one round trip to the server.

    See Also:

  2. Using the LOB locator

    When using the LOB locator you must allocate the descriptors:

    /* First create an array of OCILobLocator pointers: */
    OCILobLocator *lobp[10];
    
    for (i=0; i < 10; i++)
    { OCIDescriptorAlloc (...,&lobp[i],...);
    
    /* Then bind the descriptor as follows */
      OCIBindByPos(... &lobp[i], ...);
    }
    

Using LOBs of Size Greater than 4 GB

Starting with Oracle Database 10g Release 1 of OCI, functions were introduced to support LOBs of size greater than 4 GB. These new functions can also be used in new applications for LOBs of less than 4 GB.

Oracle Database lets you create tablespaces with blocksizes different from the database block size, and the maximum size of a LOB depends on the size of the tablespace blocks. CHUNK is a parameter of LOB storage whose value is controlled by the block size of the tablespace in which the LOB is stored. When you create a LOB column, you can specify a value for CHUNK, which is the number of bytes to be allocated for LOB manipulation. The value must be a multiple of the tablespace block size, or Oracle Database rounds up to the next multiple. (If the tablespace block size is the same as the database block size, then CHUNK is also a multiple of the database block size.) The default CHUNK size is one tablespace block, and the maximum value is 32K.

For example, suppose your database block size is 32K and you create a tablespace with a nonstandard block size of 8K. Further suppose that you create a table with a LOB column and specify a CHUNK size of 16K (which is a multiple of the 8K tablespace block size). Then the maximum size of a LOB in this column is (4 gigabytes - 1) * 16K.

In this guide, 4 GB is defined as 4 gigabytes -1, or 4,294,967,295 bytes. The maximum size of a LOB, persistent or temporary, is (4 gigabytes - 1) * (CHUNK). The maximum LOB size can range from 8 terabytes to 128 terabytes.

The maximum size of a BFILE is the maximum file size allowed in the operating system, or UB8MAXVAL, whichever is smaller.

Because the older LOB functions use ub4 as the datatypes of some parameters and ub4 datatype can only hold up to 4 GB. The newer functions use parameters of 8-byte length, oraub8, which is a datatype defined in oratypes.h. The datatypes oraub8 and orasb8 are mapped to appropriate 64-bit native datatypes depending on the compiler and operating system. Macros are used to not define oraub8 and orasb8 if compiling in 32-bit mode with strict ANSI option.

OCILobGetChunkSize() returns the usable chunk size in bytes for BLOBs, CLOBs, and NCLOBs. The number of bytes stored in a chunk is actually less than the size of the CHUNK parameter due to internal storage overhead. The function, OCILobGetStorageLimit(), is provided to return the maximum size in bytes of internal LOBs in the current installation.

Note:

Oracle Database does not support BFILEs larger than 4 gigabytes in any programmatic environment. An additional file size limit imposed by your operating system also applies to BFILEs.

New Functions for the Increased LOB Sizes

Eight functions with names that end in "2" and use the datatype oraub8 in place of the datatype ub4 were introduced in Oracle Database 10g Release 1. Other changes are made in the read and write functions (OCILobRead2(), OCILobWrite2(), and OCILobWriteAppend2()) to solve several problems:

Problem: Before Oracle Database 10g Release 1, the parameter amtp assumes either byte or char length for LOBs based on the locator type and character set. It is complicated and users did not have the flexibility to use byte length or char length according to their requirements.

Solution: Read/Write calls should take both byte_amtp and char_amtp as replacement for amtp parameter. char_amtp takes preference for CLOB and NCLOB and byte_amtp is only considered as input if char_amtp is zero. On output for CLOB and NCLOB, both byte_amtp and char_amtp are filled. For BLOB and BFILE, char_amtp parameter is ignored for both input and output.

Problem: For OCILobRead2(), there is no flag to indicate polling mode. There is no easy way for the users to say "I have a 100 byte buffer. Fill it as much as you can". Previously, they had to estimate how many characters to specify for the amount. If they guessed too much, they were forced into polling mode unintentionally. The user code thus can get trapped in the polling mode and subsequent OCI calls are all blocked.

Solution: This call should take piece as an input parameter and if OCI_ONE_PIECE is passed, it should fill the buffer as much as possible and come out even if the amount indicated by byte_amtp or char_amtp is more than the buffer length. The value of bufl is used to specify the maximum amount of bytes to read.

Problem: After calling for a LOB write in polling mode, users do not know how many chars or bytes are actually fetched till the end of the polling.

Solution: Both byte_amtp and char_amtp need to be updated after each call in polling mode.

Problem: While reading or writing data in streaming mode with callback, users have to use the same buffer for each piece of data.

Solution: The callback function needs to have two new parameters to provide a new buffer and the buffer length. Callback functions can set the new buffer parameter to NULL to follow old behavior: to use the default buffer passed in the first call for all the pieces.

Compatibility and Migration

Existing OCI programs can be enhanced to process larger amounts of LOB data (greater than 4GB). Table 7-1 summarizes compatibility issues ("old" refers to releases before Oracle Database 10g Release 1):

Table 7-1 LOB Functions Compatibility and Migration

LOB Function Old Client/New or Old Server New Client/Old Server New Client/New Server

OCILobArrayRead()

NA

OK until piece size and offset are < 4GB.

OK

OCILobArrayWrite()

NA

OK until piece size and offset are < 4GB.

OK

OCILobCopy2()

NA

OK until LOB size, piece size (amount) and offset are < 4GB.

OK

OCILobCopy()

OK; limit is 4GB.

OK

OK; limit is 4GB.

OCILobErase2()

NA

OK until piece size and offset are < 4GB.

OK

OCILobErase()

OK; limit is 4GB.

OK

OK; limit is 4GB.

OCILobGetLength2()

NA

OK

OK

OCILobGetLength()

OK; limit is 4GB.

OK

OK; OCI_ERROR if LOB size > 4GB.

OCILobLoadFromFile2()

NA

OK until LOB size, piece size (amount) and offset are < 4GB.

OK

OCILobLoadFromFile()

OK; limit is 4GB.

OK

OK; limit is 4GB.

OCILobRead2()

NA

OK until LOB size, piece size (amount) and offset are < 4GB.

OK

OCILobRead()

OK; limit 4GB.

With new server: OCI_ERROR will be returned if you try to read any amount >= 4GB from any offset < 4GB. This is because when you read any amount >= 4GB, that will result in overflow of returned value in *amtp, and so will be flagged as an error.

Note:

1) If you read up to 4GB-1 from offset, that will not be flagged as an error.

2) When you use streaming mode with polling, no error will be returned if no attempt is made to use piece size > 4GB (you can read data > 4GB in this case).

OK

OK.

OCI_ERROR will be returned if you try to read any amount >= 4GB from any offset < 4GB. This is because when you read any amount >= 4GB, that will result in overflow of returned value in *amtp, and so will be flagged as an error.

Note:

1) If you read up to 4GB-1 from offset, that will not be flagged an as error.

2) When the you use streaming mode with polling, no error will be returned if no attempt is made to use piece size > 4GB.

OCIlobTrim2()

NA

OK

OK

OCIlobTrim()

OK; limit 4GB.

OK

OK; limit 4GB.

OCILobWrite2()

NA

OK until LOB size, piece size (amount) and offset are < 4GB.

OK

OCILobWrite()

OK; limit 4GB.

With new server:

OCI_ERROR will be returned if you write any amount >= 4GB (from any offset <4GB) because that will result in overflow of returned value in *amtp.

Note: Updating a LOB of 10GB from any offset up to 4GB-1 by up to 4GB-1 amount of data will not be flagged as an error.

OK

OK.

OCI_ERROR will be returned if you write any amount > =4GB (from any offset < 4GB) because that will result in overflow of returned value in *amtp.

Note: Updating a LOB of 10GB from any offset up to 4GB-1 by up to 4GB-1 amount of data will not be flagged as an error.

OCILobWriteAppend2()

NA

OK until LOB size and piece size are <4GB.

OK

OCILobWriteAppend()

OK; limit 4GB.

With new server: OCI_ERROR will be returned if you append any amount >= 4GB of data because that will result in overflow of returned value in *amtp.

OK

OK; limit 4GB.

OCI_ERROR will be returned if you append any amount >= 4GB of data because that will result in overflow of returned value in *amtp.

OCILobGetStorageLimit()

NA

Error

OK


Use the new functions when using the new server and new client. Mixing old and new functions can result in unexpected situations such as data written using OCILobWrite2() being greater than 4GB if the application tries to read it with OCILobRead() and gets only partial data (if a callback function is not used). In most cases the application will get an error message when the size crosses 4GB and older functions are used. However, there will be no issue if you use those older functions for LOBs of size smaller than 4GB.

LOB and BFILE Functions in OCI

In all LOB operations that involve offsets into the data, the offset begins at 1. For LOB operations, such as OCILobCopy(), OCILobErase(), OCILobLoadFromFile(), and OCILobTrim(), the amount parameter is in characters for CLOBs and NCLOBs, regardless of the client-side character set.

These LOB operations refer to the amount of LOB data on the server. When the client-side character set is of varying width, the following general rules apply to the amount and offset parameters in LOB calls:

  • amount - When the amount parameter refers to the server-side LOB, the amount is in characters. When the amount parameter refers to the client-side buffer, the amount is in bytes.

  • offset - Regardless of whether the client-side character set is varying-width, the offset parameter is always in characters for CLOBs or NCLOBs and in bytes for BLOBs or BFILEs.

Exceptions to these general rules are noted in the description of the specific LOB call.

Improving LOB Read/Write Performance

Here are some hints to improve performance.

Using Data Interface For LOBs

You can bind or define character data for a CLOB column or RAW data for a BLOB column. This requires only one round trip for inserting or selecting a LOB, as opposed to the traditional LOB interface which requires multiple round trips.

See Also:

Using OCILobGetChunkSize()

OCILobGetChunkSize() returns the usable chunk size in bytes for BLOBs, CLOBs, and NCLOBs. You can use the OCILobGetChunkSize() call to improve the performance of LOB read and write operations for BasicFile LOBs. When a read or write is done on BasicFile LOB data whose size is a multiple of the usable chunk size and starts on a chunk boundary, performance is improved. There is no requirement for SecureFile LOBs to be written or read with OCILobGetChunkSize() alignment.

Calling OCILobGetChunkSize() returns the usable chunk size of the LOB, and an application can batch a series of write operations for the entire chunk, rather than issuing multiple LOB write calls for the same chunk.

Using OCILobWriteAppend2()

OCI provides a shortcut for more efficient writing of data to the end of a LOB. The OCILobWriteAppend2() call appends data to the end of a LOB without first requiring a call to OCILobGetLength() to determine the starting point for an OCILobWrite() operation. OCILobWriteAppend2() does both steps.

Using OCILobArrayRead() and OCILobArrayWrite()

Performance improvement is obtained by using these Oracle Database 10g Release 2 functions found in the sections of the LOBs documentation that describe how to read LOB data for multiple locators, using OCILobArrayRead(), and how to write LOB data for multiple LOB locators, using OCILobArrayWrite(). Code examples are also provided for using them with callback functions and in piecewise mode.

See Also:

Oracle Database SecureFiles and Large Objects Developer's Guide, sections "LOB Array Read" and "LOB Array Write"

LOB Buffering Functions

The Oracle OCI provides several calls for controlling LOB buffering for small reads and writes of internal LOB values:

These functions provide performance improvements by allowing applications using internal LOBs (BLOB, CLOB, NCLOB) to buffer small reads and writes in client-side buffers. This reduces the number of network round trips and LOB versions, thereby improving LOB performance significantly.

See Also:

Functions for Opening and Closing LOBs

The OCI provides functions to explicitly open a LOB, OCILobOpen(), to close a LOB, OCILobClose(), and to test whether a LOB is open, OCILobIsOpen(). These functions mark the beginning and end of a series of LOB operations so that specific processing such as updating indexes can be performed when a LOB is closed.

For internal LOBs, the concept of openness is associated with a LOB and not its locator. The locator does not store any information about the state of the LOB. It is possible for more than one locator to point to the same open LOB. However, for BFILEs, being open is associated with a specific locator. Hence, more than one open can be performed on the same BFILE using different locators.

If an application does not wrap LOB operations between a set of OCILobOpen() and OCILobClose() calls, then each modification to the LOB implicitly opens and closes the LOB, thereby firing any triggers associated with changes to the LOB.

If LOB operations are not wrapped inside open and close calls, any extensible indexes on the LOB are updated as LOB modifications are made, and thus are always valid and may be used at any time. If the LOB is modified between a set of OCILobOpen() and OCILobClose() calls, triggers are not fired for individual LOB modifications. Triggers are only fired after the OCILobClose() call, so indexes are not updated until after the close call and thus are not valid in between the open and close calls. OCILobIsOpen() can be used with internal LOBs and BFILEs.

An error is returned when you commit the transaction before closing all opened LOBs that were opened by the transaction. When the error is returned, the LOB is no longer marked as open, but the transaction is successfully committed. Hence, all the changes made to the LOB and non-LOB data in the transaction are committed, but the domain and functional indexing are not updated. If this happens, please rebuild your functional and domain indexes on the LOB column.

A LOB opened when there is no transaction must be closed before the end of the session. If there are LOBs open at the end of session, the LOB is no longer marked as open and the domain and functional indexing will not be updated. If this happens, rebuild your functional and domain indexes on the LOB column.

Restrictions on Opening and Closing LOBs

The LOB opening and closing mechanism has the following restrictions:

  1. An application must close all previously opened LOBs before committing a transaction. Failing to do so will result in an error. If a transaction is rolled back, all open LOBs are discarded along with the changes made since the LOBs are not closed, so associated triggers are not fired.

  2. While there is no limit to the number of open internal LOBs, there is a limit on the number of open files. Refer to SESSION_MAX_OPEN_FILES parameter in Oracle Database Reference. Assigning an already opened locator to another locator does not count as opening a new LOB.

  3. It is an error to open or close the same internal LOB twice within the same transaction, either with different locators or the same locator.

  4. It is an error to close a LOB that has not been opened.

    Note:

    The definition of a transaction within which an open LOB value must be closed is one of the following:
    • between SET TRANSACTION and COMMIT

    • between DATA MODIFYING DML or SELECT ... FOR UPDATE and COMMIT.

    • within an autonomous transaction block

    See Also:

LOB Read and Write Callbacks

OCI supports read and write callback functions. The following sections describe the use of callbacks in more detail.

The Callback Interface for Streaming

User-defined read and write callback functions for inserting or retrieving data provide an alternative to the polling methods for streaming LOB. These functions are implemented by you and registered with OCI through the OCILobRead2(), OCILobWriteAppend2(), and OCILobWrite2() calls. These callback functions are called by OCI whenever required.

Reading LOBs using Callbacks

The user-defined read callback function is registered through the OCILobRead2() function. The callback function should have the following prototype:

CallbackFunctionName ( void *ctxp, CONST void *bufp, oraub8 len, ub1 piece,
                       void **changed_bufpp, oraub8 *changed_lenp);

The first parameter, ctxp, is the context of the callback that is passed to OCI in the OCILobRead() function call. When the callback function is called, the information provided by you in ctxp is passed back to you (the OCI does not use this information on the way IN). The bufp parameter is the pointer to the storage where the LOB data is returned and bufl is the length of this buffer. It tells you how much data has been read into the buffer provided.

If the buffer length provided in the original OCILobRead2() call is insufficient to store all the data returned by the server, then the user-defined callback is called. In this case, the piece parameter indicates whether the information returned in the buffer is the first, next or last piece.

The parameters changed_bufpp and changed_lenp can be used inside the callback function to change the buffer dynamically. changed_bufpp should point to the address of the changed buffer and changed_lenp should point to the length of the changed buffer. changed_bufpp and changed_lenp need not be used inside the callback function if the application does not change the buffer dynamically.

The following code fragment implements read callback functions using OCILobRead2(). Assume that lobl is a valid locator that has been previously selected, svchp is a valid service handle and errhp is a valid error handle In the example. The user-defined function cbk_read_lob() is repeatedly called until all the LOB data has been read.

...
oraub8   offset = 1;
oraub8   loblen = 0; 
oraub8   byte_amt = 0;
oraub8   char_amt = 0
ub1      bufp[MAXBUFLEN];

sword retval; 
byte_amtp = 4294967297;     /* 4 gigabytes plus 1 */ 

if (retval = OCILobRead2(svchp, errhp, lobl, &byte_amt, &char_amt, offset,
  (void *) bufp, (oraub8) MAXBUFLEN, (void *) 0, OCI_FIRST_PIECE, 
  cbk_read_lob, (ub2) 0, (ub1) SQLCS_IMPLICIT))
{
     (void) printf("ERROR: OCILobRead2() LOB.\n");
     report_error();
}
...
sb4 cbk_read_lob(ctxp, bufxp, len, piece, changed_bufpp, changed_lenp)
void       *ctxp;
CONST void *bufxp;
oraub8      len;
ub1         piece;
void       **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\n\n", piece_count);
          piece_count = 0;
          break;
      case OCI_FIRST_PIECE:   /*--- buffer processing code goes here ---*/ 
          (void) printf("callback read the %d th piece\n", piece_count);
          /* --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\n", piece_count);
         /* --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;
}

Writing LOBs using Callbacks

Similar to read callbacks, the user-defined write callback function is registered through the OCILobWrite2() function. The callback function should have the following prototype:

CallbackFunctionName ( void *ctxp, void *bufp, oraub8 *lenp, ub1 *piecep,
                        void **changed_bufpp, oraub8 *changed_lenp);

The first parameter, ctxp, is the context of the callback that is passed to OCI in the OCILobWrite2() function call. The information provided by you in ctxp, is passed back to you when the callback function is called by the OCI (the OCI does not use this information on the way IN). The bufp parameter is the pointer to a storage area; you provide this pointer in the call to OCILobWrite().

After inserting the data provided in the call to OCILobWrite2() any data remaining is inserted by the user-defined callback. In the callback provide the data to insert in the storage indicated by bufp and also specify the length in buflp. You also indicate whether it is the next (OCI_NEXT_PIECE) or the last (OCI_LAST_PIECE) piece using the piecep parameter. You are completely responsible for the storage pointer the application provides and should make sure that it does not write more than the allocated size of the storage.

The parameters changed_bufpp and changed_lenp can be used inside the callback function to change the buffer dynamically. changed_bufpp should point to the address of the changed buffer and changed_lenp should point to the length of the changed buffer. changed_bufpp and changed_lenp need not be used inside the callback function if the application does not change the buffer dynamically.

The following code fragment implements write callback functions using OCILobWrite2(). Assume that lobl is a valid locator that has been locked for updating, svchp is a valid service handle, and errhp is a valid error handle. The user-defined function cbk_write_lob() is repeatedly called until the piecep parameter indicates that the application is providing the last piece.

...
 
ub1      bufp[MAXBUFLEN];
oraub8   byte_amt = MAXBUFLEN * 20;
oraub8   char_amt = 0;
oraub8   offset = 1;
oraub8   nbytes = MAXBUFLEN; 

/*-- code to fill bufp with data goes here. nbytes should reflect the size and
 should be less than or equal to MAXBUFLEN --*/
if (retval = OCILobWrite2(svchp, errhp, lobl, &byte_amt, &char_amt, offset,
    (void*)bufp, (ub4)nbytes, OCI_FIRST_PIECE, (void *)0, cbk_write_lob, 
    (ub2) 0, (ub1) SQLCS_IMPLICIT)) 
{
   (void) printf("ERROR: OCILobWrite2().\n");
   report_error();
   return;
}
... 
sb4 cbk_write_lob(ctxp, bufxp, lenp,  piecep, changed_bufpp, changed_lenp)
void    *ctxp; 
void    *bufxp; 
oraub8  *lenp; 
ub1       *piecep;
void  **changed_bufpp;
oraub8  *changed_lenp; 
{ 
    /*-- 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) 
        *piecep = OCI_LAST_PIECE; 
    else
        *piecep = OCI_NEXT_PIECE; 
    return OCI_CONTINUE;
}

Temporary LOB Support

The OCI provides functions for creating and freeing temporary LOBs, OCILobCreateTemporary() and OCILobFreeTemporary(), and a function for determining whether a LOB is temporary, OCILobIsTemporary().

Temporary LOBs are not permanently stored in the database, but act like local variables for operating on LOB data. OCI functions which operate on standard (persistent) LOBs can also be used on temporary LOBs.

As with persistent LOBs, all functions operate on the locator for the temporary LOB, and the actual LOB data is accessed through the locator.

Temporary LOB locators can be used as arguments to the following types of SQL statements:

  • UPDATE - The temporary LOB locator can be used as a value in a WHERE clause when testing for nullity or as a parameter to a function. The locator can also be used in a SET clause.

  • DELETE - The temporary LOB locator can be used in a WHERE clause when testing for nullity or as a parameter to a function.

  • SELECT - The temporary LOB locator can be used in a WHERE clause when testing for nullity or as a parameter to a function. The temporary LOB can also be used as a return variable in a SELECT...INTO statement when selecting the return value of a function.


Note:

If you select a permanent locator into a temporary locator, the temporary locator is overwritten with the permanent locator. In this case the temporary LOB is not implicitly freed. You must explicitly free the temporary LOB before the SELECT...INTO. If the temporary LOB is not freed explicitly, it will not be freed until the end of its duration. Unless you have another temporary locator pointing to the same LOB, you will no longer have a locator pointing to the temporary LOB, because the original locator was overwritten by the SELECT...INTO.

Creating and Freeing Temporary LOBs

You create a temporary LOB with the OCILobCreateTemporary() function. The parameters passed to this function include a value for the duration of the LOB. The default duration is for the length of the current session. All temporary LOBs are deleted at the end of the duration. Users can reclaim temporary LOB space by explicitly freeing the temporary LOB with the OCILobFreeTemporary() function. A temporary LOB is empty when it is created.

When creating a temporary LOB, you can also specify whether or not the temporary LOB is read into the server's buffer cache.

To make a temporary LOB permanent, use OCILobCopy() to copy the data from the temporary LOB into a permanent one. You can also use the temporary LOB in the VALUES clause of an INSERT statement, as the source of the assignment in an UPDATE statement, or assign it to a persistent LOB attribute and then flush the object. Temporary LOBs can be modified using the same functions which are used for standard LOBs.

Temporary LOB Durations

The OCI supports several predefined durations for temporary LOBs, and a set of functions that the application can use to define application-specific durations. The predefined durations and their associated attributes are:

  1. call, OCI_DURATION_CALL, only on the server side

  2. session, OCI_DURATION_SESSION

The session duration expires when the containing session/connection ends. The call duration expires at the end of the current OCI call.

When running in object mode, a you can also define application-specific durations. An application-specific duration, also referred to as a user duration, is defined by specifying the start of a duration using OCIDurationBegin() and the end of the duration using OCIDurationEnd().

Note:

User-defined durations are only available if an application has been initialized in object mode.

Each application-specific duration has a duration identifier that is returned by OCIDurationBegin() and is guaranteed to be unique until OCIDurationEnd() is called. An application-specific duration can be as long as a session duration.

At the end of a duration, all temporary LOBs associated with that duration are freed. The descriptor associated with the temporary LOB must be freed explicitly with the OCIDescriptorFree() call.

User-defined durations can be nested; one duration can be defined as a child duration of another user duration. It is possible for a parent duration to have child durations that have their own child durations.

Note:

When a duration is started with OCIDurationBegin(), one of the parameters is the identifier of a parent duration. When a parent duration is ended, all child durations are also ended.

Freeing Temporary LOBs

Any time that your OCI program obtains a LOB locator from SQL or PL/SQL, check that the locator is temporary. If it is, free the locator when your application is finished with it. The locator can be from a define during a select or an out bind. A temporary LOB duration is always upgraded to session when it is shipped to the client side. The application needs to do the following before the locator is overwritten by the locator of the next row:

OCILobIsTemporary(env, err, locator, is_temporary);
if(is_temporary)
    OCILobFreeTemporary(svc, err, locator);

Take Care When Assigning Pointers

Special care needs to be taken when assigning OCILobLocator pointers. Pointer assignments create a shallow copy of the LOB. After the pointer assignment, source and target LOBs point to the same copy of data. This behavior is different from using LOB APIs, such as OCILobAssign() or OCILobLocatorAssign() to perform assignments.

When the APIs are used, the locators logically point to independent copies of data after assignment.

For temporary LOBs, before pointer assignments, it is your responsibility to make sure any temporary LOB in the target LOB locator is freed by OCIFreeTemporary(). When OCILobLocatorAssign() is used, the original temporary LOB in the target LOB locator variable, if any, is freed before the assignment happens.

Before an out-bind variable is reused in executing a SQL statement, it is your responsibility to free any temporary LOB in the existing out-bind LOB locator buffer by using the OCIFreeTemporary() call.

See Also:

Temporary LOB Example

The following code example shows how temporary LOBs can be used:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <oci.h>

/* Function Prototype */
static void checkerr (/*_ OCIError *errhp, sword status _*/);
sb4 select_and_createtemp (OCILobLocator *lob_loc,
                           OCIError      *errhp,
                           OCISvcCtx     *svchp,
                           OCIStmt       *stmthp,
                           OCIEnv        *envhp);
/* This function reads in a single video Frame from the print_media table.
Then it creates a temporary lob. The temporary LOB which is created is read
through the CACHE, and is automatically cleaned up at the end of the user's
session, if it is not explicitly freed sooner. This function returns OCI_SUCCESS
if it completes successfully or OCI_ERROR if it fails. */

sb4 select_and_createtemp (OCILobLocator *lob_loc,
                           OCIError      *errhp,
                           OCISvcCtx     *svchp,
                           OCIStmt       *stmthp,
                           OCIEnv        *envhp)
{
  OCIDefine     *defnp1;
  OCIBind       *bndhp;
  text          *sqlstmt;
  int rowind =1;
  ub4 loblen = 0;
  OCILobLocator *tblob;
  printf ("in select_and_createtemp \n");
    if(OCIDescriptorAlloc((void*)envhp, (void **)&tblob,
                          (ub4)OCI_DTYPE_LOB, (size_t)0, (void**)0))
  {
    printf("failed in OCIDescriptor Alloc in select_and_createtemp \n");
    return OCI_ERROR;
 }
  /* arbitrarily select where Clip_ID =1 */
  sqlstmt=(text *)"SELECT Frame FROM print_media WHERE product_ID = 1 FOR UPDATE";
  if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt),
                     (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT))
  {
      (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n");
      return OCI_ERROR;
  }
  /* Define for BLOB */
  if (OCIDefineByPos(stmthp, &defnp1, errhp, (ub4)1, (void *) &lob_loc, (sb4)0,
             (ub2) SQLT_BLOB, (void *)0, (ub2 *)0, (ub2 *)0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: Select locator: OCIDefineByPos()\n");
    return OCI_ERROR;
  }
  /* Execute the select and fetch one row */
  if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                   (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT))
  {
    (void) printf("FAILED: OCIStmtExecute() sqlstmt\n");
    return OCI_ERROR;
  }
  if(OCILobCreateTemporary(svchp, errhp, tblob, (ub2)0, SQLCS_IMPLICIT,
                         OCI_TEMP_BLOB, OCI_ATTR_NOCACHE, OCI_DURATION_SESSION))
  {
    (void) printf("FAILED: CreateTemporary() \n");
    return OCI_ERROR;
  }
  if (OCILobGetLength(svchp, errhp, lob_loc, &loblen) != OCI_SUCCESS)
  {
    printf("OCILobGetLength FAILED\n");
    return OCI_ERROR;
  }
  if (OCILobCopy(svchp, errhp, tblob,lob_loc,(ub4)loblen, (ub4) 1, (ub4) 1))
  {
    printf( "OCILobCopy FAILED \n");
  }
  if(OCILobFreeTemporary(svchp,errhp,tblob))
  {
    printf ("FAILED: OCILobFreeTemporary call \n");
    return OCI_ERROR;
  }
     return OCI_SUCCESS;
}
int main(char *argv, int argc)
{
  /* OCI Handles */
  OCIEnv        *envhp;
  OCIServer     *srvhp;
  OCISvcCtx     *svchp;
  OCIError      *errhp;
  OCISession    *authp;
  OCIStmt       *stmthp;
  OCILobLocator *clob, *blob;
  OCILobLocator *lob_loc;
  int type =1;
  /* Initialize and Logon */
  OCIEnvCreate(&envhp, OCI_DEFAULT, (void *)0, 0, 0, 0,
        (size_t)0, (void *)0);
  (void) OCIHandleAlloc( (void *) envhp, (void **) &errhp, OCI_HTYPE_ERROR,
                          (size_t) 0, (void **) 0);
  /* server contexts */
  (void) OCIHandleAlloc( (void *) envhp, (void **) &srvhp, OCI_HTYPE_SERVER,
                         (size_t) 0, (void **) 0);
   /* service context */
  (void) OCIHandleAlloc( (void *) envhp, (void **) &svchp, OCI_HTYPE_SVCCTX,
                         (size_t) 0, (void **) 0);
    /* attach to Oracle */
  (void) OCIServerAttach( srvhp, errhp, (text *)"", strlen(""), 0);
    /* set attribute server context in the service context */
  (void) OCIAttrSet ((void *) svchp, OCI_HTYPE_SVCCTX,
                     (void *)srvhp, (ub4) 0,
                     OCI_ATTR_SERVER, (OCIError *) errhp);
   (void) OCIHandleAlloc((void *) envhp,
                        (void **)&authp, (ub4) OCI_HTYPE_SESSION,
                        (size_t) 0, (void **) 0);
   (void) OCIAttrSet((void *) authp, (ub4) OCI_HTYPE_SESSION,
                    (void *) "scott", (ub4)5,
                    (ub4) OCI_ATTR_USERNAME, errhp);
  (void) OCIAttrSet((void *) authp, (ub4) OCI_HTYPE_SESSION,
                    (void *) "password", (ub4) 5,
                    (ub4) OCI_ATTR_PASSWORD, errhp);
  /* Begin a User Session */
  checkerr(errhp, OCISessionBegin ( svchp,  errhp, authp, OCI_CRED_RDBMS,
                                    (ub4) OCI_DEFAULT));
  (void) OCIAttrSet((void *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                    (void *) authp, (ub4) 0,
                    (ub4) OCI_ATTR_SESSION, errhp);
  /* ------- Done loggin in ----------------------------------*/
   /* allocate a statement handle */
  checkerr(errhp, OCIHandleAlloc( (void *) envhp, (void **) &stmthp,
                             OCI_HTYPE_STMT, (size_t) 0, (void **) 0));
  checkerr(errhp, OCIDescriptorAlloc((void *)envhp, (void **)&lob_loc,
                   (ub4) OCI_DTYPE_LOB, (size_t) 0, (void **) 0));
  /* Subroutine calls begin here */
  printf("calling select_and_createtemp\n");
  select_and_createtemp (lob_loc, errhp, svchp,stmthp,envhp);
  return 0;
}
void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;
  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((void *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}

Prefetching of LOB Data, Length and Chunk Size

To improve OCI access of smaller LOBs, LOB data can be prefetched and cached while also fetching the locator. This applies to internal LOBs, temporary LOBs, and BFILEs. Here are the steps that the application can take:

  1. Set the OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE attribute for the session handle. The value of this attribute will indicate the default prefetch data size for a LOB locator. This enables you to enable prefetching for all the LOB locators fetched in the session. Default value for this attribute is zero (no prefetch of LOB data). This option relieves the application developer from setting the prefetch LOB size for each define handle. You can set either this attribute or set (in step 3) OCI_ATTR_LOBPREFETCH_SIZE.

  2. Perform the prepare and define steps for the statement to be executed.

  3. You can override the default prefetch size, if required, for the LOB locators to be fetched, by setting OCI_ATTR_LOBPREFETCH_SIZE attribute for the define handle. This optional attribute provides control of the prefetch size for the locators fetched from a particular column.

  4. Set OCI_ATTR_LOBPREFETCH_LENGTH attribute to the prefetch LOB length and chunk size.

  5. Execute the statement.

  6. Call OCILobRead2() or OCILobArrayRead() with individual LOB locator(s); OCI will take the data from the prefetch buffer, do the necessary character conversion, copy the data into the LOB read buffer (no change in LOB semantic). If the data asked for is bigger than the prefetch buffer then it would require additional round trips.

  7. Call OCILobGetLength2() and OCILobGetChunkSize() to obtain the length and chunk size without making round trips to the server.

Note that the prefetch size is in number of bytes for BLOBs and BFILEs and in number of characters for CLOBs.

Here is a code fragment illustrating these steps:

...
ub4 default_lobprefetch_size = 2000;                  /* Set default size to 2K */
... 
/* set lob prefetch attribute to session */
OCIAttrSet (sesshp, (ub4) OCI_HTYPE_SESSION,
            (void *)&default_lobprefetch_size,               /* attribute value */
            0,                      /* attribute size; not required to specify; */
            (ub4) OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE,
            errhp);
...
/* select statement */
char *stmt = "SELECT lob1 FROM lob_table";
...
/* declare and allocate LOB locator */
OCILobLocator * lob_locator;
lob_locator = OCIDescriptorAlloc(..., OCI_DTYPE_LOB, ...);
 
OCIDefineByPos(..., 1, (void *) &lob_locator, ..., SQLT_CLOB, ...);
...
/* Override the default prefetch size to 4K */
ub4 prefetch_size = 4000;
OCIAttrSet (defhp,  OCI_HTYPE_DEFINE,
            (void *) &prefetch_size                             /* attr value */,
            0                  /* restricting prefetch size to be ub4 max val */,
            OCI_ATTR_LOBPREFETCH_SIZE                            /* attr type */,
            errhp);
...
/* Set prefetch length attribute */
boolean prefetch_length = TRUE;
OCIAttrSet( defhp,  OCI_HTYPE_DEFINE,
            (dvoid *) &prefetch_length  /* attr value */,
            0,
            OCI_ATTR_LOBPREFETCH_LENGTH  /* attr type */,
            errhp );
...
/* execute the statement. 4K of data for the LOB is read and
 * cached in descriptor cache buffer.
 */
OCIStmtExecute (svchp, stmthp, errhp,
                1,               /* iters */
                0,               /* row offset */
                NULL,            /* snapshot IN */
                NULL,            /* snapshot out */
                OCI_DEFAULT);    /* mode */
...
oraub8 char_amtp = 4000;
oraub8 lob_len;
ub4 chunk_size;
 
/* LOB chunk size, length, and data are read from cache. No round trip. */
 
OCILobGetChunkSize (svchp, errhp, lob_locator, &chunk_size);
 
OCILobGetLength2(svchp, errhp, lob_locator, &lob_len );
 
OCILobRead2(svchp, errhp, lob_locator, NULL, &char_amtp, ...);
...

Prefetch cache allocation: The prefetch cache buffer for a descriptor will be allocated while fetching a LOB locator. The allocated buffer size will be determined by the OCI_ATTR_LOBPREFETCH_SIZE attribute for the define handle; the default value of this attribute is indicated by the OCI_ATTR_DEFAULT_LOBPREFETCH_SIZE attribute value of the session handle. If the cache buffer is already allocated, then it will be resized if required.

For the two following LOB APIs, if the source locator has cached data, then the destination locator cache is allocated or resized and cached data will be copied from source to destination.

  • OCILobAssign()

  • OCILobLocatorAssign()

Once allocated, the cache buffer memory for a descriptor is released when the descriptor itself is freed.

Prefetch cache invalidation: The cache for a descriptor gets invalidated when LOB data is updated using the locator. This means that the cache is no longer used for reading data and the next OCILobRead() call on the locator will make a round trip

The following LOB APIs will invalidate the prefetch cache for the descriptor used:

  • OCILobErase()

  • OCILobErase2()

  • OCILobTrim()

  • OCILobTrim2()

  • OCILobWrite()

  • OCILobWrite2()

  • OCILobWriteAppend()

  • OCILobWriteAppend2()

  • OCILobArrayWrite()

The following LOB APIs will invalidate the cache for the destination LOB locator:

  • OCILobAppend()

  • OCILobCopy()

  • OCILobCopy2()

  • OCILobLoadFromFile()

  • OCILobLoadFromFile2()

Performance Tuning: The prefetch buffer size needs to be decided upon based on average LOB size and client side memory. If a large amount of data is prefetched you need to ensure the memory availability. Performance gain may not be significant for prefetching large LOBs since the cost of fetching data is much higher compared to the cost of a round trip to server.

You need to have a fair idea of the LOB data size to be able to make best use of this LOB prefetch feature. The parameters are part of application design. So, the application will have to be rebuilt if any parameter value needs to be modified.

Upgrading: LOB prefetching cannot be used against a pre-11.1 release server or in a pre-11.1 client against an 11.1 or later server. When using a pre-11.1 server with an 11.1 or later client, OCISetAttr() will return an error or an error-with-information saying that "server does not support this functionality."

Options of SecureFile LOBs

For Oracle SecureFiles (LOBs with the STORE AS SECUREFILE option, which were introduced in Oracle Database 11g Release 1) you can specify the SQL parameter DEDUPLICATE in CREATE TABLE and ALTER TABLE statements. This enables you to specify that LOB data that are identical in two or more rows in a LOB column will all share the same data blocks, thus saving disk space. KEEP_DUPLICATES turns off this capability. The following options are also used with SECUREFILE:

The parameter COMPRESS turns on LOB compression. NOCOMPRESS turns LOB compression off.

The parameter ENCRYPT turns on LOB encryption and optionally selects an encryption algorithm. NOENCRYPT turns off LOB encryption. Each LOB column can have its own encryption specification, independent of the encryption of other LOB or non-LOB columns. Valid algorithms are 3DES168, AES128, AES192, and AES256.

The pre-release 11.1 LOBs paradigm is the default. It is also now explicitly set by the option STORE AS BASICFILE.

The following SQL statements, PL/SQL packages, and OCI functions are used with the SECUREFILE features:

See Also: