Oracle Call Interface Programmer's Guide
Release 8.1.6

Part Number A76975-01





Go to previous page Go to next page

LOB and FILE Operations

The following topics are covered in this chapter:


The Oracle OCI includes a set of functions for performing operations on large objects (LOBs) in a database. Internal 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. External LOBs (FILEs) 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.

The maximum length of a LOB/FILE is 4 gigabytes. FILE functionality is read-only. Oracle currently supports only binary files (BFILEs).

Creating and Modifying Internal LOBs

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 then write to it using one of the OCI LOB functions.

For any LOB write command to be successful, a transaction must be open. This means that if you commit a transaction before writing the data, then you must relock the row (by reissuing the SELECT...FOR UPDATE, for example), because the commit closes the transaction.

For information on creating internal LOBs using EMPTY_BLOB() and EMPTY_CLOB() instead of OCIAttrSet(), see Oracle8i Application Developer's Guide - Large Objects (LOBs).

Associating a FILE in a Table with an OS File

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

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:

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

For more information about object operations, such as marking, flushing, and refreshing, refer to Chapter 10, "OCI Object-Relational Programming".

Transient Objects with LOB Attributes

An application can call OCIObjectNew() and create a transient object with an internal LOB (BLOB, CLOB, NCLOB) attribute. However, the user cannot perform any operations (e.g., read or write) on the LOB attribute because transient LOBs are not currently 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 FILE attribute and use the FILE attribute to read data from the file stored in the server's file system. The application can also call OCIObjectNew() to create a transient FILE and use that FILE to read from the server's file.

Array Interface For LOBs

It is possible to use the OCI's array interface with LOBs, just as with any other datatype. Note, however, that you must do the following to allocate the descriptors:

/* First create an array of OCILocator pointers: */
OCILobLocator *lobp[10];

