Convert BASE64 to a BLOB in PL/SQL

Question

I have this function

create or replace PACKAGE BODY UOM_UTIL_RENAMED AS
FUNCTION BASE64_TO_BLOB_(P_BASE64_CLOB CLOB)
RETURN BLOB IS
V_BLOB BLOB;
V_CHUNK_SIZE PLS_INTEGER := 24000;
V_CHUNK VARCHAR2(24000);
V_CHUNK CLOB;
BEGIN DBMS_LOB.CREATETEMPORARY(V_BLOB, FALSE, DBMS_LOB.CALL);
FOR V_I IN 0..TRUNC(
  (
    DBMS_LOB.GETLENGTH(P_BASE64_CLOB) -1
  ) / V_CHUNK_SIZE
) LOOP DBMS_LOB.APPEND(
  V_BLOB, 
  TO_BLOB(
    UTL_ENCODE.BASE64_DECODE(
      UTL_RAW.CAST_TO_RAW(
        DBMS_LOB.SUBSTR(
          P_BASE64_CLOB, V_CHUNK_SIZE, V_I * V_CHUNK_SIZE + 1
        )
      )
    )
  )
);
END LOOP;
RETURN V_BLOB;
END BASE64_TO_BLOB_;

But it doesn't work for inputs longer than 24000 even though I set it to take in chunks.

But I think that's not the problem, as the error says the following

*Error report - ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2). Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

Does that mean that there's a problem with this line FUNCTION BASE64_TO_BLOB_(P_BASE64_CLOB CLOB)

Is it a problem if the input parameter is longer than 33000 characters?

Answer 1

This is what I use; see if it helps.

FUNCTION base64decode (p_clob CLOB)
  RETURN BLOB
IS
  l_blob    BLOB;
  l_raw     RAW (32767);
  l_amt     NUMBER := 7700;
  l_offset  NUMBER := 1;
  l_temp    VARCHAR2 (32767);
BEGIN
  BEGIN
     DBMS_LOB.createtemporary (l_blob, FALSE, DBMS_LOB.CALL);

     LOOP
        DBMS_LOB.read (p_clob,
                       l_amt,
                       l_offset,
                       l_temp);
        l_offset := l_offset + l_amt;
        l_raw := UTL_ENCODE.base64_decode (UTL_RAW.cast_to_raw (l_temp));
        DBMS_LOB.append (l_blob, TO_BLOB (l_raw));
     END LOOP;
  EXCEPTION
     WHEN NO_DATA_FOUND
     THEN
        NULL;
  END;

  RETURN l_blob;
END;