Skip Headers
Oracle® Database SecureFiles and Large Objects Developer's Guide
11g Release 1 (11.1)

Part Number B28393-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

7 Overview of Supplied LOB APIs

This chapter contains these topics:

Programmatic Environments That Support LOBs

Table 7-1 lists the programmatic environments that support LOB functionality.

Comparing the LOB Interfaces

Table 7-2 and Table 7-3 compare the eight LOB programmatic interfaces by listing their functions and methods used to operate on LOBs. The tables are split in two simply to accommodate all eight interfaces. The functionality of the interfaces, with regards to LOBs, is described in the following sections.

Table 7-2 Comparing the LOB Interfaces, 1 of 2

PL/SQL: DBMS_LOB (dbmslob.sql) C (OCI)(ociap.h) C++ (OCCI) (occiData.h). Also for Clob and Bfile classes. Pro*C/C++ and Pro*COBOL

DBMS_LOB.COMPARE

N/A

N/A

N/A

DBMS_LOB.INSTR

N/A

N/A

N/A

DBMS_LOB.SUBSTR

N/A

N/A

N/A

DBMS_LOB.APPEND

OCILobAppend()

Blob.append()

APPEND

N/A (use PL/SQL assign operator)

OCILobAssign()

 


ASSIGN

N/A

OCILobCharSetForm()

Clob.getCharsetForm (CLOB only)

N/A

N/A

OCILobCharSetId()

Clob.getCharsetId()

(CLOB only)

N/A

DBMS_LOB.CLOSE

OCILobClose()

Blob.close()

CLOSE

N/A

N/A

Clob.closeStream()

N/A

DBMS_LOB.COPY

OCILobCopy2()

Blob.copy()

COPY

N/A

OCILobDisableBuffering()

N/A

DISABLE BUFFERING

N/A

OCILobEnableBuffering()

N/A

ENABLE BUFFERING

DBMS_LOB.ERASE

OCILobErase2()

N/A

ERASE

DBMS_LOB.FILECLOSE

OCILobFileClose()

Clob.close()

CLOSE

DBMS_LOB.FILECLOSEALL

OCILobFileCloseAll()

N/A

FILE CLOSE ALL

DBMS_LOB.FILEEXISTS

OCILobFileExist()

Bfile.fileExists()

DESCRIBE [FILEEXISTS]

DBMS_LOB.GETCHUNKSIZE

OCILobGetChunkSize()

Blob.getChunkSize()

DESCRIBE [CHUNKSIZE]

DBMS_LOB.GET_STORAGE_LIMIT

OCILobGetStorageLimit()

N/A

N/A

DBMS_LOB.GETOPTIONS

OCILobGetOptions()

Blob/Clob::getOptions

N/A

DBMS_LOB.FILEGETNAME

OCILobFileGetName()

Bfile.getFileName() and Bfile.getDirAlias()

DESCRIBE [DIRECTORY, FILENAME]

DBMS_LOB.FILEISOPEN

OCILobFileIsOpen()

Bfile.isOpen()

DESCRIBE [ISOPEN]

DBMS_LOB.FILEOPEN

OCILobFileOpen()

Bfile.open()

OPEN

N/A (use BFILENAME operator)

OCILobFileSetName()

Bfile.setName()

FILE SET

N/A

OCILobFlushBuffer()

N/A

FLUSH BUFFER

DBMS_LOB.GETLENGTH

OCILobGetLength2()

Blob.length()

DESCRIBE [LENGTH]

N/A

OCILobIsEqual()

Use operator = ( )=/!=

N/A

DBMS_LOB.ISOPEN

OCILobIsOpen()

Blob.isOpen()

DESCRIBE [ISOPEN]

DBMS_LOB.LOADFROMFILE

OCILobLoadFromFile2()

Use the overloadedcopy() method.

LOAD FROM FILE

N/A

OCILobLocatorIsInit()

Clob.isinitialized()

N/A

DBMS_LOB.OPEN

OCILobOpen()

Blob.open

OPEN

DBMS_LOB.READ

OCILobRead()

Blob.read

READ

DBMS_LOB.SETOPTIONS

OCILobSetOptions()

Blob/Clob::setOptions

N/A

DBMS_LOB.TRIM

OCILobTrim2()

Blob.trim

TRIM

DBMS_LOB.WRITE

OCILobWrite2

Blob.write

WRITEORALOB.

DBMS_LOB.WRITEAPPEND

OCILobWriteAppend2()

N/A

WRITE APPEND

DBMS_LOB.CREATETEMPORARY

OCILobCreateTemporary()

N/A

N/A

DBMS_LOB.FREETEMPORARY

OCILobFreeTemporary()

N/A

N/A

DBMS_LOB.ISTEMPORARY

OCILobIsTemporary()

N/A

N/A

N/A

OCILobLocatorAssign()

use operator = ( ) or copy constructor

N/A


Table 7-3 Comparing the LOB Interfaces, 2 of 2

