Release 8.1.5
A68023-01
 Library |
 Product |
 Contents |
 Index |
| Pro*COBOL Precompiler Programmer's Guide Release 8.1.5 A68023-01 |
|
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:
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 (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 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.
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 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.
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.
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.
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.
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.
| 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.
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.
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.
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.
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.
Here are the rules for using LOB statements:
These general restrictions and limitations apply when manipulating LOBs with the SQL LOB statements:
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
Use the following rules and notes for the LOB statements:
amt, src_offset, dst_offset, etc.) are declared as a 4-byte integer variable, PIC S9(9) COMP. The values are restricted between 0 and 4 Gigabytes.
amt, src_offset, etc. and result in an error.
src_offset and dst_offset have default values 1.
The statements are presented alphabetically. In all the statements where it appears, database refers to a database connection
This statement appends a LOB value at the end of another LOB.
EXEC SQL [AT [:]database] LOB APPEND :src TO :dst END-EXEC.
src (IN)
An internal LOB locator uniquely referencing the source LOB.
dst (IN OUT)
An internal LOB locator uniquely referencing the destination LOB.
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.
Assigns a LOB or BFILE locator to another.
EXEC SQL [AT [:]database] LOB ASSIGN :src to :dst END-EXEC.
src (IN)
LOB or BFILE locator source copied from.
dst (IN OUT)
LOB or BFILE locator copied to.
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 an open LOB or BFILE.
EXEC SQL [AT [:]database] LOB CLOSE :src END-EXEC.
src (IN OUT)
The locator of the LOB or BFILE to be closed.
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 all or part of a LOB value into a second LOB.
EXEC SQL [AT [:]database] LOB COPY :amt FROM :src [AT :src_offset] TO :dst [AT :dst_offset] END-EXEC.
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.
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.
Creates a temporary LOB.
EXEC SQL [AT [:]database] LOB CREATE TEMPORARY :src END-EXEC.
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.
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.
Disables LOB buffering for the LOB locator.
EXEC SQL [AT [:]database] LOB DISABLE BUFFERING :src END-EXEC.
src (IN OUT)
An internal LOB locator.
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.
Enables LOB buffering for the LOB locator.
EXEC SQL [AT [:]database] LOB ENABLE BUFFERING :src END-EXEC.
src (IN OUT)
An internal LOB locator.
This statement does not support BFILEs. Subsequent reads and writes are done through the LBS.
Erases a given amount of LOB data starting from a given offset.
EXEC SQL [AT [:]database] LOB ERASE :amt FROM :src [AT :src_offset] END-EXEC.
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.
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.
Closes all BFILES opened in the current session.
EXEC SQL [AT [:]database] LOB FILE CLOSE ALL END-EXEC.
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.
Sets DIRECTORY alias and FILENAME in a BFILE locator.
EXEC SQL [AT [:]database] LOB FILE SET :file DIRECTORY = :alias, FILENAME = :filename END-EXEC.
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.
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.
Writes this LOB's buffers to the database server.
EXEC SQL [AT [:]database] LOB FLUSH BUFFER :src [FREE] END-EXEC.
src (IN OUT)
Internal LOB locator.
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 the temporary space for the LOB locator.
EXEC SQL [AT [:]database] LOB FREE TEMPORARY :src END-EXEC.
src (IN OUT)
The LOB locator pointing to the temporary LOB.
The input locator must point to a temporary LOB. The output locator is marked not initialized and can be used in subsequent LOB statements.
Copy all or a part of a BFILE into an internal LOB.
EXEC SQL [AT [:]database] LOB LOAD :amt FROM FILE :file [AT :src_offset] INTO :dst [AT :dst_offset] END-EXEC.
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.
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 a LOB or BFILE for read or read/write access.
EXEC SQL [AT [:]database] LOB OPEN :src [ READ ONLY | READ WRITE ] END-EXEC.
src (IN OUT)
LOB locator of the LOB or BFILE.
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.
Reads all or part of a LOB or BFILE into a buffer.
EXEC SQL [AT [:]database] LOB READ :amt FROM :src [AT :src_offset] INTO :buffer [WITH LENGTH :buflen] END-EXEC.
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:
| External LOB1 | Internal LOB | Precompiler External Datatype | Precompiler Maximum Length 2 | PL/SQL Datatype | PL/SQL Maximum Length |
|---|---|---|---|---|---|
|
BFILE |
BLOB |
LONG VARRAW |
2147483643 |
RAW |
32767 |
|
CLOB |
LONG VARCHAR |
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.
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:
buflen, when the WITH LENGTH clause is present.
Truncates the LOB value.
EXEC SQL [AT [:]database] LOB TRIM :src TO :newlen END-EXEC.
src (IN OUT)
LOB locator for internal LOB.
newlen (IN)
The new length of the LOB value.
This statement is not for BFILES. The new length cannot be greater than the current length, or an error is returned.
Writes the contents of a buffer to a LOB.
EXEC SQL [AT [:]database] LOB WRITE [APPEND] [ FIRST | NEXT | LAST | ONE ] :amt FROM :buffer [WITH LENGTH :buflen] INTO :dst [AT :dst_offset] END-EXEC.
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.
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".
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:
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
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:
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).
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.
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. ...
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:
********************************************************************* * LOB Demo 1: DMV Database * * * * SCENARIO: * * * * We consider the example of a database used to store driver's * * licenses. The licenses are stored as rows of a table containing * * three columns: the sss number of a person, his/her name and the * * text summary of the info found in his license. * * * * The sss number and the name are the unique social security number * * and name of an individual. The text summary is a summary of the * * information on the individual, including his driving record, * * which can be arbitrarily long and may contain comments and data * * regarding the person's driving ability. * * * * APPLICATION OVERVIEW: * * * * This example demonstrate how a Pro*COBOL client can handle the * * new LOB datatypes. Demonstrated are the mechanisms for accessing * * and storing lobs to/from tables. * * * * To run the demo: * * * * 1. Execute the script, lobdemo1.sql in Server Manager * * 2. Precompile using Pro*COBOL * * procob lobdemo1 * * 3. Compile/Link (This step is platform specific) * * * * lobdemo1.sql contains the following SQL statements: * * * * connect scott/tiger; * * * * drop table license_table; * * * * create table license_table( * * sss char(9), * * name varchar2(50), * * txt_summary clob); * * * * insert into license_table * * values('971517006', 'Dennis Kernighan', * * 'Wearing a Bright Orange Shirt'); * * * * insert into license_table * * values('555001212', 'Eight H. Number', * * 'Driving Under the Influence'); * * * * insert into license_table * * values('010101010', 'P. Doughboy', * * 'Impersonating An Oracle Employee'); * * * * insert into license_table * * values('555377012', 'Calvin N. Hobbes', * * 'Driving Under the Influence'); * * * * The main program provides the menu of actions that can be * * performed. The program stops when the number 5 (Quit) option * * is entered. Depending on the input, this main program calls * * the appropriate nested program to execute the chosen action. * * * ********************************************************************* IDENTIFICATION DIVISION. PROGRAM-ID. LOBDEMO1. DATA DIVISION. WORKING-STORAGE SECTION. 01 USERNAME PIC X(5) VARYING. 01 PASSWD PIC X(5) VARYING. 01 CHOICE PIC 9. 01 SSS PIC X(9) GLOBAL. 01 SSSEXISTS PIC 9 VALUE ZERO GLOBAL. 01 LICENSE-TXT SQL-CLOB GLOBAL. 01 NEWCRIME PIC X(35) VARYING GLOBAL. EXEC SQL INCLUDE SQLCA END-EXEC. PROCEDURE DIVISION. BEGIN-PGM. EXEC SQL WHENEVER SQLERROR GO TO SQLERROR END-EXEC. PERFORM LOGON. MAIN. DISPLAY '**************************************************'. DISPLAY '* Welcome to the DMV Database *'. DISPLAY '**************************************************'. MENU. DISPLAY " ". DISPLAY "License Options:". DISPLAY "1. List available records by SSS number". DISPLAY "2. Get information on a particular record". DISPLAY "3. Add crime to a record". DISPLAY "4. Insert new record to database". DISPLAY "5. Quit". DISPLAY " ". DISPLAY "Your Selection (1-5)? " WITH NO ADVANCING. ENTER-CHOICE. ACCEPT CHOICE. DISPLAY " ". IF (CHOICE > 5 OR CHOICE < 1) DISPLAY "Invalid selection" DISPLAY "Please enter one of the given options: " GO TO ENTER-CHOICE ELSE IF (CHOICE = 5) GO TO FINISHED. IF (CHOICE = 1) CALL "LIST-RECORDS". IF (CHOICE = 2) CALL "GET-RECORD". IF (CHOICE = 3) CALL "ADD-CRIME". IF (CHOICE = 4) CALL "NEW-RECORD". GO TO MENU. FINISHED. EXEC SQL ROLLBACK RELEASE END-EXEC. DISPLAY " ". DISPLAY "HAVE A GOOD DAY!". DISPLAY " ". STOP RUN. LOGON. MOVE "scott" TO USERNAME-ARR. MOVE 5 TO USERNAME-LEN. MOVE "tiger" TO PASSWD-ARR. MOVE 5 TO PASSWD-LEN. EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWD END-EXEC. DISPLAY " ". DISPLAY "Connecting to license database account: ", USERNAME-ARR, "/", PASSWD-ARR. DISPLAY " ". SQLERROR. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK RELEASE END-EXEC. STOP RUN. ******************************************************************* * LIST-RECORDS * Lists available records by sss number. * ******************************************************************* IDENTIFICATION DIVISION. PROGRAM-ID. LIST-RECORDS. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 SELECT-SSS PIC X(50) VARYING. 01 SSS PIC X(9). PROCEDURE DIVISION. MOVE "SELECT SSS FROM LICENSE_TABLE" TO SELECT-SSS-ARR. MOVE 29 TO SELECT-SSS-len. EXEC SQL PREPARE SSS_EXEC FROM :SELECT-SSS END-EXEC. EXEC SQL DECLARE SSS_CURSOR CURSOR FOR SSS_EXEC END-EXEC. EXEC SQL OPEN SSS_CURSOR END-EXEC. DISPLAY "Available records:". EXEC SQL WHENEVER NOT FOUND GO TO NOTFOUND END-EXEC. GETROWS. EXEC SQL FETCH SSS_CURSOR INTO :SSS END-EXEC. DISPLAY SSS. GO TO GETROWS. NOTFOUND. EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC. EXEC SQL CLOSE SSS_CURSOR END-EXEC. GO TO END-PROGRAM. SQLERROR. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. END-PROGRAM. END PROGRAM LIST-RECORDS. ******************************************************************* * GETSSS * Fills the global variable SSS with the client-supplied sss. * Sets the global variable SSSEXISTS to 0 if the sss does not * correspond to any entry in the database, else sets it to 1. ******************************************************************* IDENTIFICATION DIVISION. PROGRAM-ID. GETSSS COMMON. DATA DIVISION. WORKING-STORAGE SECTION. 01 SSSCOUNT PIC S9(4) COMP. PROCEDURE DIVISION. DISPLAY "Social Security Number? " WITH NO ADVANCING. ACCEPT SSS. DISPLAY " ". EXEC SQL SELECT COUNT(*) INTO :SSSCOUNT FROM LICENSE_TABLE WHERE SSS = :SSS END-EXEC. IF (SSSCOUNT = 0) MOVE 0 TO SSSEXISTS ELSE MOVE 1 TO SSSEXISTS. GO TO END-PROGRAM. SQLERROR. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. END-PROGRAM. END PROGRAM GETSSS. ******************************************************************* * PRINTCRIME * Obtains the length of the global clob LICENSE-TXT and * uses that in the LOB READ statement to read the clob * into a character buffer to display the contents of the clob. * The caller to this function must allocate, select and later * free the global clob LICENSE-TXT. ******************************************************************* IDENTIFICATION DIVISION. PROGRAM-ID. PRINTCRIME COMMON. DATA DIVISION. WORKING-STORAGE SECTION. 01 THE-STRING PIC X(200) VARYING. 01 TXT-LENGTH PIC S9(9) COMP. PROCEDURE DIVISION. DISPLAY "=====================". DISPLAY " CRIME SHEET SUMMARY ". DISPLAY "=====================". MOVE SPACE TO THE-STRING-ARR. EXEC SQL LOB DESCRIBE :LICENSE-TXT GET LENGTH INTO :TXT-LENGTH END-EXEC. IF (TXT-LENGTH = 0) DISPLAY "Record is clean" ELSE EXEC SQL LOB READ :TXT-LENGTH FROM :LICENSE-TXT INTO :THE-STRING END-EXEC DISPLAY THE-STRING-ARR. GO TO END-PROGRAM. SQLERROR. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. END-PROGRAM. END PROGRAM PRINTCRIME. ******************************************************************* * GET-RECORD * Allocates the global clob LICENSE-TXT then selects * the name and text what corresponds to the client-supplied * sss. It then calls PRINTCRIME to print the information and * frees the clob. ******************************************************************* IDENTIFICATION DIVISION. PROGRAM-ID. GET-RECORD. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 NAME1 PIC X(50) VARYING. PROCEDURE DIVISION. CALL "GETSSS". IF (SSSEXISTS = 1) EXEC SQL ALLOCATE :LICENSE-TXT END-EXEC EXEC SQL SELECT NAME, TXT_SUMMARY INTO :NAME1, :LICENSE-TXT FROM LICENSE_TABLE WHERE SSS = :SSS END-EXEC DISPLAY "================================================== - "========================" DISPLAY " " DISPLAY "NAME: ", NAME1-ARR, "SSS: ", SSS DISPLAY " " CALL "PRINTCRIME" DISPLAY " " DISPLAY "================================================== - "========================" EXEC SQL FREE :LICENSE-TXT END-EXEC ELSE DISPLAY "SSS Number Not Found". GO TO END-PROGRAM. SQLERROR. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. GO TO END-PROGRAM. END-PROGRAM. END PROGRAM GET-RECORD. ******************************************************************* * GETNEWCRIME * Provides a list of the possible crimes to the user and * stores the user's correct response in the global variable * NEWCRIME. ******************************************************************* IDENTIFICATION DIVISION. PROGRAM-ID. GETNEWCRIME COMMON. DATA DIVISION. WORKING-STORAGE SECTION. 01 CRIMES. 05 FILLER PIC X(35) VALUE "Driving Under the Influence". 05 FILLER PIC X(35) VALUE "Grand Theft Auto". 05 FILLER PIC X(35) VALUE "Driving Without a License". 05 FILLER PIC X(35) VALUE "Impersonating an Oracle Employee". 05 FILLER PIC X(35) VALUE "Wearing a Bright Orange Shirt". 01 CRIMELIST REDEFINES CRIMES. 05 CRIME PIC X(35) OCCURS 5 TIMES. 01 CRIME-INDEX PIC 9. 01 CHOICE PIC 9. PROCEDURE DIVISION. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. CRIMEMENU. DISPLAY " ". DISPLAY "Select from the following:". PERFORM DISPLAY-CRIME VARYING CRIME-INDEX FROM 1 BY 1 UNTIL CRIME-INDEX > 5. DISPLAY "Crime (1-5) = " WITH NO ADVANCING. ENTER-CHOICE. ACCEPT CHOICE. DISPLAY " ". IF (CHOICE > 5 OR CHOICE < 1) DISPLAY "Invalid selection" DISPLAY "Crime (1-5) = " WITH NO ADVANCING GO TO ENTER-CHOICE. MOVE CRIME(CHOICE) TO NEWCRIME-ARR. MOVE 35 TO NEWCRIME-LEN. GO TO END-PROGRAM. DISPLAY-CRIME. DISPLAY "(", CRIME-INDEX, ") ", CRIME(CRIME-INDEX). END-PROGRAM. END PROGRAM GETNEWCRIME. ******************************************************************* * APPENDTOCLOB * Obtains the length of the global clob LICENSE-TXT and * uses that in the LOB WRITE statement to append the NEWCRIME * character buffer to the global clob LICENSE-TXT. * The name corresponding the global SSS is then selected * and displayed to the screen along with value of LICENSE-TXT. * The caller to this function must allocate, select and later * free the global clob LICENSE-TXT. ******************************************************************* IDENTIFICATION DIVISION. PROGRAM-ID. APPENDTOCLOB COMMON. DATA DIVISION. WORKING-STORAGE SECTION. 01 TXT-LEN PIC S9(9) COMP. 01 CRIME-LEN PIC S9(9) COMP. 01 NAME1 PIC X(50) VARYING. PROCEDURE DIVISION. EXEC SQL WHENEVER SQLERROR GO TO SQLERROR END-EXEC. EXEC SQL LOB DESCRIBE :LICENSE-TXT GET LENGTH INTO :TXT-LEN END-EXEC. MOVE NEWCRIME-LEN TO CRIME-LEN. IF (TXT-LEN NOT = 0) COMPUTE TXT-LEN = TXT-LEN + 3 ELSE COMPUTE TXT-LEN = TXT-LEN + 1. EXEC SQL LOB WRITE :CRIME-LEN FROM :NEWCRIME INTO :LICENSE-TXT AT :TXT-LEN END-EXEC. EXEC SQL SELECT NAME INTO :NAME1 FROM LICENSE_TABLE WHERE SSS = :SSS END-EXEC DISPLAY " " DISPLAY "NAME: ", NAME1-ARR, "SSS: ", SSS DISPLAY " " CALL "PRINTCRIME" DISPLAY " " GO TO END-PROGRAM. SQLERROR. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. END-PROGRAM. END PROGRAM APPENDTOCLOB. ******************************************************************* * ADD-CRIME * Obtains a sss and crime from the user and appends * the crime to the list of crimes of the corresponding sss. ******************************************************************* IDENTIFICATION DIVISION. PROGRAM-ID. ADD-CRIME. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. PROCEDURE DIVISION. EXEC SQL WHENEVER SQLERROR GO TO SQLERROR END-EXEC. CALL "GETSSS". IF (SSSEXISTS = 1) EXEC SQL ALLOCATE :LICENSE-TXT END-EXEC CALL "GETNEWCRIME" EXEC SQL SELECT TXT_SUMMARY INTO :LICENSE-TXT FROM LICENSE_TABLE WHERE SSS = :SSS FOR UPDATE END-EXEC CALL "APPENDTOCLOB" EXEC SQL FREE :LICENSE-TXT END-EXEC ELSE DISPLAY "SSS Number Not Found". GO TO END-PROGRAM. SQLERROR. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. END-PROGRAM. END PROGRAM ADD-CRIME. ******************************************************************* * NEW-RECORD * Obtains the sss and name of a new record and inserts them * along with an empty_clob() for the clob in the table. ******************************************************************* IDENTIFICATION DIVISION. PROGRAM-ID. NEW-RECORD. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. 01 NEWNAME PIC X(50). PROCEDURE DIVISION. CALL "GETSSS". IF (SSSEXISTS = 1) DISPLAY "Record with that sss number already exists" ELSE DISPLAY "Name? " WITH NO ADVANCING ACCEPT NEWNAME DISPLAY " ". EXEC SQL ALLOCATE :LICENSE-TXT END-EXEC EXEC SQL INSERT INTO LICENSE_TABLE VALUES (:SSS, :NEWNAME, EMPTY_CLOB()) END-EXEC EXEC SQL SELECT TXT_SUMMARY INTO :LICENSE-TXT FROM LICENSE_TABLE WHERE SSS = :SSS END-EXEC DISPLAY "================================================== - "========================" DISPLAY "NAME: ", NEWNAME,"SSS: ", SSS CALL "PRINTCRIME" DISPLAY "================================================== - "========================" EXEC SQL FREE :LICENSE-TXT END-EXEC. GO TO END-PROGRAM. SQLERROR. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. END-PROGRAM. END PROGRAM NEW-RECORD. END PROGRAM LOBDEMO1.
