converting base64 image to blob

Question

I am consuming a dhl webservice which is returning me label pdf as response in base64, i am trying to convert using below method but its giving me error.

I am putting short string of base64.

DECLARE
    l_resp   VARCHAR2 (35000)
        := 'JVBERi0xLjQKJfbk/N8KMSAwIG9iago8PAovVHlwZSAvQ2F0YWxvZwovVmVyc2lvbiAvMS40Ci9QYWdlcyAyIDAgUgo+PgplbmRvYmoKMyAwIG9iago8PAovTW9kRGF0ZSAo';


    FUNCTION base64decodeclobtoblob (p_clob VARCHAR2)
        RETURN BLOB
    IS
        l_blob     BLOB;

        l_raw      RAW (32767);

        l_amt      NUMBER := 7700;

        l_offset   NUMBER := 1;

        l_temp     VARCHAR2 (10000 CHAR);
    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;

    bl       BLOB;
BEGIN
    SELECT base64decodeclobtoblob (l_resp) INTO bl FROM DUAL;
END;

Error is : PLS-00172: string literal too long

Answer 1

You are trying to read 7700 bytes from a string that does not have that many characters. Make sure you only read up to the length of the string:

DECLARE
    l_resp CLOB := 'JVBERi0xLjQKJfbk/N8KMSAwIG9iago8PAovVHlwZSAvQ2F0YWxvZwovVmVyc2lvbiAvMS40Ci9QYWdlcyAyIDAgUgo+PgplbmRvYmoKMyAwIG9iago8PAovTW9kRGF0ZSAo';
    bl     BLOB;

    FUNCTION base64decodeclobtoblob (p_clob CLOB) RETURN BLOB
    IS
      l_blob     BLOB;
      l_raw      RAW (32767);
      c_len      NUMBER := LENGTH(p_clob);
      c_max_amt  NUMBER := 10000;
      l_amt      NUMBER;
      l_offset   NUMBER := 1;
      l_temp     VARCHAR2 (10000 CHAR);
    BEGIN
      DBMS_LOB.createtemporary(l_blob, FALSE, DBMS_LOB.CALL);
      LOOP
        EXIT WHEN l_offset >= c_len;
        l_amt := LEAST(c_max_amt, c_len + 1 - l_offset);
        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;
      RETURN l_blob;
    END;
BEGIN
    bl := base64decodeclobtoblob(l_resp);
END;
/