Pro*COBOL Precompiler Programmer's Guide
Release 8.1.5

A68023-01

Library

Product

Contents

Index

Prev Next

13
Large Objects (LOBs)

This chapter describes the support provided by embedded SQL statements for the LOB (Large Object) datatypes. The four types of LOBs are introduced and compared to the older LONG and LONG RAW datatypes.

The embedded SQL interface in Pro*COBOL is shown to provide similar functionality to that of the PL/SQL language.

The LOB statements and their options and host variables are presented.

Lastly, an example of Pro*COBOL programming using the LOB interface is presented.

The main sections are:

What are LOBs?

LOBs (large objects) are database types that are used to store large amounts of data (maximum size is 4 Gigabytes) such as ASCII text, text in National Characters, files in various graphics formats, and sound wave forms.

Internal LOBs

Internal LOBs (BLOBs, CLOBs, NCLOBs) are stored in database table spaces and have transactional support (Commit, Rollback, etc. work with them) of the database server.

BLOBs (Binary LOBs) store unstructured binary (also called "raw") data, such as video clips.

CLOBs (Character LOBs) store large blocks of single-byte fixed-width character data from the database character set.

NCLOBs (National Character LOBs) store large blocks of single-byte, fixed-width, or variable-width multi-byte character data from the national character set.

External LOBs

External LOBs are operating system files outside the database tablespaces, that have no transactional support from the database server.

BFILEs (Binary Files) store data in external binary files. A BFILE can be in GIF, JPEG, MPEG, MPEG2, text, or other formats.

Security for BFILEs

The DIRECTORY object is used to access and use BFILEs. The DIRECTORY is a logical alias name for the actual physical directory in the server filesystem containing the file. Users are permitted to access the file only if granted access privilege on the DIRECTORY object.

Two kinds of SQL statements can be used with BFILEs:

A sample CREATE DIRECTORY directive is:

     EXEC SQL CREATE OR REPLACE DIRECTORY "Mydir" AS '/usr/home/mydir' END-EXEC.

Other users or roles can read the directory only if you grant them permission with a DML (Data Manipulation Language) statement, such as GRANT. For example, to allow user scott to read BFILES in directory /usr/home/mydir:

     EXEC SQL GRANT READ ON DIRECTORY "Mydir" TO scott END-EXEC.

Up to 10 BFILES can be opened simultaneously in one session. This default value can be changed by setting the SESSION_MAX_OPEN_FILES parameter.

See Oracle8i Application Developer's Guide - Fundamentals for more details on DIRECTORY objects and BFILE security. See Oracle SQL Server Reference for more details on the GRANT command.

LOBs vs. LONG and LONG RAW

LOBs are different from the older LONG and LONG RAW datatypes in many ways.

Migration of existing LONG and LONG Raw attributes to LOBs is recommended by Oracle. Oracle plans to end support of LONG and LONG RAW in future releases. See Oracle8i Migration for more information on migration.

LOB Locators

A LOB locator points to the actual LOB contents. The locator is returned when you retrieve the LOB, not the LOB's contents. LOB locators cannot be saved in one transaction or session and used again in a later transaction or session.

Temporary LOBs

You can create temporary LOBs to assist your use of database LOBs. Temporary LOBs are like local variables, and are not associated with any table. They are only accessible by their creator using their locators, and are deleted when a session ends.

There is no support for temporary BFILES. Temporary LOBs are only permitted to be input variables (IN values) in the WHERE clause of an INSERT statement, in the SET clause of an UPDATE, or in the WHERE clause of a DELETE statement. Temporary LOBs have no transactional support from the database server, which means that you cannot do COMMITS or ROLLBACKs on them.

Temporary LOB locators can span transactions. They also are deleted when the server abnormally terminates, and when an error is returned from a database SQL operation.

LOB Buffering Subsystem

The LBS (LOB Buffering Subsystem) is an area of user memory provided for use as a buffer for one or more LOBs in the client's address space.

Buffering has these advantages, especially for applications on a client that does many small reads and writes to specific regions of the LOB:

Oracle provides a simple buffer subsystem; not a cache. Oracle does not guarantee that the contents of a buffer are always synchronized with the server LOB value. Use the FLUSH statement to actually write updates in the server LOB.

Buffered read/write of a LOB are performed through its locator. A locator enabled for buffering provides a consistent read version of the LOB until you perform a write through that locator.

