2
Basic LOB Components

This chapter discusses the following topics:

• The LOB Datatype
• Varying-Width Character Data
• LOB Value and Locators
• Creating Tables that Contain LOBs

The LOB Datatype

Oracle9i regards LOBs as being of two kinds depending on their location with regard to the database -- internal LOBs and external LOBs, also referred to as BFILEs (binary files). Note that when we discuss some aspect of working with LOBs without specifying whether the LOB is internal or external, the characteristic under discussion pertains to both internal and external LOBs.

Internal LOBs

Internal LOBs, as their name suggests, are stored inside database tablespaces in a way that optimizes space and provides efficient access. Internal LOBs use copy semantics and participate in the transactional model of the server. You can recover internal LOBs in the event of transaction or media failure, and any changes to a internal LOB value can be committed or rolled back. In other words, all the ACID^{Foot 1} properties that pertain to using database objects pertain to using internal LOBs.

Internal LOB Datatypes

There are three SQL datatypes for defining instances of internal LOBs:

• BLOB, a LOB whose value is composed of unstructured binary ("raw") data.
• CLOB, a LOB whose value is composed of character data that corresponds to the database character set defined for the Oracle9i database.
• NCLOB, a LOB whose value is composed of character data that corresponds to the national character set defined for the Oracle9i database.

Internal LOBs are divided into persistent and temporary LOBs.

External LOBs (BFILEs)

External LOBs (BFILES) are large binary data objects stored in operating system files outside database tablespaces. These files use reference semantics. Apart from conventional secondary storage devices such as hard disks, BFILEs may also be located on tertiary block storage devices such as CD-ROMs, PhotoCDs and DVDs.

The BFILE datatype allows read-only byte stream access to large files on the file system of the database server.

Oracle can access BFILEs provided the underlying server operating system supports stream-mode access to these operating system (OS) files.

External LOB Datatypes

There is one datatype, BFILE, for declaring instances of external SQL LOBs.

• BFILE, a LOB whose value is composed of binary ("raw") data, and is stored outside the database tablespaces in a server-side operating system file.

Internal LOBs Use Copy Semantics, External LOBs Use Reference Semantics

• Copy semantics: Both LOB locator and value are copied
• Reference semantics: Only LOB locator is copied

Copy Semantics

Internal LOBs, namely BLOBs, CLOBs, NCLOBs, whether persistent or temporary, use copy semantics.

When you insert or update a LOB with a LOB from another row in the same table, the LOB value is copied so that each row has a different copy of the LOB value.

Internal LOBs have copy semantics so that if the LOB in the row of the table is copied to another LOB, in a different row or perhaps in the same row but in a different column, then the actual LOB value is copied, not just the LOB locator. This means in this case that there will be two different LOB locators and two copies of the LOB value.

Reference Semantics

External LOBs (BFILEs) use reference semantics. When the BFILE in the row of the table is copied to another BFILE, only the BFILE locator is copied, not the actual BFILE data, that is, not the actual operating system file.

Varying-Width Character Data

• You can create the following LOB tables:
  • Containing CLOB/NCLOB columns even if you use a varying-width CHAR/NCHAR database character set
  • Containing a type that has a CLOB attribute irrespective of whether you use a varying-width CHAR database character set
• You cannot create the following tables:
  • With NCLOBs as attributes of object types

Using DBMS_LOB.LOADFROMFILE and Functions that Access OCI

In using the OCI, or any of the programmatic environments that access OCI functionality, character set conversions are implicitly performed when translating from one character set to another. However, no implicit translation is ever performed from binary data to a character set. When you use the loadfromfile operation to populate a CLOB or NCLOB, you are populating the LOB with binary data from the BFILE. In that case, you will need to perform character set conversions on the BFILE data before executing loadfromfile.

However, we recommend that you use the sql*loader instead of loadfromfile to load data into a clob/nclob because the sql*loader will take care of all necessary character set conversions.

Converting Between Client Character Set and UCS-2

There are APIs in cartridge service that can convert between client character set and UCS-2:

• OCIUnicodeToCharSet()
• OCICharSetToUnicode()

  See Also: Chapter 3, "LOB Support in Different Programmatic Environments": "Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte For DBMS_LOB Package" "Offset and Amount Parameters: Fixed-Width Versus Varying-Width, Character or Byte"

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 using the supported programmatic environment interfaces^{Foot 2} (PL/SQL, OCI, OCCI, Pro*C/C++, Pro*COBOL, Visual Basic, Java, or OLEDB), 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 using the supported 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:

