We help IT Professionals succeed at work.

BLOB to CLOB copy

prashant_n_mhatre
on
I have a data in one table of type BLOB. I need to copy it into a CLOB in another table. Does anyone have code?

Thanks
Comment
Watch Question

u should use dbms_lob oackage for this to work out

here is an example of code

declare
  from_col blob;
  to_col   clob;
  byt_copy integer;
  off_write integer;
  off_read integer;

begin
  byt_copy := 55;  --bytes to copy

  off_write := 1;  --offset to start writing within      
                   --destination lob value

  off_read := 1;   --offset to start reading from within
                   --within destination lob value

  select blob_column into from_col from source_table;

  select clob_column into to_col from dest_table;

  dbms_lob.copy(to_col, from_col, byt_copy, off_write, off_read);

  commit;
end;
u should use dbms_lob oackage for this to work out

here is an example of code

declare
 from_col blob;
 to_col   clob;
 byt_copy integer;
 off_write integer;
 off_read integer;

begin
 byt_copy := 55;  --bytes to copy

 off_write := 1;  --offset to start writing within      
                  --destination lob value

 off_read := 1;   --offset to start reading from within
                  --within destination lob value

 select blob_column into from_col from source_table;

 select clob_column into to_col from dest_table;

 dbms_lob.copy(to_col, from_col, byt_copy, off_write, off_read);

 commit;
end;

Author

Commented:
Doesn't work...

dbms_lob.copy(to_col, from_col, byt_copy, off_write, off_read);

this statement gives error.

CERTIFIED EXPERT
Commented:
Please see the following example --- modified  based on my post at http://www.experts-exchange.com/jsp/qShow.jsp?ta=oracle&qid=20207647
Note: For multiple records, a loop needs to be constructed, for example, on a unique key.
----------------------------------------------------------------------------------------------------------

SQL> create table tb (b blob);
Table created.

SQL> insert into tb values ('AAA');
1 row created.

SQL> create table tc (c clob);
Table created.

SQL> declare
  2  b0 blob;                -- a temp blob
  3  l0 int;                 -- length of the blob
  4  c0 varchar2(32000);        -- a chunck of buffer; use multiple chuncks, if needed
  5  begin
  6    select b into b0 from tb;
  7    l0:=dbms_lob.getlength(b0);
  8    dbms_lob.read(b0,l0,1,c0);
  9    insert into tc values (c0);
 10  end;
 11  /
PL/SQL procedure successfully completed.