To assist users in migrating to LOBs, Oracle9i supports the LONG API for LOBs. This API ensures that when LONG columns are changed to LOBs, existing applications will require few changes, if any.

Although Oracle9i supports LONG as well as LOB datatypes, Oracle recommends that existing applications should migrate to use LOBs instead of LONGs because of the added benefits that LOBs provide. For example, a single row can have multiple LOB columns as opposed to only one LONG or LONG RAW column. LOBs can also be used as attributes of a user-defined type, which is not possible with either a LONG or a LONG RAW.

The LONG-to-LOB migration has two parts:

• Data migration. This consists of the procedure to move existing tables containing LONG columns to use LOBs.
• Application migration. This specifies how existing LONG applications will change for using LOBs. An application will only need to change in very rare cases.

Data Migration

ALTER TABLE now allows a LONG column to be modified to CLOB or NCLOB and a LONG_RAW column to be modified to BLOB. The syntax is as follows:

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 with the following definition:

CREATE TABLE Long_tab (id NUMBER, long_col LONG);

you can change the column long_col in table Long_tab to datatype CLOB as follows:

ALTER TABLE Long_tab MODIFY ( long_col CLOB );

Application Migration

In Oracle9i, there are new SQL, PL/SQL and OCI APIs for migrating your applications from LONG to LOBs. In SQL,

• SQL functions and operators that accept VARCHAR as arguments accept CLOBs as well

• CLOBs can be returned from SQL functions

In PL/SQL:

• A new set of overloaded methods are added to the STANDARD package for LOB support

• Implicit conversion for LOBS to VARCHARs and RAWs and vice versa is done in assignments and when passing parameters

• A user can define and bind LOB column as VARCHARs and RAWs

Finally, OCI now supports the following functions,

• Bind VARCHAR2 buffer up to 4 GB in size for INSERT/UPDATE into CLOB column

• Bind RAW buffer up to 4 GB in size for INSERT/UPDATE into BLOB column

• Define a CLOB as VARCHAR2 to SELECT the LOB data directly into a buffer

• Define a BLOB column as RAW to select the LOB data directly into a buffer

• INSERT/UPDATE in a single piece, or Piecewise or in array mode

	Visit the OTN Discussion Forum that the Oracle SQL and PL/SQL teams monitors
	Oracle 9i SQL Reference documentation contains in-depth description of LONG-to-LOB migration

Archives