See Appendix B, "The Multimedia Schema", 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 in the following.

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 CLOB;
    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".

Creating Tables that Contain LOBs

When creating tables that contain LOBs use the guidelines described in the following sections:

• Initializing Internal LOBs to NULL or Empty
• Initializing Internal LOB Columns to a Value
• Initializing External LOBs to NULL or a File Name
• Defining tablespace and storage characteristics. See Chapter 7, "Modeling and Design", "Defining Tablespace and Storage Characteristics for Internal LOBs".

Initializing Internal LOBs to NULL or Empty

You can set an internal LOB -- that is, a LOB column in a table, or a LOB attribute in an object type defined by you-- to be NULL or empty:

• Setting an Internal LOB to NULL: A LOB set to NULL has no locator. A NULL value is stored in the row in the table, not a locator. This is the same process as for all other datatypes.
• Setting an Internal LOB to Empty: By contrast, an empty LOB stored in a table is a LOB of zero length that has a locator. So, if you SELECT from an empty LOB column or attribute, you get back a locator which you can use to populate the LOB with data using supported programmatic environments, such as OCI or PL/SQL(DBMS_LOB). See Chapter 3, "LOB Support in Different Programmatic Environments".

These options are discussed in more detail in the following.

As discussed in the following, an external LOB (BFILE) can be initialized to NULL or to a filename.

Setting an Internal LOB to NULL

You may want to set the internal LOB value to NULL upon inserting the row in cases where you do not have the LOB data at the time of the INSERT or if you want to issue a SELECT statement at some later time such as:

  SELECT COUNT (*) FROM Voiced_tab WHERE Recording IS NOT NULL; 

because you want to see all the voice-over segments that have been recorded, or

  SELECT COUNT (*) FROM Voiced_tab WHERE Recording IS NULL; 

if you wish to establish which segments still have to be recorded.

Note that you cannot call OCI or DBMS_LOB functions on a NULL LOB, so you must then issue an SQL UPDATE statement to reset the null LOB column -- to EMPTY_BLOB() or EMPTY_CLOB() or to a value (for example, 'Denzel Washington') for internal LOBs, or to a filename for external LOBs.

The point is that you cannot make a function call from the supported programmatic environments on a LOB that is NULL. These functions only work with a locator, and if the LOB column is NULL, there is no locator in the row.

Setting an Internal LOB to Empty

If you do not want to set an internal LOB column to NULL, you can set the LOB value to empty using the function EMPTY_BLOB () or EMPTY_CLOB() in the INSERT statement:

INSERT INTO a_table VALUES (EMPTY_BLOB());
 

Even better is to use the returning clause (thereby eliminating a round trip that is necessary for the subsequent SELECT), and then immediately call OCI or the PL/SQL DBMS_LOB functions to populate the LOB with data.

DECLARE
   Lob_loc  BLOB;
BEGIN
   INSERT INTO a_table VALUES (EMPTY_BLOB()) RETURNING blob_col INTO Lob_loc;
 /* Now use the locator Lob_loc to populate the BLOB with data */
END;

Initializing LOBs Example Using Table Multimedia_tab

You can initialize the LOBs in Multimedia_tab by using the following INSERT statement:

INSERT INTO Multimedia_tab VALUES (1001, EMPTY_CLOB(), EMPTY_CLOB(), NULL,
    EMPTY_BLOB(), EMPTY_BLOB(), NULL, NULL, NULL, NULL);

This sets the value of story, flsub, frame and sound to an empty value, and sets photo, and music to NULL.

Initializing Internal LOB Columns to a Value

Alternatively, LOB columns, but not LOB attributes, may be initialized to a value. Which is to say -- internal LOB attributes differ from internal LOB columns in that LOB attributes may not be initialized to a value other than NULL or empty.

Note that you can initialize the LOB column to a value that contains more than 4K data.

Initializing External LOBs to NULL or a File Name

An external LOB (BFILE) can be initialized to NULL or to a filename using the BFILENAME() function.

---

¹ ACID = Access Control Information Directory. This is the attribute that determines who has what type of access and to what directory data. It contains a set of rules for structural and content access items. For more information see the Oracle Internet Directory Administrators Guide.
² Note: You could use SQL to populate a LOB column with data even if it contained NULL, for example, unless its a LOB attribute. However, you cannot use the supported programmatic environment interfaces on a NULL LOB!