Thursday, July 12, 2012

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.