CODEWITHSUNDEEP
oracle-databaseplsql
sb89
sb89

Reputation: 393

base64 encode clob to blob

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.

Upvotes: 0

Views: 2634

Answers (2)

Gary Myers
Gary Myers

Reputation: 35401

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.

Upvotes: 2

tbone
tbone

Reputation: 15493

Try:

FUNCTION base64encode(p_blob IN BLOB) RETURN CLOB
    IS
      l_clob CLOB;
      l_step PLS_INTEGER := 1998;
    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 base64encode;

Upvotes: 0

Related Questions