Oracle BLOB to base64 CLOB

Question

Can I convert an oracle BLOB to Base64 CLOB in One go?

like:

CREATE TABLE test
(
image BLOB,
imageBase64 CLOB
);

INSERT INTO test(image)
VALUES (LOAD_FILE('/full/path/to/new/image.jpg'));

UPDATE test SET imageBase64 = UTL_ENCODE.base64_encode(image);

commit;

I know I can add functions/Stored proc to do the work. Performance aspect is very important,so I am asking if there is a way to overcome the 32K limitation by directly pushing the data into a CLOB.

Answer 1

This function got from here should do the job.

CREATE OR REPLACE FUNCTION base64encode(p_blob IN BLOB)
  RETURN CLOB
-- -----------------------------------------------------------------------------------
-- File Name    : http://oracle-base.com/dba/miscellaneous/base64encode.sql
-- Author       : Tim Hall
-- Description  : Encodes a BLOB into a Base64 CLOB.
-- Last Modified: 09/11/2011
-- -----------------------------------------------------------------------------------
IS
  l_clob CLOB;
  l_step PLS_INTEGER := 12000; -- make sure you set a multiple of 3 not higher than 24573
BEGIN
  FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_blob) - 1 )/l_step) LOOP
    l_clob := l_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_blob, l_step, i * l_step + 1)));
  END LOOP;
  RETURN l_clob;
END;
/

Then the update can look like

UPDATE test SET imageBase64 = base64encode(image);

Note that maybe the function should be optimized with the function DBMS_LOB.APPEND instead of that concatenation operator. Try that if you have performance problems.

Answer 2

Provided that stored procs would despite be a viable alternative for you, here's one possible solution to your problem ...

First, let's make that nice base64encode() function of Tim Hall's into a procedure ...

create or replace procedure base64encode
    ( i_blob                        in blob
    , io_clob                       in out nocopy clob )
is
    l_step                          pls_integer := 22500; -- make sure you set a multiple of 3 not higher than 24573
    l_converted                     varchar2(32767);

    l_buffer_size_approx            pls_integer := 1048576;
    l_buffer                        clob;
begin
    dbms_lob.createtemporary(l_buffer, true, dbms_lob.call);

    for i in 0 .. trunc((dbms_lob.getlength(i_blob) - 1 )/l_step) loop
        l_converted := utl_raw.cast_to_varchar2(utl_encode.base64_encode(dbms_lob.substr(i_blob, l_step, i * l_step + 1)));
        dbms_lob.writeappend(l_buffer, length(l_converted), l_converted);

        if dbms_lob.getlength(l_buffer) >= l_buffer_size_approx then
            dbms_lob.append(io_clob, l_buffer);
            dbms_lob.trim(l_buffer, 0);
        end if;
    end loop;

    dbms_lob.append(io_clob, l_buffer);

    dbms_lob.freetemporary(l_buffer);
end;

The "trick" here is to directly use the persistent LOB locators in calls to procedures/functions. Why "persistent"? Because if you create a function that returns a LOB, then there's a temporary LOB created in background and this means some TEMP disk/memory usage and LOB content copying involved. For large LOBs this may imply a performance hit. In order to satisfy your requirement of making this the most performing possible, you should avoid this TEMP space usage. Hence, for this approach, a stored procedure instead of a function must be used.

Then, of course, the procedure must be fed with persistent LOB locators. You have to do that, again, with a stored procedure, where you e.g. insert an empty LOB (effectively creating a new LOB locator) to a table first, and then supplying that newly created LOB locator to the base64 encoding routine ...

create or replace procedure load_and_encode_image
    ( i_file_name       in varchar2 )
is
    l_input_bfile       bfile := bfilename('DIR_ANYTHING', i_file_name);
    l_image_base64_lob  test.imageBase64%type;
    l_image_raw         test.image%type;
begin
    insert into test(image, imageBase64)
    values (empty_blob(), empty_clob())
    returning image, imageBase64
    into l_image_raw, l_image_base64_lob;

    begin
        dbms_lob.fileopen(l_input_bfile);
        dbms_lob.loadfromfile(
            dest_lob => l_image_raw,
            src_lob => l_input_bfile,
            amount => dbms_lob.getlength(l_input_bfile)
        );
        dbms_lob.fileclose(l_input_bfile);
    exception
        when others then
            if dbms_lob.fileisopen(l_input_bfile) = 1 then
                dbms_lob.fileclose(l_input_bfile);
            end if;
            raise;
    end;

    base64encode(
        i_blob => l_image_raw,
        io_clob => l_image_base64_lob
    );
end;

Note: Of course, if you base64-encode only small files (the actual size depends on your PGA settings, I guess; a question for a DBA, this is), then the function-based approach may be equally performing than this procedure-based one. Base64-encoding a 200MB file on my laptop took 55 seconds with the function+update approach, 14 seconds with the procedure approach. Not exactly a speed demon, so choose what suits your needs.

Note: I believe this procedure-based approach may be further speeded up by reading the file to inmemory chunks in loop, base64-encoding the chunks to another inmemory chunks and appending them both to the target persistent LOBs. That way you should make the workload even easier by avoiding re-reading the full test.image LOB contents by the base64encode() procedure.

Answer 3

