Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

LOB Rules and Restrictions

  • Last Updated : 30 Jul, 2019

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.




      CREATE TABLE t AS 
        SELECT
        FROM   table1@remote_site; 
        
      INSERT INTO
      SELECT
      FROM   table1@remote_site; 
        
      UPDATE
      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:




      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:




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




      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.



My Personal Notes arrow_drop_up
Recommended Articles
Page :