After being used for a buffered WRITE, a locator becomes an updated locator and provides access to the latest LOB version as seen through the buffering subsystem. All further buffered WRITEs to the LOB can only be done through this updated locator. Transactions involving buffered LOB operations cannot migrate across user sessions.

The LBS is managed by the user, who is responsible for updating server LOB values by using FLUSH statements to update them. The LBS is single-user and single-threaded. Use ROLLBACK and SAVEPOINT actions to guarantee correctness in the server LOBs. Transactional support for buffered LOB operations is not guaranteed by Oracle. To ensure transactional semantics for buffered LOB updates, you must maintain logical savepoints to perform a rollback in the event of an error.

For more information on the LBS, see Oracle8i Application Developer's Guide - Fundamentals.

How to Use LOBs in Your Program

Two Ways to Access LOBs

The two methods available to access LOBs in Pro*COBOL are:

The SQL statements are designed to give users a functional equivalent to the PL/SQL interface.

The following table compares LOB access in PL/SQL and embedded SQL statements in Pro*COBOL. Empty boxes indicate missing functionality.

Table 13-1 LOB Access Methods
PL/SQL1  Pro*COBOL Embedded SQL 

COMPARE()  

 

INSTR()  

 

SUBSTR()  

 

APPEND()  

APPEND  

:=  

ASSIGN  

 

 

 

 

CLOSE()  

CLOSE  

COPY()  

COPY  

CREATETEMPORARY()  

CREATE TEMPORARY  

 

DISABLE BUFFERING  

 

ENABLE BUFFERING  

ERASE()  

ERASE  

GETCHUNKSIZE()  

DESCRIBE  

ISOPEN()  

DESCRIBE  

FILECLOSE()  

CLOSE  

FILECLOSEALL()  

FILE CLOSE ALL  

FILEEXISTS()  

DESCRIBE  

FILEGETNAME()  

DESCRIBE  

FILEISOPEN()  

DESCRIBE  

FILEOPEN()  

OPEN  

BFILENAME()  

FILE SET2  

 

FLUSH BUFFER  

FREETEMPORARY()  

FREE TEMPORARY  

GETLENGTH()  

DESCRIBE  

=  

 

ISTEMPORARY()  

DESCRIBE  

LOADFROMFILE()  

LOAD FROM FILE  

 

 

OPEN()  

OPEN  

READ()  

READ  

TRIM()  

TRIM  

WRITE()  

WRITE  

WRITEAPPEND()  

WRITE  

1 From dbmslob.sql. All routines are prefixed with 'DBMS_LOB.' except BFILENAME.
2 The BFILENAME() built in SQL function may also be used.

Note: You must explicitly lock the row before using any of the new statements that modify or change a LOB in any way. Operations that can modify a LOB value are APPEND, COPY, ERASE, LOAD FROM FILE, TRIM, and WRITE.

LOB Locators in Your Application

To use LOB locators in your Pro*COBOL application use these pseudo-types:

For example, to declare an NCLOB variable called MY-NCLOB:

 01  MY-NCLOB    SQL-NCLOB.

Initializing a LOB

Internal LOBs

To initialize a BLOB to empty, use the EMPTY_BLOB() function or, use an ALLOCATE SQL statement. For CLOBs and NCLOBs, use the EMPTY_CLOB() function. See Oracle8i SQL Reference for more about EMPTY_BLOB() and EMPTY_CLOB(). These functions are permitted only in the VALUES clause of an INSERT statement or as the source of the SET clause in an UPDATE statement.

For example:

     EXEC SQL INSERT INTO lob_table (a_blob, a_clob)
        VALUES (EMPTY_BLOB(), EMPTY_CLOB()) END-EXEC.

The ALLOCATE statement allocates a LOB locator and initializes it to empty. So, the following code is equivalent to the previous example:

...
 01  A-BLOB      SQL-BLOB.
 01  A-CLOB      SQL-CLOB.
...
     EXEC SQL ALLOCATE :A-BLOB END-EXEC.
     EXEC SQL ALLOCATE :A-CLOB END-EXEC.
     EXEC SQL INSERT INTO lob_table (a_blob, a_clob)
        VALUES (:A-BLOB, :A-CLOB) END-EXEC.

External LOBs

Use the LOB FILE SET statement to initialize the DIRECTORY alias of the BFILE and FILENAME this way:

