CLOB to BLOB

Experts !

I have a table as below.

reports
=====
report_id numeric
report_name varchar2(20)
rep_image clob
rep_image2 blob

At present i have data in first three column i.e there is no data in rep_iamge2. Now I want a single update statement or stored procedure which copy data from rep_image to rep_image2.

I am using oracle 8i

Any solution ?

Regards,
Sandeep
LVL 14
sandeep_patelAsked:
Who is Participating?
 
schwertnerCommented:
BLOB to CLOB CONVERSION EXAMPLE
-------------------------------
 
Following table has been used for the testing purpose -

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 TEXT                                               BLOB
 PLAIN_TEXT                                         CLOB
 
The BLOB column 'TEXT' contains some data as shown below -

SQL> select id, dbms_lob.getlength(text) from test;
 
        ID DBMS_LOB.GETLENGTH(TEXT)
---------- ------------------------
         1                       19
         2                      909

It is mandatory to initialize the CLOB column as follows for the rest of the
example to work -

SQL> update test set plain_text = empty_clob() where text is not null;
 
2 rows updated.
 
SQL> commit;
 
Commit complete.

Create the following procedure -

SQL> create or replace procedure blob_to_clob as
       a number;
       b number;
       c number := 1000;
       d number;
       offset number := 1;
       v_clob clob;
       amount binary_integer;
       buffer1 raw(1000);
       buffer2 varchar2(1000);
       cursor c1 is select text, rowid from  test where text is not null;
begin
  for i in c1
  loop
   select plain_text into v_clob from test where rowid = i.rowid for update;
   a := dbms_lob.getlength(i.text);
   b := ceil(a/c);
   for j in 1..b
   loop
       if (c*j <= a)
       then
          d:= c;
       else
          d:= a - c*(j-1);
       end if;
       DBMS_LOB.READ ( i.text, d, offset, buffer1);
       buffer2 := utl_raw.cast_to_varchar2(buffer1);
       DBMS_LOB.WRITEAPPEND(v_clob, d, buffer2);
       offset := offset + c;
   end loop;
   offset := 1;
   update test set plain_text = v_clob where rowid=i.rowid;
  end loop;
  commit;
end;
/

Procedure created.
 
Now execute the procedure to perform the conversion -

SQL> exec blob_to_clob();
 
PL/SQL procedure successfully completed.

You can now check the data in CLOB column as follows-

SQL> set pages 1000
SQL> set long 100000
SQL> select id, plain_text from test;


0
 
MohanKNairCommented:
It cannot be done using a single SQL update statement. You will have to code using PL/SQL. How do you want to convert CLOB to BLOB?
0
 
SujithData ArchitectCommented:
Hi Sandeep,
10g provides functions like DBMS_LOB.CONVERTTOBLOB to do what u are looking for. In 8i there are no direct ways of doing it. Using a single update to convert clob to blob is not possible.
Meanwhile, you can write a procedure to get it updated.
Find the code below that mimics converttoblob functionality.

create or replace procedure test_proc(p_id in number)
as
 a clob;
 b blob;
 c varchar2(1);
 x raw(20);
begin
 select var1 into a
 from test
 where id = p_id;

 update test
 set var2 = empty_blob()
 where id = p_id
 returning var2 into b;

 loop
  exit when length(a) = 0;
  c := substr(a,1,1);
  a := substr(a,2);
  select hextoraw(ltrim(to_char(ascii(c),'XXX'))) into x
  from dual;
  dbms_lob.writeappend(b, length(c), x);
 end loop;

 update test
 set var2 = b
 where id = p_id;

end;
/

To crosscheck your data you can use this code.
declare
 c clob;
 b blob;
 l_amt number;
  src_offset INTEGER := 1;
  dest_offset INTEGER := 1;
lang_ctx INTEGER := dbms_lob.default_lang_ctx;

 l_csid number;
 l_lang number;
 l_warn number;
begin
 select var2 into b
 from test
 where id = 22;

 dbms_lob.createtemporary(c, true);
 dbms_lob.converttoclob(c, b, DBMS_LOB.LOBMAXSIZE , src_offset , dest_offset , DBMS_LOB.default_csid, lang_ctx , l_warn);
 
 dbms_output.put_line(c);
end;
/

HTH,

Rgds,
Sujith.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SujithData ArchitectCommented:
Yet I am not clear why do u want to store this info in a BLOB column, wont be of much use anyways.
0
 
SujithData ArchitectCommented:
I have made a fw changed to my old procedure. A better way of doing the same.

create or replace procedure test_proc(p_id in number)
as
 a clob;
 b blob;
 c varchar2(1000);
 x raw(1000);
 l number;
begin
 select var1 into a
 from test
 where id = p_id;

 update test
 set var2 = empty_blob()
 where id = p_id
 returning var2 into b;

 loop
  exit when length(a) = 0;
  c := substr(a,1,1000);
  a := substr(a,1001);
  x := utl_raw.cast_to_raw(c) ;
  dbms_lob.writeappend(b, utl_raw.length(x), x);
 end loop;

 update test
 set var2 = b
 where id = p_id;

end;
/
0
 
MohanKNairCommented:
---   Create a function which converts a CLOB to BLOB.

create or replace FUNCTION CLOB2BLOB (p_clob in clob) return BLOB is
v_len number;
v_offset number := 1;
v_str varchar2(4000);
v_blob BLOB;
v_buffer PLS_INTEGER := 4000;
begin
DBMS_LOB.CREATETEMPORARY(v_blob, TRUE);
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(p_clob) / v_buffer)
LOOP
v_str := dbms_lob.substr(p_clob, 12000, v_offset);
v_len := utl_raw.length ( utl_raw.cast_to_raw( v_str) );
dbms_lob.write( v_blob, v_offset, v_len, utl_raw.cast_to_raw( v_str ) );
v_offset := v_offset + v_len;
END LOOP;
return(v_blob);
END;
/


-- Use the above function in the update statement
update reports set rep_image2=CLOB2BLOB(rep_image);
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.