7
LOB and FILE Operations

The following topics are covered in this chapter:

Using OCI Functions for LOBs

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

See Also:

For code samples showing the use of LOB operations, see the demonstration programs included with your Oracle installation. For additional information, refer to Appendix B, "OCI Demonstration Programs".
Customers who are interested in using the dbms_lob package to work with LOBs should refer to Oracle9i Supplied PL/SQL Packages and Types Reference.
For general information about LOBs and the LOB interfaces available, see the Oracle9i Application Developer's Guide - Large Objects (LOBs).
For information about temporary LOBs, refer to "Temporary LOB Support".

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.

Note:

Whenever you want 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.

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 lock the row again (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 the second document listed next:

See Also:

For information about binding LOB locators to placeholders, and using them in INSERT statements, refer to the section "Binding LOBs"
For information about LOB reads and writes from within a trigger, see Oracle9i Application Developer's Guide - Large Objects (LOBs)

Associating a FILE in a Table with an Operating System 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.

See Also:

For more information, see OCILobFileSetName(). For more information about the BFILENAME() function, please refer to the Oracle9i Application Developer's Guide - Large Objects (LOBs).

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:

Call OCIObjectNew() to create a persistent object with a LOB attribute.
Mark the object as dirty.
Flush the object, thereby inserting a row into the table
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
Call OCILobWrite() using the LOB locator in the object to write the data.

See Also:
For more information about object operations, such as marking, flushing, and refreshing, refer to Chapter 10, "OCI Object-Relational Programming" and the following chapters

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 (that is, 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.

See Also:

More detailed information about each function is found in "LOB 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. When the client-side character set is 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/NCLOBs and in bytes for BLOBs/BFILEs.

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

See Also:

For more information about FILEs, refer to the description of BFILEs in the Oracle9i Application Developer's Guide - Large Objects (LOBs)

Table 7-1 OCI LOB and FILE Functions

Function	Restrictions	Purpose
`OCILobAppend()`	Internal LOBs only	Appends data from one internal LOB onto another internal LOB. The source and the destination LOBs must already exist. The destination LOB is extended to accommodate the newly written data if it extends beyond the current length of the destination LOB. It is an error to extend the destination LOB beyond the maximum length allowed (4 gigabytes) or to try to append from a NULL LOB.
`OCILobAssign()`		Assigns one LOB/FILE locator to another. This function cannot be used for temporary LOBs; use `OCILobLocatorAssign()`.
`OCILobCharSetForm()`		Gets the character set form of a CLOB/NCLOB.
`OCILobCharSetId()`		Gets the character set ID of a CLOB/NCLOB.
`OCILobClose()`		Closes an opened LOB or BFILE.
`OCILobCopy()`	Internal LOBs only	This function copies a portion of an internal LOB into another internal LOB. The source and destination LOBs must already exist. If data already exists at the destination's start position, it is overwritten with the source data. If the destination's start position is beyond the end of the current value, zero-byte fillers (BLOBs) or spaces (CLOBs/NCLOBs) are placed in the LOB from the end of the destination value to the beginning of the newly written data from the source. The destination LOB is extended to accommodate the newly written data if it extends beyond the current length of the destination LOB. It is an error to extend the destination LOB beyond the maximum length allowed (4 gigabytes). LOB copy operations must be performed on LOBS of the same type; i.e., one CLOB can be copied to another CLOB, and one BLOB can be copied to another BLOB, but a CLOB cannot be copied to a BLOB, and vice versa.
`OCILobCreateTemporary()`		Creates a temporary LOB.
`OCILobDisableBuffering()`	Internal LOBs only	Disables LOB buffering for a given internal locator.
`OCILobEnableBuffering()`	Internal LOBs only	Enables LOB buffering for a given internal locator.
`OCILobErase()`	Internal LOBs only	Erases a specified portion of the internal LOB value starting at a specified offset. The actual number of characters/bytes erased is returned. The actual number of characters/bytes and the requested number of characters/bytes will differ if the end of the LOB data is reached before erasing the requested number of characters/bytes. If the LOB is NULL, this routine shows that 0 characters/bytes were erased.
`OCILobFileClose()`, `OCILobFileCloseAll()`		Closes a previously opened FILE, or all open FILEs. It is an error if this function is called for an internal LOB. No error is returned if the FILE exists but is not opened.
`OCILobFileExists()`		Tests to see if a FILE exists on the server.
`OCILobFileGetName()`		Gets the name and the directory alias of a FILE.
`OCILobFileIsOpen()`		Tests to see if a FILE has been opened with the input locator.
`OCILobFileOpen()`		Opens a FILE. The FILE can be opened for read-only access. It is an error if this call is made on an internal LOB.
`OCILobFileSetName()`		Sets the name and the directory alias of a FILE.
`OCILobFlushBuffer()`	Internal LOBs only	Flushes the LOB buffer.
`OCILobFreeTemporary()`		Frees the temporary LOB value.
`OCILobGetChunkSize()`		Gets the usable LOB chunk size.
`OCILobGetLength()`		This function gets the length of a LOB/FILE. If the LOB/FILE is NULL, the length is undefined. Empty internal LOBs have a length of zero. Regardless of whether the client-side character set is varying-width, the output length is in characters for CLOBs/NCLOBs and in bytes for BLOBs/BFILEs.
`OCILobIsEqual()`		Tests to see if two LOB/FILE locators are equal. Two locators are equal if and only if they both refer to the same LOB/FILE value.
`OCILobIsOpen()`		Tests whether the LOB is open.
`OCILobIsTemporary()`		Tests whether it is a temporary LOB.
`OCILobLoadFromFile()`		Populates all or part of a LOB with data from a FILE.
`OCILobLocatorAssign()`		Assigns a LOB/FILE locator to another LOB/FILE locator.
`OCILobLocatorIsInit()`		Tests to see if a LOB/FILE locator is initialized.
`OCILobOpen()`		Opens a LOB or BFILE.
OCILobRead()		This function reads a portion of the LOB/FILE value into a buffer. It is an error to try to read from a NULL LOB/FILE. If the client-side character set is varying-width, then for CLOBs and NCLOBs, the input amount is in characters and the output amount is in bytes. The input amount refers to the number of characters to read from the server-side CLOB/NCLOB. The output amount indicates how many bytes were read into the buffer bufp. When using polling mode, note the value of the amtp parameter after each `OCILobRead()` call to see how many bytes were read into the buffer because the buffer may not be entirely full. When using callbacks, the len parameter, which is input to the callback, indicates how many bytes are filled in the buffer. Be sure to check the len parameter during the callback processing because the entire buffer may not be filled with data.
OCILobTrim()	Internal LOBs only	This function truncates a LOB, trimming the LOB value to a specified smaller length.
OCILobWrite()	Internal LOBs only	This function writes data from a buffer into an internal LOB. If data already exists in the LOB, it is overwritten with the data stored in the buffer. If the client-side character set is varying-width, then for CLOBs and NCLOBs, the input amount is in bytes and the output amount is in characters. The input amount refers to the number of bytes of data that should be written to the LOB. The output amount refers to the number of characters written into the server-side CLOB/NCLOB.
OCILobWriteAppend()		Writes data starting at the end of the LOB.

Functions for Improving LOB Read/Write Performance

Using OCILobGetChunkSize()

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

Note:

For LOBs which store varying width characters, OCILobGetChunkSize() returns the number of Unicode characters that fit in a LOB chunk.

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 round-trips and LOB versions, thereby improving LOB performance significantly for small reads and writes.

See Also:

For more information on LOB buffering, refer to the chapter on LOBs in the Oracle9i Application Developer's Guide - Large Objects (LOBs)
For a code sample showing the use of LOB buffering, see the demonstration programs included with your Oracle installation. Refer to Appendix B, "OCI Demonstration Programs".

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 (that is, updating indexes, etc.) can be performed when a LOB is closed.

Note:

For internal LOBs, the concept of openness is associated with a LOB and not its locator. The locator does not store any information about whether the LOB to which it refers is open. It is possible for more than one locator to point to the same open LOB. However, for BFILES, openness 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.

Note:

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 and external LOBs (BFILEs).

It is an error to 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 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, please rebuild your functional and domain indexes on the LOB column.

Restrictions

The LOB opening and closing mechanism has the following restrictions:

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.
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 Oracle9i Database Reference. Note that assigning an already opened locator to another locator does not count as opening a new LOB.
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.

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

LOB Open/Close Example

See Also:

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 Round-trips for LOB Functions

See Also:

For a table showing the number of server round-trips required for individual OCI LOB functions, refer to Appendix C, "OCI Function Server Round-trips"

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.

Note:

The LOB read/write streaming callbacks provides a fast method for reading/writing large amounts of LOB data.

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 you and registered with OCI through the OCILobRead() and OCILobWrite() calls. The callback functions are called by OCI whenever required.

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 bufl, 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 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 by you.

If the buffer length provided by you 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 you 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");
      report_error();
    }
...
sb4 cbk_read_lob(ctxp, bufxp, len, piece)
dvoid *ctxp;
CONST dvoid *bufxp;
ub4 len;
ub1 piece;
{
static ub4 piece_count = 0;
piece_count++;
switch (piece)
{
  case OCI_LAST_PIECE:
    /* process buffer bufxp */
    --- 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:
  case OCI_NEXT_PIECE:
    /* process buffer bufxp */
    --- buffer processing code goes here ---
    (void) printf("callback read the %d th piece\n", piece_count);
    break;
  default:
    (void) printf("callback read error: unkown piece = %d.\n", piece);
  return OCI_ERROR;
  }
  return OCI_CONTINUE;
}

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

See Also:

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 *bufl, 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 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 OCILobWrite() if there is more to write, then the user-defined callback is called. In the callback you should provide the data to insert in the storage indicated by bufp and also specify the length in bufl. You should also indicate whether it is the next (OCI_NEXT_PIECE) or the last (OCI_LAST_PIECE) piece using the piecep parameter. Note that 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 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");
    report_error();
    return;
  }
 ...
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;
  else
    *piecep = OCI_NEXT_PIECE;;
  return OCI_CONTINUE;
}