...
 01  ALIAS        PIC X(14) VARYING.
 01  FILENAME     PIC X(14) VARYING.
 01  A-BFILE      SQL-BFILE.
 ...
     MOVE "lob_dir" TO ALIAS-ARR.
     MOVE 7 TO ALIAS-LEN.
     MOVE "image.gif" TO FILENAME-ARR
     MOVE 9 TO FILENAME-LEN..     
     EXEC SQL ALLOCATE :A-BFILE END-EXEC.
     EXEC SQL LOB FILE SET :A-BFILE
        DIRECTORY = :ALIAS, FILENAME = :FILENAME END-EXEC.
     EXEC SQL INSERT INTO file_table (a_bfile) VALUES (:A-BFILE) END-EXEC.

Refer to Oracle8i Application Developer's Guide - Fundamentals for a complete description of DIRECTORY object naming conventions and DIRECTORY object privileges.

Alternatively, you can use the BFILENAME('directory', 'filename') function in an INSERT or UPDATE statement to initialize a BFILE column or attribute for a particular row, and give the name of the actual physical directory and filename:

     EXEC SQL INSERT INTO file_table (a_bfile)
        VALUES (BFILENAME('lob_dir', 'image.gif'))
           RETURNING a_bfile INTO :A-BFILE END-EXEC.

Note: BFILENAME() does not check permissions on the directory or filename, or whether the physical directory actually exists. Subsequent file accesses that use the BFILE locator will do those checks and return an error if the file is inaccessible.

Temporary LOBs

A temporary LOB is initialized to empty when it is first created using the embedded SQL LOB CREATE TEMPORARY statement. The EMPTY_BLOB() and EMPTY_CLOB() functions cannot be used with temporary LOBs.

Rules for LOB Statements

Here are the rules for using LOB statements:

For All LOB Statements

These general restrictions and limitations apply when manipulating LOBs with the SQL LOB statements:

For the LOB Buffering Subsystem

For the LBS, these rules must be followed:

Note: The FLUSH statement must be used on a LOB enabled by the LOB Buffering Subsystem before

For Host Variables

Use the following rules and notes for the LOB statements:

LOB Statements

The statements are presented alphabetically. In all the statements where it appears, database refers to a database connection

APPEND

Purpose

This statement appends a LOB value at the end of another LOB.

Syntax

     EXEC SQL [AT [:]database] LOB APPEND :src TO :dst END-EXEC.

Host Variables

src (IN)

An internal LOB locator uniquely referencing the source LOB.

dst (IN OUT)

An internal LOB locator uniquely referencing the destination LOB.

Usage Notes

The data is copied from the source LOB to the end of the destination LOB, extending the destination LOB up to a maximum of 4 Gigabytes. If the LOB is extended beyond 4 Gigabytes, an error will occur.

The source and destination LOBs must already exist and the destination LOB must be initialized.

Both the source and destination LOBs must be of the same internal LOB type. It is an error to have enabled LOB buffering for either type of locator.

ASSIGN

Purpose

Assigns a LOB or BFILE locator to another.

Syntax

     EXEC SQL [AT [:]database] LOB ASSIGN :src to :dst END-EXEC.

Host Variables

src (IN)

LOB or BFILE locator source copied from.

dst (IN OUT)

LOB or BFILE locator copied to.

Usage Notes

After the assignment, both locators refer to the same LOB value. The destination LOB locator must be a valid initialized (ALLOCATEd) locator.

For internal LOBs, the source locator's LOB value is copied to the destination locator's LOB value only when the destination locator is stored in the table. For Pro*COBOL, issuing a FLUSH of an object containing the destination locator will copy the LOB value.

An error is returned when a BFILE locator is assigned to an internal LOB locator and vice-versa. It is also an error if the src and dst LOBs are not of the same type.

If the source locator is for an internal LOB that was enabled for buffering, and the source locator has been used to modify the LOB value through the LOB Buffering Subsystem, and the buffers have not been FLUSHed since the WRITE, then the source locator cannot be assigned to the destination locator. This is because only one locator per LOB can modify the LOB value through the LOB Buffering Subsystem.

CLOSE

Purpose

Close an open LOB or BFILE.

Syntax

     EXEC SQL [AT [:]database] LOB CLOSE :src END-EXEC.

Host Variables

src (IN OUT)

The locator of the LOB or BFILE to be closed.

Usage Notes

It is an error to close the same LOB twice either with different locators or with the same locator. For external LOBs, no error is produced if the BFILE exists but has not been opened.

It is an error to COMMIT a transaction before closing all previously opened LOBs. At transaction ROLLBACK time, all LOBs that are still open will be discarded without first being closed.

COPY

Purpose

