FUNCTION clob_to_blob(p_clob IN CLOB) RETURN BLOB IS
v_blob BLOB;
v_offset NUMBER DEFAULT 1;
v_amount NUMBER DEFAULT 4096;
v_offsetwrite NUMBER DEFAULT 1;
v_amountwrite NUMBER;
v_buffer VARCHAR2(4096 CHAR);
BEGIN
dbms_lob.createtemporary(v_blob, TRUE);
Begin
LOOP
dbms_lob.READ(p_clob, v_amount, v_offset, v_buffer);
v_amountwrite := utl_raw.length(utl_raw.cast_to_raw(v_buffer));
dbms_lob.WRITE(v_blob, v_amountwrite, v_offsetwrite, utl_raw.cast_to_raw(v_buffer));
v_offsetwrite := v_offsetwrite + v_amountwrite;
v_offset := v_offset + v_amount;
v_amount := 4096;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
NULL;
End;
RETURN v_blob;
END clob_to_blob;
Hy Thanks for this Function.
ReplyDeleteIt works very WELL!!!!
Thanks alot!. This works for my requiremnt.
ReplyDeletethis is really great.
ReplyDeleteworks for all environment.
Great
ReplyDeleteWorks very well!!
Thank's
This is great and it worked how i wanted...Thanks a lot again.
ReplyDeleteHi Team, Can you please provide the steps how to convert clob data type col to blob data type. I have table with 200 records and having one column with clob data type , now I would like to convert this datatype to blob. Can you someone provide me the steps .
ReplyDeleteThis comment has been removed by the author.
Deleteou could just call this function
Deleteupdate your_Table_name set blob_column_name = clob_to_blob(clob_column_name);
commit;