I need to base64 encode a blob to clob. I've done some searching and the same function seems to be suggested:
CREATE OR REPLACE FUNCTION base64encode(p_blob IN BLOB)
RETURN CLOB
-- -----------------------------------------------------------------------------------
-- File Name : https://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;
/
My question is, what determines the maximum size of l_step? I've tried setting it to 24573 and I receive the following error:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long, ORA-06512: at "SYS.UTL_ENCODE"
I tried setting it lower to 23808 and that seemed to work.
From Wikipedia "The ratio of output bytes to input bytes is 4:3 (33% overhead)"
24573 is 75% of 32767, which is the size limit for a VARCHAR2.
It isn't mentioned in the documentation, but observing the behaviour of Oracle 19c, it appears the UTL_ENCODE package line-wraps the Base64 string to a max line length of 64 characters (presumably so that the encoded string can be used directly with MIME).
On a Windows platform, this means that you will have 2 characters (CRLF) inserted for every 64 characters of standard Base64.
Rounding 32767 down to a multiple of 4, we start with 32764 as the maximum output size. Dividing by 66 tells us we have 497 lines of text, of which the last doesn't have a doesn't have a CRLF added - i.e. we need to allow for 992 extra characters inserted. Now our usable length is down to 31772, and 75% of that gives us our actual input limit of 23829, confirmed on Oracle 19c as producing a Base64 output string of length 32764.