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

• 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 /rdbms/demo directory in a future release.

In cases where the Pro*COBOL interface does not supply the required functionality, you can call OCI via C. We do not provide an example 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:

• To modify internal LOBs, see Table 3-22

• To read or examine internal and external LOB values, see Table 3-23

• To create or free temporary LOB, or check LOB locator, see Table 3-24

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

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

• For LOB buffering, see Table 3-27

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

Pro*COBOL Emvbedded SQL Statements To Modify Internal LOBs (BLOB, CLOB, and NCLOB) Values

WRITE

Table 3-22 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-23 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-24  Pro*COBOL Embedded SQL Statements ForTemporary 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-25  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-26 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-27  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 External LOBs (BFILEs)

Table 3-28  Pro*COBOL 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.