Skip Headers

Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)

Part Number A96591-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

3
LOB Support in Different Programmatic Environments

This chapter discusses the following topics:

Eight Programmatic Environments Operate on LOBs

Table 3-1 lists the eight programmatic environments (languages) that support LOB functionality. Chapter 10, Chapter 11, and Chapter 12 cover the supported LOB functions in terms of use cases. Examples are provided in each programmatic environment for most LOB use cases.

Table 3-1 LOBs' Eight Programmatic Environments
Language Precompiler or Interface Program Syntax Reference In This Chapter See...

PL/SQL

DBMS_LOB Package

Oracle9i Supplied PL/SQL Packages and Types Reference

"Using PL/SQL (DBMS_LOB Package) To Work With LOBs".

C

Oracle Call Interface for C (OCI)

Oracle Call Interface Programmer's Guide

"Using C (OCI) To Work With LOBs".

C++

Oracle Call Interface for C++ (OCCI)

Oracle C++ Call Interface Programmer's Guide

"Using C++ (OCCI) To Work With LOBs"

C/C++

Pro*C/C++ Precompiler

Pro*C/C++ Precompiler Programmer's Guide

"Using C/C++ (Pro*C) To Work with LOBs".

COBOL

Pro*COBOL Precompiler

Pro*COBOL Precompiler Programmer's Guide

"Using COBOL (Pro*COBOL) to Work with LOBs".

Visual Basic

Oracle Objects For OLE (OO4O)

Oracle Objects for OLE (OO4O) is a Windows-based product included with Oracle9i Client for Windows NT.

There are no manuals for this product, only online help. Online help is available through the Application Development submenu of the Oracle9i installation.

"Using Visual Basic (Oracle Objects for OLE (OO4O)) to Work with LOBs"."

Java

JDBC Application Programmatic Interface (API)

Oracle9i SQLJ Developer's Guide and Reference and Oracle9i JDBC Developer's Guide and Reference.

"Using Java (JDBC) To Work with LOBs".

OLEDB

OraOLEDB, an OLE DB provider for Oracle.

Oracle Provider for OLE DB Developer's Guide

Comparing the LOB Interfaces

Table 3-2 and Table 3-3compare 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 interfaces' functionality, with regards LOBs, is described in the following sections.

Table 3-2 Comparing the LOB Interfaces, 1 of 2  
PL/SQL: DBMS_LOB (dbmslob.sql) C (OCI) (ociap.h) C++ (OCCI) (occiData.h). Also for OCCIClob and OCCIBfile 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

OCILob.Append

OCCIBlob.append()

APPEND

N/A [use PL/SQL assign operator]

OCILob.Assign

ASSIGN

N/A

OCILob.CharSetForm

OCCIClob.getCharsetForm (CLOB only)

N/A

N/A

OCILob.CharSetId

OCCIClob.getCharsetId()

(CLOB only)

N/A

DBMS_LOB.CLOSE

OCILob.Close

OCCIBlob.close()

CLOSE

N/A

N/A

OCCIClob.closeStream()

N/A

DBMS_LOB.COPY

OCILob.Copy

OCCIBlob.copy()

COPY

N/A

OCILob.DisableBuffering

N/A

DISABLE BUFFERING

N/A

OCILob.EnableBuffering

N/A

ENABLE BUFFERING

DBMS_LOB.ERASE

OCILob.Erase

N/A

ERASE

DBMS_LOB.FILECLOSE

OCILob.FileClose

OCCIClob.close()

CLOSE

DBMS_LOB.FILECLOSEALL

OCILob.FileCloseAll

N/A

FILE CLOSE ALL

DBMS_LOB.FILEEXISTS

OCILob.FileExists

OCCIBfile.fileExists()

DESCRIBE [FILEEXISTS]

DBMS_LOB.GETCHUNKSIZE

OCILob.GetChunkSize

OCCIBlob.getChunkSize()

DESCRIBE [CHUNKSIZE]

DBMS_LOB.FILEGETNAME

OCILob.FileGetName

OCCIBfile.getFileName() and OCCIBfile.getDirAlias()

DESCRIBE [DIRECTORY, FILENAME]

DBMS_LOB.FILEISOPEN

OCILob.FileIsOpen

OCCIBfile.isOpen()

DESCRIBE [ISOPEN]

DBMS_LOB.FILEOPEN

OCILob.FileOpen

OCCIBfile.open()

OPEN

N/A (use BFILENAME operator)

OCILob.FileSetName

OCCIBfile.setName()

FILE SET

N/A

OCILob.FlushBuffer

N/A

FLUSH BUFFER

