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
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;
/
 |
|
Need an Oracle Health Check?
- Do you have
bad performance after an upgrade?
- Need to
certify that your database follows best practices?
BC Oracle performance gurus can quickly
certify every aspect of your
Oracle database and provide a complete verification that your database
is fully optimized. |
|
|
