|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 1 (9.0.1)
Part Number A88879-01
Migrating From LONGs to LOBs, 5 of 14
Before migrating from LONGs to LOBs, note the following issues:
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.
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 are a problem in the following cases:
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
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 on any column of the table being migrated must be manually rebuilt. This includes functional and domain indexes, must be manually rebuilt.
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:
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
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.