Copy all or part of a LOB value into a second LOB.

Syntax

     EXEC SQL [AT [:]database] LOB COPY :amt FROM :src [AT :src_offset]
        TO :dst [AT :dst_offset] END-EXEC.

Host Variables

amt (IN)

The maximum number of bytes for BLOBs, or characters for CLOBs and NCLOBs, to copy.

src (IN)

The locator of the source LOB.

src_offset (IN)

This is the number of characters for CLOB or NCLOB, and the number of bytes for a BLOB, starting from 1 at the beginning of the LOB.

dst (IN)

The locator of the destination LOB.

dst_offset (IN)

The destination offset. Same rules as for src_offset.

Usage Notes

If the data already exists at the destination's offset and beyond, it is overwritten with the source data. If the destination's offset is beyond the end of the current data, zero-byte fillers (BLOBs) or spaces (CLOBs) are written into the destination LOB from the end of the current data to the beginning of the newly written data from the source.

The destination LOB is extended to accommodate the newly written data if it extends beyond the current length of the destination LOB. It is a runtime error to extend this LOB beyond 4 Gigabytes.

It is also an error to try to copy from a LOB that is not initialized.

Both the source and destination LOBs must be of the same type. LOB buffering must not be enabled for either locator.

The amt variable indicates the maximum amount to copy. If the end of the source LOB is reached before the specified amount is copied, the operation terminates without an error.

To make a temporary LOB permanent, the COPY statement must be used to explicitly COPY the temporary LOB into a permanent one.

CREATE TEMPORARY

Purpose

Creates a temporary LOB.

Syntax

     EXEC SQL [AT [:]database] LOB CREATE TEMPORARY :src END-EXEC.

Host Variables

src (IN OUT)

Before execution, when IN, src is a LOB locator previously ALLOCATEd.

After execution, when OUT, src is a LOB locator that will point to a new empty temporary LOB.

Usage Notes

After successful execution, the locator points to a newly created temporary LOB that resides on the database server independent of a table. The temporary LOB is empty and has zero length.

At the end of a session, all temporary LOBs are freed. READs and WRITEs to temporary LOBs never go through the buffer cache.

DISABLE BUFFERING

Purpose

Disables LOB buffering for the LOB locator.

Syntax

     EXEC SQL [AT [:]database] LOB DISABLE BUFFERING :src END-EXEC.

Host Variable

src (IN OUT)

An internal LOB locator.

Usage Notes

This statement does not support BFILEs. Subsequent reads or writes will not be done through the LBS.

Note: Use a FLUSH BUFFER command to make changes permanent, since this statement does not implicitly flush the changes made in the LOB Buffering Subsystem.

ENABLE BUFFERING

Purpose

Enables LOB buffering for the LOB locator.

Syntax

     EXEC SQL [AT [:]database] LOB ENABLE BUFFERING :src END-EXEC.

Host Variable

src (IN OUT)

An internal LOB locator.

Usage Notes

This statement does not support BFILEs. Subsequent reads and writes are done through the LBS.

ERASE

Purpose

Erases a given amount of LOB data starting from a given offset.

Syntax

     EXEC SQL [AT [:]database] LOB ERASE :amt 
        FROM :src [AT :src_offset] END-EXEC.

Host Variables

amt (IN OUT)

The input is the number of bytes or characters to erase. The returned output is the actual number erased.

src (IN OUT)

An internal LOB locator.

src_offset (IN)

The offset from the beginning of the LOB, starting from 1.

Usage Notes

This statement does not support BFILEs.

After execution, amt returns the actual number of characters/bytes that were erased. The actual number and requested number will differ if the end of the LOB value is reached before erasing the requested number of characters/bytes. If the LOB is empty, amt will indicate that 0 characters/bytes were erased.

For BLOBs, erasing means zero-byte fillers overwrite the existing LOB value. For CLOBs, erasing means that spaces overwrite the existing LOB value.

FILE CLOSE ALL

Purpose

Closes all BFILES opened in the current session.

Syntax

     EXEC SQL [AT [:]database] LOB FILE CLOSE ALL END-EXEC.

Usage Notes

If there are any open files in the session whose closure has not been handled properly, you can use the FILE CLOSE ALL statement to close all files opened in the session, and resume file operations from the beginning.

FILE SET

Purpose

Sets DIRECTORY alias and FILENAME in a BFILE locator.

Syntax

     EXEC SQL [AT [:]database] LOB FILE SET :file
        DIRECTORY = :alias, FILENAME = :filename END-EXEC.

