15.3 Other Considerations While Migrating LONG Columns to LOBs

This section describes some more considerations when migrating LONG columns to LOBs.

• Migrating Applications from LONGs to LOBs
  Most APIs that work with LONG data types in the PL/SQL, JDBC and OCI environments are enhanced to also work with LOB data types.
• Alternate Methods for LOB Migration
  Online Redefinition is the preferred way for migrating LONG data types to LOBs. However, if keeping the application online during the migration is not your primary concern, then you can also use one of the following ways to migrate LONG data to LOBs.

15.3.1 Migrating Applications from LONGs to LOBs

Most APIs that work with LONG data types in the PL/SQL, JDBC and OCI environments are enhanced to also work with LOB data types.

These APIs are collectively referred to as the data interface for LOBs. Among other things, the data interface provides the following benefits:

• Changes needed are minimal in PL/SQL, JDBC and OCI applications that use tables with columns converted from LONG to LOB data types.
• You can work with LOB data types in your application without having to deal with LOB locators.

See Also:

• Data Interface for LOBs for details on JDBC and OCI APIs included in the data interface.

• SQL Semantics and LOBs for details on SQL syntax supported for LOB data types.

• PL/SQL Semantics for LOBs for details on PL/SQL syntax supported for LOB data types.

Note:

You can use various techniques to do either of the following:

• Convert columns of type LONG to either CLOB or NCLOB columns

• Convert columns of type LONG RAW to BLOB type columns

Unless otherwise noted, discussions in this chapter regarding LONG to LOB conversions apply to both of these data type conversions.

However, there are differences between LONG and LOB data types that may impact your application migration plans or require you to modify your application.

Identify Application Rewrite Using utldtree.sql

When you migrate your table from LONG to LOB column types, certain parts of your PL/SQL application may require rewriting. You can use the utility, rdbms/admin/utldtree.sql, to determine which parts.

The utldtree.sql utility enables you to recursively see all objects that are dependent on a given object. For example, you can see all objects which depend on a table with a LONG column. You can only see objects for which you have permission.

Instructions on how to use utldtree.sql are documented in the file itself. Also, utldtree.sql is only needed for PL/SQL. For SQL and OCI, you have no requirement to change your applications.

SQL Differences

• Indexes: LONG and LOB data types only support domain and functional indexes.
  • Any domain index on a LONG column must be dropped before converting the LONG column to LOB column. This index may be manually recreated after the migration.
  • Any function-based index on a LONG column is unusable during the conversion process and must be rebuilt after converting. Application code that uses function-based indexing should work without modification after the rebuild.
    To rebuild an index after converting, use the following steps:

    1. Select the index from your original table as follows:

       SELECT index_name FROM user_indexes WHERE table_name='LONG_TAB'; 

       Note:

       The table name must be capitalized in this query.
    2. For each selected index, use the command:

       ALTER INDEX <index> REBUILD

• Constraints: The only constraint allowed on LONG columns are NULL and NOT NULL. All constraints of the LONG columns are maintained for the new LOB columns. To alter the constraints for these columns, or alter any other columns or properties of this table, you have to do so in a subsequent ALTER TABLE statement.
• Default Values: If you do not specify a default value, then the default value for the LONG column becomes the default value of the LOB column.
• Triggers: Most of the existing triggers on your table are still usable. However, you cannot have LOB columns in the UPDATE OF list of an AFTER UPDATE OF trigger. For example, the following create trigger statement is not valid:

  CREATE TABLE t(lobcol CLOB);
  CREATE TRIGGER trig AFTER UPDATE OF lobcol ON t ...;

  LONG columns are allowed in such triggers. So, you must drop the AFTER UPDATE OF triggers on any LONG columns before migrating to LOBs.

• Clustered tables: LOB columns are not allowed in clustered tables, whereas LONGs are allowed. If a table is a part of a cluster, then any LONG or LONG RAW column cannot be changed to a LOB column.

Empty LOBs Compared to NULL and Zero Length LONGs

A LOB column can hold an empty LOB. An empty LOB is a LOB locator that is fully initialized, but not populated with data. Because LONG data types do not use locators, the empty concept does not apply to LONG data types.

Both LOB column values and LONG column values, inserted with an initial value of NULL or an empty string literal, have a NULL value. Therefore, application code that uses NULL or zero-length values in a LONG column functions exactly the same after you convert the column to a LOB type column.

In contrast, a LOB initialized to empty has a non-NULL value as illustrated in the following example:

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

REM     A zero length string inserts a NULL into the LONG column:
INSERT INTO long_tab values(1, ''); 

REM     A zero length string inserts a NULL into the LOB column:
INSERT INTO lob_tab values(1, '');  

REM     Inserting an empty LOB inserts a non-NULL value: 
INSERT INTO lob_tab values(1, empty_clob());  

DROP TABLE long_tab;
DROP TABLE lob_tab;

Overloading with Anchored Types

For applications using anchored types, some overloaded variables resolve to different targets during the conversion to LOBs. For example, given the procedure p overloaded with specifications 1 and 2:

procedure p(l long) is ...;       -- (specification 1)  
procedure p(c clob) is ...;       -- (specification 2)  

and the procedure call:

declare  
     var  longtab.longcol%type;  
   BEGIN  
     ...  
   p(var);  
     ...  
END;

Prior to migrating from LONG to LOB columns, this call would resolve to specification 1. Once longtab is migrated to LOB columns this call resolves to specification 2. Note that this would also be true if the parameter type in specification 1 were a CHAR, VARCHAR2, RAW, LONG RAW.

If you have migrated you tables from LONG columns to LOB columns, then you must manually examine your applications and determine whether overloaded procedures must be changed.

