LOB Restrictions

The use of LOBs are subject to some restrictions:

• Distributed LOBs are not supported. Specifically, this means that the user cannot use a remote locator in the SELECT and WHERE clauses. This includes using DBMS_LOB package functions. In addition, references to objects in remote tables with or without LOB attributes are not allowed.

  Invalid operations. For example, the following operations are invalid:

  • SELECT lobcol from table1@remote_site;

  • INSERT INTO lobtable select type1.lobattr from table1@remote_site;

  • SELECT dbms_lob.getlength(lobcol) from table1@remote_site;

  Valid operations. Valid operations on LOB columns in remote tables include:

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

  • UPDATE table1@remote...

  • DELETE table1@remote...

• Table type and clauses not supporting LOBs

  LOBs are not supported in the following table types and clauses:

  • Clustered tables and thus LOBs cannot be a cluster key.

  • GROUP BY, ORDER BY, SELECT DISTINCT, aggregates and JOINS. However, UNION ALL is allowed on tables with LOBs. UNION, MINUS, and SELECT DISTINCT are allowed on LOB attributes if the object type has a MAP or ORDER function.

  • Index organized tables. LOBs however, are supported in non-partitioned index organized tables.

  • VARRAYs.

  • NCLOBs are not allowed as attributes in object types when you create tables, but NCLOB parameters are allowed in methods. NCLOBs store fixed-width data.

• ANALYZE and ESTIMATE. LOBS are not supported in the ANALYZE... COMPUTE or ESTIMATE STATISTICS statements.

• Trigger Body. You can use the LOB column or LOB attribute in a trigger body subject to the following conditions. In general, the :new and :old LOB values bound in the trigger are read-only which means that you cannot write to the LOB. More specifically:
  1. In before row and after row triggers -
     • you can read the :old value of a LOB in both the triggers.

     • you can read the :new value of the LOB only in an after-row trigger.

  2. In INSTEAD OF triggers on views, you can read both the :new and :old values.

  3. You cannot specify the LOB column in an OF clause (Note that a BFILE can be modified without updating the underlying tables on which it is based).

  4. If you use OCI functions or DBMS_LOB routines to update LOB values or LOB attributes on object columns, the functions or routines will not fire the triggers defined on the tables containing the columns or attributes.

     See Also: Oracle8i Data Cartridge Developer's Guide, for more information about firing triggers on domain indexes.

• Client-side PL/SQL procedures. These may not call DBMS_LOB package routines. However, you can use server-side PL/SQL procedures or anonymous blocks in Pro*C/C++ to call DBMS_LOB package routines.

• Read-Only Support for External LOBs (BFILEs). Oracle8i supports read-only operations on external LOBs. If you need to update or write to external LOBs, you have to develop client side applications suited to your needs

• CACHE / NOCACHE / CACHE READS. CACHE READS LOBs are supported in this release. If you use CACHE READS LOBs and then downgrade to 8.0 or 8.1.5, your CACHE READS LOBs generates a warning and becomes CACHE LOGGING LOBs. You can explicitly alter the LOBs' storage characteristics later if you do not want your LOBs to be CACHE LOGGING.

  See Chapter 7, "Modeling and Design", "CACHE / NOCACHE / CACHE READS" .