DBMS_LOB.GETLENGTH

OCILob.GetLength

OCCIBlob.length()

DESCRIBE [LENGTH]

N/A

OCILob.IsEqual

use operator = ( )=/!=

N/A

DBMS_LOB.ISOPEN

OCILob.IsOpen

OCCIBlob.isOpen()

DESCRIBE [ISOPEN]

DBMS_LOB.LOADFROMFILE

OCILob.LoadFromFile

Use the overloadedcopy() method.

LOAD FROM FILE

N/A

OCILob.LocatorIsInit

OCCIClob.isinitialized()

N/A

DBMS_LOB.OPEN

OCILob.Open

OCCIBlob.open

OPEN

DBMS_LOB.READ

OCILob.Read

OCCIBlob.read

READ

DBMS_LOB.TRIM

OCILob.Trim

OCCIBlob.trim

TRIM

DBMS_LOB.WRITE

OCILob.Write

OCCIBlob.write

WRITEORALOB.

DBMS_LOB.WRITEAPPEND

OCILob.WriteAppend

N/A

WRITE APPEND

DBMS_LOB.CREATETEMPORARY

OCILob.CreateTemporary

N/A

N/A

DBMS_LOB.FREETEMPORARY

OCILob.FreeTemporary

N/A

N/A

DBMS_LOB.ISTEMPORARY

OCILob.IsTemporary

N/A

N/A

OCILob.LocatorAssign

use operator = ( ) or copy constructor

N/A

