Checked for relevance on 28-OCT-2009

PURPOSE
-------

How-To convert a LONG column to a CLOB to perform. substring operations on the 
data.
 
SCOPE & APPLICATION
-------------------

VB, C/C++, Java, PL/SQL, stored procedure developers

How-To convert a LONG column to a CLOB
---------------------------------------
 

There is no supplied fuction to perform. substring operations directly on LONG 
columns. However, such functions are possible with CLOB type columns.
DBMS_LOB.Copy() function can copy all or part of a source internal CLOB  to a 
CLOB or BLOB to a BLOB. You can specify the offset for both the source and 
destination LOB ( CLOB or BLOB ), and the number of bytes or characters to copy.

Now, there is function that coverts a LONG to CLOB. 

With version 8.1.X and later the TO_LOB function can be used to
convert data stored in longs and long raws to CLOBs and BLOBs respectively.

This function works fine in SQL*Plus when used as follows:

   create table long_data (a number, b long);
   create table clob_data (a number, b clob);

   insert into long_data values (1, 'Long data to be converted to clob');

   insert into clob_data select a, to_lob(b) from long_data;

   select * from clob_data;

   A B
   --------------------------------------------------------------
   1    Long data to be converted to clob

With a long raw data, the function goes as 

   create table long_data (a number, b long raw);
   create table blob_data (a number, b blob);

   insert into long_data values (1, utl_raw.cast_to_raw('Long data to be converted to clob'));

   insert into blob_data select a, to_lob(b) from long_data;


RELATED DOCUMENTS
-----------------