|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Basic LOB Components, 4 of 5
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
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
LOBcolumn stores a locator to the LOB's value which is stored in a database tablespace. Each
LOBcolumn/attribute for a given row has its own distinct
LOBlocator and also a distinct copy of the
LOBvalue stored in the database tablespace.
Before you can start writing data to an internal
LOB via one of the six programmatic environment interfacesFoot 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
UPDATE statement using the functions
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
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
AUDIO_DIR' (see the
DIRECTORYstatement in Oracle9i Reference.).
See Appendix B, "The Multimedia Schema Used for Examples in This Manual", for the definition of table
INSERT INTO Multimedia_tab VALUES (101, 'JFK interview', EMPTY_CLOB(), NULL, EMPTY_BLOB(), EMPTY_BLOB(), NULL, NULL, BFILENAME('AUDIO_DIR', 'JFK_interview'), NULL);
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);
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!