PL/SQL: DBMS_LOB (dbmslob.sql) COM (OO4O) Java (JDBC ODP.NET

DBMS_LOB.COMPARE

ORALOB.Compare

Use DBMS_LOB.

OracleClob.Compare

DBMS_LOB.INSTR

ORALOB.Matchpos

position

OracleClob.Search

DBMS_LOB.SUBSTR

N/A

getBytes for BLOBs or BFILEs

getSubString for CLOBs

N/A

DBMS_LOB.APPEND

ORALOB.Append

Use length and then putBytes or PutString

OracleClob.Append

OCILobAssign()

ORALOB.Clone

N/A [use equal sign]

OracleClob.Clone

OCILobCharSetForm()

N/A

N/A

N/A

OCILobCharSetId()

N/A

N/A

N/A

DBMS_LOB.CLOSE

N/A

use DBMS_LOB.

OracleClob.Close

DBMS_LOB.COPY

ORALOB.Copy

Use read and write

OracleClob.CopyTo

OCILobDisableBuffering()

ORALOB.DisableBuffering

N/A

N/A

OCILobEnableBuffering()

ORALOB.EnableBuffering

N/A

N/A

DBMS_LOB.ERASE

ORALOB.Erase

Use DBMS_LOB.

OracleClob.Erase

DBMS_LOB.FILECLOSE

ORABFILE.Close

closeFile

OracleBFile.CloseFile

DBMS_LOB.FILECLOSEALL

ORABFILE.CloseAll

Use DBMS_LOB.

N/A

DBMS_LOB.FILEEXISTS

ORABFILE.Exist

fileExists

OracleBFile.FileExists

DBMS_LOB.GETCHUNKSIZE

N/A

getChunkSize

OracleClob.OptimumChunkSize

DBMS_LOB.FILEGETNAME

ORABFILE.DirectoryName

ORABFILE.FileName

getDirAlias

getName

OracleBFile.DirectoryName

Oracle.BFile.FileName

DBMS_LOB.FILEISOPEN

ORABFILE.IsOpen

Use DBMS_LOB.ISOPEN

OracleBFile.IsOpen

DBMS_LOB.FILEOPEN

ORABFILE.Open

openFile

OracleBFile.OpenFile

OCILobFileSetName()

DirectoryName

FileName

Use BFILENAME

OracleBFile.DirectoryName

Oracle.BFile.FileName

OCILobFlushBuffer()

ORALOB.FlushBuffer

N/A

N/A

DBMS_LOB.GETLENGTH

ORALOB.Size

length

OracleClob.Length

N/A

N/A

equals

N/A

DBMS_LOB.ISOPEN

ORALOB.IsOpen

use DBMS_LOB. IsOpen

OracleClob.IsInChunkWriteMode

DBMS_LOB.LOADFROMFILE

ORALOB.

CopyFromBfile

Use read and then write

N/A

DBMS_LOB.OPEN

ORALOB.open

Use DBMS_LOB.

OracleClob.BeginChunkWrite

DBMS_LOB.READ

ORALOB.Read

BLOB or BFILE: getBytes and getBinaryStream

CLOB: getString and getSubString and getCharacterStream

OracleClob.Read

DBMS_LOB.TRIM

ORALOB.Trim

Use DBMS_LOB.

OracleClob.SetLength

DBMS_LOB.WRITE

ORALOB.Write

BLOB: setBytes and setBinaryStream

CLOB: setString and setCharacterStream

OracleClob.Write

DBMS_LOB.WRITEAPPEND

N/A

Use length and then putString or putBytes

OracleClob.Append

DBMS_LOB.CREATETEMPORARY

N/A

N/A

OracleClob constructors

DBMS_LOB.FREETEMPORARY

N/A

N/A

OracleClob.Dispose

DBMS_LOB.ISTEMPORARY

N/A

N/A

OracleClob.IsTemporary


Using PL/SQL (DBMS_LOB Package) to Work With LOBs

The PL/SQL DBMS_LOB package can be used for the following operations:

Provide a LOB Locator Before Running the DBMS_LOB Routine

As described in more detail in the following, DBMS_LOB routines work based on LOB locators. For the successful completion of DBMS_LOB routines, you must provide an input locator representing a LOB that exists in the database tablespaces or external file system, before you call the routine.

  • Persistent LOBs: First use SQL to define tables that contain LOB columns, and subsequently you can use SQL to initialize or populate the locators in these LOB columns.

  • External LOBs: Define a DIRECTORY object that maps to a valid physical directory containing the external LOBs that you intend to access. These files must exist, and have READ permission for Oracle Server to process. If your operating system uses case-sensitive path names, then specify the directory in the correct case. See "Directory Objects" for more information.

Once the LOBs are defined and created, you may then SELECT a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to DBMS_LOB for access to the LOB value.

Examples provided with each DBMS_LOB routine will illustrate this in the following sections.

Guidelines for Offset and Amount Parameters in DBMS_LOB Operations

The following guidelines apply to offset and amount parameters used in procedures in the DBMS_LOB PL/SQL package:

  • For character data—in all formats, fixed-width and varying-width—the amount and offset parameters are in characters. This applies to operations on CLOB and NCLOB datatypes.

  • For binary data, the offset and amount parameters are in bytes. This applies to operations on BLOB datatypes.

  • When using the following procedures:

    • DBMS_LOB.LOADFROMFILE

    • DBMS_LOB.LOADBLOBFROMFILE

    • DBMS_LOB.LOADCLOBFROMFILE

    you cannot specify an amount parameter with a value larger than the size of the BFILE you are loading from. To load the entire BFILE with these procedures, you must specify either the exact size of the BFILE, or the maximum allowable storage limit.

  • When using DBMS_LOB.READ, the amount parameter can be larger than the size of the data. The amount should be less than or equal to the size of the buffer. The buffer size is limited to 32K.

Determining Character Set ID

To determine the character set ID, you must know the character set name (a user can select from the V$NLS_VALID_VALUES view, which lists the names of the character sets that are valid as database and national character sets). Then call the function NLS_CHARSET_ID with the desired character set name as the one string argument. The character set ID is returned as an integer. UTF16 does not work because it has no character set name. Use character set ID = 1000 for UTF16. Although UTF16 is not allowed as a database or national character set, the APIs in DBMS_LOB support it for database conversion purposes. DBMS_LOB.LOADCLOBFROMFILE and other procedures in DBMS_LOB take character set ID, not character set name, as an input.

See Also:

PL/SQL Functions and Procedures for LOBs

PL/SQL functions and procedures that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are summarized in the following:

  • To modify persistent LOB values, see Table 7-4

  • To read or examine LOB values, see Table 7-5

  • To create, free, or check on temporary LOBs, see Table 7-6

  • For read-only functions on external LOBs (BFILEs), see Table 7-7

  • To open or close a LOB, or check if LOB is open, see Table 7-8

PL/SQL Functions and Procedures to Modify LOB Values

Here is a table of DBMS_LOB procedures:

Table 7-4 PL/SQL: DBMS_LOB Procedures to Modify LOB Values

Function/Procedure Description

APPEND

Appends the LOB value to another LOB

CONVERTTOBLOB

Converts a CLOB to a BLOB

CONVERTTOCLOB

Converts a BLOB to a CLOB

COPY

Copies all or part of a LOB to another LOB

ERASE

Erases part of a LOB, starting at a specified offset

FRAGMENT_DELETE

Delete the data from the LOB at the given offset for the given length

FRAGMENT_INSERT

Insert the given data (< 32KBytes) into the LOB at the given offset

FRAGMENT_MOVE

Move the given amount of bytes from the given offset to the new given offset

FRAGMENT_REPLACE

Replace the data at the given offset with the given data (< 32kBytes)

LOADFROMFILE

Load BFILE data into a persistent LOB

LOADCLOBFROMFILE

Load character data from a file into a LOB

LOADBLOBFROMFILE

Load binary data from a file into a LOB

SETOPTIONS

Sets LOB features (deduplication and compression)

TRIM

Trims the LOB value to the specified shorter length

WRITE

Writes data to the LOB at a specified offset

WRITEAPPEND

Writes data to the end of the LOB


PL/SQL Functions and Procedures for Introspection of LOBs

Table 7-5 PL/SQL: DBMS_LOB Procedures to Read or Examine Internal and External LOB values

Function/Procedure Description

COMPARE

Compares the value of two LOBs

GETCHUNKSIZE

Gets the chunk size used when reading and writing. This only works on persistent LOBs and does not apply to external LOBs (BFILEs).

GETLENGTH

Gets the length of the LOB value.

GETOPTIONS

Returns options (deduplication, compression, encryption) for SECUREFILE LOBs.

GET_STORAGE_LIMIT

Gets the LOB storage limit for the database configuration.

INSTR

Returns the matching position of the nth occurrence of the pattern in the LOB.

READ

Reads data from the LOB starting at the specified offset.

SETOPTIONS

Sets options (deduplication and compression) for a SECUREFILE LOB. overriding the default LOB column settings. Incurs a server round trip.

SUBSTR

Returns part of the LOB value starting at the specified offset.


PL/SQL Operations on Temporary LOBs

Table 7-6 PL/SQL: DBMS_LOB Procedures to Operate on Temporary LOBs

Function/Procedure Description

CREATETEMPORARY

Creates a temporary LOB

ISTEMPORARY

Checks if a LOB locator refers to a temporary LOB

FREETEMPORARY

Frees a temporary LOB


PL/SQL Read-Only Functions and Procedures for BFILEs

Table 7-7 PL/SQL: DBMS_LOB Read-Only Procedures for BFILEs

Function/Procedure Description

FILECLOSE

Closes the file. Use CLOSE() instead.

FILECLOSEALL

Closes all previously opened files

FILEEXISTS

Checks if the file exists on the server

FILEGETNAME

Gets the directory object name and file name

FILEISOPEN

Checks if the file was opened using the input BFILE locators. Use ISOPEN() instead.

FILEOPEN

Opens a file. Use OPEN() instead.


PL/SQL Functions and Procedures to Open and Close Internal and External LOBs

Table 7-8 PL/SQL: DBMS_LOB Procedures to Open and Close Internal and External LOBs

Function/Procedure Description

OPEN

Opens a LOB

ISOPEN

Sees if a LOB is open

CLOSE

Closes a LOB


These procedures are described in detail for specific LOB operations, such as, INSERT a row containing a LOB, in "Opening Persistent LOBs with the OPEN and CLOSE Interfaces".

Using OCI to Work With LOBs

Oracle Call Interface (OCI) LOB APIs enable you to access and make changes to LOBs and read data from BFILEs in C. OCI functions for LOBs are discussed in greater detail later in this section.

See Also:

Oracle Call Interface Programmer's Guide chapter "LOB and BFILE Operations" for the details of all topics discussed in this and the following section

Prefetching of LOB Data, Length, and Chunk Size

To improve OCI access of smaller LOBs, LOB data can be prefetched and cached while also fetching the locator. This applies to internal LOBs, temporary LOBs, and BFILEs.

Setting the CSID Parameter for OCI LOB APIs

If you want to read or write data in 2 byte Uunicode (UCS2) format, then set the csid (character set ID) parameter in OCILobRead2() and OCILobWrite2() to OCI_UCS2ID. The csid parameter indicates the character set id for the buffer parameter. You can set the csid parameter to any character set ID. If the csid parameter is set, then it will override the NLS_LANG environment variable.

See Also:

Fixed-Width and Varying-Width Character Set Rules for OCI

In OCI, for fixed-width client-side character sets, the following rules apply:

  • CLOBs and NCLOBs: offset and amount parameters are always in characters

  • BLOBs and BFILEs: offset and amount parameters are always in bytes

The following rules apply only to varying-width client-side character sets:

  • Offset parameter: Regardless of whether the client-side character set is varying-width, the offset parameter is always as follows:

    • CLOBs and NCLOBs: in characters

    • BLOBs and BFILEs: in bytes

  • Amount parameter: The amount parameter is always as follows:

    • When referring to a server-side LOB: in characters

    • When referring to a client-side buffer: in bytes

  • OCILobFileGetLength(): Regardless of whether the client-side character set is varying-width, the output length is as follows:

    • CLOBs and NCLOBs: in characters

    • BLOBs and BFILEs: in bytes

  • OCILobRead2(): With client-side character set of varying-width, CLOBs and NCLOBs:

    • Input amount is in characters. Input amount refers to the number of characters to read from the server-side CLOB or NCLOB.

    • Output amount is in bytes. Output amount indicates how many bytes were read into the buffer bufp.

  • OCILobWrite2(): With client-side character set of varying-width, CLOBs and NCLOBs:

    • Input amount is in bytes. The input amount refers to the number of bytes of data in the input buffer bufp.

    • Output amount is in characters. The output amount refers to the number of characters written into the server-side CLOB or NCLOB.

Other Operations

For all other LOB operations, irrespective of the client-side character set, the amount parameter is in characters for CLOBs and NCLOBs. These include OCILobCopy2(), OCILobErase2(), OCILobLoadFromFile2(), and OCILobTrim2(). All these operations refer to the amount of LOB data on the server.

NCLOBs in OCI

NCLOBs are allowed as parameters in methods.

OCILobLoadFromFile2() Amount Parameter

When using OCILobLoadFromFile2() you cannot specify amount larger than the length of the BFILE. To load the entire BFILE, you can pass the value returned by OCILobGetStorageLimit().

OCILobRead2() Amount Parameter

To read to the end of a LOB using OCILobRead2(), you specify an amount equal to the value returned by OCILobGetStorageLimit(). See "Reading Data from a LOB" for more information.

OCILobLocator Pointer Assignment

Special care must be taken when assigning OCILobLocator pointers in an OCI program—using the "=" assignment operator. Pointer assignments create a shallow copy of the LOB. After the pointer assignment, the source and target LOBs point to the same copy of data.

These semantics are different from using LOB APIs, such as OCILobAssign() or OCILobLocatorAssign() to perform assignments. When the these APIs are used, the locators logically point to independent copies of data after assignment.

For temporary LOBs, before performing pointer assignments, you must ensure that any temporary LOB in the target LOB locator is freed by calling OCIFreeTemporary(). In contrast, when OCILobLocatorAssign() is used, the original temporary LOB in the target LOB locator variable, if any, is freed automatically before the assignment happens.

LOB Locators in Defines and Out-Bind Variables in OCI

Before you reuse a LOB locator in a define or an out-bind variable in a SQL statement, you must free any temporary LOB in the existing LOB locator buffer using OCIFreeTemporary().

OCI Functions That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

OCI functions that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are as follows:

  • To modify persistent LOBs, see Table 7-9

  • To read or examine LOB values, see Table 7-10

  • To create or free temporary LOB, or check if Temporary LOB exists, see Table 7-11

  • For read only functions on external LOBs (BFILEs), see Table 7-12

  • To operate on LOB locators, see Table 7-13

  • For LOB buffering, see Table 7-14

  • To open and close LOBs, see Table 7-15

OCI Functions to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

Table 7-9 OCI Functions to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

Function/Procedure Description

OCILobAppend()

Appends LOB value to another LOB.

OCILobArrayWrite()

Writes data using multiple locators in one round trip.

OCILobCopy2()

Copies all or part of a LOB to another LOB.

OCILobErase2()

Erases part of a LOB, starting at a specified offset.

OCILobLoadFromFile2()

Loads BFILE data into a persistent LOB.

OCILObSetOptions()

Enables option settings (deduplication and compression) for a SECUREFILE LOB.

OCILobTrim2()

Truncates a LOB.

OCILobWrite2()

Writes data from a buffer into a LOB, overwriting existing data.

OCILobWriteAppend2()

Writes data from a buffer to the end of the LOB.


OCI Functions to Read or Examine Persistent LOB and External LOB (BFILE) Values

Table 7-10 OCI Functions to Read or Examine persistent LOB and external LOB (BFILE) Values

Function/Procedure Description

OCILobArrayRead()

Reads data using multiple locators in one round trip.

OCILobGetChunkSize()

Gets the chunk size used when reading and writing. This works on persistent LOBs and does not apply to external LOBs (BFILEs).

OCILobGetLength2()

Returns the length of a LOB or a BFILE.

OCILObGetOptions()

Obtains the enabled settings (deduplication, compression, encryption) for a given SECUREFILE LOB.

OCILobGetStorageLimit()

Gets the maximum length of an internal LOB.

OCILobRead2()

Reads a specified portion of a non-NULL LOB or a BFILE into a buffer.


OCI Functions for Temporary LOBs

Table 7-11 OCI Functions for Temporary LOBs

Function/Procedure Description

OCILobCreateTemporary()

Creates a temporary LOB.

OCILobIsTemporary()

Sees if a temporary LOB exists.

OCILobFreeTemporary()

Frees a temporary LOB.


OCI Read-Only Functions for BFILEs

Table 7-12 OCI Read-Only Functions for BFILES

Function/Procedure Description

OCILobFileClose()

Closes an open BFILE.

OCILobFileCloseAll()

Closes all open BFILEs.

OCILobFileExists()

Checks whether a BFILE exists.

OCILobFileGetName()

Returns the name of a BFILE.

OCILobFileIsOpen()

Checks whether a BFILE is open.

OCILobFileOpen()

Opens a BFILE.


OCI LOB Locator Functions

Table 7-13 OCI LOB-Locator Functions

Function/Procedure Description

OCILobAssign()

Assigns one LOB locator to another.

OCILobCharSetForm()

Returns the character set form of a LOB.

OCILobCharSetId()

Returns the character set ID of a LOB.

OCILobFileSetName()

Sets the name of a BFILE in a locator.

OCILobIsEqual()

Checks whether two LOB locators refer to the same LOB.

OCILobLocatorIsInit()

Checks whether a LOB locator is initialized.


OCI LOB-Buffering Functions

Table 7-14 OCI LOB-Buffering Functions

Function/Procedure Description

OCILobDisableBuffering()

Disables the buffering subsystem use.

OCILobEnableBuffering()

Uses the LOB buffering subsystem for subsequent reads and writes of LOB data.

OCILobFlushBuffer()

Flushes changes made to the LOB buffering subsystem to the database (server).


OCI Functions to Open and Close Internal and External LOBs

Table 7-15 OCI Functions to Open and Close Internal and External LOBs

Function/Procedure Description

OCILobOpen()

Opens a LOB.

OCILobIsOpen()

Sees if a LOB is open.

OCILobClose()

Closes a LOB.


OCI LOB Examples

Further OCI examples are provided in:

See also Appendix B, "OCI Demonstration Programs" in Oracle Call Interface Programmer's Guide, for further OCI demonstration script listings.

Further Information About OCI

For further information and features of OCI, refer to the OTN Web site, http://www.oracle.com/technology/ for OCI features and frequently asked questions.

Using C++ (OCCI) to Work With LOBs

Oracle C++ Call Interface (OCCI) is a C++ API for manipulating data in an Oracle database. OCCI is organized as an easy-to-use set of C++ classes that enable a C++ program to connect to a database, run SQL statements, insert/update values in database tables, retrieve results of a query, run stored procedures in the database, and access metadata of database schema objects. OCCI also provides a seamless interface to manipulate objects of user-defined types as C++ class instances.

Oracle C++ Call Interface (OCCI) is designed so that you can use OCI and OCCI together to build applications.

The OCCI API provides the following advantages over JDBC and ODBC:

You can use OCCI to make changes to an entire persistent LOB, or to pieces of the beginning, middle, or end of it, as follows:

OCCI Classes for LOBs

OCCI provides the following classes that allow you to use different types of LOB instances as objects in your C++ application:

  • Clob class to access and modify data stored in internal CLOBs and NCLOBs

  • Blob class to access and modify data stored in internal BLOBs

  • Bfile class to access and read data stored in external LOBs (BFILEs)

    See Also:

    Syntax information on these classes and details on OCCI in general is available in the Oracle C++ Call Interface Programmer's Guide.

Clob Class

The Clob driver implements a CLOB object using an SQL LOB locator. This means that a CLOB object contains a logical pointer to the SQL CLOB data rather than the data itself.

The CLOB interface provides methods for getting the length of an SQL CLOB value, for materializing a CLOB value on the client, and getting a substring. Methods in the ResultSet and Statement interfaces such as getClob() and setClob() allow you to access SQL CLOB values.

See Also:

Oracle C++ Call Interface Programmer's Guide for detailed information on the Clob class.

Blob Class

Methods in the ResultSet and Statement interfaces, such as getBlob() and setBlob(), allow you to access SQL BLOB values. The Blob interface provides methods for getting the length of a SQL BLOB value, for materializing a BLOB value on the client, and for extracting a part of the BLOB.

See Also:

Bfile Class

The Bfile class enables you to instantiate a Bfile object in your C++ application. You must then use methods of the Bfile class, such as the setName() method, to initialize the Bfile object which associates the object properties with an object of type BFILE in a BFILE column of the database.

See Also:

Oracle C++ Call Interface Programmer's Guide for detailed information on the Blob class methods and details on instantiating and initializing an Blob object in your C++ application.

Fixed-Width Character Set Rules

In OCCI, for fixed-width client-side character sets, the following rules apply:

  • Clob: offset and amount parameters are always in characters

  • Blob: offset and amount parameters are always in bytes

  • Bfile: offset and amount parameters are always in bytes

Varying-Width Character Set Rules

The following rules apply only to varying-width client-side character sets:

  • Offset parameter: Regardless of whether the client-side character set is varying-width, the offset parameter is always as follows:

    • Clob(): in characters

    • Blob(): in bytes

    • Bfile(): in bytes

  • Amount parameter: The amount parameter is always as follows:

    • Clob: in characters, when referring to a server-side LOB

    • Blob: in bytes, when referring to a client-side buffer

    • Bfile: in bytes, when referring to a client-side buffer

  • length(): Regardless of whether the client-side character set is varying-width, the output length is as follows:

    • Clob.length(): in characters

    • Blob.length(): in bytes

    • Bfile.length(): in bytes

  • Clob.read() and Blob.read(): With client-side character set of varying-width, CLOBs and NCLOBs:

    • Input amount is in characters. Input amount refers to the number of characters to read from the server-side CLOB or NCLOB.

    • Output amount is in bytes. Output amount indicates how many bytes were read into the OCCI buffer parameter, buffer.

  • Clob.write() and Blob.write(): With client-side character set of varying-width, CLOBs and NCLOBs:

    • Input amount is in bytes. Input amount refers to the number of bytes of data in the OCCI input buffer, buffer.

    • Output amount is in characters. Output amount refers to the number of characters written into the server-side CLOB or NCLOB.

Offset and Amount Parameters for Other OCCI Operations

For all other OCCI LOB operations, irrespective of the client-side character set, the amount parameter is in characters for CLOBs and NCLOBs. These include the following:

  • Clob.copy()

  • Clob.erase()

  • Clob.trim()

  • For LoadFromFile functionality, overloaded Clob.copy()

All these operations refer to the amount of LOB data on the server.

NCLOBs in OCCI

  • NCLOB instances are allowed as parameters in methods

  • NCLOB instances are allowed as attributes in object types.

Amount Parameter for OCCI LOB copy() Methods

The copy() method on Clob and Blob enables you to load data from a BFILE. You can pass one of the following values for the amount parameter to this method:

  • An amount smaller than the size of the BFILE to load a portion of the data

  • An amount equal to the size of the BFILE to load all of the data

  • The UB8MAXVAL constant to load all of the BFILE data

You cannot specify an amount larger than the length of the BFILE.

Amount Parameter for OCCI read() Operations

The read() method on an Clob, Blob, or Bfile object, reads data from a BFILE. You can pass one of the following values for the amount parameter to specify the amount of data to read:

  • An amount smaller than the size of the BFILE to load a portion of the data

  • An amount equal to the size of the BFILE to load all of the data

  • 0 (zero) to read until the end of the BFILE in streaming mode

You cannot specify an amount larger than the length of the BFILE.

Further Information About OCCI

See Also:

OCCI Methods That Operate on BLOBs, BLOBs, NCLOBs, and BFILEs

OCCI methods that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are as follows:

OCCI Methods to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

Table 7-16 OCCI Clob and Blob Methods to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values

Function/Procedure Description

Blob/Clob.append()

Appends CLOB or BLOB value to another LOB.

Blob/Clob.copy()

Copies all or part of a CLOB or BLOB to another LOB.

Blob/Clob.copy()

Loads BFILE data into a persistent LOB.

Blob/Clob.trim()

Truncates a CLOB or BLOB.

Blob/Clob.write()

Writes data from a buffer into a LOB, overwriting existing data.


OCCI Methods to Read or Examine Persistent LOB and BFILE Values

Table 7-17 OCCI Blob/Clob/Bfile Methods to Read or Examine persistent LOB and BFILE Values

Function/Procedure Description

Blob/Clob.getChunkSize()

Gets the chunk size used when reading and writing. This works on persistent LOBs and does not apply to external LOBs (BFILEs).

Blob/Clob.getOptions()

Obtains settings for existing and newly created LOBs.

Blob/Clob.length()

Returns the length of a LOB or a BFILE.

Blob/Clob.read()

Reads a specified portion of a non-NULL LOB or a BFILE into a buffer.

Blob/Clob.setOptions()

Enables LOB settings for existing and newly created LOBs.


OCCI Read-Only Methods for BFILEs

Table 7-18 OCCI Read-Only Methods for BFILES

Function/Procedure Description

Bfile.close()

Closes an open BFILE.

Bfile.fileExists()

Checks whether a BFILE exists.

Bfile.getFileName()

Returns the name of a BFILE.

Bfile.getDirAlias()

Gets the directory object name.

Bfile.isOpen()

Checks whether a BFILE is open.

Bfile.open()

Opens a BFILE.


Other OCCI LOB Methods

Table 7-19 Other OCCI LOB Methods

Methods Description

Clob/Blob/Bfile.operator=()

Assigns one LOB locator to another. Use = or the copy constructor.

Clob.getCharSetForm()

Returns the character set form of a LOB.

Clob.getCharSetId()

Returns the character set ID of a LOB.

Bfile.setName()

Sets the name of a BFILE.

Clob/Blob/Bfile.operator==()

Checks whether two LOB refer to the same LOB.

Clob/Blob/Bfile.isInitialized()

Checks whether a LOB is initialized.


OCCI Methods to Open and Close Internal and External LOBs

Table 7-20 OCCI Methods to Open and Close Internal and External LOBs

Function/Procedure Description

Clob/Blob/Bfile.Open()

Opens a LOB

Clob/Blob/Bfile.isOpen()

Sees if a LOB is open

Clob/Blob/Bfile.Close()

Closes a LOB


Using C/C++ (Pro*C) to Work With LOBs

You can make changes to an entire persistent LOB, or to pieces of the beginning, middle or end of a LOB by using embedded SQL. You can access both internal and external LOBs for read purposes, and you can write to persistent LOBs.

Embedded SQL statements allow you to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These statements are listed in the following tables, and are discussed in greater detail later in the chapter.

See Also:

Pro*C/C++ Programmer's Guide for detailed documentation, including syntax, host variables, host variable types and example code.

First Provide an Allocated Input Locator Pointer That Represents LOB

Unlike locators in PL/SQL, locators in Pro*C/C++ are mapped to locator pointers which are then used to refer to the LOB or BFILE value.

To successfully complete an embedded SQL LOB statement you must do the following:

  1. Provide an allocated input locator pointer that represents a LOB that exists in the database tablespaces or external file system before you run the statement.

  2. SELECT a LOB locator into a LOB locator pointer variable

  3. Use this variable in the embedded SQL LOB statement to access and manipulate the LOB value

Pro*C/C++ Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

Pro*C/C++ statements that operate on BLOBs, CLOBs, and NCLOBs are listed in the following tables:

  • To modify persistent LOBs, see Table 7-21

  • To read or examine LOB values, see Table 7-22

  • To create or free temporary LOB, or check if Temporary LOB exists, see Table 7-23

  • To operate close and 'see if file exists' functions on BFILEs, see Table 7-24

  • To operate on LOB locators, see Table 7-25

  • For LOB buffering, see Table 7-26

  • To open or close LOBs or BFILEs, see Table 7-27

Pro*C/C++ Embedded SQL Statements to Modify Persistent LOB Values

Table 7-21 Pro*C/C++: Embedded SQL Statements to Modify Persistent LOB Values

Statement Description

APPEND

Appends a LOB value to another LOB.

COPY

Copies all or a part of a LOB into another LOB.

ERASE

Erases part of a LOB, starting at a specified offset.

LOAD FROM FILE

Loads BFILE data into a persistent LOB at a specified offset.

TRIM

Truncates a LOB.

WRITE

Writes data from a buffer into a LOB at a specified offset.

WRITE APPEND

Writes data from a buffer into a LOB at the end of the LOB.


Pro*C/C++ Embedded SQL Statements for Introspection of LOBs

Table 7-22 Pro*C/C++: Embedded SQL Statements for Introspection of LOBs

Statement Description

DESCRIBE [CHUNKSIZE]

Gets the chunk size used when writing. This works for persistent LOBs only. It does not apply to external LOBs (BFILEs).

DESCRIBE [LENGTH]

Returns the length of a LOB or a BFILE.

READ

reads a specified portion of a non-NULL LOB or a BFILE into a buffer.


Pro*C/C++ Embedded SQL Statements for Temporary LOBs

Table 7-23 Pro*C/C++: Embedded SQL Statements for Temporary LOBs

Statement Description

CREATE TEMPORARY

Creates a temporary LOB.

DESCRIBE [ISTEMPORARY]

Sees if a LOB locator refers to a temporary LOB.

FREE TEMPORARY

Frees a temporary LOB.


Pro*C/C++ Embedded SQL Statements for BFILEs

Table 7-24 Pro*C/C++: Embedded SQL Statements for BFILES

Statement Description

FILE CLOSE ALL

Closes all open BFILEs.

DESCRIBE [FILEEXISTS]

Checks whether a BFILE exists.

DESCRIBE [DIRECTORY,FILENAME]

Returns the directory object name and filename of a BFILE.


Pro*C/C++ Embedded SQL Statements for LOB Locators

Table 7-25 Pro*C/C++ Embedded SQL Statements for LOB Locators

Statement Description

ASSIGN

Assigns one LOB locator to another.

FILE SET

Sets the directory object name and filename of a BFILE in a locator.


Pro*C/C++ Embedded SQL Statements for LOB Buffering

Table 7-26 Pro*C/C++ Embedded SQL Statements for LOB Buffering

Statement Description

DISABLE BUFFERING

Disables the use of the buffering subsystem.

ENABLE BUFFERING

Uses the LOB buffering subsystem for subsequent reads and writes of LOB data.

FLUSH BUFFER

Flushes changes made to the LOB buffering subsystem to the database (server)


Pro*C/C++ Embedded SQL Statements to Open and Close LOBs

Table 7-27 Pro*C/C++ Embedded SQL Statements to Open and Close Persistent LOBs and External LOBs (BFILEs)

Statement Description

OPEN

Opens a LOB or BFILE.

DESCRIBE [ISOPEN]

Sees if a LOB or BFILE is open.

CLOSE

Closes a LOB or BFILE.


Using COBOL (Pro*COBOL) to Work With LOBs

You can make changes to an entire persistent LOB, or to pieces of the beginning, middle or end of it by using embedded SQL. You can access both internal and external LOBs for read purposes, and you can also write to persistent LOBs.

Embedded SQL statements allow you to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These statements are listed in the following tables, and are discussed in greater detail later in the manual.

First Provide an Allocated Input Locator Pointer That Represents LOB

Unlike locators in PL/SQL, locators in Pro*COBOL are mapped to locator pointers which are then used to refer to the LOB or BFILE value. For the successful completion of an embedded SQL LOB statement you must perform the following:

  1. Provide an allocated input locator pointer that represents a LOB that exists in the database tablespaces or external file system before you run the statement.

  2. SELECT a LOB locator into a LOB locator pointer variable

  3. Use this variable in an embedded SQL LOB statement to access and manipulate the LOB value.

Where the Pro*COBOL interface does not supply the required functionality, you can call OCI using C. Such an example is not provided here because such programs are operating system dependent.

See Also:

Pro*COBOL Programmer's Guide for detailed documentation, including syntax, host variables, host variable types, and example code.

Pro*COBOL Statements That Operate on BLOBs, CLOBs, NCLOBs, and BFILEs

The following Pro*COBOL statements operate on BLOBs, CLOBs, NCLOBs, and BFILEs:

  • To modify persistent LOBs, see Table 7-28

  • To read or examine internal and external LOB values, see Table 7-29

  • To create or free temporary LOB, or check LOB locator, see Table 7-30

  • To operate close and 'see if file exists' functions on BFILEs, see Table 7-31

  • To operate on LOB locators, see Table 7-32

  • For LOB buffering, see Table 7-33

  • To open or close persistent LOBs or BFILEs, see Table 7-34

Pro*COBOL Embedded SQL Statements to Modify Persistent LOB Values

Table 7-28 Pro*COBOL Embedded SQL Statements to Modify LOB Values

Statement Description

APPEND

Appends a LOB value to another LOB.

COPY

Copies all or part of a LOB into another LOB.

ERASE

Erases part of a LOB, starting at a specified offset.

LOAD FROM FILE

Loads BFILE data into a persistent LOB at a specified offset.

TRIM

Truncates a LOB.

WRITE

Writes data from a buffer into a LOB at a specified offset

WRITE APPEND

Writes data from a buffer into a LOB at the end of the LOB.


Pro*COBOL Embedded SQL Statements for Introspection of LOBs

Table 7-29 Pro*COBOL Embedded SQL Statements for Introspection of LOBs

Statement Description

DESCRIBE [CHUNKSIZE]

Gets the Chunk size used when writing.

DESCRIBE [LENGTH]

Returns the length of a LOB or a BFILE.

READ

Reads a specified portion of a non-NULL LOB or a BFILE into a buffer.


Pro*COBOL Embedded SQL Statements for Temporary LOBs

Table 7-30 Pro*COBOL Embedded SQL Statements for Temporary LOBs

Statement Description

CREATE TEMPORARY

Creates a temporary LOB.

DESCRIBE [ISTEMPORARY]

Sees if a LOB locator refers to a temporary LOB.

FREE TEMPORARY

Frees a temporary LOB.


Pro*COBOL Embedded SQL Statements for BFILEs

Table 7-31 Pro*COBOL Embedded SQL Statements for BFILES

Statement Description

FILE CLOSE ALL

Closes all open BFILEs.

DESCRIBE [FILEEXISTS]

Checks whether a BFILE exists.

DESCRIBE [DIRECTORY, FILENAME]

Returns the directory object name and filename of a BFILE.


Pro*COBOL Embedded SQL Statements for LOB Locators

Table 7-32 Pro*COBOL Embedded SQL Statements for LOB Locator Statements

Statement Description

ASSIGN

Assigns one LOB locator to another.

FILE SET

Sets the directory object name and filename of a BFILE in a locator.


Pro*COBOL Embedded SQL Statements for LOB Buffering

Table 7-33 Pro*COBOL Embedded SQL Statements for LOB Buffering

Statement Description

DISABLE BUFFERING

Disables the use of the buffering subsystem.

ENABLE BUFFERING

Uses the LOB buffering subsystem for subsequent reads and writes of LOB data.

FLUSH BUFFER

Flushes changes made to the LOB buffering subsystem to the database (server)


Pro*COBOL Embedded SQL Statements for Opening and Closing LOBs and BFILEs

Table 7-34 Pro*COBOL Embedded SQL Statements for Opening and Closing Persistent LOBs and BFILEs

Statement Description

OPEN

Opens a LOB or BFILE.

DESCRIBE [ISOPEN]

Sees if a LOB or BFILE is open.

CLOSE

Closes a LOB or BFILE.


Using COM (Oracle Objects for OLE) to Work With LOBs

Oracle Objects for OLE (OO4O) is a set of programmable COM objects that simplifies the development of applications designed to communicate with an Oracle Database. OO4O offers high performance database access. It also provides easy access to features unique to Oracle, yet otherwise cumbersome or inefficient to use from other ODBC or OLE DB-based components, such as ADO.

You can make changes to an entire persistent LOB, or to pieces of the beginning, middle or end of it, with the Oracle Objects for OLE (OO4O) API, by using one of the following objects interfaces:

OO4O Syntax Reference

The OO4O syntax reference and further information is viewed from the OO4O online help. Oracle Objects for OLE (OO4O), is a Windows-based product included with the database.

OraBlob, OraClob, and OraBfile Object Interfaces Encapsulate Locators

These interfaces encapsulate LOB locators, so you do not deal directly with locators, but instead, can use methods and properties provided to perform operations and get state information.

OraBlob and OraClob Objects Are Retrieved as Part of Dynaset

When OraBlob and OraClob objects are retrieved as a part of a dynaset, these objects represent LOB locators of the dynaset current row. If the dynaset current row changes due to a move operation, then the OraBlob and OraClob objects represent the LOB locator for the new current row.

Use the Clone Method to Retain Locator Independent of the Dynaset Move

To retain the LOB locator of the OraBlob and OraClob object independent of the dynaset move operation, use the Clone method. This method returns the OraBlob and OraClob object. You can also use these objects as PL/SQL bind parameters.

Example of OraBlob and OraBfile

The following example shows usage of OraBlob and OraBfile.

Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraSoundClone as OraBlob, OraMyBfile as OraBFile

OraConnection.BeginTrans
set OraDyn = OraDb.CreateDynaset("select * from print_media order by product_id", ORADYN_DEFAULT)
set OraSound1 = OraDyn.Fields("Sound").value
set OraSoundClone = OraSound1

OraParameters.Add "id", 1,ORAPARAM_INPUT
OraParameters.Add "mybfile", Empty,ORAPARAM_OUTPUT
OraParameters("mybfile").ServerType = ORATYPE_BFILE

OraDatabase.ExecuteSQL ("begin  GetBFile(:id, :mybfile ") end")

Set OraMyBFile = OraParameters("mybfile").value
'Go to Next row
OraDyn.MoveNext

OraDyn.Edit
'Lets update OraSound1 data with that from the BFILE
OraSound1.CopyFromBFile  OraMyBFile
OraDyn.Update

OraDyn.MoveNext
'Go to Next row
OraDyn.Edit
'Lets update OraSound1 by appending with LOB data from 1st row represented by 
'OraSoundClone
OraSound1.Append  OraSoundClone
OraDyn.Update

OraConnection.CommitTrans

In the preceding example:

OraSound1 — represents the locator for the current row in the dynaset OraSoundClone — represents the locator for the 1st row.

A change in the current row (say a OraDyn.MoveNext) means the following:

OraSound1 — will represent the locator for the 2nd row.

OraSoundClone — will represent the locator in the 1st row. OraSoundClone only refers the locator for the 1st row irrespective of any OraDyn row navigation).

