Coding and decoding base64 in PL/SQL

I recently needed to decode large base64 payloads in Oracle 10g and save the resulting blobs in a table. Unfortunately the built-in package supports only regular types, not lobs. I searched a bit, but while I got a few pointers I found no complete code. I wrote a function solving the specific problem for the customer, but then decided to write a more complete solution at home.

The package contains two functions:

create or replace package utl_base64 is
  function decode_base64(p_clob_in in clob) return blob;

  function encode_base64(p_blob_in in blob) return clob;
end;

Pretty straightforward. The implementation is a bit more involved:

create or replace package body utl_base64 is
  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 := 48;
    v_buffer_varchar varchar2(48);
    v_buffer_raw raw(48);
  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;

  function encode_base64(p_blob_in in blob) return clob is
    v_clob clob;
    v_result clob;
    v_offset integer;
    v_chunk_size binary_integer := (48 / 4) * 3;
    v_buffer_varchar varchar2(48);
    v_buffer_raw raw(48);
  begin
    if p_blob_in is null then
      return null;
    end if;
    dbms_lob.createtemporary(v_clob, true);
    v_offset := 1;
    for i in 1 .. ceil(dbms_lob.getlength(p_blob_in) / v_chunk_size) loop
      dbms_lob.read(p_blob_in, v_chunk_size, v_offset, v_buffer_raw);
      v_buffer_raw := utl_encode.base64_encode(v_buffer_raw);
      v_buffer_varchar := utl_raw.cast_to_varchar2(v_buffer_raw);
      dbms_lob.writeappend(v_clob, length(v_buffer_varchar), v_buffer_varchar);
      v_offset := v_offset + v_chunk_size;
    end loop;
    v_result := v_clob;
    dbms_lob.freetemporary(v_clob);
    return v_result;
  end encode_base64;
end;

There are a few important points about this. The functions expect the base64 payload to have no newlines, as that is how it is passed down from BPEL. If there are newlines present the result will most likely be garbage. In addition the buffer size should be less than 64 (the built-in package adds newlines at that point) and must be evenly divided by 4.