Table 3-3 Comparing the LOB Interfaces, 2 of 2  
PL/SQL: DBMS_LOB (dbmslob.sql) Visual Basic (OO4O) Java (JDBC OLEDB

DBMS_LOB.COMPARE

ORALOB.Compare

Use DBMS_LOB.

N/A

DBMS_LOB.INSTR

ORALOB.Matchpos

position

N/A

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

N/A

N/A [use PL/SQL assign operator]

ORALOB.Clone

N/A [use equal sign]

N/A

N/A

N/A

N/A

N/A

N/A

N/A

N/A

N/A

DBMS_LOB.CLOSE

N/A

use DBMS_LOB.

N/A

DBMS_LOB.COPY

ORALOB.Copy

Use read and write

N/A

N/A

ORALOB.DisableBuffering

N/A

N/A

N/A

ORALOB.EnableBuffering

N/A

N/A

DBMS_LOB.ERASE

ORALOB.Erase

Use DBMS_LOB.

N/A

DBMS_LOB.FILECLOSE

ORABFILE.Close

closeFile

N/A

DBMS_LOB.FILECLOSEALL

ORABFILE.CloseAll

Use DBMS_LOB.

N/A

DBMS_LOB.FILEEXISTS

ORABFILE.Exist

fileExists

N/A

DBMS_LOB.GETCHUNKSIZE

N/A

getChunkSize

N/A

DBMS_LOB.FILEGETNAME

ORABFILE.

DirectoryName

ORABFILE.

FileName

getDirAlias

getName

N/A

DBMS_LOB.FILEISOPEN

ORABFILE.IsOpen

Use DBMS_LOB.ISOPEN

N/A

DBMS_LOB.FILEOPEN

ORABFILE.Open

openFile

N/A

N/A (use BFILENAME operator)

DirectoryName

FileName

Use BFILENAME

N/A

N/A

ORALOB.FlushBuffer

N/A

N/A

DBMS_LOB.GETLENGTH

ORALOB.Size

length

N/A

N/A

N/A

equals

N/A

DBMS_LOB.ISOPEN

ORALOB.IsOpen

use DBMS_LOB. IsOpen

N/A

DBMS_LOB.LOADFROMFILE

ORALOB.

CopyFromBfile

Use read and then write

N/A

DBMS_LOB.OPEN

ORALOB.open

Use DBMS_LOB.

N/A

DBMS_LOB.READ

ORALOB.Read

BLOB or BFILE: getBytes and getBinaryStream

CLOB: getString and getSubString and getCharacterStream

IRowset::GetData and ISequentialStream::Read

DBMS_LOB.TRIM

ORALOB.Trim

Use DBMS_LOB.

N/A

DBMS_LOB.WRITE

ORALOB.Write

BLOB or BFILE: putBytes and getBinaryOutputStream

CLOB: putString and getCharacterOutputStream

IRowsetChange::SetData

and

ISequentialStream::Write

DBMS_LOB.WRITEAPPEND

N/A

Use length and then putString or putBytes

N/A

DBMS_LOB.CREATETEMPORARY

N/A

N/A

N/A

DBMS_LOB.FREETEMPORARY

N/A

N/A

N/A

DBMS_LOB.ISTEMPORARY

N/A

N/A

N/A

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 Invoking 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 invoke the routine.

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.

PL/SQL - LOB Guidelines

Client PL/SQL Procedures Cannot Call DBMS_LOB Routines

Client-side PL/SQL procedures cannot call DBMS_LOB package routines.

However, you can use server-side PL/SQL procedures or anonymous blocks in Pro*C/C++ to call DBMS_LOB package routines.

Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte For DBMS_LOB Package

For DBMS_LOB package, for example, for both fixed and varying-width character sets, the following rules apply:

DBMS_LOB.LOADFROMFILE: Amount Parameter Value

When using DBMS_LOB.LOADFROMFILE, you cannot specify an amount parameter value larger than the size of the BFILE. (Although you can specify the LOBMAXSIZE constant for the amount parameter value to load the entire BFILE.)

DBMS_LOB.READ: Amount Parameter Can be Larger than Data Size

When using DBMS_LOB.READ, the amount parameter can be larger than the size of the data. In PL/SQL, the amount should be less than or equal to the size of the buffer, and the buffer size is limited to 32K.

See Also:

PL/SQL Functions and Procedures that Operate on LOBs

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

PL/SQL Functions/Procedures To Modify BLOB, CLOB, and NCLOB Values

Table 3-4 PL/SQL: DBMS_LOB Procedures to Modify BLOB, CLOB, and NCLOB Values
Function/Procedure Description

APPEND()

Appends the LOB value to another LOB

COPY()

Copies all or part of a LOB to another LOB

ERASE()

Erases part of a LOB, starting at a specified offset

LOADFROMFILE()

Load BFILE data into an internal LOB

LOADCLOBFROMFILE()

Load character data from a file into a LOB

LOADBLOBFROMFILE()

Load binary data from a file into a LOB

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/Procedures To Read or Examine Internal and External
LOB Values

Table 3-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 internal LOBs and does not apply to external LOBs (BFILEs).

GETLENGTH()

Gets the length of the LOB value

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

SUBSTR()

Returns part of the LOB value starting at the specified offset

PL/SQL Functions/Procedures To Operate on Temporary LOBs

Table 3-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/Procedures for BFILEs

Table 3-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 alias 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/Procedures To Open and Close Internal and External LOBs

Table 3-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 these chapters:

You can access many of the PL/SQL LOB example scripts from these chapters, at $ORACLE_HOME/rdbms/demo/lobs/plsql.

Using C (OCI) To Work With LOBs

Oracle Call Interface (OCI) can be used to make changes to an entire internal LOB, or to pieces of the beginning, middle or end of it through OCI, as follows:

OCI also includes functions that you can use to:

These functions are listed in the following tables, and are discussed in greater detail later in this section.

Set CSID Parameter To OCI_UCS2ID to Read/Write in UCS2

If you want to read or write data in 2 byte unicode (UCS2) format, set the csid (character set ID) parameter in OCILobRead and OCILobWrite 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, it will override the NLS_LANG environment variable.

See Also:

Offset and Amount Parameters: Fixed-Width Versus Varying-Width,
Character or Byte

Fixed Width Character Set Rules

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

Varying-Width Character Set Rules

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

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 OCILobCopy, OCILobErase, OCILobLoadFromFile, and OCILobTrim. All these operations refer to the amount of LOB data on the server.

See also:

Oracle9i Database Globalization Support Guide

NCLOBs

NCLOBs parameters are allowed in methods.

OCILobLoadFromFile: Specify Amount Parameter to be Less than Length of BFILE

When using OCILobLoadFromFile you cannot specify amount larger than the length of the BFILE.

OCILobRead: Specify Amount Parameter to be 4 gigabytes - 1

In OCILobRead, you can specify amount = 4 gigabytes-1, and it will read to the end of the LOB.

OCI LOB Examples

Further OCI examples are provided in:

You can access most of the OCI LOB example scripts from your Oracle9i distribution software at $ORACLE_HOME/rdbms/demo/lobs/oci.

There are further example OCI scripts:

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://otn.oracle.com/ for OCI features and FAQs.

OCI Functions that Operate on BLOBs, BLOBs, NCLOBs, and BFILEs

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

OCI Functions To Modify Internal LOB (BLOB, CLOB, and NCLOB) Values

Table 3-9 OCI Functions To Modify Internal LOB (BLOB, CLOB, and NCLOB) Values 
Function/Procedure Description

OCILobAppend()

Appends LOB value to another LOB.

OCILobCopy()

Copies all or part of a LOB to another LOB.

OCILobErase()

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

OCILobLoadFromFile()

Loads BFILE data into an internal LOB.

OCILobTrim()

Truncates a LOB.

OCILobWrite()

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

OCILobWriteAppend()

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

OCI Functions To Read or Examine Internal LOB and External LOB (BFILE) Values

Table 3-10 OCI Functions To Read or Examine internal LOB and external LOB (BFILE) Values
Function/Procedure Description

OCILobGetChunkSize()

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

OCILobGetLength()

Returns the length of a LOB or a BFILE.

OCILobRead()

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

OCI Functions For Temporary LOBs

Table 3-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 3-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 3-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 3-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 3-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 Example -- Is the LOB Open: main() and seeIfLOBIsOpen

To work with the OCI examples in the remainder of the book, you can use a main() like the following. Here, it is used with seeIfLOBIsOpen as an example.

int main(char *argv, int argc)
{
 /* Declare OCI Handles to be used */
  OCIEnv        *envhp;
  OCIServer     *srvhp; 
  OCISvcCtx     *svchp;
  OCIError      *errhp;
  OCISession    *authp;
  OCIStmt       *stmthp;
  OCILobLocator *Lob_loc;

 /* Create and Initialize an OCI Environment: */
  (void) OCIEnvCreate(&envhp, (ub4)OCI_DEFAULT, (dvoid *)0,
                      (dvoid * (*)(dvoid *, size_t)) 0,
                      (dvoid * (*)(dvoid *, dvoid *, size_t))0,
                      (void (*)(dvoid *, dvoid *))0,
                      (size_t) 0, (dvoid **) 0);

 /* Allocate error handle: */
  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, 
                        (size_t) 0, (dvoid **) 0);

 /* Allocate server contexts: */
  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER,
                        (size_t) 0, (dvoid **) 0);

 /* Allocate service context: */
  (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX,
                        (size_t) 0, (dvoid **) 0);

 /* Attach to the Oracle database:  */
  (void) OCIServerAttach(srvhp, errhp, (text *)"", strlen(""), 0);

 /* Set the server context attribute in the service context: */
  (void) OCIAttrSet ((dvoid *) svchp, OCI_HTYPE_SVCCTX, 
                     (dvoid *)srvhp, (ub4) 0,
                     OCI_ATTR_SERVER, (OCIError *) errhp);

 /* Allocate the session handle: */
  (void) OCIHandleAlloc((dvoid *) envhp, 
                        (dvoid **)&authp, (ub4) OCI_HTYPE_SESSION,
                        (size_t) 0, (dvoid **) 0);
 
 /* Set the username in the session handle:*/
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                    (dvoid *) "samp", (ub4)4,
                    (ub4) OCI_ATTR_USERNAME, errhp);
 /* Set the password in the session handle: */
  (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION,
                    (dvoid *) "samp", (ub4) 4,
                    (ub4) OCI_ATTR_PASSWORD, errhp);

 /* Authenticate and begin the session: */
  checkerr(errhp, OCISessionBegin (svchp,  errhp, authp, OCI_CRED_RDBMS, 
                                   (ub4) OCI_DEFAULT));

 /* Set the session attribute in the service context: */
  (void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
                    (dvoid *) authp, (ub4) 0,
                   (ub4) OCI_ATTR_SESSION, errhp);

 /* ------- At this point a valid session has been created -----------*/
  printf ("user session created \n");

 /* Allocate a statement handle: */
  checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp,
           OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0));

 /* =============  Sample procedure call begins here ===================*/  

  printf ("calling seeIfLOBIsOpen...\n");
  seeIfLOBIsOpen(envhp, errhp, svchp, stmthp);

  return 0;
}

