Home > Database, Oracle > Coding and decoding base64 in PL/SQL

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.

Advertisements
Categories: Database, Oracle
  1. Sándor Hatvani
    2010-07-12 at 11:30

    Dear Eric,

    I try to use your Base64 encoder/decoder package to decode an image, then encode it.
    After decoding and encoding I compare the contents of the two CLOBs and those never match. Please, give me some instructions.

    Thank you very much.

    Sanyi

    • 2010-07-12 at 14:16

      Well, there are two pitfalls here. The first is the requirement of the base64 payload to have no newlines, the second that there are actually several different base64 standards.

      Most likely the newlines are to blame here. Inspect the original clob. SQL Developer is free and can display clobs as text. You can also use dbms_lob.substr(yourclob, 1000) and check the first 1000 characters. If the data has newlines they must be removed.

      Just to make sure things work as they should, try to go the other way. Encode something and decode it and see if the processed content matches the original. It should.

      Good luck!

  2. Sándor Hatvani
    2010-07-13 at 13:08

    Ahh!!! Thank you very much again, and thank you for your code, and your help.

  3. Raul Perez
    2012-08-09 at 15:43

    Hello!
    could help me please?
    Through a WebService send me a picture, I could load the WebService I can not do is decode it.
    The image is base64 encoded and sent as a String and I am trying to do is to decode and display it on an item image.
    I just want to show I do not want to keep it, and do not know how to decode base64 then display it on an item image.

    I am working with Oracle Forms 10g pl / sql

    My email is: razape12@yahoo.com

    Thank you very much for your attention!

    • 2012-08-10 at 05:56

      Unfortunately I have no experience whatsoever with Oracle Forms. You should be able to use my code for decoding base64 into a blob (watch out for newlines in the encoded data, though – see previous comments!) and based on a quick search it should be possible to display the blob directly using Forms. Don’t ask me how, though, I found no example code.

      Alternatively you can create a table containing the blob and display it using Forms. That may be easier and you can use the table temporarily – insert the data when it is needed, then schedule a delete job that gets rid of all old images on a daily basis, for example.

      Good luck!

  4. 2012-10-10 at 01:13

    I came across the same issue with the webservice returning an encoded Base64 image and had to display that in Crystal Report. I have searched online but was unable to find a way to decode the image in Crystal.
    My solution was to decode the string and store it as a blob in a temporary table.
    Oracle has a built in function to decode Base64 string (Reference: http://psoug.org/reference/utl_encode.html)
    Example:
    UTL_ENCODE.BASE64_DECODE(rawtohex(‘xxxxxx’))
    Hope this helps.

    • 2012-10-10 at 18:32

      Unfortunately the built-in function you are using is limited to fairly small objects. The RAW type is limited to 2000 bytes in 11g and in PL/SQL to 32k. My approach handles much larger objects. Still, if it solves your problem it is better not having to support custom code!

  5. 2013-05-16 at 13:34

    finaly found something that works, thank you

    • SweetLime
      2013-12-03 at 12:24

      Hello,

      Would you please share your code?

      I need to send an image stored in longrow field inside Oracle database
      through web-service?
      Thank you…

      • 2013-12-03 at 20:57

        I already shared my code. Unfortunately the code for blob may not work for longrow, it is an ancient data type. Also I haven’t shared the web service code, as it is not in the database and not in a very reusable format (a SOA Suite integration). Good luck, though!

  6. Brett M
    2014-07-31 at 02:46

    Your code works perfectly and was just what i needed, thanks

  7. Paul
    2017-01-20 at 17:51

    Hi Erik! The package works! Thank you for save me.

  8. 2017-03-18 at 22:03

    Thank you Erik, that really helped and saved me lot of time
    I need t upload a file to a webservice using Base64 encoding. I will use your code and update you my feedback.

  9. Mozo
    2017-04-14 at 20:29

    You just saved my arse

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: