Base64 decode using PL/SQL

Oracle 10g

For those of us that deal with internet based communication, Base64 encode/decode is a must. It is a method for us to move binary data files over a text based communication channel.

In my environment, I receive files over HTTP via the MOD_PLSQL gateway. MOD_PLSQL implements the bare necessities in terms of HTTP communication, so it comes as no surprise that it does not decode uploaded files if those files arrive as GZIP or Base64 encoded. If I think about it, it shouldn't decode them automatically, but at the very least it should record the encoding of the file for later use.

Fortunately, I just happen to know that the 3rd party sends the file encoded using Base64. Only thing is, the PL/SQL package for decoding Base64 strings UTL_ENCODE only decodes RAW datatypes which limits us to 32767 characters. For Base64 strings larger than 32767 characters in size, we needed to role our own solution. So here is my version of decode to support decoding clob to blob.
An important point to note here is the chunk size (declared as l_BASE64_LN_LENGTH in my script) used when reading through the Base64 string. The size must be a multiple of two. 

CREATE OR REPLACE function base64_decode(p_file in clob) return blob is

 l_BASE64_LN_LENGTH constant pls_integer := 48;
 l_result           blob := empty_blob();
 l_pos              number := 1;
 l_amount           number;
 l_sbuf             varchar2(32767);
 l_buffer           raw(32767);
 l_rbuf             raw(32767);
 l_length           pls_integer;

begin

 dbms_lob.createTemporary(l_result, true, dbms_lob.CALL);
 l_length  := dbms_lob.getLength(p_file);
 while l_pos <= l_length loop

  l_amount := l_BASE64_LN_LENGTH;
  dbms_lob.read(p_file, l_amount, l_pos, l_sbuf);

  l_rbuf   := utl_raw.cast_to_raw(l_sbuf);
  l_buffer := utl_encode.base64_decode(l_rbuf);

  dbms_lob.writeappend(l_result, 
                       utl_raw.length(l_buffer), 
                       l_buffer);

  l_pos    := l_pos + l_BASE64_LN_LENGTH;

 end loop;

 return l_result;

end base64_decode;
/