This chapter describes APIs that perform basic operations on BLOB
, CLOB
, and NCLOB
datatypes. The operations given in this chapter can be used with either persistent or temporary LOB instances. Note that operations in this chapter do not apply to BFILE
s. APIs covered in this chapter are listed in Table 14-1.
See Also:
|
The following information is given for each operation described in this chapter:
Preconditions describe dependencies that must be met and conditions that must exist before calling each operation.
Usage Notes provide implementation guidelines such as information specific to a given programmatic environment or datatype.
Syntax refers you to the syntax reference documentation for each supported programmatic environment.
Examples describe any setup tasks necessary to run the examples given. Demonstration files listed are available in subdirectories under $ORACLE_HOME/rdbms/demo/lobs/
named plsql
, oci
, vb
, and java
. The driver program lobdemo.sql
is in /plsql
and the driver program lobdemo.c
is in /oci
.
This chapter contains these topics:
Table 14-1, "Environments Supported for Basic LOB APIs" indicates which programmatic environments are supported for the APIs discussed in this chapter. The first column describes the operation that the API performs. The remaining columns indicate with "Yes" or "No" whether the API is supported in PL/SQL, OCI, OCCI, COBOL, Pro*C/C++, Visual Basic (VB), and JDBC.
Table 14-1 Environments Supported for Basic LOB APIs
This operation appends one LOB instance to another.
Preconditions
Before you can append one LOB to another, the following conditions must be met:
Two LOB instances must exist.
Both instances must be of the same type, for example both BLOB
or both CLOB
types.
You can pass any combination of persistent or temporary LOB instances to this operation.
Usage Notes
Persistent LOBs: You must lock the row you are selecting the LOB from prior to updating a LOB value if you are using the PL/SQL DBMS_LOB Package or OCI. While the SQL INSERT
and UPDATE
statements implicitly lock the row, locking the row can be done explicitly using the SQL SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs, or by using an OCI
pin
or lock
function in OCI programs. For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".
Syntax
See the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — APPEND
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobAppend()
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB APPEND.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide for information on embedded SQL statements and directives — LOB APPEND
Visual Basic (OO4O): (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > append
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference for information on creating and populating LOB columns in Java.
Examples
To run the following examples, you must create two LOB instances and pass them when you call the given append operation. Creating a LOB instance is described in Chapter 12, "Operations Specific to Persistent and Temporary LOBs".
Examples for this use case are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lappend.sql
OCI: lappend.c
Visual Basic (OO4O): lappend.bas
Java (JDBC): lappend.java
This section describes how to get the character set form of a LOB instance.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this operation.
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobCharSetForm()
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL): There is no applicable syntax reference for this operation
C/C++ (Pro*C/C++): There is no applicable syntax reference for this operation.
Visual Basic (OO4O): There is no applicable syntax reference for this operation.
Java (JDBC): There is no applicable syntax reference for this operation.
The example demonstrates how to determine the character set form of the foreign language text (ad_fltextn
).
This functionality is currently available only in OCI:
OCI: lgetchfm.c
This section describes how to determine the character set ID.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this operation.
C (OCI): Oracle Call Interface Programmer's Guide "Relational Functions" — LOB Functions, OCILobCharSetId()
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL): There is no applicable syntax reference for this operation.
C/C++ (Pro*C/C++): There is no applicable syntax reference for this operation
Visual Basic (OO4O): There is no applicable syntax reference for this operation.
Java (JDBC): There is no applicable syntax reference for this operation.
This functionality is currently available only in OCI:
OCI: lgetchar.c
This operation loads a LOB with data from a BFILE
. This procedure can be used to load data into any persistent or temporary LOB instance of any LOB datatype.
See Also:
|
Preconditions
Before you can load a LOB with data from a BFILE
, the following conditions must be met:
The BFILE must exist.
The target LOB instance must exist.
Usage Notes
Note the following issues regarding this operation.
Use LOADCLOBFROMFILE When Loading Character Data
When you use the DBMS_LOB.LOADFROMFILE
procedure to load a CLOB
or NCLOB
instance, you are loading the LOB with binary data from the BFILE
and no implicit character set conversion is performed. For this reason, using the DBMS_LOB.LOADCLOBFROMFILE
procedure is recommended when loading character data, see Loading a CLOB or NCLOB with Data from a BFILE on for more information.
Specifying Amount of BFILE Data to Load
The value you pass for the amount parameter to functions listed in Table 14-2 must be one of the following:
An amount less than or equal to the actual size (in bytes) of the BFILE
you are loading.
The maximum allowable LOB size (in bytes). Passing this value, loads the entire BFILE
. You can use this technique to load the entire BFILE
without determining the size of the BFILE
before loading. To get the maximum allowable LOB size, use the technique described in Table 14-2.
Table 14-2 Maximum LOB Size for Load from File Operations
Environment | Function | To pass maximum LOB size, get value of: |
---|---|---|
|
|
|
|
|
|
|
(For LOBs of any size.) |
|
|
(For LOBs less than 4 gigabytes in size.) |
|
Syntax
See the following syntax references for details on using this operation in each programmatic environment:
PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — LOADFROMFILE.
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobLoadFromFile().
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB LOAD, LOB OPEN, LOB CLOSE.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB LOAD
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From help Topics, Contents tab, select OO4O Automation Server > OBJECTS > OraBfile > METHODS > CopyFromBFILE and select OO4O Automation Server > OBJECTS > OraDynaset, OraDatabase, OraConnection
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.
Examples
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lloaddat.sql
OCI: lloaddat.c
Visual Basic (OO4O): lloaddat.bas
Java (JDBC): lloaddat.java
This procedure loads a BLOB
with data from a BFILE
. This procedure can be used to load data into any persistent or temporary BLOB
instance.
See Also:
|
Preconditions
The following conditions must be met before calling this procedure:
The target BLOB
instance must exist.
The source BFILE
must exist.
You must open the BFILE
. (After calling this procedure, you must close the BFILE
at some point.)
Usage Notes
Note the following with respect to this operation:
New Offsets Returned
Using DBMS_LOB.LOADBLOBFROMFILE
to load binary data into a BLOB
achieves the same result as using DBMS_LOB.LOADFROMFILE
, but also returns the new offsets of BLOB.
Specifying Amount of BFILE Data to Load
The value you pass for the amount parameter to the DBMS_LOB.LOADBLOBFROMFILE
function must be one of the following:
An amount less than or equal to the actual size (in bytes) of the BFILE
you are loading.
The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE
.
Passing this value causes the function to load the entire BFILE
. This is a useful technique for loading the entire BFILE
without introspecting the size of the BFILE
.
Syntax
See Oracle Database PL/SQL Packages and Types Reference, "DBMS_LOB" — LOADBLOBFROMFILE procedure for syntax details on this procedure.
Examples
This example is available in PL/SQL only. This API is not provided in other programmatic environments. The online file is lldblobf.sql
. This example illustrates:
How to use LOADBLOBFROMFILE
to load the entire BFILE
without getting its length first.
How to use the return value of the offsets to calculate the actual amount loaded.
This procedure loads a CLOB
or NCLOB
with character data from a BFILE
. This procedure can be used to load data into a persistent or temporary CLOB
or NCLOB
instance.
See Also:
|
Preconditions
The following conditions must be met before calling this procedure:
The target CLOB
or NCLOB
instance must exist.
The source BFILE
must exist.
You must open the BFILE
. (After calling this procedure, you must close the BFILE
at some point.)
Usage Notes
You can specify the character set id of the BFILE
when calling this procedure. Doing so, ensures that the character set is properly converted from the BFILE
data character set to the destination CLOB
or NCLOB
character set.
Specifying Amount of BFILE Data to Load
The value you pass for the amount parameter to the DBMS_LOB.LOADCLOBFROMFILE
function must be one of the following:
An amount less than or equal to the actual size (in characters) of the BFILE
data you are loading.
The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE
Passing this value causes the function to load the entire BFILE
. This is a useful technique for loading the entire BFILE
without introspecting the size of the BFILE
.
Syntax
See Oracle Database PL/SQL Packages and Types Reference, "DBMS_LOB" — LOADCLOBFROMFILE procedure for syntax details on this procedure.
Examples
The following examples illustrate different techniques for using this API:
The following example illustrates:
How to use default csid (0
).
How to load the entire file without calling getlength
for the BFILE
.
How to find out the actual amount loaded using return offsets.
This example assumes that ad_source
is a BFILE
in UTF8 character set format and the database character set is UTF8. The online file is lldclobf.sql
.
The following example illustrates:
How to get the character set ID from the character set name using the NLS_CHARSET_ID
function.
How to load a stream of data from a single BFILE
into different LOBs using the returned offset value and the language context lang_ctx
.
How to read a warning message.
This example assumes that ad_file_ext_01
is a BFILE in JA16TSTSET format and the database national character set is AL16UTF16. The online file is lldclobs.sql
.
This operation determines whether a LOB is open.
Preconditions
The LOB instance must exist before executing this procedure.
Usage Notes
When a LOB is open, it must be closed at some point later in the session.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — OPEN, ISOPEN.
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobIsOpen().
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB DESCRIBE.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB DESCRIBE ... ISOPEN ...
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.
Examples
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lisopen.sql
OCI: lisopen.c
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): lisopen.java
Here is how to check a BLOB or a CLOB.
To see if a CLOB
is open, your JDBC application can use the isOpen
method defined in oracle.sql.CLOB
. The return Boolean value indicates whether the CLOB
has been previously opened or not. The isOpen
method is defined as follows:
/** * Check whether the CLOB is opened. * @return true if the LOB is opened. */ public boolean isOpen () throws SQLException
The usage example is:
CLOB clob = ... // See if the CLOB is opened boolean isOpen = clob.isOpen (); ...
To see if a BLOB
is open, your JDBC application can use the isOpen
method defined in oracle.sql.BLOB
. The return Boolean value indicates whether the BLOB
has been previously opened or not. The isOpen
method is defined as follows:
/** * Check whether the BLOB is opened. * @return true if the LOB is opened. */ public boolean isOpen () throws SQLException
The usage example is:
BLOB blob = ... // See if the BLOB is opened boolean isOpen = blob.isOpen (); ...
This section describes APIs that allow you to read LOB data. You can use this operation to read LOB data into a buffer. This is useful if your application requires displaying large amounts of LOB data or streaming data operations.
Usage Notes
Note the following when using these APIs.
Streaming Mechanism
The most efficient way to read large amounts of LOB data is to use OCILobRead2
() with the streaming mechanism enabled.
Amount Parameter
The value you pass for the amount parameter is restricted for the APIs described in Table 14-3.
Table 14-3 Maximum LOB Size for Amount Parameter
Environment | Function | Value of amount parameter is limited to: |
---|---|---|
DBMS_LOB |
|
The size of the buffer, 32Kbytes. |
OCI |
(For LOBs less than 4 gigabytes in size.) |
Specifying this amount reads the entire file. |
OCI |
(For LOBs of any size.) |
Specifying this amount reads the entire file. |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — OPEN, READ, CLOSE.
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" —, OCILobOpen(), OCILobRead2(), OCILobClose().
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB READ.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB READ
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oraclob > METHODS > read, and > OBJECTS > Oraclob > PROPERTIES > offset
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): ldisplay.sql
OCI: ldisplay.c
C++ (OCCI): No example is provided in this release.
Visual Basic (OO4O): ldisplay.bas
Java (JDBC): ldisplay.java
This section describes how to read data from LOBs using OCILobRead2()
.
Usage Notes
Note the following when using this operation.
The most efficient way to read large amounts of LOB data is to use OCILobRead2()
with the streaming mechanism enabled using polling or callback. To do so, specify the starting point of the read using the offset
parameter as follows:
ub8 char_amt = 0; ub8 byte_amt = 0; ub4 offset = 1000; OCILobRead2(svchp, errhp, locp, &byte_amt, &char_amt, offset, bufp, bufl, OCI_ONE_PIECE, 0, 0, 0, 0);
When using polling mode, be sure to look at the value of the byte_amt
parameter after each OCILobRead2()
call to see how many bytes were read into the buffer because the buffer may not be entirely full.
When using callbacks, the lenp
parameter, which is input to the callback, will indicate how many bytes are filled in the buffer. Be sure to check the lenp
parameter during your callback processing because the entire buffer may not be filled with data (see the Oracle Call Interface Programmer's Guide.)
A chunk is one or more Oracle blocks. You can specify the chunk size for the LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The API you are using has a function that returns the amount of space used in the LOB chunk to store the LOB value. In PL/SQL use DBMS_LOB.GETCHUNKSIZE
. In OCI, use OCILobGetChunkSize()
.
You will improve performance if you run write
requests using a multiple of the value returned by one of these functions. The reason for this is that you are using the same unit that the Oracle database uses when reading data from disk. If it is appropriate for your application, then you should batch reads until you have enough for an entire chunk instead of issuing several LOB read calls that operate on the same LOB chunk.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — OPEN
, GETCHUNKSIZE
, READ
, CLOSE
.
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobOpen()
, OCILobRead2()
, OCILobClose()
.
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB READ.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB READ
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oraclob > METHODS > read
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lread.sql
OCI: lread.c
Visual Basic (OO4O): lread.bas
Java (JDBC): lread.java
This section describes how to read LOB data for multiple locators in one round trip, using OCILobArrayRead()
.
Usage Notes
This function improves performance in reading LOBs in the size range less than about 512 Kbytes. For an OCI application example, assume that the program has a prepared SQL statement such as:
SELECT lob1 FROM lob_table for UPDATE;
where lob1
is the LOB column and lob_array
is an array of define variables corresponding to a LOB column:
OCILobLocator * lob_array[10]; ... for (i=0; i<10, i++) /* initialize array of locators */ lob_array[i] = OCIDescriptorAlloc(..., OCI_DTYPE_LOB, ...); ... OCIDefineByPos(..., 1, (dvoid *) lob_array, ... SQLT_CLOB, ...); /* Execute the statement with iters = 10 to do an array fetch of 10 locators. */ OCIStmtExecute ( <service context>, <statement handle>, <error handle>, 10, /* iters */ 0, /* row offset */ NULL, /* snapshot IN */ NULL, /* snapshot out */ OCI_DEFAULT /* mode */); ... ub4 array_iter = 10; char *bufp[10]; oraub8 bufl[10]; oraub8 char_amtp[10]; oraub8 offset[10]; for (i=0; i<10; i++) { bufp[i] = (char *)malloc(1000); bufl[i] = 1000; offset[i] = 1; char_amtp[i] = 1000; /* Single byte fixed width char set. */ } /* Read the 1st 1000 characters for all 10 locators in one * round trip. Note that offset and amount need not be * same for all the locators. */ OCILobArrayRead(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of read buffers */ bufl, /* array of buffer lengths */ OCI_ONE_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT);/* character set form */ ... for (i=0; i<10; i++) { /* Fill bufp[i] buffers with data to be written */ strncpy (bufp[i], "Test Data------", 15); bufl[i] = 1000; offset[i] = 50; char_amtp[i] = 15; /* Single byte fixed width char set. */ } /* Write the 15 characters from offset 50 to all 10 * locators in one round trip. Note that offset and * amount need not be same for all the locators. */ */ OCILobArrayWrite(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of read buffers */ bufl, /* array of buffer lengths */ OCI_ONE_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT);/* character set form */ ...
Streaming Support
LOB array APIs can be used to read/write LOB data in multiple pieces. This can be done by using polling method or a callback function.Here data is read/written in multiple pieces sequentially for the array of locators. For polling, the API would return to the application after reading/writing each piece with the array_iter
parameter (OUT) indicating the index of the locator for which data is read/written. With a callback, the function is called after reading/writing each piece with array_iter
as IN parameter.
Note that:
It is possible to read/write data for a few of the locators in one piece and read/write data for other locators in multiple pieces. Data is read/written in one piece for locators which have sufficient buffer lengths to accommodate the whole data to be read/written.
Your application can use different amount value and buffer lengths for each locator.
Your application can pass zero as the amount value for one or more locators indicating pure streaming for those locators. In the case of reading, LOB data is read to the end for those locators. For writing, data is written until OCI_LAST_PIECE
is specified for those locators.
LOB Array Read in Polling Mode
The following example reads 10Kbytes of data for each of 10 locators with 1Kbyte buffer size. Each locator needs 10 pieces to read the complete data. OCILobArrayRead()
needs to be called 100 (10*10) times to fetch all the data.First we call OCILobArrayRead()
with OCI_FIRST_PIECE
as piece
parameter. This call returns the first 1K piece for the first locator.Next OCILobArrayRead()
is called in a loop until the application finishes reading all the pieces for the locators and returns OCI_SUCCESS
. In this example it loops 99 times returning the pieces for the locators sequentially.
/* Fetch the locators */ ... /* array_iter parameter indicates the number of locators in the array read. * It is an IN parameter for the 1st call in polling and is ignored as IN * parameter for subsequent calls. As OUT parameter it indicates the locator * index for which the piece is read. */ ub4 array_iter = 10; char *bufp[10]; oraub8 bufl[10]; oraub8 char_amtp[10]; oraub8 offset[10]; sword st; for (i=0; i<10; i++) { bufp[i] = (char *)malloc(1000); bufl[i] = 1000; offset[i] = 1; char_amtp[i] = 10000; /* Single byte fixed width char set. */ } st = OCILobArrayRead(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of read buffers */ bufl, /* array of buffer lengths */ OCI_FIRST_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); /* character set form */ /* First piece for the first locator is read here. * bufp[0] => Buffer pointer into which data is read. * char_amtp[0 ] => Number of characters read in current buffer * */ While ( st == OCI_NEED_DATA) { st = OCILobArrayRead(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of read buffers */ bufl, /* array of buffer lengths */ OCI_NEXT_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); /* array_iter returns the index of the current array element for which * data is read. for example, aray_iter = 1 implies first locator, * array_iter = 2 implies second locator and so on. * * lob_array[ array_iter - 1]=> Lob locator for which data is read. * bufp[array_iter - 1] => Buffer pointer into which data is read. * char_amtp[array_iter - 1] => Number of characters read in current buffer */ ... /* Consume the data here */ ... }
LOB Array Read with Callback
The following example reads 10Kbytes of data for each of 10 locators with 1Kbyte buffer size. Each locator needs 10 pieces to read all the data. The callback function is called 100 (10*10) times to return the pieces sequentially.
/* Fetch the locators */ ... ub4 array_iter = 10; char *bufp[10]; oraub8 bufl[10]; oraub8 char_amtp[10]; oraub8 offset[10]; sword st; for (i=0; i<10; i++) { bufp[i] = (char *)malloc(1000); bufl[i] = 1000; offset[i] = 1; char_amtp[i] = 10000; /* Single byte fixed width char set. */ } st = OCILobArrayRead(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of read buffers */ bufl, /* array of buffer lengths */ OCI_FIRST_PIECE, /* piece information */ ctx, /* callback context */ cbk_read_lob, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); ... /* Callback function for LOB array read. */ sb4 cbk_read_lob(dvoid *ctxp, ub4 array_iter, 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(last piece) for %dth locator \n\n", piece_count, array_iter ); piece_count = 0; break; case OCI_FIRST_PIECE: /*--- buffer processing code goes here ---*/ (void) printf("callback read the 1st piece for %dth locator\n", array_iter); /* --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 for %dth locator\n", piece_count, array_iter); /* --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; } ...
Polling LOB Array Read
The next example is polling LOB data in OCILobArrayRead()
with variable amtp
, bufl
, and offset
.
/* Fetch the locators */ ... ub4 array_iter = 10; char *bufp[10]; oraub8 bufl[10]; oraub8 char_amtp[10]; oraub8 offset[10]; sword st; for (i=0; i<10; i++) { bufp[i] = (char *)malloc(1000); bufl[i] = 1000; offset[i] = 1; char_amtp[i] = 10000; /* Single byte fixed width char set. */ } /* For 3rd locator read data in 500 bytes piece from offset 101. Amount * is 2000, that is, total number of pieces will be 2000/500 = 4. */ offset[2] = 101; bufl[2] = 500; char_amtp[2] = 2000; /* For 6th locator read data in 100 bytes piece from offset 51. Amount * is 0 indicating pure polling, that is, data will be read till the end of * the LOB is reached. */ offset[5] = 51; bufl[5] = 100; char_amtp[5] = 0; /* For 8th locator read 100 bytes of data in one piece. Note amount * is less than buffer length indicating single piece read. */ offset[7] = 61; bufl[7] = 200; char_amtp[7] = 100; st = OCILobArrayRead(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of read buffers */ bufl, /* array of buffer lengths */ OCI_FIRST_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); /* character set form */ /* First piece for the first locator is read here. * bufp[0] => Buffer pointer into which data is read. * char_amtp[0 ] => Number of characters read in current buffer * */ while ( st == OCI_NEED_DATA) { st = OCILobArrayRead(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of read buffers */ bufl, /* array of buffer lengths */ OCI_NEXT_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); /* array_iter returns the index of the current array element for which * data is read. for example, aray_iter = 1 implies first locator, * array_iter = 2 implies second locator and so on. * * lob_array[ array_iter - 1]=> Lob locator for which data is read. * bufp[array_iter - 1] => Buffer pointer into which data is read. * char_amtp[array_iter - 1]=>Number of characters read in current buffer */ ... /* Consume the data here */ ... }
Syntax
Use the following syntax references for the OCI programmatic environment:
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobArrayRead()
.
Example
An example is provided in the following programmatic environment:
OCI: lreadarr.c
This section describes how to read a portion of a LOB using SUBSTR.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — SUBSTR, OPEN, CLOSE
C (OCI): There is no applicable syntax reference for this use case.
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — ALLOCATE, LOB OPEN, LOB READ, LOB CLOSE
.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB READ. See PL/SQL DBMS_LOB.SUBSTR
.
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > PROPERTIES > offset, chunksize, and >OBJECTS > Oraclob > METHODS > read
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lsubstr.sql
OCI: No example is provided with this release.
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): lsubstr.bas
Java (JDBC): lsubstr.java
This section describes how to compare all or part of two LOBs.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — COMPARE.
C (OCI): There is no applicable syntax reference for this use case.
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide or information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — EXECUTE. Also reference PL/SQL DBMS_LOB.COMPARE.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — EXECUTE. Also reference PL/SQL DBMS_LOB.COMPARE
.
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oradynaset > METHODS > movenext
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lcompare.sql
C (OCI): No example is provided with this release.
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): lcompare.bas
Java (JDBC): lcompare.java
This section describes how to see if a pattern exists in a LOB using INSTR
.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — INSTR.
C (OCI): There is no applicable syntax reference for this use case.
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — EXECUTE. Also reference PL/SQL DBMS_LOB.INSTR
.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — EXECUTE. Also reference PL/SQL DBMS_LOB.INSTR
.
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): linstr.sql
C (OCI): No example is provided with this release.
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): linstr.java
This section describes how to determine the length of a LOB.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — GETLENGTH
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobGetLength2()
.
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB DESCRIBE.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB DESCRIBE ...GET LENGTH...
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Oradynaset > PROPERTIES > fields
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package) llength.sql
OCI: llength.c
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): llength.bas
Java (JDBC): llength.java
This section describes how to copy all or part of a LOB to another LOB. These APIs copy an amount of data you specify from a source LOB to a destination LOB.
Usage Notes
Note the following issues when using this API.
Specifying Amount of Data to Copy
The value you pass for the amount
parameter to the DBMS_LOB.COPY
function must be one of the following:
An amount less than or equal to the actual size of the data you are loading.
The maximum allowable LOB size: DBMS_LOB.LOBMAXSIZE
.
Passing this value causes the function to read the entire LOB. This is a useful technique for reading the entire LOB without introspecting the size of the LOB.
Note that for character data, the amount is specified in characters, while for binary data, the amount is specified in bytes.
Locking the Row Prior to Updating
If you plan to update a LOB value, then you must lock the row containing the LOB prior to updating. While the SQL INSERT
and UPDATE
statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs, or by using an OCI
pin
or lock
function in OCI programs.
For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".
Syntax
See the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — COPY
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobCopy2
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — LOB COPY. Also reference PL/SQL DBMS_LOB.COPY.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide for information on embedded SQL statements and directives — LOB COPY
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > copy, and > OBJECTS > Oradynaset > METHODS > movenext, edit
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lcopy.sql
OCI: lcopy.c
Visual Basic (OO4O): lcopy.bas
Java (JDBC): lcopy.java
This section describes how to copy a LOB locator. Note that different locators may point to the same or different data, or to current or outdated data.
Note: Assigning one LOB to another using PL/SQL entails using the ":=" sign. This is an advanced topic that is discussed in more detail in "Read Consistent Locators". |
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Refer to "Read Consistent Locators" for information on assigning one lob locator to another.
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobAssign()
, OCILobIsEqual()
.
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB Statements, and embedded SQL and precompiler directives — ALLOCATE, LOB ASSIGN.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — SELECT, LOB ASSIGN
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > copy
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lcopyloc.sql
OCI: lcopyloc.c
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): lcopyloc.bas
Java (JDBC): lcopyloc.java
This section describes how to determine whether one LOB locator is equal to another. If two locators are equal, then this means that they refer to the same version of the LOB data.
Syntax
Use the following syntax references for each programmatic environment:
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobAssign()
, OCILobIsEqual()
.
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB ASSIGN
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.
Examples are provided in the following programmatic environments:
PL/SQL: No example is provided with this release.
OCI: lequal.c
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): lequal.java
This section describes how to determine whether a LOB locator is initialized.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this use case.
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobLocatorIsInit()
.
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL): There is no applicable syntax reference for this use case.
C/C++ (Pro*C/C++) Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives". See C(OCI), OCILobLocatorIsInit()
.
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): There is no applicable syntax reference for this use case.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): No example is provided with this release.
OCI: linit.c
C (OCCI)): No example is provided with this release.
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.
This section describes how to write-append the contents of a buffer to a LOB.
Usage Notes
Note the following issues regarding usage of this API.
The writeappend
operation writes a buffer to the end of a LOB.
For OCI, the buffer can be written to the LOB in a single piece with this call; alternatively, it can be rendered piecewise using callbacks or a standard polling method.
Writing Piecewise: When to Use Callbacks or Polling
If the value of the piece parameter is OCI_FIRST_PIECE
, then data must be provided through callbacks or polling.
If a callback function is defined in the cbfp parameter, then this callback function will be called to get the next piece after a piece is written to the pipe. Each piece will be written from bufp.
If no callback function is defined, then OCILobWriteAppend2
() returns the OCI_NEED_DATA
error code. The application must call OCILobWriteAppend2
() again to write more pieces of the LOB. In this mode, the buffer pointer and the length can be different in each call if the pieces are of different sizes and from different locations. A piece value of OCI_LAST_PIECE
terminates the piecewise write.
Locking the Row Prior to Updating Prior to updating a LOB value using the PL/SQL DBMS_LOB
package or the OCI, you must lock the row containing the LOB. While the SQL INSERT
and UPDATE
statements implicitly lock the row, locking is done explicitly by means of an SQL SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs, or by using an OCI
pin
or lock
function in OCI programs.
For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — WRITEAPPEND
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobWriteAppend2()
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB WRITE APPEND.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB WRITE APPEND
Visual Basic (OO4O): No syntax reference is provided with this release.
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lwriteap.sql
OCI: lwriteap.c
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): lwriteap.java
This section describes how to write the contents of a buffer to a LOB.
Usage Notes
Note the following issues regarding usage of this API.
The most efficient way to write large amounts of LOB data is to use OCILobWrite2
() with the streaming mechanism enabled, and using polling or a callback. If you know how much data will be written to the LOB, then specify that amount when calling OCILobWrite2
(). This ensures that LOB data on the disk is contiguous. Apart from being spatially efficient, the contiguous structure of the LOB data will make for faster reads and writes in subsequent operations.
A chunk is one or more Oracle blocks. You can specify the chunk size for the LOB when creating the table that contains the LOB. This corresponds to the data size used by Oracle Database when accessing or modifying the LOB value. Part of the chunk is used to store system-related information and the rest stores the LOB value. The API you are using has a function that returns the amount of space used in the LOB chunk to store the LOB value. In PL/SQL use DBMS_LOB.GETCHUNKSIZE
. In OCI, use OCILobGetChunkSize()
.
Use a Multiple of the Returned Value to Improve Write Performance
You will improve performance if you run write requests using a multiple of the value returned by one of these functions. The reason for this is that the LOB chunk is versioned for every write
operation. If all writes
are done on a chunk basis, then no extra or excess versioning is incurred or duplicated. If it is appropriate for your application, then you should batch writes until you have enough for an entire chunk instead of issuing several LOB write calls that operate on the same LOB chunk.
Locking the Row Prior to Updating
Prior to updating a LOB value using the PL/SQL DBMS_LOB
Package or OCI, you must lock the row containing the LOB. While the SQL INSERT
and UPDATE
statements implicitly lock the row, locking is done explicitly by means of a SQL SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs, or by using an OCI
pin
or lock
function in OCI programs.
For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".
Using DBMS_LOB.WRITE to Write Data to a BLOB
When you are passing a hexadecimal string to DBMS_LOB.WRITE() to write data to a BLOB, use the following guidelines:
The amount
parameter should be <= the buffer length
parameter
The length
of the buffer should be ((amount
*2) - 1). This guideline exists because the two characters of the string are seen as one hexadecimal character (and an implicit hexadecimal-to-raw conversion takes place), that is, every two bytes of the string are converted to one raw byte.
The following example is correct:
declare blob_loc BLOB; rawbuf RAW(10); an_offset INTEGER := 1; an_amount BINARY_INTEGER := 10; BEGIN select blob_col into blob_loc from a_table where id = 1; rawbuf := '1234567890123456789'; dbms_lob.write(blob_loc, an_amount, an_offset, rawbuf); commit; END;
Replacing the value for an_amount
in the previous example with the following values, yields error message, ora_21560:
an_amount BINARY_INTEGER := 11;
or
an_amount BINARY_INTEGER := 19;
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — WRITE
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobWrite2().
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB WRITE.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB WRITE
Visual Basic (OO4O): (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > write, copyfromfile
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lwrite.sql
OCI: lwrite.c
Visual Basic (OO4O): lwrite.bas
Java (JDBC): lwrite.java
This section describes how to write LOB data for multiple locators in one round trip, using OCILobArrayWrite()
.
Usage Notes
LOB Array Write in Polling Mode
The following example writes 10Kbytes of data for each of 10 locators with a 1K buffer size. OCILobArrayWrite()
has to be called 100 (10*10 ) times to write all the data. The function is used in a similar manner to OCILobWrite2()
.
/* Fetch the locators */ ... /* array_iter parameter indicates the number of locators in the array read. * It is an IN parameter for the 1st call in polling and is ignored as IN * parameter for subsequent calls. As an OUT parameter it indicates the locator * index for which the piece is written. */ ub4 array_iter = 10; char *bufp[10]; oraub8 bufl[10]; oraub8 char_amtp[10]; oraub8 offset[10]; sword st; int i, j; for (i=0; i<10; i++) { bufp[i] = (char *)malloc(1000); bufl[i] = 1000; /* Fill bufp here. */ ... offset[i] = 1; char_amtp[i] = 10000; /* Single byte fixed width char set. */ } for (i = 1; i <= 10; i++) { /* Fill up bufp[i-1] here. The first piece for ith locator would be written from bufp[i-1] */ ... st = OCILobArrayWrite(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of write buffers */ bufl, /* array of buffer lengths */ OCI_FIRST_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); /* character set form */ for ( j = 2; j < 10; j++) { /* Fill up bufp[i-1] here. The jth piece for ith locator would be written from bufp[i-1] */ ... st = OCILobArrayWrite(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of write buffers */ bufl, /* array of buffer lengths */ OCI_NEXT_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); /* array_iter returns the index of the current array element for which * data is being written. for example, aray_iter = 1 implies first locator, * array_iter = 2 implies second locator and so on. Here i = array_iter. * * lob_array[ array_iter - 1] => Lob locator for which data is written. * bufp[array_iter - 1] => Buffer pointer from which data is written. * char_amtp[ array_iter - 1] => Number of characters written in * the piece just written */ } /* Fill up bufp[i-1] here. The last piece for ith locator would be written from bufp[i -1] */ ... st = OCILobArrayWrite(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of write buffers */ bufl, /* array of buffer lengths */ OCI_LAST_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); } ...
LOB Array Write with Callback
The following example writes 10Kbytes of data for each of 10 locators with a 1K buffer size. A total of 100 pieces need to be written (10 pieces for each locator). The first piece is provided by the OCILobArrayWrite()
call. The callback function is called 99 times to get the data for subsequent pieces to be written.
/* Fetch the locators */ ... ub4 array_iter = 10; char *bufp[10]; oraub8 bufl[10]; oraub8 char_amtp[10]; oraub8 offset[10]; sword st; for (i=0; i<10; i++) { bufp[i] = (char *)malloc(1000); bufl[i] = 1000; offset[i] = 1; char_amtp[i] = 10000; /* Single byte fixed width char set. */ } st = OCILobArrayWrite(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of write buffers */ bufl, /* array of buffer lengths */ OCI_FIRST_PIECE, /* piece information */ ctx, /* callback context */ cbk_write_lob /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); ... /* Callback function for LOB array write. */ sb4 cbk_write_lob(dvoid *ctxp, ub4 array_iter, dvoid *bufxp, oraub8 *lenp, ub1 *piecep, ub1 *changed_bufpp, oraub8 *changed_lenp) { static ub4 piece_count = 0; piece_count++; printf (" %dth piece written for %dth locator \n\n", piece_count, array_iter); /*-- 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 for current locator) *piecep = OCI_LAST_PIECE; else if (this is the first data buffer for the next locator) *piecep = OCI_FIRST_PIECE; piece_count = 0; else *piecep = OCI_NEXT_PIECE; return OCI_CONTINUE; } ...
Polling LOB Data in Array Write
The next example is polling LOB data in OCILobArrayWrite()
with variable amtp
, bufl
, and offset
.
/* Fetch the locators */ ... ub4 array_iter = 10; char *bufp[10]; oraub8 bufl[10]; oraub8 char_amtp[10]; oraub8 offset[10]; sword st; int i, j; int piece_count; for (i=0; i<10; i++) { bufp[i] = (char *)malloc(1000); bufl[i] = 1000; /* Fill bufp here. */ ... offset[i] = 1; char_amtp[i] = 10000; /* Single byte fixed width char set. */ } /* For 3rd locator write data in 500 bytes piece from offset 101. Amount * is 2000, that is, total number of pieces will be 2000/500 = 4. */ offset[2] = 101; bufl[2] = 500; char_amtp[2] = 2000; /* For 6th locator write data in 100 bytes piece from offset 51. Amount * is 0 indicating pure polling, that is, data will be written * till OCI_LAST_PIECE */ offset[5] = 51; bufl[5] = 100; char_amtp[5] = 0; /* For 8th locator write 100 bytes of data in one piece. Note amount * is less than buffer length indicating single piece write. */ offset[7] = 61; bufl[7] = 200; char_amtp[7] = 100; for (i = 1; i <= 10; i++) { /* Fill up bufp[i-1] here. The first piece for ith locator would be written from bufp[i-1] */ ... /* Calculate number of pieces need to be written */ piece_count = char_amtp[i-1]/bufl[i-1]; /* Single piece case */ if (char_amtp[i-1] <= bufl[i-1]) piece_count = 1; /* Zero amount indicates pure polling. So we can write as many * pieces as needed. Let us write 50 pieces. */ if (char_amtp[i-1] == 0) piece_count = 50; st = OCILobArrayWrite(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of write buffers */ bufl, /* array of buffer lengths */ OCI_FIRST_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); /* character set form */ for ( j = 2; j < piece_count; j++) { /* Fill up bufp[i-1] here. The jth piece for ith locator would be written * from bufp[i-1] */ ... st = OCILobArrayWrite(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of write buffers */ bufl, /* array of buffer lengths */ OCI_NEXT_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); /* array_iter returns the index of the current array element for which * data is being written. for example, aray_iter = 1 implies first locator, * array_iter = 2 implies second locator and so on. Here i = array_iter. * * lob_array[ array_iter - 1] => Lob locator for which data is written. * bufp[array_iter - 1] => Buffer pointer from which data is written. * char_amtp[ array_iter - 1] => Number of characters written in * the piece just written */ } /* Fill up bufp[i-1] here. The last piece for ith locator would be written from * bufp[i -1] */ ... /* If piece_count is 1 it is a single piece write. */ if (piece_count[i] != 1) st = OCILobArrayWrite(<service context>, <error handle>, &array_iter, /* array size */ lob_array, /* array of locators */ NULL, /* array of byte amounts */ char_amtp, /* array of char amounts */ offset, /* array of offsets */ (void **)bufp, /* array of write buffers */ bufl, /* array of buffer lengths */ OCI_LAST_PIECE, /* piece information */ NULL, /* callback context */ NULL, /* callback function */ 0, /* character set ID - default */ SQLCS_IMPLICIT); } ...
Syntax
Use the following syntax references for the OCI programmatic environment:
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobArrayWrite()
.
Example
An example is provided in the following programmatic environment:
OCI: lwritearr.c
This section describes how to trim a LOB to the size you specify.
Usage Notes
Note the following issues regarding usage of this API.
Locking the Row Prior to Updating
Prior to updating a LOB value using the PL/SQL DBMS_LOB
Package, or OCI, you must lock the row containing the LOB. While the SQL INSERT
and UPDATE
statements implicitly lock the row, locking is done explicitly by means of:
A SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs.
An OCI
pin
or lock
function in OCI programs.
For more details on the state of the locator after an update, refer to "Example of Updating LOBs Through Updated Locators".
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — TRIM
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobTrim2().
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB TRIM.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" — LOB TRIM
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > trim
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference Chapter 7, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.
Examples
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): ltrim.sql
OCI: ltrim.c
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): ltrim.bas
Java (JDBC): ltrim.java
This section describes how to erase part of a LOB.
Usage Notes
Note the following issues regarding usage of this API.
Locking the Row Prior to Updating
Prior to updating a LOB value using the PL/SQL DBMS_LOB
Package or OCI, you must lock the row containing the LOB. While INSERT
and UPDATE
statements implicitly lock the row, locking is done explicitly by means of a SELECT
FOR
UPDATE
statement in SQL and PL/SQL programs, or by using the OCI
pin
or lock
function in OCI programs.
For more details on the state of the locator after an update, refer to"Example of Updating LOBs Through Updated Locators".
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBM_LOB Package): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — ERASE
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobErase2().
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB ERASE.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" — LOB ERASE
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > erase
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference, "Working With LOBs" — Creating and Populating a BLOB or CLOB Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): lerase.sql
OCI: lerase.c
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): lerase.bas
Java (JDBC): lerase.java
This section describes how to enable LOB buffering.
Usage Notes
Enable LOB buffering when you are performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.
For more information, refer to "LOB Buffering Subsystem".
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL: This API is not available in any supplied PL/SQL packages.
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCIEnableLobBuffering(), OCIDisableLobBuffering(), OCIFlushBuffer().
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB ENABLE BUFFERING.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL and Precompiler Directives" — LOB ENABLE BUFFERING
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > EnableBuffering
Java (JDBC): There is no applicable syntax reference for this use case.
Examples are provided in the following programmatic environments:
PL/SQL: No example is provided.
C (OCI): No example is provided with this release. Using this API is similar to that described in the example, "Disabling LOB Buffering".
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): lenbuf.bas
Java (JDBC): No example provided.
This section describes how to flush the LOB buffer.
Usage Notes
Enable buffering when performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.
Notes:
|
For more information, refer to "LOB Buffering Subsystem".
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this use case.
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCIEnableLobBuffering(), OCIDisableLobBuffering(), OCIFlushBuffer().
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB FLUSH BUFFER.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB FLUSH BUFFER.
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > FlushBuffer.
Java (JDBC): There is no applicable syntax reference for this use case.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): No example is provided with this release.
C (OCI): No example is provided with this release. Using this API is similar to that described in the example, "Disabling LOB Buffering".
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): No example is provided with this release.
Java (JDBC): No example is provided with this release.
This section describes how to disable LOB buffering.
Usage Notes
Enable buffering when performing a small read or write of data. Once you have completed these tasks, you must disable buffering before you can continue with any other LOB operations.
Note:
|
For more information, refer to"LOB Buffering Subsystem"
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB Package): There is no applicable syntax reference for this use case.
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCIEnableLobBuffering()
, OCIDisableLobBuffering()
, OCIFlushBuffer()
.
C++ (OCCI): Oracle C++ Call Interface Programmer's Guide
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB DISABLE BUFFER.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB DISABLE BUFFER
Visual Basic (OO4O) (Oracle Objects for OLE (OO4O) Online Help): From Help Topics, Contents tab, select OO4O Automation Server > OBJECTS > Orablob > METHODS > DisableBuffering
Java (JDBC): There is no applicable syntax reference for this use case.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): No example is provided with this release.
OCI: ldisbuf.c
C++ (OCCI): No example is provided with this release.
Visual Basic (OO4O): ldisbuf.bas
Java (JDBC): No example is provided with this release.
This section describes how to determine whether a LOB instance is temporary.
Syntax
Use the following syntax references for each programmatic environment:
PL/SQL (DBMS_LOB): Oracle Database PL/SQL Packages and Types Reference "DBMS_LOB" — ISTEMPORARY, FREETEMPORARY
C (OCI): Oracle Call Interface Programmer's Guide "LOB Functions" — OCILobIsTemporary().
COBOL (Pro*COBOL) Pro*COBOL Programmer's Guide for information on LOBs, usage notes on LOB statements, and embedded SQL and precompiler directives — LOB DESCRIBE, ISTEMPORARY.
C/C++ (Pro*C/C++): Pro*C/C++ Programmer's Guide Appendix F, "Embedded SQL Statements and Directives" — LOB DESCRIBE...ISTEMPORARY
Visual Basic (OO4O): There is no applicable syntax reference for this use case.
Java (JDBC): Oracle Database JDBC Developer's Guide and Reference, "Working With LOBs" — Creating and Populating a BLOB
or CLOB
Column.
Examples are provided in the following programmatic environments:
PL/SQL (DBMS_LOB Package): listemp.sql
OCI: listemp.c
Visual Basic (OO4O): No example is provided.
To see if a BLOB is temporary, the JDBC application can either use the isTemporary
instance method to determine whether the current BLOB object is temporary, or pass the BLOB object to the static isTemporary
method to determine whether the specified BLOB object is temporary. These two methods are defined inlistempb.java
.
This JDBC API replaces previous workarounds that use DBMS_LOB.isTemporary().
To determine whether a CLOB is temporary, the JDBC application can either use the isTemporary
instance method to determine whether the current CLOB object is temporary, or pass the CLOB object to the static isTemporary method. These two methods are defined in listempc.java
.
You can convert a BLOB
instance to a CLOB
using the PL/SQL procedure DBMS_LOB.CONVERTTOCLOB
. This technique is convenient if you have character data stored in binary format that you want to store in a CLOB
. You specify the character set of the binary data when calling this procedure. See Oracle Database PL/SQL Packages and Types Reference for details on syntax and usage of this procedure.
You can convert a CLOB
instance to a BLOB
instance using the PL/SQL procedure DBMS_LOB.CONVERTTOBLOB
. This technique is a convenient way to convert character data to binary data using LOB APIs. See Oracle Database PL/SQL Packages and Types Reference for details on syntax and usage of this procedure.