void checkerr(errhp, status)
OCIError *errhp;
sword status;
{
  text errbuf[512];
  sb4 errcode = 0;

  switch (status)
  {
  case OCI_SUCCESS:
    break;
  case OCI_SUCCESS_WITH_INFO:
    (void) printf("Error - OCI_SUCCESS_WITH_INFO\n");
    break;
  case OCI_NEED_DATA:
    (void) printf("Error - OCI_NEED_DATA\n");
    break;
  case OCI_NO_DATA:
    (void) printf("Error - OCI_NODATA\n");
    break;
  case OCI_ERROR:
    (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode,
                        errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR);
    (void) printf("Error - %.*s\n", 512, errbuf);
    break;
  case OCI_INVALID_HANDLE:
    (void) printf("Error - OCI_INVALID_HANDLE\n");
    break;
  case OCI_STILL_EXECUTING:
    (void) printf("Error - OCI_STILL_EXECUTE\n");
    break;
  case OCI_CONTINUE:
    (void) printf("Error - OCI_CONTINUE\n");
    break;
  default:
    break;
  }
}

/* Select the locator into a locator variable */

sb4 select_frame_locator(Lob_loc, errhp, svchp, stmthp)
OCILobLocator *Lob_loc;
OCIError      *errhp;
OCISvcCtx     *svchp;
OCIStmt       *stmthp;     
{
  text      *sqlstmt = 
                 (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1";
  OCIDefine *defnp1;

  checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, 
                                  (ub4)strlen((char *)sqlstmt),
                                  (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT));
  
  checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1,
                                  (dvoid *)&Lob_loc, (sb4)0, 
                                  (ub2) SQLT_BLOB,(dvoid *) 0, 
                                  (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT));

  /* execute the select and fetch one row */
  checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                 (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                 (ub4) OCI_DEFAULT));
  
  return (0);
}

