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;
it's possible to chunk in pieces multiples of 48 in order to do DECODE64_CLOB?? thanks in advance
ReplyDeletei find a free online base64 encoder and decoder to convert the base64 string.
ReplyDeleteExcelent!! works fine for me... thanks
ReplyDeleteNot working for me, getting badly chunked chars (=,+).
ReplyDeleteCIKICAgIE1ldG9kb1BhZ289IlBVRSIKICAgIEx1Z2FyRXhwZWRpY2lvbg==PSI2NDA0MCIKICAgIFNlcmllPSJBIgogICAgPgoKICAgIDxjZmRpOkVtaXN
Awesome! Excellent... Thanks a lot!
ReplyDeleteThis actually was a very informative and detailed post about SQL and its related aspects.Really looking forward to know more about this.
ReplyDeleteSQL Server Load Soap API