Host Variables

file (IN OUT)

BFILE locator where the DIRECTORY alias and FILENAME is set.

alias (IN)

DIRECTORY alias name to set.

filename (IN)

The FILENAME to set.

Usage Notes

The given BFILE locator must be first ALLOCATEd prior to its use in this statement.

Both the DIRECTORY alias name and FILENAME must be provided.

The maximum length of the DIRECTORY alias is 30 bytes. The maximum length of the FILENAME is 255 bytes.

The only external datatypes supported for use with the DIRECTORY alias name and FILENAME attributes are CHARZ, STRING, VARCHAR, VARCHAR2 and CHARF.

It is an error to use this statement with anything but an external LOB locator.

FLUSH BUFFER

Purpose

Writes this LOB's buffers to the database server.

Syntax

     EXEC SQL [AT [:]database] LOB FLUSH BUFFER :src [FREE] END-EXEC.

Host Variables

src (IN OUT)

Internal LOB locator.

Usage Notes

Writes the buffer data to the database LOB in the server from the LOB referenced by the input locator.

LOB buffering must have already been enabled for the input LOB locator.

The FLUSH operation, by default, does not free the buffer resources for reallocation to another buffered LOB operation. However, if you want to free the buffer explicitly, you can include the optional FREE keyword to so indicate.

FREE TEMPORARY

Purpose

Free the temporary space for the LOB locator.

Syntax

     EXEC SQL [AT [:]database] LOB FREE TEMPORARY :src END-EXEC.

Host Variable

src (IN OUT)

The LOB locator pointing to the temporary LOB.

Usage Notes

The input locator must point to a temporary LOB. The output locator is marked not initialized and can be used in subsequent LOB statements.

LOAD FROM FILE

Purpose

Copy all or a part of a BFILE into an internal LOB.

Syntax

     EXEC SQL [AT [:]database] LOB LOAD :amt 
        FROM FILE :file [AT :src_offset] INTO :dst [AT :dst_offset] END-EXEC.

Host Variables

amt (IN)

Maximum number of bytes to be loaded.

file (IN OUT)

The source BFILE locator.

src_offset (IN)

The number of bytes offset from the beginning of the file, starting from 1.

dst (IN OUT)

The destination LOB locator which can be BLOB, CLOB, be NCLOB.

dst_offset (IN)

The number of bytes (for BLOBs) or characters (CLOBs and NCLOBs) from the beginning of the destination LOB where writing will begin. It starts at 1.

Usage Notes

The data is copied from the source BFILE to the destination internal LOB. No character set conversions are performed when copying the BFILE data to a CLOB or NCLOB. Therefore, the BFILE data must already be in the same character set as the CLOB or NCLOB in the database.

The source and destination LOBs must already exist. If the data already exists at the destination's start position, it is overwritten with the source data. If the destination's start position is beyond the end of the current data, zero-byte fillers (BLOBs) or spaces (CLOBs and NCLOBs) are written into the destination LOB from the end of the data to the beginning of the newly written data from the source.

The destination LOB is extended to accommodate the newly written data if it extends beyond the current length of the destination LOB. It is an error to extend this LOB beyond 4 Gigabytes.

It is also an error to copy from a BFILE that is not initialized.

The amount parameter indicates the maximum amount to load. If the end of the source BFILE is reached before the specified amount is loaded, the operation terminates without error.

OPEN

Purpose

Open a LOB or BFILE for read or read/write access.

Syntax

     EXEC SQL [AT [:]database] LOB OPEN :src 
        [ READ ONLY | READ WRITE ] END-EXEC.

Host Variables

src (IN OUT)

LOB locator of the LOB or BFILE.

Usage Notes

The default mode in which a LOB or BFILE can be OPENed is for READ ONLY access.

For internal LOBs, being OPEN is associated with the LOB, not the locator. Assigning an already OPENed locator to another locator does not count as OPENing a new LOB. Instead, both locators refer to the same LOB. For BFILEs, being OPEN is associated with the locator.

Only 32 LOBs can be OPEN at any one time. An error will be returned when the 33rd LOB is OPENed.

There is no support for writable BFILEs. Therefore, when you OPEN a BFILE in READ WRITE mode, an error is returned.

It is also an error to open a LOB in READ ONLY mode and then attempt to WRITE to the LOB.

READ

Purpose

Reads all or part of a LOB or BFILE into a buffer.

