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

score 0 · Accepted Answer · answered Aug 21 '23 at 09:32

0

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;
/

answered Aug 21 '23 at 09:32

MT0

143,790
11
59
117

I didn't posted the complete base64 string due to editor character limit. above using the code i am still not able to pass base64 string to clob parameter – Adeel Aslam Aug 21 '23 at 11:42
@AdeelAslam If the error is on line 2 then use `l_resp CLOB := EMPTY_CLOB() || 'first 4000 characters' || 'second 4000 characters' || '... repeat for blocks of 4000 characters';` as a string literal is limited to the length of a `VARCHAR2` (which is 4000 characters in SQL). – MT0 Aug 21 '23 at 12:03
I understand , but the question is how we will pass a string more than 32k to the function as clob while function is accepting clob as parameter , – Adeel Aslam Aug 21 '23 at 12:38
@AdeelAslam As I stated: `l_resp CLOB := EMPTY_CLOB() || 'first 4000 characters' || 'second 4000 characters' || '... repeat for blocks of 4000 characters' || 'eighth 4000 characters' || 'ninth 4000 characters';` will give you more than 32k characters. Alternatively, pass the CLOB as a bind variable or load the data into a CLOB from a file; you are only having this issue as you are trying to create a CLOB with string literals (which are limited to 4000 characters per literal). – MT0 Aug 21 '23 at 12:50

converting base64 image to blob

1 Answers1