Skip Headers

Oracle® Database Application Developer's Guide - Large Objects
10g Release 1 (10.1)

Part Number B10796-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

Migrating Table Columns from LONGs to LOBs

This chapter describes techniques for migrating tables that use LONG datatypes to LOB datatypes. This chapter covers the following topics:

Benefits of Migrating LONG Columns to LOB Columns

There are many benefits to migrating table columns from LONG datatypes to LOB datatypes.


You can use the techniques described in this chapter 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 datatype conversions.

The following list compares the semantics of LONG and LOB datatypes in various application development scenarios:

Preconditions for Migrating LONG Columns to LOB Columns

This section describes preconditions that must be met before converting a LONG column to a LOB column.

See Also:

"Migrating Applications from LONGs to LOBs" before converting your table to determine whether any limitations on LOB columns will prevent you from converting to LOBs.

Dropping a Domain Index on a LONG Column Before Converting to a LOB

Any domain index on a LONG column must be dropped before converting the LONG column to LOB column. See "Indexes on Columns Converted from LONG to LOB Datatypes" for more information.

Preventing Generation of Redo Space on Tables Converted to LOB Datatypes

Generation of redo space can cause performance problems during the process of converting LONG columns. Redo changes for the table are logged during the conversion process only if the table has LOGGING on.

Redo changes for the column being converted from LONG to LOB are logged only if the storage characteristics of the LOB column indicate LOGGING. The logging setting (LOGGING or NOLOGGING) for the LOB column is inherited from the tablespace in which the LOB is created.

To prevent generation of redo space during migration, do the following before migrating your table:

  2. ALTER TABLE Long_tab MODIFY (long_col CLOB [default <default_val>]) LOB (long_col) STORE AS (NOCACHE NOLOGGING);

    Note that you must also specify NOCACHE when you specify NOLOGGING in the STORE AS clause.

  3. ALTER TABLE Long_tab MODIFY LOB (long_col) (CACHE);
  5. Make a backup of the tablespaces containing the table and the LOB column.

Using utldtree.sql to Determine Where Your Application Needs Change

You can use the utility, rdbms/admin/utldtree.sql, to determine which parts of your application require rewriting when you migrate your table from LONG to LOB column types. This 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. Note that, you will 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 do not need to change your applications.

Converting Tables from LONG to LOB Datatypes

This section describes the following techniques for migrating existing tables from LONG to LOB datatypes:

Using ALTER TABLE to Convert LONG Columns to LOB Columns

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 datatype CLOB using following ALTER TABLE statement:

ALTER TABLE Long_tab MODIFY ( long_col CLOB );


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:

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

Migration Issues

General issues concerning migration include the following:

Copying a LONG to a LOB Column Using the TO_LOB Operator

If you do not want to use ALTER TABLE, as described earlier in this section, then you can use the TO_LOB operator on a LONG column to copy it to a LOB column. 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;  

If the INSERT 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; 


DROP TABLE Long_tab;  
CREATE SYNONYM Long_tab FOR Lob_tab;

This series of operations is equivalent to changing the datatype 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:

Online Redefinition of Tables with LONG Columns

Tables with LONG and LONG RAW columns can be migrated using online table redefinition. This technique is suitable for migrating LONG columns in database tables where high availability is critical.

To use this technique, you must convert LONG columns to LOB types during the redefinition process as follows:

This conversion is performed using the TO_LOB() operator in the column mapping of the DBMS_REDEFINITION.START_REDEF_TABLE() procedure.


You cannot perform online redefinition of tables with LONG or LONG RAW columns unless you convert the columns to LOB types as described in this section.

General tasks involved in the online redefinition process are given in the following list. Issues specific to converting LONG and LONG RAW columns are called out. See the related documentation referenced at the end of this section for additional details on the online redefinition process that are not described here.

The following example demonstrates online redefinition with LOB columns.

REM Grant privleges required for online redefinition.

REM Privileges required to perform cloning of dependent objects.

connect pm/pm

drop table cust;
create table cust(c_id   number primary key,
                  c_zip  number,
                  c_name varchar(30) default null,
                  c_long long
insert into cust values(1, 94065, 'hhh', 'ttt');

-- Creating Interim Table 
-- There is no need to specify constraints because they are 
-- copied over from the original table.
create table cust_int(c_id   number not null,
                  c_zip  number,
                  c_name varchar(30) default null,
                  c_long clob

 col_mapping varchar2(1000);
--  map all the columns in the interim table to the original table
 col_mapping :=
               'c_id             c_id  , '||
               'c_zip            c_zip , '||
               'c_name           c_name, '||
               'to_lob(c_long)   c_long';

 dbms_redefinition.start_redef_table('pm', 'cust', 'cust_int',

 error_count pls_integer := 0;
  dbms_redefinition.copy_table_dependents('pm', 'cust', 'cust_int',
                                          1, true,true,true,false,

  dbms_output.put_line('errors := ' || to_char(error_count));

exec  dbms_redefinition.finish_redef_table('pm', 'cust', 'cust_int');

-- Drop the interim table
drop table cust_int;

desc cust;

-- The following insert statement fails. This illustrates 
-- that the primary key constraint on the c_id column is 
-- preserved after migration. 

insert into cust values(1, 94065, 'hhh', 'ttt');

select * from cust;

See Also:

The following related documentation provides additional details on the redefinition process described earlier in this section:

Migrating Applications from LONGs to LOBs

This section discusses differences between LONG and LOB datatypes that may impact your application migration plans or require you to modify your application.

Most APIs that work with LONG datatypes in the PL/SQL and OCI environments are enhanced to also work with LOB datatypes. These APIs are collectively referred to as the data interface for persistent LOBs, or simply the data interface. Among other things, the data interface provides the following benefits:

LOB Columns Not Allowed in 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.

LOB Columns Not Allowed in UPDATE OF Triggers

You cannot have LOB columns in the UPDATE OF list of an UPDATE OF trigger. LONG columns are allowed in such triggers. For example, the following create trigger statement is not valid:

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

All other triggers work on LOB columns.

Indexes on Columns Converted from LONG to LOB Datatypes

Indexes on any column of the table being migrated must be manually rebuilt after converting any LONG column to a LOB column. This includes function based indexes.

Any function based index on a LONG column will be unusable during the conversion process and must be rebuilt after converting. Application code that uses function based indexing should work without modification after converting.

Note that, any domain indexes on a LONG column must be dropped before converting the LONG column to LOB column. You can rebuild the domain index after converting.

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


    The table name must be capitalized in this query.

  2. For the selected index, use the command:

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 datatypes do not use locators, the "empty" concept does not apply to LONG datatypes.

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 will function 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);

INSERT INTO long_tab values(1, NULL);

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

INSERT INTO lob_tab values(1, NULL);

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:

     var  longtab.longcol%type;  

Prior to migrating from LONG to LOB columns, this call would resolve to specification 1. Once longtab is migrated to LOB columns this call will resolve 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 Datatypes

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

If your application uses these implicit conversions, then you will 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:

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 will produce 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.