Syntax

     EXEC SQL [AT [:]database] LOB READ :amt FROM :src [AT :src_offset]
        INTO :buffer [WITH LENGTH :buflen] END-EXEC.

Host Variables

amt (IN OUT)

The input is the number of characters or bytes to be read. The output is the actual number of characters or bytes that were read.

If the amount of bytes to be read is larger than the buffer length it is assumed that the LOB is being READ in a polling mode. On input if this value is 0, then the data will be read in a polling mode from the input offset until the end of the LOB.

The number of bytes or characters actually read is returned in amt. If the data is read in pieces, amt will always contain the length of the last piece read.

When the end of a LOB is reached an ORA-1403: no data found error will be issued.

When reading in a polling mode, the application must invoke the LOB READ repeatedly to read more pieces of the LOB until no more data is left. Control the use of the polling mode with the NOT FOUND condition in a WHENEVER directive to catch the ORA-1403 error.

src (IN)

The LOB or BFILE locator.

src_offset (IN)

This is the absolute offset from the beginning of the LOB value from which to start reading. For character LOBs it is the number of characters from the beginning of the LOB. For binary LOBs or BFILEs it is the number of bytes. The first position is 1.

buffer (IN/OUT)

A buffer into which the LOB data will be read. The external datatype of the buffer is restricted to only a few types depending on the type of the source LOB. The maximum length of the buffer depends on the external datatype being used to store the LOB value. The following table summarizes the legal external datatypes and their corresponding maximum lengths categorized by source LOB type:

Table 13-2 Source LOB and Precompiler Datatypes
External LOB1  Internal LOB  Precompiler External Datatype  Precompiler Maximum Length 2  PL/SQL Datatype  PL/SQL Maximum Length 

BFILE  

BLOB  

RAW

VARRAW

LONG RAW

LONG VARRAW  

65535

65533

2147483647

2147483643  

RAW  

32767  

CLOB  

VARCHAR2

VARCHAR

LONG VARCHAR  

65535

65533

2147483643  

VARCHAR2  

32767  

NCLOB  

NVARCHAR2  

4000  

NVARCHAR2  

4000  

1 Any of the external datatypes shown can be used with BFILES.
2 Lengths are measured in bytes, not characters.

buflen (IN)

Specifies the length of the given buffer when it cannot be determined otherwise.

Usage Notes

A BFILE must already exist on the database server and it must have been opened using the input locator. You must have permission to read the file and you must have read permission on the directory.

It is an error to try to read from an un-initialized LOB or BFILE.

The length of the buffer is determined this way:

TRIM

Purpose

Truncates the LOB value.

Syntax

     EXEC SQL [AT [:]database] LOB TRIM :src TO :newlen END-EXEC.

Host Variables

src (IN OUT)

LOB locator for internal LOB.

newlen (IN)

The new length of the LOB value.

Usage Notes

This statement is not for BFILES. The new length cannot be greater than the current length, or an error is returned.

WRITE

Purpose

Writes the contents of a buffer to a LOB.

Syntax

     EXEC SQL [AT [:]database] LOB WRITE [APPEND] [ FIRST | NEXT | LAST | ONE ]
        :amt FROM :buffer [WITH LENGTH :buflen] 
           INTO :dst [AT :dst_offset] END-EXEC.

Host Variables

amt (IN OUT)

The input is the number of characters or bytes to be written.

The output is the actual number of characters or bytes that is written.

When writing using a polling method, amt will return the cumulative total length written for the execution of the WRITE statement after a WRITE LAST is executed. If the WRITE statement is interrupted, amt will be undefined.

buffer (IN)

A buffer from which the LOB data is written. See "READ" for the lengths of datatypes.

dst (IN OUT)

The LOB locator.

dst_offset (IN)

The offset from the beginning of the LOB (counting from 1), in characters for CLOBs and NCLOBs, in bytes for BLOBs.

buflen (IN)

The buffer length when it cannot be calculated in any other way.

Usage Notes

If LOB data already exists, it is overwritten with the data stored in the buffer. If the offset specified is beyond the end of the data currently in the LOB, zero-byte fillers or spaces are inserted into the LOB.

Specifying the keyword APPEND in the WRITE statement causes the data to automatically be written to the end of the LOB. When APPEND is specified, the destination offset is assumed to be the end of the LOB. It is an error to specify the destination offset when using the APPEND option in the WRITE statement.

The buffer can be written to the LOB in one piece (using the ONE orientation which is the default) or it can be provided piece-wise using a standard polling method.

