Error when decoding base 64 to blob

Question

I am using the following function to convert a large base64 encoded file(image or voice) into a blob file and store it in the Oracle database (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production).

I am able to store it and retrieve it but the image is getting corrupted. Only a potion of the image is getting retrieved. I tried using small images(11KB size) and it is working fine. But for larger images(88KB to 700KB) only a portion of the image is retrieved.

The problem is with the base-64 decoding. Earlier I was not able to get even the smaller image due to corruption, but when I increased the buffer size, it came fine. Now the buffer size is at its maximum at 32767 as its the maximum for varchar2 and raw.

Can anyone provide a suitable workaround or solution.

function decode_base64(p_clob_in in clob) return blob is
    v_blob blob;
    v_result blob;
    v_offset integer;
    v_buffer_size binary_integer := 32767;     -- 24, 48, 3072
    v_buffer_varchar varchar2(32767);
    v_buffer_raw raw(32767);

  begin

    if p_clob_in is null then
      return null;
    end if;

    dbms_lob.createtemporary(v_blob, true);
    v_offset := 1;

    for i in 1 .. ceil(dbms_lob.getlength(p_clob_in) / v_buffer_size) 
    loop
      dbms_lob.read(p_clob_in, v_buffer_size, v_offset, v_buffer_varchar);
      v_buffer_raw := utl_raw.cast_to_raw(v_buffer_varchar);
      v_buffer_raw := utl_encode.base64_decode(v_buffer_raw);
      dbms_lob.writeappend(v_blob, utl_raw.length(v_buffer_raw), v_buffer_raw);
      v_offset := v_offset + v_buffer_size;
    end loop;

    v_result := v_blob;
    dbms_lob.freetemporary(v_blob);

    return v_result;

  end decode_base64;

The code that i use to call the function and insert the blob into the table is given below...

 PROCEDURE create_notes (
      p_task_id              IN       NUMBER
     ,p_note_title           IN       VARCHAR2
     ,p_note_detail          IN       VARCHAR2
     ,p_attach_name          IN       VARCHAR2
     ,p_attachment           IN       CLOB
     ,p_attach_type          IN       VARCHAR2
     ,x_return_code          OUT      VARCHAR2
     ,x_return_message       OUT      VARCHAR2
   )
IS
  l_blob_data BLOB;
BEGIN
.
.
.
 IF p_attachment IS NOT NULL THEN


            SELECT incident_id INTO l_pk1_value FROM csf_ct_tasks where task_id = p_task_id;

                        l_blob_data :=  xx_utl_base64.decode_base64(p_attachment);
INSERT INTO fnd_lobs
                        (file_id, file_name, file_content_type, upload_date,
                        expiration_date, program_name, program_tag, file_data,
                        LANGUAGE, oracle_charset, file_format
                        )
                        VALUES (l_media_id, p_attach_name,p_attach_type,  -- 'audio/mpeg','application/pdf','image/jpeg'
                        SYSDATE,
                        NULL, 'FNDATTCH', NULL, l_blob_data,               --l_blob_data,EMPTY_BLOB ()
                        'US', 'UTF8', 'binary'
                        )
                        RETURNING file_data
                        INTO x_blob;
COMMIT;
END IF:

Attaching the original picture and its decoded version, below.

Answer 1

I got the below code from net. It worked like a charm. Dont know whats the problem with my old code though.

 FUNCTION base64decode(p_clob CLOB)
         RETURN BLOB

        IS
         l_blob    BLOB;
         l_raw     RAW(32767);
         l_amt     NUMBER := 7700;
         l_offset  NUMBER := 1;
         l_temp    VARCHAR2(32767);
        BEGIN
         BEGIN
           DBMS_LOB.createtemporary (l_blob, FALSE, DBMS_LOB.CALL);
           LOOP
             DBMS_LOB.read(p_clob, l_amt, l_offset, l_temp);
             l_offset := l_offset + l_amt;
             l_raw    := UTL_ENCODE.base64_decode(UTL_RAW.cast_to_raw(l_temp));
             DBMS_LOB.append (l_blob, TO_BLOB(l_raw));
           END LOOP;
         EXCEPTION
           WHEN NO_DATA_FOUND THEN
             NULL;
         END;
         RETURN l_blob;
        END;