OraMyBFile — refers to the locator obtained from an PL/SQL "OUT" parameter as a result of executing a PL/SQL procedure, either by doing an OraDatabase.ExecuteSQL.

Note:

A LOB obtained by executing SQL is only valid for the duration of the transaction. For this reason, "BEGINTRANS" and "COMMITTRANS" are used to specify the duration of the transaction.

OO4O Methods and Properties to Access Data Stored in LOBs

Oracle Objects for OLE (OO4O) includes methods and properties that you can use to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs.

See Also:

The OO4O online help for detailed information including parameters, parameter types, return values, and example code. Oracle Objects for OLE (OO4O), a Windows-based product included with the database, has no manuals, only online help. The OO4O online help is available through the Application Development submenu of the database installation.

The following OO4O methods and properties operate on BLOBs, CLOBs, NCLOBs, and BFILEs:

  • To modify persistent LOBs, see Table 7-35

  • To read or examine internal and external LOB values, see Table 7-36

  • To open and close BFILEs, see Table 7-37

  • For LOB buffering, see Table 7-38

  • Properties such as to see if LOB is NULL, or to get or set polling amount, see Table 7-39

  • For read-only BFILE methods, see Table 7-40

  • For BFILE properties, see Table 7-41

OO4O Methods to Modify BLOB, CLOB, and NCLOB Values