void seeIfLOBIsOpen(envhp, errhp, svchp, stmthp)
OCIEnv    *envhp;
OCIError  *errhp;
OCISvcCtx *svchp;
OCIStmt   *stmthp;
{
  OCILobLocator *Lob_loc;
  int isOpen;

  /* allocate locator resources */
  (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&Lob_loc,
                            (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0);

  /* Select the locator */
  (void)select_frame_locator(Lob_loc, errhp, svchp, stmthp);
  
  /* See if the LOB is Open */
  checkerr (errhp, OCILobIsOpen(svchp, errhp, Lob_loc, &isOpen));
 
  if (isOpen)
  {
    printf("  Lob is Open\n");
    /* ... Processing given that the LOB has already been Opened */
  }
  else
  {
    printf("  Lob is not Open\n");
    /* ... Processing given that the LOB has not been Opened */
  }

  /* Free resources held by the locators*/
  (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB);
    return;
}

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 collection of C++ classes which enable a C++ program to connect to a database, execute SQL statements, insert/update values in database tables, retrieve results of a query, execute 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 Oracle C++ Call Interface (OCCI) to make changes to an entire internal LOB, or to pieces of the beginning, middle, or end of it, as follows:

Distinct Classes for Each LOB Type

Unlike OCI which uses a common API for operations on BLOBs, CLOBs, and BFILEs, OCCI has distinct classes for each LOB types, as follows:

OCCIClob Class

The OCCIClob driver implements a CLOB object using an SQL locator(CLOB). 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 (Character Large Object) value, for materializing a CLOB value on the client, and getting a substring. Methods in the interfaces ResultSet statement such as getClob() and setClob() allow you to access SQL CLOB values.

OCCIBlob Class

Methods in the OCCIResultSet and OCCIStatement interfaces, such as getBlob() and setBlob(), allow you to access SQL BLOB values. The OCCIBlob 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.

These methods are listed in the following tables.

See Also:

Offset and Amount Parameters: Fixed-Width Versus Varying-Width,
Character or Byte

Fixed Width Character Set Rules

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

Varying-Width Character Set Rules

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

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:

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

See also:

Oracle9i Database Globalization Support Guide

NCLOBs

Loading from Files with OCCIClob.copy() and OCCIBlob.copy(): Amount Parameter

The LoadFromFile functionality in OCCI is provided though the OCCIClob.copy() and OCCIBlob.copy() methods. These take an OCCIBfile argument.

You cannot specify amounts larger than the length of the BFILE. You must specify an amount parameter less than length of the BFILE.

OCCIClob.read(), OCCIBlob.read(), and OCCIBfile.read():Amount Parameter

When reading in OCCIClobs, OCCIBlobs, and OCCIBfiles, specify the amount = 4 gigabytes-1, and it will read to the end of the LOB.

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 Internal LOB (BLOB, CLOB, and NCLOB) Values

Table 3-16 OCCI Clob and OCCIBlob Methods To Modify Internal LOB (BLOB, CLOB, and NCLOB) Values
Function/Procedure Description

OCCIBlob.append()

Appends CLOB or BLOB value to another LOB.

OCCIBlob.copy()

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

OCCIBlob.copy()

Loads BFILE data into an internal LOB.

OCCIBlob.trim()

Truncates a CLOB or BLOB.

OCCIBlob.write()

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

OCCI Methods To Read or Examine Internal LOB and BFILE Values

Table 3-17 OCCI Blob/OCCIClob/OCCIBfile Methods To Read or Examine internal LOB and external LOB (BFILE) Values
Function/Procedure Description

OCCIBlob.getChunkSize()

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

OCCIBlob.length()

Returns the length of a LOB or a BFILE.

OCCIBlob.read()

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

OCCI Read-Only Methods For BFILEs

Table 3-18  OCCI Read-Only Methods For BFILES
Function/Procedure Description

OCCIBfile.close()

Closes an open BFILE.

OCCIBfile.fileExists()

Checks whether a BFILE exists.

OCCIBfile.getFileName()

Returns the name of a BFILE.

OCCIBfile.getDirAlias()

Gets the directory alias.

OCCIBfile.isOpen()

Checks whether a BFILE is open.

OCCIBfile.open()

Opens a BFILE.

Other OCCI LOB Methods

Table 3-19  Other OCCI LOB Methods
Methods Description

OCCIClob/Blob/Bfile.=

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

OCCIClob.getCharSetForm()

Returns the character set form of a LOB.

OCCIClob.getCharSetId()

Returns the character set ID of a LOB.

OCCIBfile.setName()

Sets the name of a BFILE.

OCCIClob/Blob/Bfile.IsEqual()

Checks whether two LOB refer to the same LOB.

OCCIClob/Blob/Bfile.isInitialized()

Checks whether a LOB is initialized.

OCCI Methods To Open and Close Internal and External LOBs

Table 3-20  OCCI Methods To Open and Close Internal and External LOBs
Function/Procedure Description

OCCIClob/Blob/Bfile.Open()

Opens a LOB

OCCIClob/Blob/Bfile.IsOpen()

Sees if a LOB is open

OCCIClob/Blob/Bfile.Close()

Closes a LOB

Using C/C++ (Pro*C) To Work with LOBs

You can make changes to an entire internal 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 internal 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++ Precompiler 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 execute 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

Examples provided with each embedded SQL LOB statement are illustrated in:

You can access these Pro*C/C++ LOB example scripts from $ORACLE_HOME/rdbms/demo/lobs/.

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

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

Pro*C/C++ Embedded SQL Statements To Modify Internal LOB Values

Table 3-21 Pro*C/C++: Embedded SQL Statements To Modify Internal LOB (BLOB, CLOB, and NCLOB) 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 an internal 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 To Read or Examine Internal and External LOB Values

Table 3-22 Pro*C/C++: Embedded SQL Statements To Read or Examine Internal and External LOB Values
Statement Description

DESCRIBE [CHUNKSIZE]

Gets the Chunk size used when writing. This works for internal 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 3-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 3-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 alias and/or filename of a BFILE.

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

Table 3-25  Pro*C/C++ Embedded SQL Statements for LOB Locators
Statement Description

ASSIGN

Assigns one LOB locator to another.

FILE SET

Sets the directory alias and filename of a BFILE in a locator.

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

Table 3-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 Internal
and External LOBs

Table 3-27  Pro*C/C++ Embedded SQL Statements To Open and Close Internal 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 internal 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 internal 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 execute 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.

Examples provided with each embedded SQL LOB statement are illustrated in:

You can access these Pro*COBOL LOB example scripts from $ORACLE_HOME/rdbms/demo/lobs/.

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

Pro*COBOL Embedded SQL Statements To Modify Internal LOB Values

Table 3-28 Pro*COBOL Embedded SQL Statements To Modify BLOB, CLOB, and NCLOB 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 an internal 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 To Read or Examine Internal
and External LOB Values

Table 3-29 Pro*COBOL Embedded SQL Statements To Read or Examine Internal and External LOB Values
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 3-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 3-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 alias and/or filename of a BFILE.

Pro*COBOL Embedded SQL Statements For LOB Locators

Table 3-32 Pro*COBOL Embedded SQL Statements For LOB Locators Statements
Statement Description

ASSIGN

Assigns one LOB locator to another.

FILE SET

Sets the directory alias and filename of a BFILE in a locator.

Pro*COBOL Embedded SQL Statements For LOB Buffering

Table 3-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 To Open and Close Internal LOBs
and BFILEs

Table 3-34  Pro*COBOL Embedded SQL Statements To Open and CLose Internal 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 Visual Basic (Oracle Objects for OLE (OO4O)) to Work with LOBs

Oracle Objects for OLE (OO4O) is a collection 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 internal 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

Syntax

The OO4O syntax reference and further information is viewed from the OO4O online help. Oracle Objects for OLE (OO4O), a Windows-based product included with Oracle9i Client for Windows NT, has no manuals, only online help.

Its online help is available through the Application Development submenu of the Oracle9i installation. To view specific methods and properties from the Help Topics menu, select the Contents tab > OO4O Automation Server > Methods or Properties.

Further Information

For further information about OO4O, refer to the following Web site:

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 and Represent LOB Locators

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, 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. Functions and examples are explained in greater detail in Chapters 10 and 12.

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

OraConnection.BeginTrans
set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab order by clip_
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 represenetd 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. These methods and properties are listed in the following tables, and are discussed in greater detail in the following chapters:

You can also access OO4O LOB example scripts at $ORACLE_HOME/rdbms/demo/lobs.

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 Oracle9i Client for Windows NT, has no manuals, only online help. The OO4O online help is available through the Application Development submenu of the Oracle9i installation.

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

OO4O Methods To Modify BLOB, CLOB, and NCLOB Values

Table 3-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 3-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 3-37 OO4O Methods To Open and Close External LOBs (BFILEs)
Method Description

OraBFile.Open

Opens BFILE.

OraBFile.Close

Closes BFILE.

and Close

OO4O Methods For Internal LOB-Buffering

Table 3-38 OO4O Methods For Internal 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 3-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 3-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

Table 3-41 OO4O Properties For Operating on External LOBs (BFILEs)
Property Description

OraBFile.DirectoryName

Gets/Sets the server side directory alias name.

OraBFile.FileName(Read/Write)

Gets/Sets the server side filename.

OraBFile.Exists

Checks whether a BFILE exists.

on External LOBs (BFILEs)

Using Java (JDBC) To Work with LOBs

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

Changing Internal Persistent LOBs Using Java

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

These objects also implement java.sql.Blob and java.sql.Clob interfaces according to the JDBC 2.0 specification. With this implementation, an oracle.sql.BLOB can be used wherever a java.sql.Blob is expected and an oracle.sql.CLOB can be used wherever a java.sql.Clob is expected.

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

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:

Using OracleResultSet: BLOB and CLOB Objects Retrieved Represent LOB Locators of Current Row

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(), the retrieved locator still refers to the original LOB row.

To retrieve the locator for the most current row, you must call getXXXX() on the OracleResultSet each time a move operation is made, where XXXX is a BLOB, CLOB or BFILE.

JDBC Syntax References and Further Information

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

See:


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 3-42 DBC oracle.sql.BLOB Methods To Modify BLOB Values
Method Description

int putBytes(long, byte[])

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

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

Table 3-43 DBC 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 3-44 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 getBinaryOutputStream()

Writes to LOB as a binary stream

JDBC oracle.sql.CLOB Methods To Modify CLOB Values

Table 3-45 JDBC oracle.sql.CLOB Methods to Modify CLOB Values
Method Description

int putString(long, java.lang.String)

Inserts the string into the LOB, starting at the given offset

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 3-46 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

boolean equals(java.lang.Object)

Compares this LOB with another

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 3-47 JDBC oracle.sql.CLOB Methods and Properties for CLOB-Buffering
Method Description

java.io.InputStream getAsciiStream()

Reads the LOB as an ASCII stream

java.io.OutputStream getAsciiOutputStream()

Writes to the LOB from an ASCII stream

java.io.Reader getCharacterStream()

Reads the LOB as a character stream

java.io.Writer getCharacterOutputStream()

Writes to LOB from a character stream

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

Table 3-48 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

boolean equals(java.lang.Object)

Compares this BFILE with another. Compares locator bytes.

long length()

Returns the length of the BFILE

boolean fileExists()

Checks if the operating system (OS) file referenced by this BFILE exists

public void openFile()

Opens the OS file referenced by this BFILE

public void closeFile()

Closes the OS file referenced by this BFILE

public boolean isFileOpen()

Checks if this BFILE is already open

public java.lang.String getDirAlias()

Gets the directory alias 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 3-49 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: OracleBlob and OracleClob Do Not Work in Oracle8i 8.1.x and
Higher Releases

OracleBlob and OracleClob were Oracle specific functions used in JDBC 8.0.x drivers to access LOB data. In Oracle8i Releases 8.1.x and higher, OracleBlob and OracleClob are deprecated.

If you use OracleBlob or OracleClob to access LOB data, you will receive the following typical error message, for example, when attempting to manipulate LOBs with Oracle8i Release 8.1.5 JDBC Thin Driver:

"Dumping lobs java.sql.SQLException: ORA-03115: unsupported network datatype or 
representation etc."

See Oracle9i JDBC Developer's Guide and Reference for a description of these non-supported functions and alternative and improved JDBC methods.

For further ideas on working with LOBs with Java, refer to the LOB examples shipped with Oracle9i or see http://www.oracle.com/.

JDBC Temporary LOB APIs

Oracle9i JDBC drivers contain APIs to create and close temporary LOBs. These APIs can replace prior releases' workaround of using the following procedures from the DBMS_LOB PL/SQL package:

Table 3-50 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 3-51 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

See Also:

Chapter 11, "Temporary LOBs"

JDBC: Opening and Closing LOBs

oracle.sql.CLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Clob interface. Table 3-51 lists the new Oracle extension APIs in oracle.sql.CLOB for accessing temporary CLOBs.

Oracle9i 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's implementation of standard JDBC java.sql.Blob interface. Table 3-52 lists the Oracle extension APIs in oracle.sql.BLOB that open and close BLOBs. These are new for this release.

Table 3-52 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

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 Opened

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

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's implementation of the standard JDBC java.sql.Clob interface. Table 3-53 lists the new Oracle extension APIs in oracle.sql.CLOB to open and close CLOBs.

Table 3-53 JDBC: Opening and Closing CLOBs
Methods Description

public void open(int mode) throws SQLException

Open the CLOB

public boolean isOpen() throws SQLExceptio

See if the CLOB is opened

public void close() throws SQLException

Close the CLOB

Opening the CLOB

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

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

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 3-54 lists the new Oracle extension APIs in oracle.sql.BFILE for opening and closing BFILEs.

Table 3-54 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 readonly 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's 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 (); 
  } 
} 

