Converting to base-64 CLOB in PL/SQL

Recently I have been working on a project to send and retrieve files from a Microsoft SharePoint site using it's web service. The file contents are base-64 encoded and transmitted in a SOAP envelope.

Problem:
Encode a clob to base-64.

Trouble:
Oracle provides a utility package (UTL_ENCODE) that will encode VARCHAR2s to base-64, so we must chunk up the clob and convert it piece at a time. What size chunk turns out to be the problem.

Confusing:
The data density between ascii and base64 is different, meaning it takes more bits to store base64 as ascii then ascii as ascii. In PL/SQL, all base64 transformations take place in the RAW datatype which can only be an even number of characters (two characters represent a byte).

What this means:
If we take a chunk of data and convert it to base64 and the result doesn't fit perfectly into the RAW, the data will be padded with zeros. Not a problem if there is only one chunk, but if you concatenate several of these base64 chunks, you will have extra zeros thrown in seemingly randomly.

How I fixed this:
I tried a handful of chunk-sizes before I made the computer figure it out for me. I took a clob that could be converted in one chunk (less than the size of a VARCHAR2(4000)) and saved the result so that I had initial clob and my desired output. Then I wrote my loop the would chunk and convert the initial clob given a chuck-size. I stuck that in another loop that would test every chunk-size between 1 and half the clob plus 1. The procedure would print out whether the chunk size worked. Turns out 48, 96, 144, 192, ... showed up as 'Success'. Multiples of 48, I would not have guessed.

So here is the function, pass it a clob and you shall be returned a clob in base64.
FUNCTION ENCODE64_CLOB(IN_CLOB CLOB) RETURN CLOB IS
       --The chunk size must be a multiple of 48
       CHUNKSIZE INTEGER := 576;
       PLACE   INTEGER := 1;
       FILE_SIZE INTEGER;
       TEMP_CHUNK VARCHAR(4000);
       OUT_CLOB  CLOB;
BEGIN
       FILE_SIZE := LENGTH(IN_CLOB);
       WHILE (PLACE <= FILE_SIZE)
       LOOP
              TEMP_CHUNK := SUBSTR(IN_CLOB, PLACE, CHUNKSIZE);
              OUT_CLOB := OUT_CLOB 
              || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(TEMP_CHUNK)));
              PLACE := PLACE + CHUNKSIZE;
       END LOOP;
       RETURN OUT_CLOB;
END;

6 comments:

  1. it's possible to chunk in pieces multiples of 48 in order to do DECODE64_CLOB?? thanks in advance

    ReplyDelete
  2. Excelent!! works fine for me... thanks

    ReplyDelete
  3. Not working for me, getting badly chunked chars (=,+).
    CIKICAgIE1ldG9kb1BhZ289IlBVRSIKICAgIEx1Z2FyRXhwZWRpY2lvbg==PSI2NDA0MCIKICAgIFNlcmllPSJBIgogICAgPgoKICAgIDxjZmRpOkVtaXN

    ReplyDelete
  4. Awesome! Excellent... Thanks a lot!

    ReplyDelete
  5. This actually was a very informative and detailed post about SQL and its related aspects.Really looking forward to know more about this.

    SQL Server Load Soap API

    ReplyDelete