This chapter contains these topics:
The OCI includes a set of functions for performing operations on large objects (LOBs) in a database. Persistent LOBs (BLOBs
, CLOBs
, NCLOBs
) are stored in the database tablespaces in a way that optimizes space and provides efficient access. These LOBs have the full transactional support of the database server. BFILEs are large data objects stored in the server's operating system files outside the database tablespaces.
The OCI also provides support for temporary LOBs, which can be used like local variables for operating on LOB data.
BFILE
s are read-only. Oracle supports only binary BFILE
s.
See Also:
Appendix B, "OCI Demonstration Programs" for code samples showing the use of LOBs.
$ORACLE_HOME/rdbms/demo/lobs/oci/
for specific LOB code samples.
Oracle Database PL/SQL Packages and Types Reference for the DBMS_LOB
package
Oracle Database Application Developer's Guide - Large Objects.
LOB instances can be either persistent (stored in the database) or temporary (existing only in the scope of your application). Do not confuse the concept of a persistent LOB with a persistent object.
There are two ways of creating and modifying persistent LOBs:
Using the data interface
You can create a LOB by inserting character data into a CLOB
column or RAW
data into a BLOB column directly. You can also modify LOBs by using a SQL UPDATE
statement, binding character data into a CLOB
column or RAW
data into a BLOB
column.
Insert, update, and select of remote LOBs (over a dblink) is supported as long as neither the remote server or the local server is of a release less than Oracle Database 10g Release 2. The data interface only supports data size up to 2 GB -1, the maximum size of an sb4
.
See Also:
Oracle Database Application Developer's Guide - Large Objects chapter "Data Interface for Persistent LOBs", for more information and examples.Using the LOB locator
You create a new internal LOB by initializing a new LOB locator using OCIDescriptorAlloc()
, calling OCIAttrSet()
to set it to empty (using the OCI_ATTR_LOBEMPTY
attribute), and then binding the locator to a placeholder in an INSERT
statement. Doing so inserts the empty locator into a table with a LOB column or attribute. You can then SELECT
...FOR
UPDATE
this row to get the locator, and write to it using one of the OCI LOB functions.
Note:
To modify a LOB column or attribute (write, copy, trim, and so forth), you must lock the row containing the LOB. One way to do this is to use aSELECT...FOR UPDATE
statement to select the locator before performing the operation.For any LOB write command to be successful, a transaction must be open. If you commit a transaction before writing the data, you must lock the row again (by reissuing the SELECT...FOR UPDATE
, for example), because the commit closes the transaction.
The BFILENAME()
function can be used in an INSERT
statement to associate an external server-side (operating system) file with a BFILE
column or attribute in a table. Using BFILENAME()
in an UPDATE
statement associates the BFILE
column or attribute with a different operating system file. OCILobFileSetName()
can also be used to associate a BFILE
in a table with an operating system file. BFILENAME()
is usually used in an INSERT
or UPDATE
without bind variables and OCILobFileSetName()
is used for bind variables.
See Also:
Oracle Database Application Developer's Guide - Large Objects for more information about the BFILENAME()
function
An OCI application can use OCIObjectNew()
to create a persistent or transient object with a LOB attribute.
It is possible to use the OCI to create a new persistent object with a LOB attribute and write to that LOB attribute. The application would follow these steps when using a LOB locator:
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
Re-pin the latest version of the object (or refresh the object), thereby retrieving the object from the database and acquiring a valid locator for the LOB
Call OCILobWrite()
using the LOB locator in the object to write the data.
See Also:
Chapter 10, "OCI Object-Relational Programming" and the chapters that follow it, for more information about objectsThere is a second way of writing to a LOB attribute: when using the data interface, you can bind or define character data for a CLOB
attribute or RAW
data for a BLOB attribute.
See Also:
"Binding LOB Data" for usage and examples for both INSERT
and UPDATE statements
"Defining LOB Data" for usage and examples of SELECT
statements
An application can call OCIObjectNew()
and create a transient object with an internal LOB (BLOB
, CLOB
, NCLOB
) attribute. However, you cannot perform any operations, such as read or write, on the LOB attribute because transient objects with LOB attributes are not supported. Calling OCIObjectNew()
to create a transient internal LOB type will not fail, but the application cannot use any LOB operations with the transient LOB.
An application can, however, create a transient object with a BFILE
attribute and use the BFILE
attribute to read data from a file stored in the server's file system. The application can also call OCIObjectNew()
to create a transient BFILE
.
You can use the OCI array interface with LOBs, just as with any other datatype. There are two ways of using the array interface.
Using the data interface
You can bind or define arrays of character data for a CLOB
column or RAW
data for a BLOB
column. You can use array bind and define interfaces to insert and select multiple rows with LOBs in one round trip to the server.
See Also:
"Binding LOB Data" for usage and examples for both INSERT
and UPDATE statements
"Defining LOB Data" for usage and examples of SELECT
statements
Using the LOB locator
When using the LOB locator you must allocate the descriptors:
/* First create an array of OCILobLocator pointers: */ OCILobLocator *lobp[10]; for (i=0; i < 10; i++) { OCIDescriptorAlloc (...,&lobp[i],...); /* Then bind the descriptor as follows */ OCIBindByPos(... &lobp[i], ...); }
Starting with Oracle Database 10g Release 1 of OCI, functions were introduced to support LOBs of size greater than 4 GB. These new functions can also be used in new applications for LOBs of less than 4 GB.
Oracle Database lets you create tablespaces with blocksizes different from the database block size, and the maximum size of a LOB depends on the size of the tablespace blocks. CHUNK
is a parameter of LOB storage whose value is controlled by the block size of the tablespace in which the LOB is stored. When you create a LOB column, you can specify a value for CHUNK
, which is the number of bytes to be allocated for LOB manipulation. The value must be a multiple of the tablespace block size, or Oracle Database rounds up to the next multiple. (If the tablespace block size is the same as the database block size, then CHUNK
is also a multiple of the database block size.) The default CHUNK
size is one tablespace block, and the maximum value is 32K.
For example, suppose your database block size is 32K and you create a tablespace with a nonstandard block size of 8K. Further suppose that you create a table with a LOB column and specify a CHUNK
size of 16K (which is a multiple of the 8K tablespace block size). Then the maximum size of a LOB in this column is (4 gigabytes - 1) * 16K.
In this guide, 4 GB is defined as 4 gigabytes -1, or 4,294,967,295 bytes. The maximum size of a LOB, persistent or temporary, is (4 gigabytes - 1) * (CHUNK
). The maximum LOB size can range from 8 terabytes to 128 terabytes.
The maximum size of a BFILE
is the maximum file size allowed in the operating system, or UB8MAXVAL
, whichever is smaller.
Because the older LOB functions use ub4
as the datatypes of some parameters and ub4
datatype can only hold up to 4 GB. The newer functions use parameters of 8-byte length, oraub8
, which is a datatype defined in oratypes.h
. The datatypes oraub8
and orasb8
are mapped to appropriate 64-bit native datatypes depending on the compiler and operating system. Macros are used to not define oraub8
and orasb8
if compiling in 32-bit mode with strict ANSI option.
OCILobGetChunkSize()
returns the value, in bytes for BLOB
s, or in characters for CLOB
s, to be used in reading and writing LOBs. For varying-width character sets, the value is the number of Unicode characters that fit. The number of bytes stored in a chunk is actually less than the size of the CHUNK
parameter due to internal storage overhead. The function, OCILobGetStorageLimit()
, is provided to return the maximum size in bytes of internal LOBs in the current installation.
Note:
Oracle Database does not supportBFILE
s larger than 4 gigabytes in any programmatic environment. An additional file size limit imposed by your operating system also applies to BFILE
s.Eight functions with names that end in "2" and use the datatype oraub8
in place of the datatype ub4
were introduced in Oracle Database 10g Release 1. Other changes are made in the read and write functions (OCILobRead2()
, OCILobWrite2()
, and OCILobWriteAppend2()
) to solve several problems:
Problem: Before Oracle Database 10g Release 1, the parameter amtp
assumes either byte or char length for LOBs based on the locator type and character set. It is complicated and users did not have the flexibility to use byte length or char length according to their requirements.
Solution: Read/Write calls should take both byte_amtp
and char_amtp
as replacement for amtp
parameter. char_amtp
takes preference for CLOB
and NCLOB
and byte_amtp
is only considered as input if char_amtp
is zero. On output for CLOB
and NCLOB
, both byte_amtp
and char_amtp
are filled. For BLOB
and BFILE,
char_amtp
parameter is ignored for both input and output.
Problem: For OCILobRead2()
, there is no flag to indicate polling mode. There is no easy way for the users to say "I have a 100 byte buffer. Fill it as much as you can". Previously, they had to estimate how many characters to specify for the amount. If they guessed too much, they were forced into polling mode unintentionally. The user code thus can get trapped in the polling mode and subsequent OCI calls are all blocked.
Solution: This call should take piece
as an input parameter and if OCI_ONE_PIECE
is passed, it should fill the buffer as much as possible and come out even if the amount indicated by byte_amtp
or char_amtp
is more than the buffer length. The value of bufl
is used to specify the maximum amount of bytes to read.
Problem: After calling for a LOB write in polling mode, users do not know how many chars or bytes are actually fetched till the end of the polling.
Solution: Both byte_amtp
and char_amtp
need to be updated after each call in polling mode.
Problem: While reading or writing data in streaming mode with callback, users have to use the same buffer for each piece of data.
Solution: The callback function needs to have two new parameters to provide a new buffer and the buffer length. Callback functions can set the new buffer parameter to NULL
to follow old behavior: to use the default buffer passed in the first call for all the pieces.
Existing OCI programs can be enhanced to process larger amounts of LOB data (greater than 4GB). Table 7-1 summarizes compatibility issues ("old" refers to releases before Oracle Database 10g Release 1):
Table 7-1 LOB Functions Compatibility and Migration
LOB Function | Old Client/New or Old Server | New Client/Old Server | New Client/New Server |
---|---|---|---|
|
NA |
OK until piece size and offset are < 4GB. |
OK |
|
NA |
OK until piece size and offset are < 4GB. |
OK |
|
NA |
OK until LOB size, piece size (amount) and offset are < 4GB. |
OK |
|
OK; limit is 4GB. |
OK |
OK; limit is 4GB. |
|
NA |
OK until piece size and offset are < 4GB. |
OK |
|
OK; limit is 4GB. |
OK |
OK; limit is 4GB. |
|
NA |
OK |
OK |
|
OK; limit is 4GB. |
OK |
OK; |
|
NA |
OK until LOB size, piece size (amount) and offset are < 4GB. |
OK |
|
OK; limit is 4GB. |
OK |
OK; limit is 4GB. |
|
NA |
OK until LOB size, piece size (amount) and offset are < 4GB. |
OK |
|
OK; limit 4GB. With new server: Note: 1) If you read up to 4GB-1 from offset, that will not be flagged as an error. 2) When you use streaming mode with polling, no error will be returned if no attempt is made to use piece size > 4GB (you can read data > 4GB in this case). |
OK |
OK.
Note: 1) If you read up to 4GB-1 from offset, that will not be flagged an as error. 2) When the you use streaming mode with polling, no error will be returned if no attempt is made to use piece size > 4GB. |
|
NA |
OK |
OK |
|
OK; limit 4GB. |
OK |
OK; limit 4GB. |
|
NA |
OK until LOB size, piece size (amount) and offset are < 4GB. |
OK |
|
OK; limit 4GB. With new server:
Note: Updating a LOB of 10GB from any offset up to 4GB-1 by up to 4GB-1 amount of data will not be flagged as an error. |
OK |
OK.
Note: Updating a LOB of 10GB from any offset up to 4GB-1 by up to 4GB-1 amount of data will not be flagged as an error. |
|
NA |
OK until LOB size and piece size are <4GB. |
OK |
|
OK; limit 4GB. With new server: |
OK |
OK; limit 4GB.
|
|
NA |
Error |
OK |
Use the new functions when using the new server and new client. Mixing old and new functions can result in unexpected situations such as data written using OCILobWrite2()
being greater than 4GB if the application tries to read it with OCILobRead()
and gets only partial data (if a callback function is not used). In most cases the application will get an error message when the size crosses 4GB and older functions are used. However, there will be no issue if you use those older functions for LOBs of size smaller than 4GB.
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 CLOB
s and NCLOB
s, regardless of the client-side character set.
These LOB operations refer to the amount of LOB data on the server. When the client-side character set is of varying width, the following general rules apply to the amount
and offset
parameters in LOB calls:
amount
- When the amount parameter refers to the server-side LOB, the amount is in characters. When the amount parameter refers to the client-side buffer, the amount is in bytes.
offset
- Regardless of whether the client-side character set is varying-width, the offset parameter is always in characters for CLOBs
or NCLOBs
and in bytes for BLOBs
or BFILEs
.
Exceptions to these general rules are noted in the description of the specific LOB call.
Here are some hints to improve performance.
You can bind or define character data for a CLOB
column or RAW
data for a BLOB
column. This requires only one round trip for inserting or selecting a LOB, as opposed to the traditional LOB interface which requires multiple round trips.
See Also:
"Binding LOB Data" for usage and examples for both INSERT
and UPDATE statements
"Defining LOB Data" for usage and examples of SELECT
statements
You can use 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 CLOB
s and NCLOB
s. When a read or write is done on 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.
Calling OCILobGetChunkSize()
returns the usable chunk size of the LOB, and an application can batch a series of write operations for the entire chunk, rather than issuing multiple LOB write calls for the same chunk.
To read through the end of a LOB, call OCILobRead2()
with an amount of 4 gigabytes. This avoids the round trip involved with first calling OCILobGetLength()
.
Note:
For LOBs which store varying width characters,OCILobGetChunkSize()
returns the number of Unicode characters that fit in a LOB chunk.OCI provides a shortcut for more efficient writing of data to the end of a LOB. The OCILobWriteAppend2()
call appends data to the end of a LOB without first requiring a call to OCILobGetLength()
to determine the starting point for an OCILobWrite()
operation. OCILobWriteAppend2()
does both steps.
Performance improvement is obtained by using these Oracle Database 10g Release 2 functions found in the sections of the LOBs documentation that describe how to read LOB data for multiple locators, using OCILobArrayRead()
, and how to write LOB data for multiple LOB locators, using OCILobArrayWrite()
. Code examples are also provided for using them with callback functions and in piecewise mode.
See Also:
Oracle Database Application Developer's Guide - Large Objects, sections "LOB Array Read" and "LOB Array Write"The Oracle OCI provides several calls for controlling LOB buffering for small reads and writes of internal LOB values:
These functions provide performance improvements by allowing applications using internal LOBs (BLOB
, CLOB
, NCLOB
) to buffer small reads and writes in client-side buffers. This reduces the number of network round trips and LOB versions, thereby improving LOB performance significantly.
See Also:
Oracle Database Application Developer's Guide - Large Objects. For more information on LOB buffering, refer to the chapter on LOBs.
"LOB Function Round Trips" for a list of the server round trips required for each function
The OCI provides functions to explicitly open a LOB, OCILobOpen()
, to close a LOB, OCILobClose()
, and to test whether a LOB is open, OCILobIsOpen()
. These functions mark the beginning and end of a series of LOB operations so that specific processing such as updating indexes can be performed when a LOB is closed.
For internal LOBs, the concept of openness is associated with a LOB and not its locator. The locator does not store any information about the state of the LOB. It is possible for more than one locator to point to the same open LOB. However, for BFILE
s, being open is associated with a specific locator. Hence, more than one open can be performed on the same BFILE
using different locators.
If an application does not wrap LOB operations between a set of OCILobOpen()
and OCILobClose()
calls, then each modification to the LOB implicitly opens and closes the LOB, thereby firing any triggers associated with changes to the LOB.
If LOB operations are not wrapped inside open and close calls, any extensible indexes on the LOB are updated as LOB modifications are made, and thus are always valid and may be used at any time. If the LOB is modified between a set of OCILobOpen()
and OCILobClose()
calls, triggers are not fired for individual LOB modifications. Triggers are only fired after the OCILobClose()
call, so indexes are not updated until after the close call and thus are not valid in between the open and close calls. OCILobIsOpen()
can be used with internal LOBs and BFILEs
.
An error is returned when you commit the transaction before closing all opened LOBs that were opened by the transaction. When the error is returned, the LOB is no longer marked as open, but the transaction is successfully committed. Hence, all the changes made to the LOB and non-LOB data in the transaction are committed, but the domain and functional indexing are not updated. If this happens, please rebuild your functional and domain indexes on the LOB column.
A LOB opened when there is no transaction must be closed before the end of the session. If there are LOBs open at the end of session, the LOB is no longer marked as open and the domain and functional indexing will not be updated. If this happens, rebuild your functional and domain indexes on the LOB column.
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 since 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 Oracle Database Reference. 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
See Also:
Appendix B, "OCI Demonstration Programs" for examples of the use of the OCILobOpen()
and OCILobCLose()
calls in the online demonstration programs
OCI supports read and write callback functions. The following sections describe the use of callbacks in more detail.
User-defined read and write callback functions for inserting or retrieving data provide an alternative to the polling methods for streaming LOB. These functions are implemented by you and registered with OCI through the OCILobRead2()
, OCILobWriteAppend2()
, and OCILobWrite2()
calls. These callback functions are called by OCI whenever required.
The user-defined read callback function is registered through the OCILobRead2()
function. The callback function should have the following prototype:
CallbackFunctionName ( dvoid *ctxp, CONST dvoid *bufp, oraub8 len, ub1 piece, dvoid **changed_bufpp, oraub8 *changed_lenp);
The first parameter, ctxp
, is the context of the callback that is passed to OCI in the OCILobRead()
function call. When the callback function is called, the information provided by you in ctxp
is passed back to you (the OCI does not use this information on the way IN). The bufp
parameter is the pointer to the storage where the LOB data is returned and bufl
is the length of this buffer. It tells you how much data has been read into the buffer provided.
If the buffer length provided in the original OCILobRead2()
call is insufficient to store all the data returned by the server, then the user-defined callback is called. In this case, the piece
parameter indicates whether the information returned in the buffer is the first, next or last piece.
The parameters changed_bufpp
and changed_lenp
can be used inside the callback function to change the buffer dynamically. changed_bufpp
should point to the address of the changed buffer and changed_lenp
should point to the length of the changed buffer. changed_bufpp
and changed_lenp
need not be used inside the callback function if the application does not change the buffer dynamically.
The following code fragment implements read callback functions using OCILobRead2()
. Assume that lobl
is a valid locator that has been previously selected, svchp
is a valid service handle and errhp
is a valid error handle In the example. The user-defined function cbk_read_lob()
is repeatedly called until all the LOB data has been read.
... oraub8 offset = 1; oraub8 loblen = 0; oraub8 byte_amt = 0; oraub8 char_amt = 0 ub1 bufp[MAXBUFLEN]; sword retval; byte_amtp = 4294967297; /* 4 gigabytes plus 1 */ if (retval = OCILobRead2(svchp, errhp, lobl, &byte_amt, &char_amt, offset, (dvoid *) bufp, (oraub8) MAXBUFLEN, (dvoid *) 0, OCI_FIRST_PIECE, cbk_read_lob, (ub2) 0, (ub1) SQLCS_IMPLICIT)) { (void) printf("ERROR: OCILobRead2() LOB.\n"); report_error(); } ... sb4 cbk_read_lob(ctxp, bufxp, len, piece, changed_bufpp, changed_lenp) dvoid *ctxp; CONST dvoid *bufxp; oraub8 len; ub1 piece; dvoid **changed_bufpp; oraub8 *changed_lenp; { static ub4 piece_count = 0; piece_count++; switch (piece) { case OCI_LAST_PIECE: /*--- buffer processing code goes here ---*/ (void) printf("callback read the %d th piece\n\n", piece_count); piece_count = 0; break; case OCI_FIRST_PIECE: /*--- buffer processing code goes here ---*/ (void) printf("callback read the %d th piece\n", piece_count); /* --Optional code to set changed_bufpp and changed_lenp if the buffer needs to be changed dynamically --*/ break; case OCI_NEXT_PIECE: /*--- buffer processing code goes here ---*/ (void) printf("callback read the %d th piece\n", piece_count); /* --Optional code to set changed_bufpp and changed_lenp if the buffer needs to be changed dynamically --*/ break; default: (void) printf("callback read error: unkown piece = %d.\n", piece); return OCI_ERROR; } return OCI_CONTINUE; }
Similar to read callbacks, the user-defined write callback function is registered through the OCILobWrite2()
function. The callback function should have the following prototype:
CallbackFunctionName ( dvoid *ctxp, dvoid *bufp, oraub8 *lenp, ub1 *piecep, dvoid **changed_bufpp, oraub8 *changed_lenp);
The first parameter, ctxp
, is the context of the callback that is passed to OCI in the OCILobWrite2()
function call. The information provided by you in ctxp
, is passed back to you when the callback function is called by the OCI (the OCI does not use this information on the way IN). The bufp
parameter is the pointer to a storage area; you provide this pointer in the call to OCILobWrite()
.
After inserting the data provided in the call to OCILobWrite2()
any data remaining is inserted by the user-defined callback. In the callback provide the data to insert in the storage indicated by bufp
and also specify the length in buflp
. You also indicate whether it is the next (OCI_NEXT_PIECE
) or the last (OCI_LAST_PIECE
) piece using the piecep
parameter. You are completely responsible for the storage pointer the application provides and should make sure that it does not write more than the allocated size of the storage.
The parameters changed_bufpp
and changed_lenp
can be used inside the callback function to change the buffer dynamically. changed_bufpp
should point to the address of the changed buffer and changed_lenp
should point to the length of the changed buffer. changed_bufpp
and changed_lenp
need not be used inside the callback function if the application does not change the buffer dynamically.
The following code fragment implements write callback functions using OCILobWrite2()
. Assume that lobl
is a valid locator that has been locked for updating, svchp
is a valid service handle, and errhp
is a valid error handle. The user-defined function cbk_write_lob()
is repeatedly called until the piecep
parameter indicates that the application is providing the last piece.
... ub1 bufp[MAXBUFLEN]; oraub8 byte_amt = MAXBUFLEN * 20; oraub8 char_amt = 0; oraub8 offset = 1; oraub8 nbytes = MAXBUFLEN; /*-- code to fill bufp with data goes here. nbytes should reflect the size and should be less than or equal to MAXBUFLEN --*/ if (retval = OCILobWrite2(svchp, errhp, lobl, &byte_amt, &char_amt, offset, (dvoid*)bufp, (ub4)nbytes, OCI_FIRST_PIECE, (dvoid *)0, cbk_write_lob, (ub2) 0, (ub1) SQLCS_IMPLICIT)) { (void) printf("ERROR: OCILobWrite2().\n"); report_error(); return; } ... sb4 cbk_write_lob(ctxp, bufxp, lenp, piecep, changed_bufpp, changed_lenp) dvoid *ctxp; dvoid *bufxp; oraub8 *lenp; ub1 *piecep; dvoid **changed_bufpp; oraub8 *changed_lenp; { /*-- code to fill bufxp with data goes here. *lenp should reflect the size and should be less than or equal to MAXBUFLEN -- */ /* --Optional code to set changed_bufpp and changed_lenp if the buffer needs to be changed dynamically --*/ if (this is the last data buffer) *piecep = OCI_LAST_PIECE; else *piecep = OCI_NEXT_PIECE; return OCI_CONTINUE; }
The OCI provides functions for creating and freeing temporary LOBs, OCILobCreateTemporary()
and OCILobFreeTemporary()
, and a function for determining whether a LOB is temporary, OCILobIsTemporary()
.
Temporary LOBs are not permanently stored in the database, but act like local variables for operating on LOB data. OCI functions which operate on standard (persistent) LOBs can also be used on temporary LOBs.
As with persistent LOBs, all functions operate on the locator for the temporary LOB, and the actual LOB data is accessed through the locator.
Temporary LOB locators can be used as arguments to the following types of SQL statements:
UPDATE
- The temporary LOB locator can be used as a value in a WHERE
clause when testing for nullity or as a parameter to a function. The locator can also be used in a SET
clause.
DELETE
- The temporary LOB locator can be used in a WHERE
clause when testing for nullity or as a parameter to a function.
SELECT
- The temporary LOB locator can be used in a WHERE
clause when testing for nullity or as a parameter to a function. The temporary LOB can also be used as a return variable in a SELECT...INTO
statement when selecting the return value of a function.
Note:
If you select a permanent locator into a temporary locator, the temporary locator is overwritten with the permanent locator. In this case the temporary LOB is not implicitly freed. You must explicitly free the temporary LOB before theSELECT...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
.You create a temporary LOB with the OCILobCreateTemporary()
function. The parameters passed to this function include a value for the duration of the LOB. The default duration is for the length of the current session. All temporary LOBs are deleted at the end of the duration. Users can reclaim temporary LOB space by explicitly freeing the temporary LOB with the OCILobFreeTemporary()
function. A temporary LOB is empty when it is created.
When creating a temporary LOB, you can also specify whether or not the temporary LOB is read into the server's buffer cache.
To make a temporary LOB permanent, use OCILobCopy()
to copy the data from the temporary LOB into a permanent one. You can also use the temporary LOB in the VALUES
clause of an INSERT
statement, as the source of the assignment in an UPDATE
statement, or assign it to a persistent LOB attribute and then flush the object. Temporary LOBs can be modified using the same functions which are used for standard LOBs.
The OCI supports several predefined durations for temporary LOBs, and a set of functions that the application can use to define application-specific durations. The predefined durations and their associated attributes are:
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 OCIDurationBegin()
and the end of the duration using OCIDurationEnd()
.
Note:
User-defined durations are only available if an application has been initialized in object mode.Each application-specific duration has a duration identifier that is returned by OCIDurationBegin()
and is guaranteed to be unique until OCIDurationEnd()
is called. An application-specific duration can be as long as a session duration.
At the end of a duration, all temporary LOBs associated with that duration are freed. The descriptor associated with the temporary LOB must be freed explicitly with the OCIDescriptorFree()
call.
User-defined durations can be nested; one duration can be defined as a child duration of another user duration. It is possible for a parent duration to have child durations that have their own child durations.
Note:
When a duration is started withOCIDurationBegin()
, one of the parameters is the identifier of a parent duration. When a parent duration is ended, all child durations are also ended.Any time that your OCI program obtains a LOB locator from SQL or PL/SQL, check that the locator is temporary. If it is, free the locator when your application is finished with it. The locator can be from a define during a select or an out bind. A temporary LOB duration is always upgraded to session when it is shipped to the client side. The application needs to do the following before the locator is overwritten by the locator of the next row:
OCILobIsTemporary(env, err, locator, is_temporary); if(is_temporary) OCILobFreeTemporary(svc, err, locator);
See Also:
Special care needs to be taken when assigning OCILobLocator
pointers. Pointer assignments create a shallow copy of the LOB. After the pointer assignment, source and target LOBs point to the same copy of data. This behavior is different from using LOB APIs, such as OCILobAssign()
or OCILobLocatorAssign()
to perform assignments.
When the APIs are used, the locators logically point to independent copies of data after assignment.
For temporary LOBs, before pointer assignments, it is your responsibility to make sure any temporary LOB in the target LOB locator is freed by OCIFreeTemporary()
. When OCILobLocatorAssign()
is used, the original temporary LOB in the target LOB locator variable, if any, is freed before the assignment happens.
Before an out-bind variable is reused in executing a SQL statement, it is your responsibility to free any temporary LOB in the existing out-bind LOB locator buffer by using the OCIFreeTemporary()
call.
See Also:
Oracle Database Application Developer's Guide - Large Objects, "Temporary LOB Performance Guidelines" section.
Oracle Database Application Developer's Guide - Large Objects , for a discussion on optimal performance of temporary LOBS.
The following code example shows how temporary LOBs can be used:
#include <stdio.h> #include <stdlib.h> #include <string.h> #include <oci.h> /* Function Prototype */ static void checkerr (/*_ OCIError *errhp, sword status _*/); sb4 select_and_createtemp (OCILobLocator *lob_loc, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIEnv *envhp); /* This function reads in a single video Frame from the print_media table. Then it creates a temporary lob. The temporary LOB which is created is read through the CACHE, and is automatically cleaned up at the end of the user's session, if it is not explicitly freed sooner. This function returns OCI_SUCCESS if it completes successfully or OCI_ERROR if it fails. */ sb4 select_and_createtemp (OCILobLocator *lob_loc, OCIError *errhp, OCISvcCtx *svchp, OCIStmt *stmthp, OCIEnv *envhp) { OCIDefine *defnp1; OCIBind *bndhp; text *sqlstmt; int rowind =1; ub4 loblen = 0; OCILobLocator *tblob; printf ("in select_and_createtemp \n"); if(OCIDescriptorAlloc((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 print_media WHERE product_ID = 1 FOR UPDATE"; if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n"); return OCI_ERROR; } /* Define for BLOB */ if (OCIDefineByPos(stmthp, &defnp1, errhp, (ub4)1, (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 */ OCIEnvCreate(&envhp, OCI_DEFAULT, (dvoid *)0, 0, 0, 0, (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; } }