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	ProC/C++ Precompiler Programmer's Guide*	"Using C/C++ (Pro*C) To Work with LOBs".
COBOL	Pro*COBOL Precompiler	ProCOBOL 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.	*ProC/C++ and ProCOBOL*
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:

Internal persistent LOBs and Temporary LOBs: Read and modify operations, either entirely or in a piece-wise manner.
BFILEs: Read operations

See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for detailed documentation, including parameters, parameter types, return values, and example code.

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.

Internal 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, specify the directory in the correct case. See Chapter 12, "External LOBs (BFILEs)", "Directory Object" 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.

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:

CLOBs and NCLOBs: Offset and amount parameters are always in characters
BLOBs and BFILES: Offset and amount parameters are always in bytes

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:

To modify internal LOB values, see Table 3-4
To read or examine LOB values, see Table 3-5
To create, free, or check on temporary LOBs, see Table 3-6
For read-only functions on external LOBs (BFILEs), see Table 3-7
To open or close a LOB, or check if LOB is open, see Table 3-8

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:

For reading from internal and external LOBs (BFILEs)
For writing to internal LOBs

OCI also includes functions that you can use to:

Access data stored in internal LOBs (BLOBs, CLOBs, NCLOBs) and external LOBs (BFILEs)
Modify internal LOBs (BLOBs, CLOBs, NCLOBs

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:

Oracle Call Interface Programmer's Guide for detailed documentation, including parameters, parameter types, return values, and example code.
Oracle9i Database Globalization Support Guide for detailed information about implementing applications in different languages.

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:

CLOBs and NCLOBs: offset and amount parameters are always in characters
BLOBs and BFILES: 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:
- 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
OCILobRead: 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'.
OCILobWrite: 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 input buffer 'bufp'.
- Output amount is in characters. 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 OCILobCopy, OCILobErase, OCILobLoadFromFile, and OCILobTrim. All these operations refer to the amount of LOB data on the server.

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:

For UNIX systems at:
- /ORACLE_HOME/rdbms/demo/demolb.c
- /ORACLE_HOME/rdbms/demo/demolb2.c
- /ORACLE_HOME/rdbms/demo/demolbs.c
For Windows NT at:
- \ORACLE_HOME\Oci\Samples\demolb.c,....

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:

To modify internal LOBs, see Table 3-9
To read or examine LOB values, see Table 3-10
To create or free temporary LOB, or check if Temporary LOB exists, see Table 3-11
For read only functions on external LOBs (BFILEs), see Table 3-12
To operate on LOB locators, see Table 3-13
For LOB buffering, see Table 3-14
To open and close LOBs, see Table 3-15

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 `BFILE`s.
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:

OCCI encompasses more Oracle functionality than JDBC. OCCI provides all the functionality of OCI that JDBC does not provide.
OCCI provides compiled performance. With compiled programs, the source code is already written as "close to the machine" as possible. Since JDBC is an interpreted API, it cannot provide the performance of a compiled API. With an interpreted program, performance degrades as each line of code must be interpreted individually into code that is close to the machine.
OCCI provides memory management with smart pointers. You do not have to be concerned about managing memory for OCCI objects. This results in robust higher performance application code.
Navigational access of OCCI enables you to intuitively access objects and call methods. Changes to objects persist without need to write corresponding SQL statements. If you use the client side cache, the navigational interface performs better than the object interface.
With respect to ODBC, the OCCI API is simpler to use. Since ODBC is built on the C language, OCCI has all the advantages C++ provides over C. Moreover, ODBC has a reputation as being difficult to learn. The OCCI, by contrast, is designed for ease of use.

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:

For reading from internal and external LOBs (BFILEs)
For writing to internal LOBs

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 to access and modify data stored in internal CLOBs and NCLOBs
OCCIBlob Class to access and modify data stored in internal BLOBs
OCCIBfile Class to access and read data stored in external LOBs (BFILEs)

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:

Oracle C++ Call Interface Programmer's Guide for detailed documentation, including parameters, parameter types, return values, and example code.
Oracle9i Database Globalization Support Guide for detailed information about implementing applications in different languages.

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:

OCCIClob: offset and amount parameters are always in characters
OCCIBlob: offset and amount parameters are always in bytes
OCCIBfile: 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:
- OCCIClob(): in characters
- OCCIBlob(): in bytes
- OCCIBfile(): in bytes
Amount parameter: The amount parameter is always as follows:
- OCCIClob: in characters, when referring to a server-side LOB
- OCCIBlob: in bytes, when referring to a client-side buffer
- OCCIBfile: 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:
- OCCIClob.length(): in characters
- OCCIBlob.length(): in bytes
- OCCIBfile.length(): in bytes
OCCIClob.read() and OCCIBlob.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'.
OCCIClob.write() and OCCIBlob.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:

OCCIClob.copy()
OCCIClob.erase()
OCCIClob.trim()
For LoadFromFile functionality, overloaded OCCICLob.copy()

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

NCLOBs

NCLOBs parameters are allowed in methods
NCLOBs parameters are not allowed as attributes in object types

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:

Oracle C++ Call Interface Programmer's Guide
http://www.oracle.com/ search for articles and product information featruring OCCI.

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

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

To modify internal LOBs, see Table 3-16
To read or examine LOB values, see Table 3-17
For read only methods on external LOBs (BFILEs), see Table 3-18
Other LOB OCCI methods are described in Table 3-19
To open and close LOBs, see Table 3-20

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 C`LOB or BLOB` valu

3 LOB Support in Different Programmatic Environments

Eight Programmatic Environments Operate on LOBs

Table 3-1 LOBs' Eight Programmatic Environments

Comparing the LOB Interfaces

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

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

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

Provide a LOB Locator Before Invoking the DBMS_LOB Routine

PL/SQL - LOB Guidelines

Client PL/SQL Procedures Cannot Call DBMS_LOB Routines

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

DBMS_LOB.LOADFROMFILE: Amount Parameter Value

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

PL/SQL Functions and Procedures that Operate on LOBs

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

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

PL/SQL Functions/Procedures To Operate on Temporary LOBs

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

PL/SQL Read-Only Functions/Procedures for BFILEs

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

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

Using C (OCI) To Work With LOBs

Set CSID Parameter To OCI_UCS2ID to Read/Write in UCS2

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

Fixed Width Character Set Rules

Varying-Width Character Set Rules

Other Operations

NCLOBs

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

OCILobRead: Specify Amount Parameter to be 4 gigabytes - 1

OCI LOB Examples

Further Information About OCI

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

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

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

OCI Functions For Temporary LOBs

Table 3-11 OCI Functions for Temporary LOBs

OCI Read-Only Functions For BFILEs

Table 3-12 OCI Read-Only Functions for BFILES

OCI LOB Locator Functions

Table 3-13 OCI LOB-Locator Functions

OCI LOB-Buffering Functions

Table 3-14 OCI LOB-Buffering Functions

OCI Functions To Open and Close Internal and External LOBs

Table 3-15 OCI Functions To Open and Close Internal and External LOBs

OCI Example -- Is the LOB Open: main() and seeIfLOBIsOpen

Using C++ (OCCI) To Work With LOBs

Distinct Classes for Each LOB Type

OCCIClob Class

OCCIBlob Class

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

Fixed Width Character Set Rules

Varying-Width Character Set Rules

OffSet and Amount Parameters for Other OCCI Operations

NCLOBs

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

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

Further Information About OCCI

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

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

3
LOB Support in Different Programmatic Environments

PL/SQL Functions/Procedures To Read or Examine Internal and External
LOB Values

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

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