In the above example, the user-defined function cbk_write_lob() is repeatedly called until you indicate that the application is providing the last piece using the piecep parameter.

See Also:

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

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 nullness 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. 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, you 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:

call (OCI_DURATION_CALL), only on the server side
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 the OCIDurationBegin() function and the end of the duration using the OCIDurationEnd() function.

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

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. For more information, see OCIDurationBegin().

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 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,
                                     (dvoid**)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 
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,
                       (dvoid *) &lob_loc,
                       (sb4)0, 
                       (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;
  }
  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 */
  (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)
  {
  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((dvoid *)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;
  }
}

7 LOB and FILE Operations

Using OCI Functions for LOBs

Creating and Modifying Internal LOBs

Associating a FILE in a Table with an Operating System File

LOB Attributes of an Object

Writing to a LOB Attribute of an Object

Transient Objects with LOB Attributes

Array Interface For LOBs

LOB and FILE Functions

Table 7-1 OCI LOB and FILE Functions

Functions for Improving LOB Read/Write Performance

Using OCILobGetChunkSize()

Using OCILobWriteAppend()

LOB Buffering Functions

Functions for Opening and Closing LOBs

Restrictions

LOB Open/Close Example

Server Round-trips for LOB Functions

LOB Read and Write Callbacks

The Callback Interface for Streaming

Reading LOBs using Callbacks

Writing LOBs using Callbacks

Temporary LOB Support

Creating and Freeing Temporary LOBs

Temporary LOB Durations

Temporary LOB Example

7
LOB and FILE Operations