12.7 How do I…Manage large objects with DBMS_LOB?

Problem

I need to store large blocks containing unstructured data such as text, graphic images, video clips, and sound waveforms in the database. Our applications must be able to randomly access these large objects (LOBs) of data efficiently. I know that the DMBS_LOB package allows efficient access to LOBs. How do I use the DBMS_LOB package to manage LOBs?

Technique

LOBs support random access to data and can have a maximum size of up to four gigabytes as compared to the LONG scalar datatype, which supports only sequential access and has a maximum size of two gigabytes.

There are four LOB datatypes: A BLOB contains unstructured binary data. A CLOB contains single-byte character data. A NCLOB contains fixed width single or multi-byte NLS character data. A BFILE is a BLOB stored as an external file outside the database. A BFILE is read-only supporting only random (non-sequential) reads. The source and destination buffers are of the RAW datatype for BLOB and BFILE, and VARCHAR2 datatype for CLOB and NCLOB.

For LOB operations, Oracle uses pointers called as LOB locators that specify the location of the LOB stored out-of-line. The LOB locator for a BFILE is a pointer to the location of the binary file in the operating system. Locators cannot span transactions or sessions and all LOBs, except BFILEs, can participate in transactions. The DBMS_LOB package can be used to manipulate LOBs. The functions and procedures in the DBMS_LOB package are listed in Tables 12.11, 12.12, and 12.13.

Table 12.11 DBMS_LOB procedures to modify BLOB, CLOB, NLOB values

Name Type Description
APPEND Procedure Appends the contents of source LOB to destination LOB.
COPY Procedure Copies the source LOB to destination LOB partially or fully.
ERASE Procedure Clears the LOB partially or fully.
TRIM Procedure Trims the LOB to the new length.
WRITE Procedure Writes data from a buffer to LOB.

Table 12.12 DBMS_LOB functions/procedures to read/examine LOB values

Name Type Description
COMPARE Function Compares two LOBs partially or fully.
GETLENGTH Function Returns length of the LOB.
INSTR Function Searches for a pattern in the LOB.
READ Procedure Reads data from LOB into a buffer.
SUBSTR Function Returns a partial LOB value.

Table 12.13 DBMS_LOB functions/procedures specific to BFILEs

Name Type Description
FILEOPEN Function Opens the specified BFILE file.
FILEISOPEN Function Returns TRUE if the file is currently open.
FILEEXISTS Function Checks for the actual existence of the file.
FILEGETNAME Procedure Returns the name of the BFILE file.
FILECLOSE Procedure Closes the specified BFILE file.
FILECLOSEALL Procedure Closes all open BFILE files.

In this How-To, examples are provided for each DBMS_LOB routine to illustrate their use in the following steps.

Steps

1. Run SQL*Plus and connect as the WAITE user account. Run CHP12_23.SQL, as shown in Figure 12.26, which creates the sample table with LOB type columns and inserts sample data into it. The sample table contains an integer column as the primary key and four LOB columns of BLOB, CLOB, NCLOB, and BFILE datatypes. Run the script to create the sample table and data.

In the CREATE TABLE statement, line 2 declares the primary key. Lines 3 to 6 declare a locator for each of the BLOB, CLOB, NCLOB, and BFILE datatypes. Additionally, you can specify a LOB storage clause, and the NOCACHE and NOLOGGING options with the CREATE TABLE statement as discussed in Chapter 4.

The first INSERT statement uses the EMPTY_BLOB() and EMPTY_CLOB() functions to initialize the BLOB column with an empty BLOB locator and the CLOB and NCLOB columns with empty CLOB locators. In the second INSERT statement, the CLOB column is initialized with a locator that points to an LOB value containing the string specified in the statement.

An LOB column must contain a locator that points to an empty or populated value before you can write any data to the LOB. The BFILENAME() function is used to associate a BFILE column with an external file. The first parameter to the BFILENAME() function is the directory_alias and the second one is the filename. The directory_alias is created beforehand with the CREATE OR REPLACE DIRECTORY statement to create a directory object. BFILENAME() does not check for the existence of the physical directory, however, until actual file access is performed.

2. Run the CHP12_24.SQL file in SQL*Plus. The PL/SQL block in the file writes a line of text to a CLOB. Figure 12.27 shows the results of the operation in SQL*Plus.

Line 2 declares the CLOB locator controlling access to the CLOB object. Line 5 declares a VARCHAR2 buffer to hold character data that needs to be written to the CLOB object. Lines 7 through 10 select a CLOB locator for writing by using the FOR UPDATE clause in the SELECT statement. Line 11 uses the LENGTH function to determine the number of bytes in the buffer to be written to the CLOB. The variable in line 12 is the offset in the CLOB at which the buffer contents are written. Line 13 calls the WRITE procedure in the DBMS_LOB package to write the contents of the buffer to the CLOB. Line 14 issues a COMMIT to complete the transaction and release the lock on the row that was acquired due to the FOR UPDATE clause of the SELECT statement.

3. Run the CHP12_25.SQL file in SQL*Plus. The PL/SQL block in the file demonstrates the use of the GETLENGTH function in the DBMS_LOB package to find out the length of a CLOB value. Figure 12.28 shows the results of the operation in SQL*Plus.

Line 2 declares the CLOB locator controlling access to the CLOB object. Lines 4 through 7 query the CLOB locator from the sample table. Lines 8 and 9 display the length of the CLOB value pointed by the CLOB locator using the DBMS_LOB.GETLENGTH function and display it in SQL*Plus.

