DBMS_LOB

PL/SQL 8.0 introduces DBMS_LOB, which is used to manipulate “large object” data—namely, Oracle8’s new BLOB, CLOB, NCLOB, and BFILE data types. Similar data types such as LONG, RAW, and LONG RAW were only serially accessible in earlier releases of PL/SQL, but with DBMS_LOB these new data types can be accessed piecewise for reading, writing, and comparison operations.

Because BFILE data is stored outside the database as a file, writes are not supported except through the operating system. There are several procedures that apply to BFILE data only. These are

FILEEXISTS

FILEGETNAME

FILEOPEN

FILEISOPEN

FILECLOSE

FILECLOSEALL

Other LOB data is accessed through the following procedures:

LOADFROMFILE

GETLENGTH

READ

SUBSTR

COMPARE

INSTR

COPY

WRITE

APPEND

TRIM

ERASE

In order to access data using DBMS_LOB, a “locator” must be retrieved for the LOB in question. This locator must be stored in a variable whose data type matches the LOB it references. The locator is then passed to DBMS_LOB, where it is used like a pointer is used in a C program. In the case of a BFILE, this locator must be retrieved using the BFILENAME function. We will refer to locators for BFILEs as “file locators,” and the rest will be called “LOB locators.”

---

Note:  
There are two things you must do before you can access a BFILE.

First, create a directory alias in the database for an existing path using the CREATE DIRECTORY command.

Then retrieve the file locator for the BFILE calling the BFILENAME function and passing in the directory alias and a filename. The alias must be uppercase, and the file must already exist on the operating system.

---

PROCEDURE APPEND(

     dest_lob IN OUT BLOB,

     src_lob IN BLOB);

This procedure appends the src_lob to the dest_lob and is overloaded to accept BLOB, CLOB, and NCLOB data.

PROCEDURE APPEND(

     dest_lob IN OUT CLOB CHARACTER SET ANY_CS,

     src_lob IN CLOB CHARACTER SET dest_lob%CHARSET);

The 'ANY_CS’ in the syntax of DBMS_LOB routines for CLOBs allows them to accept a CLOB or NCLOB variables as inputs. The %CHARSET returns the character set of its argument to ensure that the source and destination objects are of the same character set.

FUNCTION COMPARE(

     lob_1 IN BLOB, CLOB, NCLOB or BFILE

     lob_2 IN BLOB, CLOB, NCLOB or BFILE

     amount IN INTEGER DEFAULT 4294967295,

     offset_1 IN INTEGER DEFAULT 1,

     offset_2 IN INTEGER DEFAULT 1)

RETURN INTEGER;

You can use this function to compare the two entire LOBs or two same-length pieces. The LOBs must be of the same type. It returns a 0 for a non-match and a 1 for a match.

PROCEDURE COPY(

     dest_lob IN OUT BLOB, CLOB or NCLOB,

     src_lob IN BLOB, CLOB or NCLOB,

     amount IN INTEGER DEFAULT 1,

     dest_offset IN INTEGER DEFAULT 1,

     src_offset IN INTEGER DEFAULT 1);