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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.