Trimming LOBs using JDBC

Oracle9i JDBC drivers contain APIs to trim internal LOBs. These APIs replace previous techniques that used DBMS_LOB.trim().

JDBC: Trimming BLOBs

oracle.sql.BLOB class is Oracle JDBC driver's implementation of the standard JDBC java.sql.Blob interface. Table 3-55 lists the new Oracle extension API in oracle.sql.BLOB that trims BLOBs.

Table 3-55 JDBC: Trimming BLOBs
Methods Description

public void trim(long newlen) throws SQLException

Trims the BLOB

The trim API is defined as follows:

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

The newlen parameter specifies the new length of the BLOB.

JDBC: Trimming CLOBs

oracle.sql.CLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Clob interface. Table 3-56 lists the new Oracle extension API in oracle.sql.CLOB that trims CLOBs.

Table 3-56 JDBC: Trimming CLOBs
Methods Description

public void trim(long newlen) throws SQLException

Trims the CLOB

The trim API is defined as follows:

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

The newlen parameter specifies the new length of the CLOB.

See:

Chapter 10, "Internal Persistent LOBs" "Java (JDBC): Trimming LOB Data" , for an example.

JDBC: New LOB Streaming APIs

Oracle9i JDBC drivers contains the new LOB streaming APIs to read from/write to a LOB at the requested position from a Java stream. In prior releases, LOB streaming APIs did not specify the offset.

