LONG-to-LOB Migration Limitations

Before migrating from LONGs to LOBs, note the following issues:

Clustered Tables

LOBs are not allowed in clustered tables, whereas LONGs are allowed. So if a table is a part of a cluster, its LONG or LONG RAW column cannot be changed to LOB.

Replication

Oracle does not support the replication of columns that use the LONG and LONG RAW datatypes. Oracle simply omits columns containing these datatypes from replicated tables. You must convert LONG datatypes to LOBs in Oracle8i and then replicate.

This is not a restriction imposed by LONG-to-LOB, but instead, the LONG-to-LOB migration enables the replication of these columns.

If a table is replicated or has materialized views, and its LONG column is changed to LOB, you may have to manually fix the replicas.

Triggers

Triggers are a problem in the following cases:

• You cannot have LOB columns in the UPDATE OF list in the UPDATE OF trigger. LONG columns are allowed. For example, you cannot say:

  create table t(lobcol CLOB);
  create trigger trig before/after update of lobcol on t ...;
  

  Hence, in the following case the trigger becomes invalidated and cannot be recompiled:

  create table t(lobcol LONG);
  create or replace trigger trig before (or after) update of lobcol on t
  for each row
  begin
    dbms_output.put_line('lmn');
  end;
  /
  insert into t values('abc');
  UPDATE t SET lobcol = 'xyz';
  
  ALTER TABLE t MODIFY (lobcol CLOB); -- invalidates the trigger
  UPDATE t SET lobcol = 'xyz'; -- doesn't execute because trigger
                               -- can't be revalidated
  

• If a view with a LOB column has an INSTEAD OF TRIGGER, then you cannot specify a string INSERT/UPDATE into the LOB column. However, if this is a LONG column before the migration, then a string INSERT/UPDATE is allowed. So certain SQL statements which worked before will not work now. For example:

  CREATE TABLE t(a LONG);
  CREATE VIEW v AS SELECT * FROM t;
  CREATE TRIGGER trig INSTEAD OF insert on v....;
  INSERT INTO v VALUES ('abc')      -- works now
  ALTER TABLE t MODIFY (a CLOB);
  INSERT INTO v VALUES ('abc');     -- does not work now
  
  

  The above statement throws an error because implicit conversion from character data to LOBs is not supported in instead-of triggers.

These restrictions may be removed in a future release. All other triggers work without a problem.

Indexes

Indexes on any column of the table being migrated must be manually rebuilt. This includes functional and domain indexes, must be manually rebuilt.

• Domain indexes for the LONG column must be dropped before ALTERing a LONG column to LOB.

• Functional indexes on LONG columns converted to LOBs should work without any changes to your code.

LONGs, LOBs, and NULLs

There is a difference in how NULL and zero-length LONGs and LOBs behave. Applications migrating from LONG-to-LOB are not affected by this behavior, as described below:

Consider these two tables, long_tab and lob_tab:

CREATE TABLE long_tab(id NUMBER, long_col LONG);
CREATE TABLE lob_tab(id NUMBER, lob_col LOB);

NULL LONGs Versus Zero Length LONGs

Zero length LONGs and NULL LONGs are the same. So the following two statements each produce the same result, each one inserting a NULL in the LONG column:

INSERT INTO long_tab values(1, NULL);
INSERT INTO long_tab values(1, ''); -- Zero length string inserts NULL into the 
LONG column

NULL LOBs Versus Zero Length LOBs

For LOBs, the following two statements also insert a NULL in the LOB column:

INSERT INTO lob_tab values(1, NULL);
INSERT INTO lob_tab values(1, '');  -- A zero length string inserts NULL into 
LOB column

However, if we truly insert a zero-length LOB using the empty_clob() constructor, the LOB column will be non-NULL.

INSERT INTO lob_tab values(1, empty_clob());  -- A zero length LOB is not the 
same as NULL