Skip Headers

Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)

Part Number A96591-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Basic LOB Components

This chapter discusses the following topics:

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 ACIDFoot 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:

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 LOBs do not participate in transactions. Any support for integrity and durability must be provided by the underlying file system as governed by the operating system.

External LOB Datatypes

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

Internal LOBs Use Copy Semantics, External LOBs Use Reference Semantics

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

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.

See Also:

Converting Between Client Character Set and UCS-2

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

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.

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 interfacesFoot 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.

See Also::

"Inserting a LOB Value using EMPTY_CLOB() or EMPTY_BLOB()" in Chapter 10, "Internal Persistent LOBs".

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.

See Also::

"INSERT a Row Using BFILENAME()" in Chapter 12, "External LOBs (BFILEs)".

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,
    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 
          EMPTY_BLOB(), NULL, NULL, NULL, 
          Map_typ('Moon Mountain', 23, 34, 45, 56, EMPTY_BLOB(), NULL));


You cannot initialize a LOB object attribute with a literal.

Accessing a LOB Through a Locator


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.

    Image1 CLOB;
    ImageNum INTEGER := 101;
    SELECT story INTO Image1 FROM Multimedia_tab
        WHERE clip_id = ImageNum;
    DBMS_OUTPUT.PUT_LINE('Size of the Image is: ' ||
    /* more LOB routines */

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

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:

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:


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.

   Lob_loc  BLOB;
 /* Now use the locator Lob_loc to populate the BLOB with data */

Initializing LOBs Example Using Table Multimedia_tab

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


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.

See Also:

Chapter 7, "Modeling and Design"

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.

See Also:

Chapter 12, "External LOBs (BFILEs)", "Directory Object" -- "Initializing a BFILE Locator".

1 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.
2 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!

Go to previous page Go to next page
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index