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.htmUsing 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; /