CLOB to BLOB

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?

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.

Yet I am not clear why do u want to store this info in a BLOB column, wont be of much use anyways.