Basic LOB Components, 4 of 5

LOB Value and Locators

Inline storage of the LOB value

Data stored in a LOB is termed the LOB's value. The value of an internal LOB may or may not be stored inline with the other row data. If you do not set DISABLE STORAGE IN ROW and the internal LOB value is less than approximately 4,000 bytes, then the value is stored inline; otherwise it is stored outside the row. Since LOBs are intended to be large objects, inline storage will only be relevant if your application mixes small and large LOBs.

As mentioned in Chapter 7, "Modeling and Design", "ENABLE | DISABLE STORAGE IN ROW" , the LOB value is automatically moved out of the row once it extends beyond approximately 4,000 bytes.

LOB Locators

Regardless of where the value of the internal LOB is stored, a locator is stored in the row. You can think of a LOB locator as a pointer to the actual location of the LOB value. A LOB locator is a locator to an internal LOB while a BFILE locator is a locator to an external LOB. When the term locator is used without an identifying prefix term, it refers to both LOB locators and BFILE locators.

Internal LOB Locators. For internal LOBs, the LOB column stores a locator to the LOB's value which is stored in a database tablespace. Each LOB column/attribute for a given row has its own distinct LOB locator and also a distinct copy of the LOB value stored in the database tablespace.
External LOB Locators. For external LOBs (BFILEs), the LOB column stores a BFILE locator to the external operating system file. Each BFILE column/attribute for a given row has its own BFILE locator. However, two different rows can contain a BFILE locator that points to the same operating system file.

Setting the LOB Column/Attribute to Contain a Locator

Internal LOBs

Before you can start writing data to an internal LOB via one of the six programmatic environment interfaces^{Foot 1} (PL/SQL, OCI, Pro*C/C++, Pro*COBOL, Visual Basic, or Java), the LOB column/attribute must be made non-null, that is, it must contain a locator. You can accomplish this by initializing the internal LOB to empty in an INSERT/UPDATE statement using the functions EMPTY_BLOB() for BLOBs or EMPTY_CLOB() for CLOBs and NCLOBs.

External LOBs

Before you can start accessing the external LOB (BFILE) value via one of the six programmatic environment interfaces, the BFILE column/attribute must be made non-null. You can initialize the BFILE column to point to an external operating system file by using the BFILENAME() function.

Invoking the EMPTY_BLOB() or EMPTY_CLOB() function in and of itself does not raise an exception. However, using a LOB locator that was set to empty to access or manipulate the LOB value in any PL/SQL DBMS_LOB or OCI routine will raise an exception.

Valid places where empty LOB locators may be used include the VALUES clause of an INSERT statement and the SET clause of an UPDATE statement.

The following INSERT statement:

Populates story with the character string 'JFK interview',
Sets flsub, frame and sound to an empty value,
Sets photo to NULL, and
Initializes music to point to the file 'JFK_interview' located under the logical directory 'AUDIO_DIR' (see the CREATE DIRECTORY statement in Oracle9i Reference.).

Note:
Character strings are inserted using the default character set for the instance.

See Appendix B, "The Multimedia Schema Used for Examples in This Manual", for the definition of table Multimedia_tab.

INSERT INTO Multimedia_tab VALUES (101, 'JFK interview', EMPTY_CLOB(), NULL,
    EMPTY_BLOB(), EMPTY_BLOB(), NULL, NULL, 
    BFILENAME('AUDIO_DIR', 'JFK_interview'), NULL);

Similarly, the LOB attributes for the Map_typ column in Multimedia_tab can be initialized to NULL or set to empty as shown below. Note that you cannot initialize a LOB object attribute with a literal.

INSERT INTO Multimedia_tab 
  VALUES (1, EMPTY_CLOB(), EMPTY_CLOB(), NULL, EMPTY_BLOB(), 
          EMPTY_BLOB(), NULL, NULL, NULL, 
          Map_typ('Moon Mountain', 23, 34, 45, 56, EMPTY_BLOB(), NULL);

Accessing a LOB Through a Locator

SELECTing a LOB

Performing a SELECT on a LOB returns the locator instead of the LOB value. In the following PL/SQL fragment you select the LOB locator for story and place it in the PL/SQL locator variable Image1 defined in the program block. When you use PL/SQL DBMS_LOB functions to manipulate the LOB value, you refer to the LOB using the locator.


DECLARE
    Image1       BLOB;
    ImageNum     INTEGER := 101;
BEGIN
    SELECT story INTO Image1 FROM Multimedia_tab
        WHERE clip_id = ImageNum;
    DBMS_OUTPUT.PUT_LINE('Size of the Image is: ' ||
        DBMS_LOB.GETLENGTH(Image1));
    /* more LOB routines */
END;

In the case of OCI, locators are mapped to locator pointers which are used to manipulate the LOB value. The OCI LOB interface is described Chapter 3, "LOB Support in Different Programmatic Environments" and in the Oracle Call Interface Programmer's Guide.

Using LOB locators and transaction boundaries, and read consistent locators are described in Chapter 5, "Large Objects: Advanced Topics".

¹ Note: You could use SQL to populate a LOB column with data even if it contained NULL, i.e., unless its a LOB attribute. However, you cannot use one of the six programmatic environment interfaces on a NULL LOB!