Table 7-35 OO4O Methods to Modify BLOB, CLOB, and NCLOB Values

Methods Description

OraBlob.Append

OraClob.Append

Appends BLOB value to another LOB.

Appends CLOB or NCLOB value to another LOB.

OraBlob.Copy

OraClob.Copy

Copies a portion of a BLOB into another LOB

Copies a portion of a CLOB or NCLOB into another LOB

OraBlob.Erase

OraClob.Erase

Erases part of a BLOB, starting at a specified offset

Erases part of a CLOB or NCLOB, starting at a specified offset

OraBlob.CopyFromBFile

OraClob.CopyFromBFile

Loads BFILE data into an internal BLOB

Loads BFILE data into an internal CLOB or NCLOB

OraBlob.Trim

OraClob.Trim

Truncates a BLOB

Truncates a CLOB or NCLOB

OraBlob.CopyFromFile

OraClob.CopyFromFile

Writes data from a file to a BLOB

Writes data from a file to a CLOB or NCLOB

OraBlob.Write

OraClob.Write

Writes data to the BLOB

Writes data to the CLOB or NCLOB


OO4O Methods to Read or Examine Internal and External LOB Values

Table 7-36 OO4O Methods to Read or Examine Internal and External LOB Values

Function/Procedure Description

OraBlob.Read

