LOB Locators

LOBs can be stored with other row data or separate from row data. Regardless of the storage location, each LOB has a locator, which can be viewed as a handle or pointer to the actual location. Selecting a LOB returns the LOB locator instead of the LOB value.

The following PL/SQL code selects the LOB locator for b_lob and place it a PL/SQL local variable named image1:

DECLARE
       image1  BLOB;
       image_no  INTEGER := 101;
BEGIN
       SELECT b_lob  INTO image1 FROM lob_table
                  WHERE key_value = image_no;
             ...
END;

When you use an API function to manipulate the LOB value, you refer to the LOB using the locator. The PL/SQL DBMS_LOB package contains useful routines to manipulate LOBs, such as PUT_LINE and GETLENGTH:

BEGIN
     DBMS_OUTPUT.PUT_LINE('Size of the Image is: ', 
                       DBMS_LOB.GETLENGTH(image1));
END;

In the OCI, LOB locators are mapped to LOBLocatorPointers (OCILobLocator *).

The OCI LOB interface and the PL/SQL DBMS_LOB package are described briefly in this chapter. The OCI is described in more detail in the Oracle Call Interface Programmer's Guide. The DBMS_LOB API is described in the Oracle8i Application Developer's Guide - Large Objects (LOBs).

For a BFILE, the LOB column has its own distinct locator, which refers to the LOB's value that is stored in an external file in the server's file system. This implies that two rows in a table with a BFILE column may refer to the same file or two distinct files. A BFILE locator variable in a PL/SQL or OCI program behaves like any other automatic variable. With respect to file operations, it behaves like a file descriptor available as part of the standard I/O library of most conventional programming languages.