This chapter describes the usage and semantics of LOBs required for application development, and covers various techniques for working with LOBs.
Most of the discussions in this chapter regarding persistent LOBs assume that you are dealing with existing LOBs in tables. The task of creating tables with LOB columns is typically performed by your database administrator.
This chapter contains these topics:
The techniques you use when accessing a cell in a LOB column differ depending on the state of the given cell. A cell in a LOB Column can be in one of the following states:
The table cell is created, but the cell holds no locator or value.
A LOB instance with a locator exists in the cell, but it has no value. The length of the LOB is zero.
A LOB instance with a locator and a value exists in the cell.
You can lock a row containing a LOB to prevent other database users from writing to the LOB during a transaction. To lock a row containing a LOB, specify the
FOR UPDATE clause when you select the row. While the row is locked, other users cannot lock or update the LOB, until you end your transaction.
The LOB APIs include operations that enable you to explicitly open and close a LOB instance. You can open and close a persistent LOB instance of any type:
BFILE. You open a LOB to achieve one or both of the following results:
Open the LOB in read-only mode.
This ensures that the LOB (both the LOB locator and LOB value) cannot be changed in your session until you explicitly close the LOB. For example, you can open the LOB to ensure that the LOB is not changed by some other part of your program while you are using the LOB in a critical operation. After you perform the operation, you can then close the LOB.
Open the LOB in read write/mode—persistent
NCLOB instances only.
Opening a LOB in read write mode defers any index maintenance on the LOB column until you close the LOB. Opening a LOB in read write mode is only useful if there is an extensible index on the LOB column and you do not want the database to perform index maintenance every time you write to the LOB. This technique can increase the performance of your application if you are doing several write operations on the LOB while it is open.
If you open a LOB, then you must close the LOB at some point later in your session. This is the only requirement for an open LOB. While a LOB instance is open, you can perform as many operations as you want on the LOB—provided the operations are allowed in the given mode.
See Also:"Opening Persistent LOBs with the OPEN and CLOSE Interfaces" for details on usage of these APIs.
There are two techniques that you can use to access and modify LOB values:
You can perform bind and define operations on
BLOB columns in C applications using the data interface for LOBs in OCI. Doing so, enables you to insert or select out data in a LOB column without using a LOB locator as follows:
Using a bind variable associated with a LOB column to insert character data into a
RAW data into a
Using a define operation to define an output buffer in your application that holds character data selected from a
RAW data selected from a
See Also:Chapter 20, "Data Interface for Persistent LOBs" for more information on implicit assignment of LOBs to other data types.
The value of a LOB instance stored in the database can be accessed through a LOB locator, a reference to the location of the LOB value. Database tables store only locators in
BFILE columns. Note the following with respect to LOB locators and values:
To access or manipulate a LOB value, you pass the LOB locator to the various LOB APIs.
A LOB locator can be assigned to any LOB instance of the same type.
The characteristics of a LOB as being temporary or persistent have nothing to do with the locator. The characteristics of temporary or persistent apply only to the LOB instance.
There are differences between the semantics of locators for LOB types
NCLOB on one hand, and the semantics of locators for the
BFILE type on the other hand:
BFILE columns, the row stores a locator to the external operating system file that holds the value of the
BFILE instance in a given row has its own distinct locator; however, two different rows can contain a
BFILE locator that points to the same operating system file.
Regardless of where the value of a LOB is stored, a locator is stored in the table row of any initialized LOB column. Note that when the term locator is used without an identifying prefix term, it refers to both LOB locators and
BFILE locators. Also, when you select a LOB from a table, the LOB returned is always a temporary LOB. For more information on working with locators for temporary LOBs, see "LOBs Returned from SQL Functions".
print_media of the Oracle Database Sample Schema
PM, is used in many examples in this documentation and is defined as:
CREATE TABLE print_media ( product_id NUMBER(6) , ad_id NUMBER(6) , ad_composite BLOB , ad_sourcetext CLOB , ad_finaltext CLOB , ad_fltextn NCLOB , ad_textdocs_ntab textdoc_tab , ad_photo BLOB , ad_graphic BFILE , ad_header adheader_typ ) NESTED TABLE ad_textdocs_ntab STORE AS textdocs_nestedtab;
See Also:"Creating a Table Containing One or More LOB Columns" for the details of how
print_mediaand its associated tables and files are created.
Any LOB instance that is
NULL does not have a locator. Before you can pass a LOB instance to any LOB API routine, the instance must contain a locator. For example, you can select a
NULL LOB from a row, but you cannot pass the instance to the PL/SQL DBMS_LOB.READ procedure. The following sub-sections describe how to initialize a persistent LOB column and how to initialize a
Before you can start writing data to a persistent LOB using the supported programmatic environment interfaces (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 persistent LOB to empty in an
UPDATE statement using the functions
Note:You can use SQL to populate a LOB column with data even if it contains a
See Also:Chapter 11, "LOB Storage" for more information on initializing LOB columns.
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 function raises 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
Note:Character strings are inserted using the default character set for the instance.
INSERT statement in the
ad_sourcetext with the character string 'my Oracle',
ad_fltextn to an empty value,
ad_graphic to point to the file
my_picture located under the logical directory
CREATE OR REPLACE DIRECTORY my_directory_object AS 'oracle/work/tklocal'; INSERT INTO print_media VALUES (1726, 1, EMPTY_BLOB(), 'my Oracle', EMPTY_CLOB(), EMPTY_CLOB(), NULL, NULL, BFILENAME('my_directory_object', 'my_picture'), NULL);
Similarly, the LOB attributes for the
ad_header column in
print_media can be initialized to
NULL, empty, or a character/raw literal, which is shown in the following statement:
INSERT INTO print_media (product_id, ad_id, ad_header) VALUES (1726, 1, adheader_typ('AD FOR ORACLE', sysdate, 'Have Grid', EMPTY_BLOB()));
"OCILobLocator Pointer Assignment" for details on LOB locator semantics in OCI
Before you can access
BFILE values using LOB APIs, the
BFILE column or attribute must be made non-
NULL. You can initialize the
BFILE column to point to an external operating system file by using the
See Also:"Accessing BFILEs" for more information on initializing BFILE columns.
You can access a LOB instance using the following techniques:
Support for columns that use LOB data types is built into many SQL functions. This support enables you to use SQL semantics to access LOB columns in SQL. In most cases, you can use the same SQL semantics on a LOB column that you would use on a
See Also:For details on SQL semantics support for LOBs, see Chapter 16, "SQL Semantics and LOBs".
You can select a LOB directly into
RAW buffers using the LONG-to-LOB API in OCI and PL/SQL. In the following PL/SQL example,
ad_finaltext is selected into a
DECLARE final_ad VARCHAR(32767); BEGIN SELECT ad_finaltext INTO final_ad FROM print_media WHERE product_id = 2056 and ad_id = 12001 ; /* PUT_LINE can only output up to 255 characters at a time */ ... DBMS_OUTPUT.PUT_LINE(final_ad); /* more calls to read final_ad */ ... END;
See Also:For more details on accessing LOBs using the data interface, see Chapter 20, "Data Interface for Persistent LOBs".
You can access and manipulate a LOB instance by passing the LOB locator to the LOB APIs supplied with the database. An extensive set of LOB APIs is provided with each supported programmatic environment. In OCI, a LOB locator is mapped to a locator pointer which is used to access the LOB value.
Note:In all environments, including OCI, the LOB APIs operate on the LOB value implicitly—there is no requirement to dereference the LOB locator.
This section provides details on LOB rules and restrictions.
LOB columns are subject to the following rules and restrictions:
You cannot specify a LOB as a primary key column.
Oracle Database has limited support for remote LOBs. Remote LOBs are supported in three ways.
1. Create table as select or insert as select.
CREATE TABLE t AS SELECT * FROM table1@remote_site; INSERT INTO t SELECT * FROM table1@remote_site; UPDATE t SET lobcol = (SELECT lobcol FROM table1@remote_site); INSERT INTO table1@remote_site SELECT * FROM local_table; UPDATE table1@remote_site SET lobcol = (SELECT lobcol FROM local_table); DELETE FROM table1@remote_site <WHERE clause involving non_lob_columns>
In statements structured like the preceding examples, only standalone LOB columns are allowed in the select list.
2. Functions on remote LOBs returning scalars. SQL and PL/SQL functions having a LOB parameter and returning a scalar data type are supported. Other SQL functions and
DBMS_LOB APIs are not supported for use with remote LOB columns. For example, the following statement is supported:
CREATE TABLE tab AS SELECT DBMS_LOB.GETLENGTH@dbs2(clob_col) len FROM tab@dbs2; CREATE TABLE tab AS SELECT LENGTH(clob_col) len FROM tab@dbs2;
However, the following statement is not supported because
DBMS_LOB.SUBSTR returns a LOB:
CREATE TABLE tab AS SELECT DBMS_LOB.SUBSTR(clob_col) from tab@dbs2;
3. Data Interface for remote LOBs. You can insert a character or binary buffer into a remote
BLOB, and select a remote
BLOB into a character or binary buffer. For example (in PL/SQL):
SELECT clobcol1, type1.blobattr INTO varchar_buf1, raw_buf2 FROM table1@remote_site; INSERT INTO table1@remotesite (clobcol1, type1.blobattr) VALUES varchar_buf1, raw_buf2; INSERT INTO table1@remotesite (lobcol) VALUES ('test'); UPDATE table1 SET lobcol = 'xxx';
These are the only supported syntax involving LOBs in remote tables. No other usage is supported.
Clusters cannot contain LOBs, either as key or nonkey columns.
VARRAY of any LOB type
VARRAY of any type containing a LOB type, such as an object type with a LOB attribute
ANYDATA of any LOB type
ANYDATA of any type containing a LOB
You cannot specify LOB columns in the
BY clause of a query, or in the
BY clause of a query or in an aggregate function.
You cannot specify a LOB column in a
UNIQUE statement or in a join. However, you can specify a LOB attribute of an object type column in a
DISTINCT statement or in a query that uses the
MINUS set operator if the column's object type has a
ORDER function defined on it.
The first (
INITIAL) extent of a LOB segment must contain at least three database blocks.
The minimum extent size is 14 blocks. For an 8K block size (the default), this is equivalent to 112K.
When creating an
AFTER UPDATE DML trigger, you cannot specify a LOB column in the
You cannot specify a LOB column as part of an index key. However, you can specify a LOB column in the indextype specification of a domain index. In addition, Oracle Text lets you define an index on a
SELECT operation, you can bind up to 4000 bytes of data to LOB columns and attributes.
If a table has both
LONG and LOB columns, you cannot bind more than 4000 bytes of data to both the
LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the
LONG or the LOB column.
Note:For a table on which you have defined an
UPDATEDML trigger, if you use OCI functions or
DBMS_LOBpackage to change the value of a LOB column or the LOB attribute of an object type column, the database does not fire the DML trigger.
Chapter 4, "Using Oracle SecureFiles LOBs" for the SecureFiles capabilities (encryption, compression, and deduplication).
Chapter 18, "Migrating Columns from LONGs to LOBs" under "Migrating Applications from LONGs to LOBs", describes LONG to LOB migration limitations for clustered tables, replication, triggers, domain indexes, and function-based indexes.
"Unsupported Use of LOBs in SQL" for restrictions on SQL semantics.
For details on the INITIAL extent of a LOB segment, see "Restriction on First Extent of a LOB Segment" .
LOBs in partitioned index-organized tables are subject to other restrictions. See "Restrictions for LOBs in Partitioned Index-Organized Tables" for more information.
Other general LOB restrictions include the following:
In SQL Loader, A field read from a LOB cannot be used as an argument to a clause. See "Database Utilities for Loading Data into LOBs".
Session migration is not supported for
BFILEs in shared server (multithreaded server) mode. This implies that operations on open
BFILEs can persist beyond the end of a call to a shared server. In shared server sessions,
BFILE operations are bound to one shared server, they cannot migrate from one server to another.
Case-insensitive searches on
CLOB columns often do not succeed. For example, to do a case-insensitive search on a
ALTER SESSION SET NLS_COMP=LINGUISTIC; ALTER SESSION SET NLS_SORT=BINARY_CI; SELECT * FROM ci_test WHERE LOWER(clob_col) LIKE 'aa%';
The select fails without the
LOWER function. Oracle Text does do case-insensitive searches. Use
DBMS_LOB.INSTR() as another alternative. See Chapter 16, "SQL Semantics and LOBs".