4. Run the CHP12_26.SQL file in SQL*Plus. The PL/SQL block in the file reads a line of text from a CLOB. Figure 12.29 shows the results of the operation in SQL*Plus.

Line 2 declares the CLOB locator controlling access to the CLOB object. Line 3 declares a bind variable for the CLOB locator. Lines 8 through 11 query the CLOB locator from the sample table. Line 12 reads 20 bytes starting at offset 1 from the CLOB. Line 13 displays the retrieved text in SQL*Plus. Line 16 uses the bind variable to retrieve only the first 10 bytes from the CLOB. Line 17 displays the partially retrieved text in SQL*Plus.

5. Run the CHP12_27.SQL file in SQL*Plus, as shown in Figure 12.30. The PL/SQL block in the file exposes some more LOB manipulation techniques that you will typically need to use.

Line 2 declares the CLOB locator controlling access to the CLOB object. In line 4, the EMPTY_CLOB() function is used to initialize a CLOB locator to point to an empty CLOB. Lines 5 through 8 update the CLOB locator in the sample table using the empty CLOB initialized in line 4, and also sets the BLOB value to an empty BLOB. Line 9 through 13 are used to copy the CLOB value from one record to another. Alternatively, the COPY procedure in the DBMS_LOB package can also be used.

6. Run the CHP12_28.SQL file in SQL*Plus, as shown in Figure 12.31. The PL/SQL block in the file reads the contents of a BFILE and writes it to a BLOB.

Line 2 declares the BFILE locator for file operations. Line 3 declares the BLOB locator controlling access to the BLOB object. Line 5 declares a buffer of the RAW datatype as we are dealing with binary data here. Lines 7 and 8 select a BLOB locator for writing by using the FOR UPDATE clause in the SELECT statement. Lines 9 and 10 select the BFILE locator for reading the contents of the external file.

Ensure that the file BFILE1.DAT exists in the C:\TEMP directory. This file could be an image or any other file with binary contents that you’ll want to store in the database as a BLOB object.

Line 11 opens the BFILE locator by calling the FILEOPEN procedure. Line 12 calls the GETLENGTH function, which returns the total number of bytes in the BFILE. Lines 13 reads the complete BFILE into the RAW buffer. Line 14 writes contents of the buffer to the BLOB. Line 15 closes the BFILE locator using the FILECLOSE procedure. The exception handler in lines 17 to 19 is raised if the call to the READ procedure in line 13 fails if it tries to read beyond the end of file. Because the RAW datatype has a maximum limit of 32,767 bytes, if you are uploading a file larger than that, use a buffer of a suitable size and do the read and write in a loop, incrementing the starting position for the next read and write by the number of bytes read.

7. Run the CHP12_29.SQL file in SQL*Plus, as shown in Figure 12.32. The PL/SQL block in it compares the contents of two BFILEs.

Line 2 and 3 declare the BFILE locators for file operations on the two external files. Line 6 selects the BFILE locator for reading the contents of the external file. BFILEs are read-only. Line 7 creates a new BFILE locator by calling the BFILENAME procedure and passing the directory alias (created in Step 1) and the filename. Line 10 calls the COMPARE procedure in the DBMS_LOB package to compare the contents of the two files. In addition to passing the BFILE locators, the length of the first BFILE is passed as the third parameter in line 11 by calling the GETLENGTH function. A return code of 0 means that the two files were the same; otherwise, they are different. Line 17 closes all open BFILE locators in the block by calling the FILECLOSEALL procedure of the DBMS_LOB package.

How It Works

The DBMS_LOB package is used to manage LOBs, which include functions and procedures to read, write, append, copy, and erase LOBs. The directory alias for all accessible directories must be created before performing any operations on BFILEs. Oracle must also have permissions to access these files.

Step 1 creates the sample table with columns of LOB datatypes and inserts data into it. The EMPTY_CLOB() and EMPTY_BLOB() functions are used within an INSERT statement to create LOB locators pointing to empty LOB values. Any read/write operations will fail if the LOB was initialized to NULL instead. Step 2 performs a CLOB write operation and Step 3 displays the length of the CLOB in SQL*Plus. Step 4 reads data from the CLOB and displays it. Step 5 shows the use of EMPTY_CLOB() and EMPTY_BLOB() in PL/SQL and copies LOB data by nesting a SELECT statement in an UPDATE statement.

The COPY and ERASE procedures in the DBMS_LOB package provide an easier means of achieving the same effect as you can specify the number of bytes and the starting offset within the LOB. The APPEND procedure appends one LOB to another, but both have to be of the same LOB datatype.

Step 6 reads a BFILE into a buffer and writes the contents of the buffer to a BLOB column, which is useful in uploading binary data files to be stored as BLOBs in the database. Step 7 uses the COMPARE function to compare the contents of two BFILEs.

Comments

The DBMS_LOB package in PL/SQL 3.0 provides routines to access and manipulate portions of LOBs or complete LOBs. All DBMS_LOB routines work based on LOB locators. For the successful completion of DBMS_LOB routines, you must provide an input locator that represents a LOB which already exists in the database tablespaces or external file system.

For internal LOBs, you must first use SQL DDL to define tables that contain LOB columns, and subsequently SQL DML to initialize or populate the locators in these LOB columns. For external LOBs, you must ensure that a DIRECTORY object that represents a valid, existing physical directory has been defined, and physical files exist with read permission for Oracle.

Once the LOBs are defined and created, you can then SELECT a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to DBMS_LOB for manipulation of the LOB value.