Polling is begun by using FIRST, then NEXT to write subsequent pieces. The LAST keyword is used to write the final piece that terminates the write.

Using this piece-wise write mode, the buffer and the length can be different in each call if the pieces are of different sizes and from different locations.

If the total amount of data passed to Oracle is less than the amount specified by the amt parameter after doing all the writes, an error results.

The same rules apply for determining the buffer length as in the READ statement. See "READ".

DESCRIBE

Purpose

This is a statement that is equivalent to several OCI and PL/SQL statements (which is why it is saved for last). Use the LOB DESCRIBE SQL statement to retrieve attributes from a LOB. This capability is similar to OCI and PL/SQL procedures. The LOB DESCRIBE statement has this format:

Syntax

     EXEC SQL [AT [:]database] LOB DESCRIBE :src GET attribute1 [{, attributeN}]
        INTO :hv1 [[INDICATOR] :hv_ind1] [{, :hvN [[INDICATOR] :hv_indN] }] 
           END-EXEC.

where an attribute can be any of these choices:

CHUNKSIZE | DIRECTORY | FILEEXISTS | FILENAME | ISOPEN | ISTEMPORARY | LENGTH

Host variables

src (IN)

The LOB locator of an internal or external LOB.

hv1 ... hvN (OUT)

The host variables that receive the attribute values, in the order specified in the attribute name list.

hv_ind1 ... hv_indN (OUT)

Optional host variables that receive the indicator NULL status in the order of the attribute name list.

The following table describes the attributes, which LOB it is associated with, and the COBOL types into which they should be read:

Table 13-3 LOB Attributes
LOB Attribute  Attribute Description  Restrictions  COBOL Type 

CHUNKSIZE  

The amount (in bytes for BLOBs and characters for CLOBs/NCLOBs) of space used in the LOB chunk to store the LOB value. You speed up performance if you issue READ/WRITE requests using a multiple of this chunk size. If all WRITEs are done on a chunk basis, no extra/excess versioning is done nor duplicated. Users could batch up the WRITE until they have enough for a chunk instead of issuing several WRITE calls for the same CHUNK.  

BLOBs, CLOBs and NCLOBs only  

PIC S9(9) COMP  

DIRECTORY  

The name of the DIRECTORY alias for the BFILE. The length, n, is between 1 and 30 bytes. Use that length in the picture.  

FILE LOBs only  

PIC X(n) VARYING  

FILEEXISTS  

Determines whether or not the BFILE exists on the server's OS filesystem. FILEEXISTS is true when it is non-zero; false when it equals 0.  

FILE LOBs only  

PIC S9(9) COMP  

FILENAME  

The name of the BFILE. The length, n, is between 1 and 255 bytes. Use that length in the picture.  

FILE LOBs only  

PIC X(n) VARYING  

ISOPEN  

For BFILEs, if the input BFILE locator was never used in an OPEN statement, the BFILE is considered not to be OPENed by this locator. However, a different BFILE locator may have OPENed the BFILE. More than one OPEN can be performed on the same BFILE using different locators. For LOBs, if a different locator OPENed the LOB, the LOB is still considered to be OPEN by the input locator. ISOPEN is true when it is non-zero; false when it equals 0.  

 

PIC S9(9) COMP  

ISTEMPORARY  

Determines whether or not the input LOB locator refers to a temporary LOB or not. ISTEMPORARY is true when it is non-zero; false when it equals 0.  

BLOBs, CLOBs and NCLOBs only  

PIC S9(9) COMP  

LENGTH  

Length of BLOBs and BFILEs in bytes, CLOBs and NCLOBs in characters. For BFILEs, the length includes EOF if it exists. Empty internal LOBs have zero length. LOBs/BFILEs that are not initialized have undefined length.  

 

PIC 9(9) COMP  

Usage Notes

Indicator variables should be declared as PIC S9(4) COMP. After execution has completed, SQLERRD(3) contains the number of attributes retrieved without error. If there was an execution error, the attribute at which it occurred is one more than the contents of SQLERRD(3).

DESCRIBE Example

Here is a simple Pro*COBOL example that extracts the DIRECTORY and FILENAME attributes of a given BFILE:

...
 01  A-BFILE      SQL-BFILE.
 01  DIRECTORY    PIC X(30) VARYING.
 01  FILENAME     PIC X(30) VARYING.
 01  D-IND        PIC S9(4) COMP.
 01  F-IND        PIC S9(4) COMP.
 01  FEXISTS      PIC S9(9) COMP.
 01  ISOPN        PIC S9(9) COMP.  
