Tuesday, September 15, 2009

Oracle: How to convert CLOB to BLOB

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;

8 comments:

  1. Hy Thanks for this Function.

    It works very WELL!!!!

    ReplyDelete
  2. Thanks alot!. This works for my requiremnt.

    ReplyDelete
  3. this is really great.
    works for all environment.

    ReplyDelete
  4. Great

    Works very well!!

    Thank's

    ReplyDelete
  5. This is great and it worked how i wanted...Thanks a lot again.

    ReplyDelete
  6. Hi 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 .

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. ou could just call this function

      update your_Table_name set blob_column_name = clob_to_blob(clob_column_name);
      commit;

      Delete