New JDBC BLOB Streaming APIs

oracle.sql.BLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Blob interface. Table 3-57 lists the new Oracle extension APIs in oracle.sql.BLOB that manipulate the BLOB content from the requested position.

Table 3-57 JDBC: New BLOB Streaming APIs
Methods Description

public java.io.OutputStream getBinaryOutputStream (long pos) throws SQLException

Writes to the BLOB from a stream

public java.io.InputStream getBinaryStream(long pos) throws SQLException

Reads from the BLOB as a stream

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 getBinaryOutputStream(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

New CLOB Streaming APIs

oracle.sql.CLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Clob interface. Table 3-58 lists the new Oracle extension APIs in oracle.sql.CLOB that manipulate the CLOB content from the requested position.

Table 3-58 JDBC: New CLOB Streaming APIs
Methods Description

public java.io.OutputStream getAsciiOutputStream (long pos) throws SQLException

Writes to the CLOB from an ASCII stream

public java.io.Writer getCharacterOutputStream(long pos) throws SQLException

Writes to the CLOB from a character stream

public java.io.InputStream getAsciiStream(long pos) throws SQLException

Reads from the CLOB as an ASCII stream

public java.io.Reader getCharacterStream(long pos) throws SQLException

Reads from the CLOB as a character stream

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 getAsciiOutputStream(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 getCharacterOutputStream(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

New BFILE Streaming APIs

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

Table 3-59 JDBC: New 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)


Note:

Some of the Java code strings (in quotes) in the example should appear on one line, but instead, they wrap to the next lines. For example, the stmt.execute lines. Be aware of this if you are using this code and ensure that the strings appear on one line.



/* 
 * 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's 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.getCharacterOutputStream(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.getBinaryOutputStream(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 a 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:

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.

OLEDB (Oracle Provider for OLEDB -- 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 Visual Basic, 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:


Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback