|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)
Part Number A96591-01
This chapter discusses the following topics:
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, 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.
There are three SQL datatypes for defining instances of internal LOBs:
Internal LOBs are divided into persistent and temporary 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.
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.
There is one datatype, BFILE, for declaring instances of external SQL
LOBwhose value is composed of binary ("raw") data, and is stored outside the database tablespaces in a server-side operating system file.
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.
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.
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.
There are APIs in cartridge service that can convert between client character set and UCS-2:
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 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
UPDATE statement using the functions
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
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 Database Reference.).
See Appendix B, "The Multimedia Schema", 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 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));
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".
When creating tables that contain LOBs use the guidelines described in the following sections:
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:
NULLhas 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.
LOBstored in a table is a
LOBof zero length that has a locator. So, if you
SELECTfrom an empty
LOBcolumn or attribute, you get back a locator which you can use to populate the
LOBwith 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
BFILE) can be initialized to
NULL or to a filename.
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:
because you want to see all the voice-over segments that have been recorded, or
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_CLOB() or to a value (for example, 'Denzel Washington') for internal LOBs, or to a filename for external
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.
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
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;
You can initialize the
Multimedia_tab by using the following
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
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.
An external LOB (BFILE) can be initialized to NULL or to a filename using the BFILENAME() function.
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!