Encoding BLOBs into Base64

I was asked recently whether there was any way to convert a BLOB into Base64 in PL/SQL easily – it’s possible in Java, but the resulting string may well be more than 32k which seems to be a limit in what can be returned.

Digging out the trusty Oracle Built Ins guide, there is a UTL_ENCODE package which can do this, but only up to 32k it seems, because it only handles a RAW datatype.  So, the way round the limitation would be to strip out the first <n> characters in the BLOB, convert them to a raw, then encode them, then put them into a new CLOB, which can handle the greater than 32k limit.

Putting together a few calls to UTL_ENCODE, DBMS_LOB and UTL_RAW (since we need to convert the RAW back into a VARCHAR2 to be able to append it to a CLOB), I came up with the following:

CREATE OR REPLACE PROCEDURE encode_to_base64 ( p_blob IN  BLOB
                                             , p_clob OUT CLOB ) AS
  l_raw          RAW(32767);
  l_length       PLS_INTEGER;
  l_loops        PLS_INTEGER;
  l_pos          PLS_INTEGER;
  l_encoded_raw  RAW(32767);
  l_encoded_vc2  VARCHAR2(32767);
  l_size         PLS_INTEGER;

BEGIN

  l_size := 12288;

  l_length := DBMS_LOB.getLength(p_blob);

  IF l_length > l_size THEN
    l_loops := ROUND((l_length/l_size)+0.5);
  ELSE
    l_loops := 1;
  END IF;

  l_pos := 1;

  FOR i IN 1..l_loops LOOP
    DBMS_LOB.READ(p_blob, l_size, l_pos, l_raw);
    l_encoded_raw := UTL_ENCODE.base64_encode(l_raw);
    l_encoded_vc2 := UTL_RAW.cast_to_varchar2(l_encoded_raw);
    DBMS_LOB.writeAppend ( p_clob, LENGTH(l_encoded_vc2), l_encoded_vc2);
    l_pos := l_pos + l_size;
  END LOOP;

  DBMS_LOB.writeAppend(p_clob,1,CHR(10));

END encode_to_base64;
/

That should do it – hope it’s useful!

1 thought on “Encoding BLOBs into Base64

  1. Hi
    This procedure seems like I can use for printing employee photos that are stored as BLOB objects to to XML Publisher output (.pdf).
    I am fairly new to Oracle PL SQL Procedures. If I have a table as following, how do I use the above procedure to convert employee photos in to CLOB? Any suggestions would really help me.

    EMPLOYEE_PHOTO
    —————
    EMPLID Varchar2(11)
    PSIMAGEVER Integer
    EMPLOYEE_PHOTO BLOB

Comments are closed.

By continuing to use the site, you agree to the use of cookies. more information

In common with almost all professionally run websites, this website logs the IP address of each visitor in order to keep it running reliably. This is also essential for protecting the website and its visitors from malicious attacks, including infection with malware.

This website provides information as a service to visitors such as yourself, and to do this reliably and efficiently, it sometimes places small amounts of information on your computer or device (e.g. mobile phone). This includes small files known as cookies. The cookies stored by this website cannot be used to identify you personally.

We use cookies to understand what pages and information visitors find useful, and to detect problems such as broken links, or pages which are taking a long time to load.

We sometimes use cookies to remember a choice you make on one page, when you have moved to another page if that information can be used to make the website work better. For example:
- avoiding the need to ask for the same information several times during a session (e.g. when filling in forms), or
- remembering that you have logged in, so that you don’t have to re-enter your username and password on every page.

You can prevent the setting of cookies by adjusting the settings on your browser (see your browser Help for how to do this). Be aware that disabling cookies will affect the functionality of this and many other websites that you visit.

Close