Oracle Call Interface Programmer's Guide
Release 8.1.5

A67846-01

Library

Product

Contents

Index

Prev Next

7
LOB and FILE Operations

The following topics are covered in this chapter:

Overview

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 Oracle8i Supplied Packages Reference. For general information about LOBs and the LOB interfaces available, see the Oracle8i Application Developer's Guide - Large Objects (LOBs).

For information about temporary LOBs, refer to "Temporary LOB Support".

Locators

An OCI program uses locators to point to the data of a LOB or FILE.

LOB Locators

A database table stores a LOB locator which points to the LOB data. When an OCI application issues a SQL query that includes a LOB column in its select-list, fetching the result(s) of the query returns the locator, rather than the actual LOB value. In the OCI, the LOB locator maps to the datatype OCILobLocator.

Note: The LOB value can be stored inline in a database table if it is less than approximately 4,000 bytes.

Internal LOBs have copy semantics. Thus, if a LOB in one row is copied to a LOB in another row, the actual LOB value is copied, and a new LOB locator is created for the copied LOB.

The OCI functions for LOBs take LOB locators as their arguments. The OCI functions assume that the LOB to which the locator points has already been created, whether or not the LOB contains some value.

An application first fetches the locator using SQL, and then performs further operations using the locator. The OCI functions never take the actual LOB value as a parameter. It is good practice to use a locator in a LOB modification call if and only if its snapshot is recent enough that it sees the current value of the LOB data, since it is the current value that gets modified.

You allocate memory for an internal LOB locator with a call to OCIDescriptorAlloc() by passing OCI_DTYPE_LOB as the descriptor type. To allocate memory for an external LOB (FILE) locator, pass OCI_DTYPE_FILE. After you have allocated the LOB locator memory, you must initialize it before passing it to any OCI LOB routines. You can accomplish this by any of the following methods:

You can also initialize a LOB locator to empty by calling OCIAttrSet() on the locator's OCI_ATTR_LOBEMPTY attribute. A locator initialized in this way may only be used to create an empty LOB in the database. Thus, it can only be used in the VALUES clause of a SQL INSERT statement, or as the source of the SET clause of a SQL UPDATE statement.

Warning: Locators for LOB and FILE operations are not interchangeable. Locators for LOB operations must be allocated as type OCI_DTYPE_LOB, and locators for FILE operations must be allocated as type OCI_DTYPE_FILE. An internal LOB locator may not be assigned to an external LOB (FILE) locator, and vice versa.

See Also: For more information about locators, including the LOB locator, see the section "Descriptors and Locators". For examples showing the use of OCI LOB calls, refer the list of demonstration programs in Appendix B, "OCI Demonstration Programs" and the description of OCILobWrite().

For more information about LOBs, locators, and read-consistent LOBs, see the Oracle8i Application Developer's Guide - Large Objects (LOBs).

FILE Locators

A FILE locator may be considered to be a pointer to a file on the server's file system. Oracle does not provide any transactional semantics on FILEs, and Oracle currently supports only read-only operations on binary FILEs (BFILEs).

Since operations on both internal LOBs and FILEs are similar, all OCI LOB/FILE functions expect a LOB locator as an input to all operations. The only difference is in the way the FILE locator is allocated. When allocating a locator for FILEs, you must pass OCI_DTYPE_FILE as the descriptor type in the OCIDescriptorAlloc() call.

Warning: Locators for LOB and FILE operations are not interchangeable. Locators for LOB operations must be allocated as type OCI_DTYPE_LOB, and locators for FILE operations must be allocated as type OCI_DTYPE_FILE. An internal LOB locator may not be assigned to an external LOB (FILE) locator, and vice versa.

See Also: For information about associating a BFILE with an OS file, see the section "Associating a FILE in a Table with an OS File".

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

Note: For information about LOB reads and writes from within a trigger, see Oracle8i Application Developer's Guide - Large Objects (LOBs).

See Also: For information about binding LOB locators to placeholders, and using them in INSERT statements, refer to the section "Binding 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.

See Also: For more information, see OCILobFileSetName(). For more information about the BFILENAME() function, please refer to the Oracle8i 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:

  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.

See Also: For more information about FILEs, refer to the description of BFILEs in the Oracle8i 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()

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.

Note: For LOBs which store varying width characters, OCILobGetChunkSize() returns the number of Unicode (UCS-2) 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 roundtrips 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 Oracle8i 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 (e.g., updating indices, etc.) can be performed when a LOB is closed.

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

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

Restrictions

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 DML statements that start a transaction (including SELECT...FOR UPDATE) 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.

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

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 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,
                                     (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;
  }
}





Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index