2 Working with LOBs

Using the Data Interface for LOBs

You can perform bind and define operations on CLOB and 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 CLOB, or RAW data into a BLOB.

• Using a define operation to define an output buffer in your application that holds character data selected from a CLOB, or RAW data selected from a BLOB.

  See Also:

  Chapter 20, "Data Interface for Persistent LOBs" for more information on implicit assignment of LOBs to other data types.

Using the LOB Locator to Access and Modify LOB Values

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 CLOB, BLOB, NCLOB and 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.

Table print_media

The table 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;

Initializing a LOB Column to Contain a Locator

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 BFILE column.

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);

INSERT INTO print_media (product_id, ad_id, ad_header) 
    VALUES (1726, 1, adheader_typ('AD FOR ORACLE', sysdate, 
    'Have Grid', EMPTY_BLOB()));

Accessing a LOB Using SQL

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 VARCHAR2 column.

Accessing a LOB Using the Data Interface

You can select a LOB directly into CHAR or 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 VARCHAR2 buffer final_ad.

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;

Accessing a LOB Using the Locator Interface

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.

Rules for LOB Columns

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 CLOB or BLOB, and select a remote CLOB or 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.

• The following data structures are supported only as temporary instances. You cannot store these instances in database tables:

  • 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 ORDER BY clause of a query, or in the GROUP BY clause of a query or in an aggregate function.

• You cannot specify a LOB column in a SELECT... DISTINCT or SELECT... UNIQUE statement or in a join. However, you can specify a LOB attribute of an object type column in a SELECT... DISTINCT statement or in a query that uses the UNION or MINUS set operator if the column's object type has a MAP or 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 UPDATE OF clause.

• 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 CLOB column.

• In an INSERT... AS 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.

Restrictions for LOB Operations

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 CLOB column:

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