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 tables below, 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:

• Chapter 9, "Internal Persistent LOBs"

• Chapter 10, "Temporary LOBs"

• Chapter 11, "External LOBs (BFILEs)"

You will also be able to access these example scripts from your Oracle8i software CD /rdbams/demo directory in a future release.

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

Pro*C statements that operate on BLOBs, CLOBs, and NCLOBs are listed below:

• To modify internal LOBs, see Table 3-15

• To read or examine LOB values, see Table 3-16

• To create or free temporary LOB, or check if Temporary LOB exists, see Table 3-17

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

• To operate on LOB locators, see Table 3-19

• For LOB buffering, see Table 3-20

• To open or close LOBs or BFILEs, see Table 3-21

Pro*C/C++ Embedded SQL Statements To Modify Internal LOBs (BLOB, CLOB, and NCLOB) Values

Table 3-15 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-16 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-17  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-18  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-19  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-20  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 LOBs and External LOBs (BFILEs)

Table 3-21  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.