Some applications that included overloaded procedures with LOB arguments before migrating may still break. This includes applications that do not use LONG anchored types. For example, given the following specifications (1 and 2) and procedure call for procedure p:

procedure p(n number) is ...;       -- (1)  
procedure p(c clob) is ...;         -- (2)  
  
p('123');                 -- procedure call 

Before migrating, the only conversion allowed was CHAR to NUMBER, so specification 1 would be chosen. After migrating, both conversions are allowed, so the call is ambiguous and raises an overloading error.

Some Implicit Conversions Are Not Supported for LOB Data Types

PL/SQL permits implicit conversion from NUMBER, DATE, ROW_ID, BINARY_INTEGER, and PLS_INTEGER data types to a LONG; however, implicit conversion from these data types to a LOB is not allowed.

If your application uses these implicit conversions, then you have to explicitly convert these types using the TO_CHAR operator for character data or the TO_RAW operator for binary data. For example, if your application has an assignment operation such as:

number_var := long_var;  -- The RHS is a LOB variable after converting.

then you must modify your code as follows:

number_var := TO_CHAR(long_var); 
-- Assuming that long_var is of type CLOB after conversion

The following conversions are not supported for LOB types:

• BLOB to VARCHAR2, CHAR, or LONG

• CLOB to RAW or LONG RAW

This applies to all operations where implicit conversion takes place. For example if you have a SELECT statement in your application as follows:

SELECT long_raw_column INTO my_varchar2 VARIABLE FROM my_table

and long_raw_column is a BLOB after converting your table, then the SELECT statement produces an error. To make this conversion work, you must use the TO_RAW operator to explicitly convert the BLOB to a RAW as follows:

SELECT TO_RAW(long_raw_column) INTO my_varchar2 VARIABLE FROM my_table

The same holds for selecting a CLOB into a RAW variable, or for assignments of CLOB to RAW and BLOB to VARCHAR2.

15.3.2 Alternate Methods for LOB Migration

Online Redefinition is the preferred way for migrating LONG data types to LOBs. However, if keeping the application online during the migration is not your primary concern, then you can also use one of the following ways to migrate LONG data to LOBs.

Using ALTER TABLE to Convert LONG Columns to LOB Columns

See Also:

Migration Considerations

You can use the ALTER TABLE statement in SQL to convert a LONG column to a LOB column.

To do so, use the following syntax:

ALTER TABLE [<schema>.]<table_name>
   MODIFY ( <long_column_name> { CLOB | BLOB | NCLOB } 
  [DEFAULT <default_value>]) [LOB_storage_clause];

For example, if you had a table that was created as follows:

CREATE TABLE Long_tab (id NUMBER, long_col LONG);

then you can change the column long_col in table Long_tab to data type CLOB using following ALTER TABLE statement:

ALTER TABLE Long_tab MODIFY ( long_col CLOB );

Note:

The ALTER TABLE statement copies the contents of the table into a new space, and frees the old space at the end of the operation. This temporarily doubles the space requirements.

Note that when using the ALTER TABLE statement to convert a LONG column to a LOB column, only the following options are allowed:

• DEFAULT option, which enables you to specify a default value for the LOB column.

• The LOB_storage_clause, which enables you to specify the LOB storage characteristics for the converted column. This clause can be specified in the MODIFY clause.

Other ALTER TABLE options are not allowed when converting a LONG column to a LOB type column.

Copying a LONG to a LOB Column Using the TO_LOB Operator

You can use the CREATE TABLE AS SELECT statement or the INSERT AS SELECT statement with the TO_LOB operator to copy data from a LONG column to a CLOB or NCLOB column, or from a LONG RAW column to a BLOB column. For example, if you have a table with a LONG column that was created as follows:

CREATE TABLE Long_tab (id NUMBER, long_col LONG);  

then you can do the following to copy the column to a LOB column:

CREATE TABLE Lob_tab (id NUMBER, clob_col CLOB);  
INSERT INTO Lob_tab SELECT id, TO_LOB(long_col) FROM long_tab;  
COMMIT;

If the INSERT statement returns an error because of lack of undo space, then you can incrementally migrate LONG data to the LOB column using the WHERE clause. After you ensure that the data is accurately copied, you can drop the original table and create a view or synonym for the new table using one of the following sequences:

DROP TABLE Long_tab;  
CREATE VIEW Long_tab (id, long_col) AS SELECT * from Lob_tab; 

or

DROP TABLE Long_tab;  
CREATE SYNONYM Long_tab FOR Lob_tab;

This series of operations is equivalent to changing the data type of the column Long_col of table Long_tab from LONG to CLOB. With this technique, you have to re-create any constraints, triggers, grants, and indexes on the new table.

Use of the TO_LOB operator is subject to the following limitations:

• You can use TO_LOB to copy data to a LOB column, but not to a LOB attribute of an object type.

• You cannot use TO_LOB with a remote table. For example, the following statements do not work:

  INSERT INTO tb1@dblink (lob_col) SELECT TO_LOB(long_col) FROM tb2; 
  INSERT INTO tb1 (lob_col) SELECT TO_LOB(long_col) FROM tb2@dblink; 
  CREATE TABLE tb1 AS SELECT TO_LOB(long_col) FROM tb2@dblink; 
  

• You cannot use the TO_LOB operator in the CREATE TABLE AS SELECT statement to convert a LONG or LONG RAW column to a LOB column when creating an index organized table.

  To work around this limitation, create the index organized table, and then do an INSERT AS SELECT of the LONG or LONG RAW column using the TO_LOB operator.

• You cannot use TO_LOB inside any PL/SQL block.