LOB Rules and Restrictions

This section provides details on LOB rules and restrictions.

Rules for LOB Columns

LOB columns do have the following rules and restrictions:

1. You cannot specify a LOB as a primary key column.
2. Oracle Database has limited support for remote LOBs. Remote LOBs are supported in three ways.
   • Create table as select or insert as select.
     SQL

     CREATE TABLE t AS
       SELECT *
       FROM   table1@remote_site;
     
     INSERT INTO t
     SELECT *
     FROM   table1@remote_site;
     
     UPDATE t
     SET    lobolm = (SELECT lobclm
                      FROM   table1@remote_site);
     
     INSERT INTO table1@remote_site
     SELECT *
     FROM   local_table;
   • Functions on remote LOBs returning scalars. SQL and PL/SQL functions having a LOB parameter and returning a scalar datatype are supported. Other DBMS_LOB APIs and SQL functions are not supported for use with remote LOB columns. For example, the following statement is supported:
     SQL

     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;

     Also, the following statement is not supported because of DBMS_LOB.SUBSTR returns a LOB:

     SQL

     CREATE TABLE tab AS
       SELECT dbms_lob.Substr(clob_col)
       FROM   tab@dbs2;
   • Data Interface for remote LOBs. You can insert a binary or character buffer into a remote BLOB or CLOB, and select a remote CLOB or BLOB into a character or binary buffer. For example (in PL/SQL):
     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.

3. Clusters cannot contain LOBs, they can contain either as a key or non-key columns.
4. Below defined 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
5. You cannot specify LOB columns in the GROUP BY clause of a query, or in the ORDER BY clause of a query or in an aggregate function.
6. You cannot specify a LOB column in a SELECT… UNIQUE or SELECT… DISTINCT statement or in a join. You can specify a LOB attribute of an object type column in a query that uses the UNION or MINUS set operator or in a SELECT… DISTINCT statement if the column’s object type has a MAP or ORDER BY function defined on it.
7. The first (INITIAL) extent of a LOB segment must have contain at least three database blocks.
8. While creating an AFTER UPDATE DML trigger, you cannot specify a LOB column in the UPDATE OF clause.
9. You cannot define a LOB column as a part of an index key. However, you can specify a LOB column in the indextype specification of a domain index.
10. In an INSERT… AS SELECT operation, LOB lets you to bind up to 4000 bytes of data to LOB columns and attributes.
11. If a table contains 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 whereas, you can bind more than 4000 bytes of data to either of the LONG or the LOB column.