Converting BLOB data to CLOB 1

[Shantha]

Hi,

I am facing problems in populating the data from a BLOB field to a CLOB field.

Could somebody help in this regards.

Shantha.

Talent is what you possess;
genius is what possesses you

 

[TheOracleGuy]

Hello Shantha

By using the UTL_RAW Oracle built-in package, available with Oracle PL/SQL 8 or higher.

Example:

drop table test_blob
Table dropped

drop table test_clob
Table dropped

CREATE TABLE test_blob (c1 NUMBER,
c2 BLOB)
Table created

CREATE TABLE test_clob (c1 NUMBER,
c2 CLOB)
Table created

INSERT INTO test_blob
VALUES (1, '48656C6C6F' || UTL_RAW.cast_to_raw (' there!'))
1 row inserted


select * from test_blob
C1 C2
---------- -----------
1 <BLOB>

1 row selected


INSERT INTO test_clob
VALUES (1, 'Some data for record 1.')
1 row inserted


select * from test_clob
C1 C2
---------- ----------------------------------------
1 Some data for record 1.

1 row selected


insert into test_clob
select 2, UTL_RAW.cast_to_varchar2(c2)
from test_blob
where c1 = 1
1 row inserted


select * from test_clob
C1 C2
---------- ----------------------------------------
1 Some data for record 1.
2 Hello there!

2 rows selected

Hope this helps.

Regards

TheOracleGuy