OraClob.Read

OraBFile.Read

Reads a specified portion of a non-NULL BLOB into a buffer

Reads a specified portion of a non-NULL CLOB into a buffer

Reads a specified portion of a non-NULL BFILE into a buffer

OraBlob.CopyToFile

OraClob.CopyToFile

Reads a specified portion of a non-NULL BLOB to a file

Reads a specified portion of a non-NULL CLOB to a file


OO4O Methods to Open and Close External LOBs (BFILEs)

Table 7-37 OO4O Methods to Open and Close External LOBs (BFILEs)

Method Description

OraBFile.Open

Opens BFILE.

OraBFile.Close

Closes BFILE.


OO4O Methods for Persistent LOB Buffering

Table 7-38 OO4O Methods for Persistent LOB Buffering

Method Description

OraBlob.FlushBuffer

OraClob.FlushBuffer

Flushes changes made to the BLOB buffering subsystem to the database

Flushes changes made to the CLOB buffering subsystem to the database

OraBlob.EnableBuffering

OraClob.EnableBuffering

Enables buffering of BLOB operations

Enables buffering of CLOB operations

OraBlob.DisableBuffering

OraClob.DisableBuffering

Disables buffering of BLOB operations

Disables buffering of CLOB operations


OO4O Properties for Operating on LOBs

Table 7-39 OO4O Properties for Operating on LOBs

Property Description

IsNull (Read)

Indicates when a LOB is NULL

PollingAmount(Read/Write)

Gets/Sets total amount for Read/Write polling operation

Offset(Read/Write)

Gets/Sets offset for Read/Write operation. By default, it is set to 1.

Status(Read)

Returns the polling status. Possible values are

  • ORALOB_NEED_DATA There is more data to be read or written

  • ORALOB_NO_DATA There is no more data to be read or written

  • ORALOB_SUCCESS_LOB data read/written successfully

Size(Read)

Returns the length of the LOB data


OO4O Read-Only Methods for External Lobs (BFILEs)

Table 7-40 OO4O Read-Only Methods for External LOBs (BFILEs)

Methods Description

OraBFile.Close

Closes an open BFILE

OraBFile.CloseAll

Closes all open BFILEs

OraBFile.Open

Opens a BFILE

OraBFile.IsOpen

Determines if a BFILE is open


OO4O Properties for Operating on External LOBs (BFILEs)

Table 7-41 OO4O Properties for Operating on External LOBs (BFILEs)

Property Description

OraBFile.DirectoryName

Gets/Sets the server side directory object name.

OraBFile.FileName(Read/Write)

Gets/Sets the server side filename.

OraBFile.Exists

Checks whether a BFILE exists.


Using Java (JDBC) to Work With LOBs

You can perform the following tasks on LOBs with Java (JDBC):

Modifying Internal Persistent LOBs Using Java

You can make changes to an entire persistent LOB, or to pieces of the beginning, middle, or end of a persistent LOB in Java by means of the JDBC API using the classes:

  • oracle.sql.BLOB

  • oracle.sql.CLOB

