Converting Oracle long to lob
data types
Satyanarayana Reddy has published this method for
converting LONG to LOB datatypes:
http://www.dba-village.com/village/dvp_tips.TipDetails?TipIdA=2490
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 execute 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 Oracle 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;
/
|