I solved this same problem at work by using a Java stored procedure. There is no chunking/contatenation of VARCHAR2s involved in such an approach, since the ability to encode/decode base64 is natively built into Java, simply writing an Oracle function that thinly wraps the Java method works well and is high-performance since as soon as you've executed it a few times, the HotSpot JVM compiles the Java proc into low-level code (high performance just like a C stored function). I'll edit this answer later and add the details about that Java code.

But to step back just one step, question why are you storing this data as both a BLOB and base64 encoded (CLOB)? Is it because you have clients that want to consume the data in the latter format? I'd really prefer to only store the BLOB format. One reason why is that the base64 encoded version can be double the size of the original binary BLOB, so storing them both means possibly 3x the storage.

One solution, the one I implemented at work, to create your own Java stored function base64_encode() that encodes binary --> base64 and then use that function to encode base64 on the fly at query time (it's not expensive). From the application/client side, you would query something like SELECT base64_encode(image) FROM test WHERE ...

If the application code can't be touched (ie COTS application) or if your developers aren't thrilled about using a function, you could abstract this for them (since you are using 11g+) by using a VIRTUAL (computed) column on the table which contains the computed base64_encode(image). It would function like a view, in that it wouldn't physically store the encoded CLOBs, but would generate them at query time. To any client, they would not be able to tell they are not reading a physical column. The other benefit is that if you ever update the jpg (BLOB), the virtual CLOB is immediately and automatically updated. If you ever have to insert/update/delete a huge batch of BLOBs, you'd save 66% of the redo/archivelog volume from not having to process all the CLOBs.

Lastly, for performance, make very sure you are using SecureFile LOBs (both for BLOBs and CLOBs). They really are much faster and better in just about every way.

UPDATE - I found my code, at least the version that uses a Java Stored Procedure to do the opposite (converting a base64 encoded CLOB to its binary BLOB version). It would not be that difficult to write the inverse.

--DROP FUNCTION base64_decode ;
--DROP java source base64;

-- This is a PLSQL java wrapper function
create or replace
FUNCTION base64_decode (
   myclob clob)
   RETURN blob
AS LANGUAGE JAVA
   NAME 'Base64.decode (
            oracle.sql.CLOB) 
            return oracle.sql.BLOB';
/

-- The Java code that base64 decodes a clob and returns a blob.
create or replace and compile java source named base64 as
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import sun.misc.BASE64Decoder;
import oracle.jdbc.driver.*;

public class Base64 {

public static oracle.sql.BLOB decode(oracle.sql.CLOB  myBase64EncodedClob)
  {

    BASE64Decoder base64   = new BASE64Decoder();
    OutputStream  outstrm  = null;
    oracle.sql.BLOB myBlob = null;
    ByteArrayInputStream instrm = null;

    try
    {
      if (!myBase64EncodedClob.equals("Null"))
      {
        Connection conn = new OracleDriver().defaultConnection();
        myBlob = oracle.sql.BLOB.createTemporary(conn, false,oracle.sql.BLOB.DURATION_CALL);
        outstrm = myBlob.getBinaryOutputStream();
        ByteArrayOutputStream byteOutStream = new ByteArrayOutputStream();

        InputStream in = myBase64EncodedClob.getAsciiStream();
        int c;
        while ((c = in.read()) != -1)
        {
          byteOutStream.write((char) c);
        }

        instrm = new ByteArrayInputStream(byteOutStream.toByteArray());

        try  // Input stream to output Stream
        {
          base64.decodeBuffer(instrm, outstrm);
        }
        catch (Exception e)
        {
          e.printStackTrace();
        }

        outstrm.close();
        instrm.close();
        byteOutStream.close();
        in.close();
        conn.close();
      }

    }
    catch (Exception e)
    {
        e.printStackTrace();
    }

    return myBlob;

  }  // Public decode

} // Class Base64
;
/

Answer 4

The easiest way that I found, that works with special characters (in your case you don't have this problem), is using dbms_lob.converttoclob.

Create encapsulated Procedure:

CREATE OR REPLACE FUNCTION blob2clob(blob_i IN BLOB) RETURN CLOB IS
  l_clob         CLOB;
  l_dest_offset  NUMBER := 1;
  l_src_offset   NUMBER := 1;
  l_amount       INTEGER := dbms_lob.lobmaxsize;
  l_clob_csid    NUMBER := nls_charset_id('WE8ISO8859P15'); --dbms_lob.default_csid;
  l_lang_context INTEGER := dbms_lob.default_lang_ctx;
  l_warning      INTEGER;
BEGIN
  ---------------------------
  -- Create Temporary BLOB --
  ---------------------------
  dbms_lob.createtemporary(lob_loc => l_clob,
                           cache   => TRUE);
  --------------------------
  -- Convert CLOB to BLOB --
  --------------------------
  dbms_lob.converttoclob(dest_lob     => l_clob,
                         src_blob     => blob_i,
                         amount       => l_amount,
                         dest_offset  => l_dest_offset,
                         src_offset   => l_src_offset,
                         blob_csid    => l_clob_csid,
                         lang_context => l_lang_context,
                         warning      => l_warning);
  --
  RETURN l_clob;
END blob2clob;

Then you can use:

blob2clob(utl_encode.base64_encode(image))