These classes implement java.sql.Blob and java.sql.Clob interfaces according to the JDBC 3.0 specification, which has methods for LOB modification. They also include legacy Oracle proprietary methods for LOB modification. These legacy methods are marked as deprecated and will be removed in a future release.

Starting in Oracle release 11.1 the minimum supported version of the JDK is JDK5. JDK6 will also be supported when it is released by Sun Microsystems, Inc. To use JDK5, place ojdbc5.jar in your CLASSPATH. To use JDK6, place ojdbc6.jar in your CLASSPATH. ojdbc5.jar supports the JDBC 3.0 specification and ojdbc6.jar supports the JDBC4.0 specification which is new with JDK6.

Oracle recommends that you discontinue use of the deprecated proprietary APIs described in Table 7-42. They will be removed in a future release.

Table 7-42 BLOB Method Equivalents

Oracle Proprietary Method (Deprecated) JDBC 3.0 Standard Method Replacement

putBytes(long pos, byte [] bytes)

setBytes(long pos, byte[] bytes)

putBytes(long pos, byte [] bytes, int length)

setBytes(long pos, byte[] bytes, int offset, int len)

getBinaryOutputStream(long pos)

setBinaryStream(long pos)

trim (long len)

truncate(long len)


Table 7-43 CLOB Method Equivalents

Oracle Proprietary Method (Deprecated) JDBC 3.0 Standard Method Replacement

putString(long pos, String str)

setString(long pos, String str)

N/A

setString(long pos, String str, int offset, int len)

getAsciiOutputStream(long pos)

setAsciiStream(long pos)

getCharacterOutputStream(long pos)

setCharacterStream(long pos)

trim (long len)

truncate(long len)


Reading Internal Persistent LOBs and External LOBs (BFILEs) With Java

With JDBC you can use Java to read both internal persistent LOBs and external LOBs (BFILEs).

BLOB, CLOB, and BFILE Classes

  • BLOB and CLOB Classes. In JDBC theses classes provide methods for performing operations on large objects in the database including BLOB and CLOB data types.

  • BFILE Class. In JDBC this class provides methods for performing operations on BFILE data in the database.

The BLOB, CLOB, and BFILE classes encapsulate LOB locators, so you do not deal with locators but instead use methods and properties provided to perform operations and get state information.

Calling DBMS_LOB Package from Java (JDBC)

Any LOB functionality not provided by these classes can be accessed by a call to the PL/SQL DBMS_LOB package. This technique is used repeatedly in the examples throughout this manual.

Referencing LOBs Using Java (JDBC)

You can get a reference to any of the preceding LOBs in the following two ways:

  • As a column of an OracleResultSet

  • As an OUT type PL/SQL parameter from an OraclePreparedStatement

Using OracleResultSet: BLOB and CLOB Objects Retrieved

When BLOB and CLOB objects are retrieved as a part of an OracleResultSet, these objects represent LOB locators of the currently selected row.

If the current row changes due to a move operation, for example, rset.next(), then the retrieved locator still refers to the original LOB row.

To retrieve the locator for the most current row, you must call getBLOB(), getCLOB(), or getBFILE() on the OracleResultSet each time a move operation is made depending on whether the instance is a BLOB, CLOB or BFILE.

JDBC Syntax References and Further Information

For further JDBC syntax and information about using JDBC with LOBs:

See Also:

JDBC Methods for Operating on LOBs

The following JDBC methods operate on BLOBs, CLOBs, and BFILEs:

JDBC oracle.sql.BLOB Methods to Modify BLOB Values

Table 7-44 JDBC oracle.sql.BLOB Methods To Modify BLOB Values

Method Description

int setBytes(long, byte[])

Inserts the byte array into the BLOB, starting at the given offset


JDBC oracle.sql.BLOB Methods to Read or Examine BLOB Values

Table 7-45 JDBC oracle.sql.BLOB Methods to Read or Examine BLOB Values

Method Description

byte[] getBytes(long, int)

Gets the contents of the LOB as an array of bytes, given an offset

long position(byte[],long)

Finds the given byte array within the LOB, given an offset

long position(Blob,long)

Finds the given BLOB within the LOB

public boolean equals(java.lang.Object)

Compares this LOB with another. Compares the LOB locators.

public long length()

Returns the length of the LOB

public int getChunkSize()

Returns the ChunkSize of the LOB


JDBC oracle.sql.BLOB Methods and Properties for BLOB Buffering

Table 7-46 JDBC oracle.sql.BLOB Methods and Properties for BLOB Buffering

Method Description

public java.io.InputStream getBinaryStream())

Streams the LOB as a binary stream

public java.io.OutputStream setBinaryStream()

Retrieves a stream that can be used to write to the BLOB value that this Blob object represents


JDBC oracle.sql.CLOB Methods to Modify CLOB Values

Table 7-47 JDBC oracle.sql.CLOB Methods to Modify CLOB Values

Method Description

int setString(long, java.lang.String)

JDBC 3.0: Writes the given Java String to the CLOB value that this Clob object designates at the position pos.

int putChars(long, char[])

Inserts the character array into the LOB, starting at the given offset


JDBC oracle.sql.CLOB Methods to Read or Examine CLOB Value

Table 7-48 JDBC oracle.sql.CLOB Methods to Read or Examine CLOB Values

Method Description

java.lang.String getSubString(long, int)

Returns a substring of the LOB as a string

int getChars(long, int, char[])

Reads a subset of the LOB into a character array

long position(java.lang.String, long)

Finds the given String within the LOB, given an offset

long position(oracle.jdbc2.Clob, long)

Finds the given CLOB within the LOB, given an offset

long length()

Returns the length of the LOB

int getChunkSize()

Returns the ChunkSize of the LOB


JDBC oracle.sql.CLOB Methods and Properties for CLOB Buffering

Table 7-49 JDBC oracle.sql.CLOB Methods and Properties for CLOB Buffering

Method Description

java.io.InputStream getAsciiStream()

Implements the Clob interface method. Gets the CLOB value designated by this Clob object as a stream of ASCII bytes

java.io.OutputStream setAsciiStream(long pos)

JDBC 3.0: Retrieves a stream to be used to write ASCII characters to the CLOB value that this Clob object represents, starting at position pos

java.io.Reader getCharacterStream()

Reads the CLOB as a character stream

java.io.Writer setCharacterStream(long pos)

JDBC 3.0: Retrieves a stream to be used to write Unicode characters to the CLOB value that this Clob object represents, starting at position pos


JDBC oracle.sql.BFILE Methods to Read or Examine External LOB (BFILE) Values

Table 7-50 JDBC oracle.sql.BFILE Methods to Read or Examine External LOB (BFILE) Values

Method Description

byte[] getBytes(long, int)

Gets the contents of the BFILE as an array of bytes, given an offset

int getBytes(long, int, byte[])

Reads a subset of the BFILE into a byte array

long position(oracle.sql.BFILE, long)

Finds the first appearance of the given BFILE contents within the LOB, from the given offset

long position(byte[], long)

Finds the first appearance of the given byte array within the BFILE, from the given offset

long length()

Returns the length of the BFILE

boolean fileExists()

Checks if the operating system file referenced by this BFILE exists

public void openFile()

Opens the operating system file referenced by this BFILE

public void closeFile()

Closes the operating system file referenced by this BFILE

public boolean isFileOpen()

Checks if this BFILE is already open

public java.lang.String getDirAlias()

Gets the directory object name for this BFILE

public java.lang.String getName()

Gets the file name referenced by this BFILE


JDBC oracle.sql.BFILE Methods and Properties for BFILE Buffering

Table 7-51 JDBC oracle.sql.BFILE Methods and Properties for BFILE Buffering

Method Description

public java.io.InputStream getBinaryStream()

Reads the BFILE as a binary stream


JDBC Temporary LOB APIs

Oracle Database JDBC drivers contain APIs to create and close temporary LOBs. These APIs can replace workarounds that use the following procedures from the DBMS_LOB PL/SQL package in prior releases:

  • DBMS_LOB.createTemporary()

  • DBMS_LOB.isTemporary()

  • DBMS_LOB.freeTemporary()

Table 7-52 JDBC: Temporary BLOB APIs

Methods Description

public static BLOB createTemporary(Connection conn,

boolean cache, int duration) throws SQLException

Creates a temporary BLOB

public static boolean isTemporary(BLOB blob)

throws SQLException

Checks if the specified BLOB locator refers to a temporary BLOB

public boolean isTemporary() throws SQLException

Checks if the current BLOB locator refers to a temporary BLOB

public static void freeTemporary(BLOB temp_blob)

throws SQLException

Frees the specified temporary BLOB

public void freeTemporary() throws SQLException

Frees the temporary BLOB


Table 7-53 JDBC: Temporary CLOB APIs

Methods Description

public static CLOB createTemporary(Connection conn,

boolean cache, int duration) throws SQLException

Creates a temporary CLOB

public static boolean isTemporary(CLOB clob)

throws SQLException

Checks if the specified CLOB locator refers to a temporary CLOB

public boolean isTemporary() throws SQLException

Checks if the current CLOB locator refers to a temporary CLOB

public static void freeTemporary(CLOB temp_clob)

throws SQLException

Frees the specified temporary CLOB

public void freeTemporary() throws SQLException

Frees the temporary CLOB


JDBC: Opening and Closing LOBs

oracle.sql.CLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob interface. Table 7-53 lists the Oracle extension APIs in oracle.sql.CLOB for accessing temporary CLOBs.

Oracle Database JDBC drivers contain APIs to explicitly open and close LOBs. These APIs replace previous techniques that use DBMS_LOB.open() and DBMS_LOB.close().

JDBC: Opening and Closing BLOBs

oracle.sql.BLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Blob interface. Table 7-54 lists the Oracle extension APIs in oracle.sql.BLOB that open and close BLOBs.

Table 7-54 JDBC: Opening and Closing BLOBs

Methods Description

public void open(int mode) throws SQLException

Opens the BLOB

public boolean isOpen() throws SQLException

