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

      edit
      close

      play_arrow

      link
      brightness_4
      code

      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; 

      chevron_right

      
      

    • 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:
      filter_none

      edit
      close

      play_arrow

      link
      brightness_4
      code

      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; 

      chevron_right

      
      

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

      filter_none

      edit
      close

      play_arrow

      link
      brightness_4
      code

      CREATE TABLE tab AS 
        SELECT dbms_lob.Substr(clob_col) 
        FROM   tab@dbs2; 

      chevron_right

      
      

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

      edit
      close

      play_arrow

      link
      brightness_4
      code

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

      chevron_right

      
      

      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

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.




Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.