OraLobEditor - Edit Oracle LOB (BLOB, CLOB) data »

Download OraLobEditor Free Trial »

Form http://www.dba-oracle.com/oracle_news/2005_5_9_converting_long_lob_data_types.htm

Converting Oracle long to lob data types


Using TO_LOB one can easily convert LONGS to LOBS. 

 

Using the same conversion function one can convert LONG into CLOB and LONGRAW TO BLOB. 

 

Have a look into following example 

 

SQL>create table tlong(itemcd number(30),itemdesc long); 

/ 

Table created. 

 

SQL>Create table tlob(ItemCd Number(30),Itemdesc clob); 

Table created 

 

Now dump some values from some table into table tlong 

SQL>insert into tlong select icode,iname from InvTab; 

2000 rows created. 

Now try to insert into lob table from long table 

SQL>Insert into tlob select itemcd,TO_LOB(itemdesc) from tlong 

2000 rows created. 

 

but in PL/SQL you need to handle different way 

normal select into insert will not work,but excute immediate will be the workaround in oracle 8.1.7 

 

Let's see how it can be done PL/SQL 

 

begin 

insert into tlob select itemcd,TO_LOB(itemdesc) from tlong; 

end; 

/ 

 

the above pl/sql block works well with oralce 9i and oracle 10g but fails in oracle 8.1.7 with the following error 

ERROR at line 2: 

ORA-06550: line 2, column 33: 

PLS-00201: identifier 'TO_LOB' must be declared 

ORA-06550: line 2, column 1: 

PL/SQL: SQL Statement ignored 

 

Then you should use dynamic sql (Execute Immediate 'SQL statement') as follows 

 

begin 

execute immediate 'insert into tlob select itemcd,TO_LOB(itemdesc) from tlong'; 

end; 

/