convert clob to blob

u can use 'dbms_lob' package to transfer data from ur clob column to the blob.

If more details r required than let me know.

It would be great if you send me more detail.
i tried to use DBMS_LOB.copy and DBMS_LOB.append but they did not work.
They work if you want to copy from BLOB to BLOB or CLOB to CLOB not cross.

Oracle does not have a function to do it directly. There are indirect ways, for example,
CLOB ---------------------------> FILE or VARCHAR2 --------------------------------> BLOB.
                extproc/plsql                                                 sql*load/plsql

In the following, you may find the simple example be useful:

SQL> create table c2b (c clob, i int, b blob);
Table created.

SQL> insert into c2b values(hextoraw('A1'),1,null);
1 row created.

SQL> insert into c2b values(hextoraw('B2'),2,null);
1 row created.

SQL> declare
      2  c0 clob;                       -- a temp clob
      3  l0 int;                           -- length of the clob
      4  b0 varchar2(32000);  -- a chunck of buffer; use multiple chuncks, if needed
      5  begin
      6  for k in 1 .. 2 loop
      7    select c into c0 from c2b where i=k;
      8    l0:=dbms_lob.getlength(c0);
      9    dbms_lob.read(c0,l0,1,b0);
     10   --  insert into c2b (b) values (rawtohex(b0));
     11    insert into c2b (b) values (b0);
     12  end loop;
    13  end;
    14  /
    PL/SQL procedure successfully completed.

After my post, I noticed a similar question had been asked few days. I modified the example and posted there ---
https://www.experts-exchange.com/jsp/qShow.jsp?qid=20201726

Just realized that, in the example, it should be an update not an insert

i.e.,

11    insert into c2b (b) values (b0);

should be be changed to

 11    update c2b set b=b0;

waynezhu

Oracle tech retruned to me with the same idea as you have this is the code

declare
a number;
v_blob blob := empty_blob;
cursor c1 is select document_body,bbody, rowid from tmp1 for update ;
amount binary_integer;
j number := 0;
offset integer;
buffer varchar2(32000);
buffer2 raw(32000);
begin
for i in c1 loop
select bbody into v_blob from tmp1 where rowid = i.rowid;
a := dbms_lob.getlength(i.document_body);
dbms_output.put_line(a);
DBMS_LOB.READ ( i.document_body, a, 1, buffer);
buffer2 := utl_raw.cast_to_raw(buffer);
dbms_lob.write(v_blob, a, 1, buffer2);
update tmp1 set bbody = v_blob where rowid=i.rowid;
end loop;
end;

But I get the following error

ORA-21560: argument 2 is null, invalid, or out of range ORA-06512: at
"SYS.DBMS_LOB", line 648 ORA-06512: at line 15

Some of the data, are around 200000 bytes.
What do you think about their solutiuon?
Why am I getting the error?

thanks
Paymon

Paymon,
In PL/SQL, the max buffer size is limited to 32767 (=32K-1). If the length (200000 in your example) is great than that, it has to be read piece by piece. The 3rd argument of dbms_lob.read() is the offset where you position each piece. Basically, you need construct a loop to do it. I have a working example somewhere using the dbms_lob.substr(), but the principle is same. If you need, I will locate it and make some pertinent modification to use the dbms_lob.read(), and post it later.
Thanks and regards,
Wayne
 

I finally got it run with your help and orcale tech support.

Thanks for your help again.

BTW: why do we need to use
buffer2 := utl_raw.cast_to_raw(buffer);?

Paymon