Sees if the BLOB is open

public void close() throws SQLException

Closes the BLOB


Opening the BLOB Using JDBC

To open a BLOB, your JDBC application can use the open method as defined in oracle.sql.BLOB class as follows:

/** 
 * Open a BLOB in the indicated mode. Valid modes include MODE_READONLY,
 * and MODE_READWRITE. It is an error to open the same LOB twice. 
 */ 
public void open (int mode) throws SQLException

Possible values of the mode parameter are:

public static final int MODE_READONLY 
public static final int MODE_READWRITE 

Each call to open opens the BLOB. For example:

BLOB blob = ... 
blob.open (BLOB.MODE_READWRITE);

Checking If the BLOB Is Open Using JDBC

To see if a BLOB is opened, 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 ();

Closing the BLOB Using JDBC

To close a BLOB, your JDBC application can use the close method defined in oracle.sql.BLOB. The close API is defined as follows:

/** 
  * Close a previously opened BLOB. 
  */ 
public void close () throws SQLException

The usage example is:

BLOB blob = ... 
// close the BLOB 
blob.close ();

JDBC: Opening and Closing CLOBs

Class oracle.sql.CLOB is the Oracle JDBC driver implementation of the standard JDBC java.sql.Clob interface. Table 7-55 lists the Oracle extension APIs in oracle.sql.CLOB to open and close CLOBs.

Table 7-55 JDBC: Opening and Closing CLOBs

Methods Description

public void open(int mode) throws SQLException

Open the CLOB

public boolean isOpen() throws SQLException

See if the CLOB is opened

public void close() throws SQLException

Close the CLOB


Opening the CLOB Using JDBC

To open a CLOB, your JDBC application can use the open method defined in oracle.sql.CLOB class as follows:

/** 
 * Open a CLOB in the indicated mode. Valid modes include MODE_READONLY,
 * and MODE_READWRITE. It is an error to open the same LOB twice. 
 */ 
public void open (int mode) throws SQLException

The possible values of the mode parameter are:

public static final int MODE_READONLY 
public static final int MODE_READWRITE 

Each call to open opens the CLOB. For example,

CLOB clob = ... 
clob.open (CLOB.MODE_READWRITE);

Checking If the CLOB Is Open Using JDBC

To see if a CLOB is opened, 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 ();

Closing the CLOB Using JDBC

To close a CLOB, the JDBC application can use the close method defined in oracle.sql.CLOB. The close API is defined as follows:

/** 
* Close a previously opened CLOB. 
*/ 
public void close () throws SQLException

The usage example is:

CLOB clob = ... 
// close the CLOB 
clob.close ();

JDBC: Opening and Closing BFILEs

oracle.sql.BFILE class wraps the database BFILE object. Table 7-56 lists the Oracle extension APIs in oracle.sql.BFILE for opening and closing BFILEs.

Table 7-56 JDBC API Extensions for Opening and Closing BFILEs

Methods Description

public void open() throws SQLException

Opens the BFILE

public void open(int mode) throws SQLException

Opens the BFILE

public boolean isOpen() throws SQLException

Checks if the BFILE is open

public void close() throws SQLException

Closes the BFILE


Opening BFILEs

To open a BFILE, your JDBC application can use the OPEN method defined in oracle.sql.BFILE class as follows:

/** 
 * Open a external LOB in the read-only mode. It is an error 
 * to open the same LOB twice. 
 */ 
public void open () throws SQLException 

/** 
 * Open a external LOB in the indicated mode. Valid modes include 
 * MODE_READONLY only. It is an error to open the same 
 * LOB twice. 
 */ 
public void open (int mode) throws SQLException

The only possible value of the mode parameter is:

public static final int MODE_READONLY 

Each call to open opens the BFILE. For example,

BFILE bfile = ... 
bfile.open ();

Checking If the BFILE Is Open

To see if a BFILE is opened, your JDBC application can use the isOpen method defined in oracle.sql.BFILE. The return Boolean value indicates whether the BFILE has been previously opened or not. The isOpen method is defined as follows:

/** 
 * Check whether the BFILE is opened. 
 * @return true if the LOB is opened. 
 */ 
public boolean isOpen () throws SQLException

The usage example is:

BFILE bfile = ... 
// See if the BFILE is opened 
boolean isOpen = bfile.isOpen ();

Closing the BFILE

To close a BFILE, your JDBC application can use the close method defined in oracle.sql.BFILE. The close API is defined as follows:

/** 
 * Close a previously opened BFILE. 
*/ 
public void close () throws SQLException

The usage example is --

BFILE bfile = ... 
// close the BFILE 
bfile.close ();

Usage Example (OpenCloseLob.java)

/* 
 * This sample shows how to open/close BLOB and CLOB. 
 */ 

// You need to import the java.sql package to use JDBC 
import java.sql.*; 

// You need to import the oracle.sql package to use oracle.sql.BLOB 
import oracle.sql.*; 

class OpenCloseLob 
{ 
  public static void main (String args []) 
       throws SQLException 
  { 
    // Load the Oracle JDBC driver 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); 

    String url = "jdbc:oracle:oci8:@"; 
    try { 
      String url1 = System.getProperty("JDBC_URL"); 
      if (url1 != null) 
        url = url1; 
    } catch (Exception e) { 
      // If there is any security exception, ignore it 
      // and use the default 
    } 

    // Connect to the database 
    Connection conn = 
      DriverManager.getConnection (url, "scott", "tiger"); 
    // It is faster when auto commit is off 
    conn.setAutoCommit (false); 

    // Create a Statement 
    Statement stmt = conn.createStatement (); 

    try 
    { 
      stmt.execute ("drop table basic_lob_table"); 
    } 
    catch (SQLException e) 
    { 
      // An exception could be raised here if the table did not exist already. 
    } 

// Create a table containing a BLOB and a CLOB 
stmt.execute ("create table basic_lob_table (x varchar2 (30), b blob, c clob)"); 

// Populate the table 
stmt.execute (
    "insert into basic_lob_table values"
    + " ('one', '010101010101010101010101010101', 'onetwothreefour')"); 

    // Select the lobs 
    ResultSet rset = stmt.executeQuery ("select * from basic_lob_table"); 
    while (rset.next ()) 
    { 
      // Get the lobs 
      BLOB blob = (BLOB) rset.getObject (2); 
      CLOB clob = (CLOB) rset.getObject (3); 

      // Open the lobs 
      System.out.println ("Open the lobs"); 
      blob.open (BLOB.MODE_READWRITE); 
      clob.open (CLOB.MODE_READWRITE); 

      // Check if the lobs are opened 
      System.out.println ("blob.isOpen()="+blob.isOpen()); 
      System.out.println ("clob.isOpen()="+clob.isOpen()); 

      // Close the lobs 
      System.out.println ("Close the lobs"); 
      blob.close (); 
      clob.close (); 

      // Check if the lobs are opened 
      System.out.println ("blob.isOpen()="+blob.isOpen()); 
      System.out.println ("clob.isOpen()="+clob.isOpen()); 
    } 

    // Close the ResultSet 
    rset.close (); 

    // Close the Statement 
    stmt.close (); 

    // Close the connection 
    conn.close (); 
  } 
} 

Truncating LOBs Using JDBC

Oracle Database JDBC drivers contain APIs to truncate persistent LOBs. These APIs replace previous techniques that used DBMS_LOB.trim().

JDBC: Truncating BLOBs

oracle.sql.BLOB class is Oracle JDBC driver implementation of the standard JDBC java.sql.Blob interface. Table 7-57 lists the Oracle extension API in oracle.sql.BLOB that truncates BLOBs.

Table 7-57 JDBC: Truncating BLOBs

Methods Description

public void truncate(long newlen) throws SQLException

Truncates the BLOB


The truncate API is defined as follows:

/** 
*Truncate the value of the BLOB to the length you specify in the newlen parameter. 
 * @param newlen the new length of the BLOB. 
 */ 
public void truncate (long newlen) throws SQLException

The newlen parameter specifies the new length of the BLOB.

JDBC: Truncating CLOBs

oracle.sql.CLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob interface. Table 7-58 lists the Oracle extension API in oracle.sql.CLOB that truncates CLOBs.

Table 7-58 JDBC: Truncating CLOBs

Methods Description

public void truncate(long newlen) throws SQLException

Truncates the CLOB


The truncate API is defined as follows:

/** 
*Truncate the value of the CLOB to the length you specify in the newlen parameter.
 * @param newlen the new length of the CLOB. 
 */ 
public void truncate (long newlen) throws SQLException

The newlen parameter specifies the new length of the CLOB.

See:

"Trimming LOB Data", for an example.

JDBC BLOB Streaming APIs

The JDBC interface provided with the database includes LOB streaming APIs that enable you to read from or write to a LOB at the requested position from a Java stream.

The oracle.sql.BLOB class implements the standard JDBC java.sql.Blob interface. Table 7-59 lists BLOB Streaming APIs.

Table 7-59 JDBC: BLOB Streaming APIs

Methods Description

public java.io.OutputStream

setBinaryStream (long pos) throws SQLException

JDBC 3.0: Retrieves a stream that can be used to write to the BLOB value that this Blob object represents, starting at position pos

public java.io.InputStream

getBinaryStream() throws SQLException

JDBC 3.0: Retrieves a stream that can be used to read the BLOB value that this Blob object represents, starting at the beginning

public java.io.InputStream

getBinaryStream(long pos) throws SQLException

Oracle extension: Retrieves a stream that can be used to read the BLOB value that this Blob object represents, starting at position pos


These APIs are defined as follows:

/** 
 * Write to the BLOB from a stream at the requested position. 
 * 
 * @param pos is the position data to be put. 
 * @return a output stream to write data to the BLOB 
 */ 
public java.io.OutputStream setBinaryStream(long pos) throws SQLException

/** 
 * Read from the BLOB as a stream at the requested position. 
 * 
 * @param pos is the position data to be read. 
 * @return a output stream to write data to the BLOB 
 */ 