...

Finally, select a BFILE locator from some LOB table and do the DESCRIBE:

     EXEC SQL ALLOCATE :A-BFILE END-EXEC.
     EXEC SQL INSERT INTO lob_table (a_bfile) VALUES (BFILENAME ('lob.dir',
        'image.gif')) END-EXEC.
     EXEC SQL SELECT a_bfile INTO :A-BFILE FROM lob_table WHERE ... END-EXEC.
     EXEC SQL DESCRIBE :A-BFILE GET DIRECTORY, FILENAME, FILEEXISTS, ISOPEN
        INTO :DIRECTORY:D-IND, :FILENAME:F-IND, FEXISTS, ISOPN ND-EXEC.

Indicator variables are only valid for use with the DIRECTORY and FILENAME attributes. These attributes are character strings whose values may be truncated if the host variable buffers used to hold their values are not large enough. When truncation occurs, the value of the indicator will be set to the original length of the attribute.

READ and WRITE Using the Polling Method

Here is an outline of using READ with the polling method:

Start the read polling by setting the amount to zero in the first LOB READ (or set the amount to the size of the total data to be read). The amount is first set to zero in this case outline which omits details:

      EXEC SQL ALLOCATE :CLOB1 END-EXEC.

      EXEC SQL WHENEVER NOT FOUND GOTO END-OF-CLOB END-EXEC.

      EXEC SQL SELECT A_CLOB INTO :CLOB1 FROM LOB_TABLE WHERE ... END-EXEC.

      MOVE 0 TO AMT.
      EXEC SQL LOB READ :AMT FROM :VLOB1 AT :OFFSET INTO :BUFFER END-EXEC.

 READ-LOOP.
      EXEC SQL LOB READ :AMT FROM :CLOB1 INTO BUFFER $END-EXEC.
      GO TO READ-LOOP.

 END-OF-CLOB.     
      EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.

      EXEC SQL FREE :CLOB1 END-EXEC.

The following code outline writes data from a buffer into an internal CLOB. The value of AMT (16 characters) in the initial write statement should be the length of the entire data you will write. The buffer is 5 characters long.

If EOF is read in the initial read then do the LOB WRITE ONE. If not, start polling with a LOB WRITE FIRST of the buffer. Read the data, and do a LOB WRITE NEXT of the output. No offset is needed in the LOB WRITE NEXT because data is written at the end of the last write. After EOF is read, break out of the read loop and do a LOB WRITE LAST. The amount returned must equal the initial amount value (16).

     MOVE 16 TO AMT.
     PERFORM READ-NEXT-RECORD.
     MOVE INREC TO BUFFER-ARR.
     MOVE 5 TO BUFFER-LEN.
     IF (END-OF-FILE = "Y")
         EXEC SQL LOB WRITE ONE :AMT FROM :BUFFER INTO CLOB1 
            AT :OFFSET END-EXEC.
         PERFORM DISPLAY-CLOB
     ELSE
         EXEC SQL LOB WRITE FIRST :AMT FROM :BUFFER INTO :CLOB1
            AT :OFFSET END-EXEC.
     PERFORM READ-NEXT-RECORD.
     PERFORM WRITE-TO-CLOB 
        UNTIL END-OF-FILE = "Y".
     MOVE INREC TO BUFFER-ARR.
     MOVE 1 TO BUFFER-LEN.
     EXEC SQL LOB WRITE LAST :AMT FROM :BUFFER INTO :CLOB1 END-EXEC.
     PERFORM DISPLAY-CLOB.
     ...
 WRITE-TO-CLOB.
     MOVE INREC TO BUFFER-ARR.
     MOVE 5 TO BUFFER-LEN.
     EXEC SQL LOB WRITE NEXT :AMT FROM :BUFFER INTO :CLOB1 END-EXEC.
     PERFORM READ-NEXT RECORD.

 READ-NEXT-RECORD.
     MOVE SPACES TO INREC.
     READ INFILE NEXT RECORD
        AT END
        MOVE "Y" TO END-OF-FILE.
 ...

LOB Sample Program:LOBDEMO1.PCO

This program, LOBDEMO1.PCO, illustrates several LOB embedded SQL statements. The source code is in your demo directory. The application uses a table named license_table whose columns are social security number, name, and a CLOB containing text summarizing driving offenses. Several simplified SQL operations of a typical motor vehicle department are modeled.

The possible actions are:




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index