How convert CLOB to BLOB in Oracle?

Question

Firstly I converted a BLOB of an image to CLOB, and then converted that CLOB back to BLOB. I cannot see the reconverted image. How can i solve this problem?

blob_to_clob function:

CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB AS
  v_clob CLOB;
  v_varchar VARCHAR2(32767);
  v_start PLS_INTEGER := 1;
  v_buffer PLS_INTEGER := 32767;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
  FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
  LOOP
    v_varchar := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start));
    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar);
    v_start := v_start + v_buffer;
  END LOOP;
  RETURN v_clob;
END blob_to_clob;

clob_to_blob function:

CREATE OR REPLACE FUNCTION clob_to_blob2(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 (lob_loc => p_clob,
        amount  => v_amount,
        offset  => v_offset,
        buffer  => v_buffer);

      v_amountwrite := utl_raw.length (r => utl_raw.cast_to_raw(c => v_buffer));

      dbms_lob.WRITE (lob_loc => v_blob,
        amount  => v_amountwrite,
        offset  => v_offsetwrite,
        buffer  => 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_blob2;

How can you convert a BLOB to a CLOB? Especially in case of UTF-8 you may have many invalid "characters" which would be deleted. Are talking about [Base64](https://en.wikipedia.org/wiki/Base64) encoding? — Wernfried Domscheit, Nov 10 '16 at 11:37
You can't do this any more than you can put toast back in the toaster and press "untoast". You lost information in the first conversion that you can't put back with the second. — , Nov 10 '16 at 12:59
[Related question here](http://stackoverflow.com/q/42002816/521799) — Lukas Eder, Feb 02 '17 at 12:55

score 10 · Answer 1 · answered Apr 26 '17 at 17:26

Code like this will perform minimal recoding:

create or replace function clob2blob(AClob CLOB) return BLOB is
  Result BLOB;
  o1 integer;
  o2 integer;
  c integer;
  w integer;
begin
  o1 := 1;
  o2 := 1;
  c := 0;
  w := 0;
  DBMS_LOB.CreateTemporary(Result, true);
  DBMS_LOB.ConvertToBlob(Result, AClob, length(AClob), o1, o2, 0, c, w);
  return(Result);
end clob2blob;
/

But CLOB can not properly contain all Image data without any encoding like Base64

score 3 · Answer 2 · answered Oct 15 '20 at 23:26

You can use:

CREATE FUNCTION clob_to_blob(
  value            IN CLOB,
  charset_id       IN INTEGER DEFAULT DBMS_LOB.DEFAULT_CSID,
  error_on_warning IN NUMBER  DEFAULT 0
) RETURN BLOB
IS
  result       BLOB;
  dest_offset  INTEGER := 1;
  src_offset   INTEGER := 1;
  lang_context INTEGER := DBMS_LOB.DEFAULT_LANG_CTX;
  warning      INTEGER;
  warning_msg  VARCHAR2(50);
BEGIN
  DBMS_LOB.CreateTemporary(
    lob_loc => result,
    cache   => TRUE
  );

  DBMS_LOB.CONVERTTOBLOB(
    dest_lob     => result,
    src_clob     => value,
    amount       => LENGTH( value ),
    dest_offset  => dest_offset,
    src_offset   => src_offset,
    blob_csid    => charset_id,
    lang_context => lang_context,
    warning      => warning
  );
  
  IF warning != DBMS_LOB.NO_WARNING THEN
    IF warning = DBMS_LOB.WARN_INCONVERTIBLE_CHAR THEN
      warning_msg := 'Warning: Inconvertible character.';
    ELSE
      warning_msg := 'Warning: (' || warning || ') during CLOB conversion.';
    END IF;
    
    IF error_on_warning = 0 THEN
      DBMS_OUTPUT.PUT_LINE( warning_msg );
    ELSE
      RAISE_APPLICATION_ERROR(
        -20567, -- random value between -20000 and -20999
        warning_msg
      );
    END IF;
  END IF;

  RETURN result;
END clob_to_blob;
/

How convert CLOB to BLOB in Oracle?

2 Answers2

Linked