public java.io.InputStream getBinaryStream(long pos) throws SQLException

JDBC CLOB Streaming APIs

The oracle.sql.CLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob interface. Table 7-60 lists the CLOB streaming APIs.

Table 7-60 JDBC: CLOB Streaming APIs

Methods Description

public java.io.OutputStream

setAsciiStream (long pos) throws SQLException

JDBC 3.0: Retrieves a stream to be used to write ASCII characters to the CLOB value that this Clob object represents, starting at position pos

public java.io.Writer

setCharacterStream (long pos) throws SQLException

JDBC 3.0: Retrieves a stream to be used to write Unicode characters to the CLOB value that this Clob object represents, starting, at position pos

public java.io.InputStream

getAsciiStream() throws SQLException

JDBC 3.0: Retrieves a stream that can be used to read ASCII characters from the CLOB value that this Clob object represents, starting at the beginning

public java.io.InputStream

getAsciiStream(long pos) throws SQLException

Oracle extension: Retrieves a stream that can be used to read ASCII characters from the CLOB value that this Clob object represents, starting at position pos

public java.io.Reader

getCharacterStream() throws SQLException

JDBC 3.0: Retrieves a stream that can be used to read Unicode characters from the CLOB value that this Clob object represents, starting at the beginning

public java.io.Reader

getCharacterStream(long pos) throws SQLException

Oracle extension: Retrieves a stream that can be used to read Unicode characters from the CLOB value that this Clob object represents, starting at position pos


These APIs are defined as follows:

/** 
  * Write to the CLOB from a stream at the requested position. 
  * @param pos is the position data to be put. 
  * @return a output stream to write data to the CLOB 
  */ 
public java.io.OutputStream setAsciiStream(long pos) throws SQLException 

/** 
* Write to the CLOB from a stream at the requested position. 
     * @param pos is the position data to be put. 
     * @return a output stream to write data to the CLOB 
     */ 
  public java.io.Writer setCharacterStream(long pos) throws SQLException 

    /** 
     * Read from the CLOB as a stream at the requested position. 
     * @param pos is the position data to be put. 
     * @return a output stream to write data to the CLOB 
     */ 
  public java.io.InputStream getAsciiStream(long pos) throws SQLException 

   /** 
    * Read from the CLOB as a stream at the requested position. 
    * @param pos is the position data to be put. 
    * @return a output stream to write data to the CLOB 
    */ 
   public java.io.Reader getCharacterStream(long pos) throws SQLException

BFILE Streaming APIs

oracle.sql.BFILE class wraps the database BFILEs. Table 7-61 lists the Oracle extension APIs in oracle.sql.BFILE that reads BFILE content from the requested position.

Table 7-61 JDBC: BFILE Streaming APIs

Methods Description

public java.io.InputStream

getBinaryStream(long pos) throws SQLException

Reads from the BFILE as a stream


These APIs are defined as follows:

/** 
 * Read from the BLOB as a stream at the requested position. 
 * 
 * @param pos is the position data to be read. 
 * @return a output stream to write data to the BLOB 
 */ 
public java.io.InputStream getBinaryStream(long pos) throws SQLException

JDBC BFILE Streaming Example (NewStreamLob.java)

/* 
 * This sample shows how to read/write BLOB and CLOB as streams. 
 */ 

import java.io.*; 

// You need to import the java.sql package to use JDBC 
import java.sql.*; 

// You need to import the oracle.sql package to use oracle.sql.BLOB 
import oracle.sql.*; 

class NewStreamLob 
{ 
  public static void main (String args [])  throws Exception 
  { 
    // Load the Oracle JDBC driver 
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); 

    String url = "jdbc:oracle:oci8:@"; 
    try { 
      String url1 = System.getProperty("JDBC_URL"); 
      if (url1 != null) 
        url = url1; 
    } catch (Exception e) { 
      // If there is any security exception, ignore it 
      // and use the default 
    } 

    // Connect to the database 
    Connection conn = 
      DriverManager.getConnection (url, "scott", "tiger"); 
    // It is faster when auto commit is off 
    conn.setAutoCommit (false); 

    // Create a Statement 
    Statement stmt = conn.createStatement (); 

    try 
    { 
      stmt.execute ("drop table basic_lob_table"); 
    } 
    catch (SQLException e) 
    { 
      // An exception could be raised here if the table did not exist already. 
    } 

    // Create a table containing a BLOB and a CLOB 
    stmt.execute (
         "create table basic_lob_table"  
         + "(x varchar2 (30), b blob, c clob)"); 

    // Populate the table 
    stmt.execute (
         "insert into basic_lob_table values"
         + "('one', '010101010101010101010101010101', 'onetwothreefour')"); 
  
    System.out.println ("Dumping lobs"); 

    // Select the lobs 
    ResultSet rset = stmt.executeQuery ("select * from basic_lob_table"); 
    while (rset.next ()) 
    { 
      // Get the lobs 
      BLOB blob = (BLOB) rset.getObject (2); 
      CLOB clob = (CLOB) rset.getObject (3); 

      // Print the lob contents 
      dumpBlob (conn, blob, 1); 
      dumpClob (conn, clob, 1); 

      // Change the lob contents 
      fillClob (conn, clob, 11, 50); 
      fillBlob (conn, blob, 11, 50); 
    } 
    rset.close (); 

    System.out.println ("Dumping lobs again"); 

    rset = stmt.executeQuery ("select * from basic_lob_table"); 
    while (rset.next ()) 
    { 
      // Get the lobs 
      BLOB blob = (BLOB) rset.getObject (2); 
      CLOB clob = (CLOB) rset.getObject (3); 

      // Print the lobs contents 
      dumpBlob (conn, blob, 11); 
      dumpClob (conn, clob, 11); 
    } 
    // Close all resources 
    rset.close(); 
    stmt.close(); 
    conn.close(); 
  } 

  // Utility function to dump Clob contents 
  static void dumpClob (Connection conn, CLOB clob, long offset) 
    throws Exception 
  { 
    // get character stream to retrieve clob data 
    Reader instream = clob.getCharacterStream(offset); 

    // create temporary buffer for read 
    char[] buffer = new char[10]; 

    // length of characters read 
    int length = 0; 

    // fetch data 
    while ((length = instream.read(buffer)) != -1) 
    { 
      System.out.print("Read " + length + " chars: "); 

      for (int i=0; i<length; i++) 
        System.out.print(buffer[i]); 
      System.out.println(); 
    } 

    // Close input stream 
    instream.close(); 
  } 

  // Utility function to dump Blob contents 
  static void dumpBlob (Connection conn, BLOB blob, long offset) 
    throws Exception 
  { 
    // Get binary output stream to retrieve blob data 
    InputStream instream = blob.getBinaryStream(offset); 
    // Create temporary buffer for read 
    byte[] buffer = new byte[10]; 
    // length of bytes read 
    int length = 0; 
    // Fetch data 
    while ((length = instream.read(buffer)) != -1) 
    { 
      System.out.print("Read " + length + " bytes: "); 

      for (int i=0; i<length; i++) 
        System.out.print(buffer[i]+" "); 
      System.out.println(); 
    } 

    // Close input stream 
    instream.close(); 
  } 

  // Utility function to put data in a Clob 
  static void fillClob (Connection conn, CLOB clob, long offset, long length) 
    throws Exception 
  { 
    Writer outstream = clob.setCharacterStream(offset); 

    int i = 0; 
    int chunk = 10; 

    while (i < length) 
    { 
      outstream.write("aaaaaaaaaa", 0, chunk); 

      i += chunk; 
      if (length - i < chunk) 
         chunk = (int) length - i; 
    } 
    outstream.close(); 
  } 

  // Utility function to put data in a Blob 
  static void fillBlob (Connection conn, BLOB blob, long offset, long length) 
    throws Exception 
  { 
    OutputStream outstream = blob.setBinaryStream(offset); 

    int i = 0; 
    int chunk = 10; 

    byte [] data = { 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 }; 

    while (i < length) 
    { 
      outstream.write(data, 0, chunk); 

      i += chunk; 
      if (length - i < chunk) 
         chunk = (int) length - i; 
    } 
    outstream.close(); 
  } 
} 

JDBC and Empty LOBs

An empty BLOB can be created from the following API from oracle.sql.BLOB:

public static BLOB empty_lob () throws SQLException 

Similarly, the following API from oracle.sql.CLOB creates an empty CLOB:

public static CLOB empty_lob () throws SQLException 

Empty LOB instances are created by JDBC drivers without making database round trips. Empty LOBs can be used in the following cases:

  • "set" APIs of PreparedStatement

  • "update" APIs of updatable result set

  • attribute value of STRUCTs

  • element value of ARRAYs

    Note:

    Empty LOBs are special marker LOBs but not real LOB values.

JDBC applications cannot read or write to empty LOBs created from the preceding APIs. An ORA-17098 "Invalid empty lob operation" results if your application attempts to read/write to an empty LOB.

Oracle Provider for OLE DB (OraOLEDB)

Oracle Provider for OLE DB (OraOLEDB) offers high performance and efficient access to Oracle data for OLE DB and ADO developers. Developers programming with COM, C++, or any COM client can use OraOLEDB to access Oracle databases.

OraOLEDB is an OLE DB provider for Oracle. It offers high performance and efficient access to Oracle data including LOBs, and also allows updates to certain LOB types.

The following LOB types are supported by OraOLEDB:

Overview of Oracle Data Provider for .NET (ODP.NET)

Oracle Data Provider for .NET (ODP.NET) is an implementation of a data provider for the Oracle database. ODP.NET uses Oracle native APIs to offer fast and reliable access to Oracle data and features from any .NET application. ODP.NET also uses and inherits classes and interfaces available in the Microsoft .NET Framework Class Library. The ODP.NET supports the following LOBs as native datatypes with .NET: BLOB, CLOB, NCLOB, and BFILE.

COM and .NET are complementary development technologies. Microsoft recommends that developers use the .NET Framework rather than COM for new development.