for (i=0; i < 10; i++)
{ OCIDescriptorAlloc (...,&lobp[i],...);

/* Then bind the descriptor as follows */
  OCIBindByPos(.......&lobp[i], .....);

LOB and FILE Functions

The functions in Table 7-1 are available to operate on LOBs and FILEs. More detailed information about each function is found in Chapter 15, "OCI Relational Functions". These LOB/FILE calls are not valid when an application is connected to an Oracle release 7 server.

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. The following general rules apply to the amount and offset parameters in LOB calls:

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

Functions for Improving LOB Read/Write Performance

Using OCILobGetChunkSize()

Users can take advantage of the OCILobGetChunkSize() call to improve the performance of LOB read and write operations. OCILobGetChunkSize() returns the usable chunk size in bytes for BLOBs and in characters for CLOBs and NCLOBs. When a read or write is done using data whose size is a multiple of the usable chunk size and starts on a chunk boundary, performance improves. A user can specify the chunk size for a LOB column when creating a table that contains the LOB.

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

To read through the end of a LOB, call OCILobRead() with an amount of 4 gigabytes. This avoids the round-trip involved with first calling OCILobGetLength() because OCILobRead() with an amount of 4 gigabytes reads until the end of the LOB is reached.

Using OCILobWriteAppend()

The OCI provides a shortcut to make it more efficient to write data to the end of a LOB. The OCILobWriteAppend() enables an application to append data to the end of a LOB without first requiring a call to OCILobGetLength() to determine the starting point for an OCILobWrite() operation. OCILobWriteAppend() takes care of both steps.

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 of LOBs in client-side buffers. This reduces the number of network roundtrips and LOB versions, thereby improving LOB performance significantly for small reads and writes.

Functions for Opening and Closing LOBs

The OCI provides functions to explicitly open (OCILobOpen()) and close (OCILobClose()) a LOB, and also to test whether a particular LOB is already open (OCILobIsOpen()). These functions allow an application to mark the beginning and end of a series of LOB operations so that specific processing (e.g., updating indices, etc.) can be performed when a LOB is closed.

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.


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 (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 Oracle8i Reference. Note that 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 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


    • within an autonomous transaction block

    A LOB opened when there is no transaction must be closed before the end of session. If there are LOBs open at the end of session, the openness will be discarded and no triggers of extensible indexes are fired.

LOB Open/Close Examples

For examples of the use of the OCILobOpen() and OCILobCLose() calls, see the list of online demonstration programs in Appendix B, "OCI Demonstration Programs".

Server Roundtrips for LOB Functions

For a table showing the number of server roundtrips required for individual OCI LOB functions, refer to Appendix C, "OCI Function Server Roundtrips".

LOB Read and Write Callbacks

The OCI LOB read and write functions provide the ability to define callback functions which can be used to provide data to be written or handle data that was read. This allows the client application to perform optional processing on the data. One example usage of this would be to use the callbacks to implement a compression algorithm for writing the data and a decompression algorithm for reading it.

The following sections describe the use of callbacks in more detail.

The Callback Interface for Streaming

Your application can use user-defined read and write callback functions to insert data into or retrieve data from a LOB. This provides an alternative to the polling method for streaming data into a LOB and retrieving data from a LOB. The user-defined callbacks have a specific prototype which is described below. These functions are implemented by the user and registered with OCI through the OCILobRead() and OCILobWrite() calls. The callback functions are called by OCI whenever required.

Figure 7-1 User-defined Callback

Reading LOBs using Callbacks

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

CallbackFunctionName ( dvoid *ctxp, CONST dvoid *bufp, ub4 len, ub1 piece)

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 the user in ctxp is passed back to the user (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 the user how much data has been read into the buffer provided by the user.

If the buffer length provided by the user in the original OCILobRead() 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 to the user whether the information returned in the buffer in the first, next or last piece.

The following is a code fragment of a typical way to implement read callback functions. Assume here that lobl is a valid locator that has been previously selected, svchp is a valid service handle and errhp is a valid error handle.

ub4   offset = 1;
ub4   loblen = 0;
ub1   bufp[MAXBUFLEN];
ub4   amtp = 0;
sword retval;
amtp = 4294967295;             /* 4 gigabytes minus 1 */
if (retval = OCILobRead(svchp, errhp, lobl, &amtp, offset, (dvoid *) bufp,
         (ub4) MAXBUFLEN, (dvoid *) bufp, cbk_read_lob,
        (ub2) 0, (ub1) SQLCS_IMPLICIT))
      (void) printf("ERROR: OCILobRead() LOB.\n");
sb4 cbk_read_lob(ctxp, bufxp, len, piece)
dvoid *ctxp;
CONST dvoid *bufxp;
ub4 len;
ub1 piece;
static ub4 piece_count = 0;
switch (piece)
    /* process buffer bufxp */
    --- buffer processing code goes here ---
    (void) printf("callback read the %d th piece\n\n", piece_count);
    piece_count = 0;
    /* process buffer bufxp */
    --- buffer processing code goes here ---
    (void) printf("callback read the %d th piece\n", piece_count);
    (void) printf("callback read error: unkown piece = %d.\n", piece);
  return OCI_ERROR;
  return OCI_CONTINUE;

In the above example the user defined function cbk_read_lob() is repeatedly called until all the LOB data has been read by the user.

For an example of the use of OCILobRead() using polling and callbacks, see the list of online demonstration programs in Appendix B, "OCI Demonstration Programs".

Writing LOBs using Callbacks

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

 CallbackFunctionName ( dvoid *ctxp, dvoid *bufp, ub4 *lenp, ub1 *piecep)

The first parameter, ctxp, is the context of the callback that is passed to OCI in the OCILobWrite() function call. The information provided by the user in ctxp, is passed back to the user 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 that contains the LOB data to be inserted, and bufl is the length of this storage area. The user provides this pointer in the call to OCILobWrite(). After inserting the data provided in the call to OCILobWrite() if there is more to write, then the user defined callback is called. In the callback the user should provide the data to insert in the storage indicated by bufp and also specify the length in bufl. The user should also indicate whether it is the next (OCI_NEXT_PIECE) or the last (OCI_LAST_PIECE) piece using the piecep parameter. Note that the user is 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 following is a code fragment of a typical way to implement write callback functions.

Assume here 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

ub4   offset = 1;
ub1   bufp[MAXBUFLEN];
ub4   amtp = MAXBUFLEN * 20;
ub4   nbytes = MAXBUFLEN;
/* Fill bufp with some data */
-- code to fill bufp with data goes here. nbytes should reflect the size and 
should be less than or equal to MAXBUFLEN -- 
if (retval = OCILobWrite(svchp, errhp, lobl, &amtp, offset, (dvoid*) 
        bufp,(ub4)nbytes, OCI_FIRST_PIECE, (dvoid *)0, cbk_write_lob, 
        (ub2) 0, (ub1) SQLCS_IMPLICIT))
    (void) printf("ERROR: OCILobWrite().\n");
sb4 cbk_write_lob(ctxp, bufxp, lenp, piecep)
dvoid *ctxp;
dvoid *bufxp;
ub4 *lenp;
ub1 *piecep;
  /* Fill bufxp with  data */
  -- code to fill bufxp with data goes here. *lenp should reflect the size 
  and should be less than or equal to MAXBUFLEN -- 
  if (this is the last data buffer)
    *piecep = OCI_LAST_PIECE;
    *piecep = OCI_NEXT_PIECE;;
  return OCI_CONTINUE;

In the above example, the user defined function cbk_write_lob is repeatedly called until the user indicates that the application is providing the last piece using the piecep parameter.

For an example of the use of OCILobWrite() using polling and callbacks, see the list of online demonstration programs in Appendix B, "OCI Demonstration Programs".

Temporary LOB Support

The OCI provides functions for creating and freeing temporary LOBs, OCILobCreateTemporary() and OCILobFreeTemporary(), plus a function for querying whether or not a given LOB is a temporary LOB, OCILobIsTemporary().

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

As with standard 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:

Creating and Freeing Temporary LOBs

A user creates 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. At the end of the duration all temporary LOBs are deleted. 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, users can also specify whether or not the temporary LOB is read into the server's buffer cache.

To make a temporary LOB permanent, the application can use OCILobCopy() to copy the data from the temporary LOB into a permanent one. The application can also use the temporary LOB in the VALUES clause of an INSERT statement, use the temporary LOB as the source of the assignment in an UPDATE statement, or assign the temporary LOB to a persistent LOB attribute and the flush the object.

Temporary LOBs can be modified with 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 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 user 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 the OCIDurationBegin() function and the end of the duration using the OCIDurationEnd() function.

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

At the end of a duration, all temporary LOBs associated with that duration are freed. However, 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 which, in turn, have their own child durations.

Temporary LOB Example

The following code example shows how temporary LOBs might 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 Multimedia_tab 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((dvoid*)envhp, (dvoid **)&tblob,
                                     (ub4)OCI_DTYPE_LOB, (size_t)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 Multimedia_tab WHERE Clip_ID = 1 FOR 
  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, 
                       (ub4) 1,
                       (dvoid *) &lob_loc,
                       (ub2) SQLT_BLOB,
                       (dvoid *) 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;
 (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");
    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 */
  (void) OCIInitialize((ub4) OCI_DEFAULT, (dvoid *)0,
                       (dvoid * (*)(dvoid *, size_t)) 0,
                       (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                       (void (*)(dvoid *, dvoid *)) 0 );
  (void) OCIEnvInit( (OCIEnv **) &envhp, 
                     OCI_DEFAULT, (size_t) 0, 
                     (dvoid **) 0 );
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, 
                          (size_t) 0, (dvoid **) 0);
  /* server contexts */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                         (size_t) 0, (dvoid **) 0);
   /* service context */
  (void) OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                         (size_t) 0, (dvoid **) 0);
    /* attach to Oracle */
  (void) OCIServerAttach( srvhp, errhp, (text *)"", strlen(""), 0);
    /* set attribute server context in the service context */
  (void) OCIAttrSet ((dvoid *) svchp, OCI_HTYPE_SVCCTX, 
                     (dvoid *)srvhp, (ub4) 0,
                     OCI_ATTR_SERVER, (OCIError *) errhp);
   (void) OCIHandleAlloc((dvoid *) envhp, 
                        (dvoid **)&authp, (ub4) OCI_HTYPE_SESSION,
                        (size_t) 0, (dvoid **) 0);
   (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                    (dvoid *) "scott", (ub4)5,
                    (ub4) OCI_ATTR_USERNAME, errhp);
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                    (dvoid *) "tiger", (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((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                    (dvoid *) authp, (ub4) 0,
                    (ub4) OCI_ATTR_SESSION, errhp);
  /* ------- Done loggin in ----------------------------------*/
   /* allocate a statement handle */
  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
                                  OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));
  checkerr(errhp, OCIDescriptorAlloc((dvoid *)envhp, (dvoid **) &lob_loc, 
                                     (ub4) OCI_DTYPE_LOB, 
                                     (size_t) 0, (dvoid **) 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)
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    (void) printf("Error - OCI_NEED_DATA\n");
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    (void) printf("Error - OCI_CONTINUE\n");

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.