This discussion is archived
5 Replies Latest reply: Nov 19, 2012 5:40 AM by KeithJamieson RSS

base64 encode

user12223156 Newbie
Currently Being Moderated
Hi

I want to encode (Base64) the "PNG" image. It is working fine with small size image,When i'm giving the large size "PNG" image getting Error

"ORA-06502: PL/SQL: numeric or value error: raw variable length too long".

DECLARE
p_dir          varchar2(100):='/test/bala_test/';
p_file     varchar2(100):= 'test_img.PNG';
dest_dir     varchar2(100):='/test/bala_test/';
dest_file varchar2(100):='test_image.html';
dest_handle     UTL_FILE.file_type;
p_clob     CLOB;
l_bfile     BFILE;
l_step     PLS_INTEGER := 24573;

BEGIN
dbms_output.put_line('Encoding starts');
l_bfile := BFILENAME('BFILE_DIR', p_file);

dbms_output.put_line('File is going to open');
DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);

FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(l_bfile) - 1 )/l_step) LOOP
dbms_output.put_line('Inside encodeing');
p_clob := p_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(l_bfile, l_step, i * l_step + 1)));
END LOOP;

p_clob:= '<img src="data:image/png;base64,'||p_clob||'" width="32" height="32">';
dest_handle := UTL_FILE.fopen(p_dir, dest_file, 'w', 32767);
UTL_FILE.put(dest_handle, p_clob);
DBMS_LOB.fileclose(l_bfile);

dbms_output.put_line('Encoding ends successfully');
exception
when others then
dbms_output.put_line('Error : '||SQLERRM);
end;
/


Kindly Assist me on this.

Thanks inadvance.

Regards
Balasundaram
  • 1. Re: base64 encode
    Solomon Yakobson Guru
    Currently Being Moderated
    user12223156 wrote:
    When i'm giving the large size "PNG" image getting Error
    Look at your code. it:

    a) splits PNG" image into 24573 chunks.
    b) encodes each chunk using UTL_ENCODE.base64_encode
    c) converts each chunk to VARCHAR2 using UTL_RAW.cast_to_varchar2

    And error occurs in step c). WHy? VARCHAR2 is limited to 4000 bytes. So each encoded chunk must not exceed 4000, otherwise you get ORA-06502: PL/SQL: numeric or value error: raw variable length too long. And that's what happens when "PNG" image is largere. Instead of hardcoding number of chunks, calculate it by dividing "PNG" image size by 4000.

    SY.
  • 2. Re: base64 encode
    KeithJamieson Expert
    Currently Being Moderated
    Change your read length to 19200. That should fix your encoding issue.

    Here is a comment from my routine:

    Base64 encoding encodes every 3 bytes of input data into 4 bytes of output data.
    It uses equal signs to indicate nodata and only at the end of the encoded sequence
    If you use a value that's not divisible by 48 you will still get a legitimate encoding
    as long as it's divisible by 3, but you will get some lines longer than others when you
    append them together.


    Also you will then run into a problem outputting your clob.

    I don't think utl_file can just put a clob as you are trying to do.

    Edited by: Keith Jamieson on Nov 19, 2012 11:18 AM
    added comment


    Nb: My routine uses 480 as a read length, and I have not had a problem with it so far.

    Edited by: Keith Jamieson on Nov 19, 2012 11:22 AM
  • 3. Re: base64 encode
    user12223156 Newbie
    Currently Being Moderated
    Thanks. that RAW length error got resolved. but when im trying to print into the file or console its getting error that ORA-06502: PL/SQL: numeric or value error.
  • 4. Re: base64 encode
    user12223156 Newbie
    Currently Being Moderated
    DECLARE
    p_dir varchar2(100):='test/bala_test/';
    p_file varchar2(100):= 'img1.png';
    p_clob CLOB;
    l_bfile BFILE;
    l_step PLS_INTEGER := 19200;
    dest_file varchar2(100):='test_image.html';
    dest_handle UTL_FILE.file_type;
    BEGIN
    dbms_output.put_line('Encoding starts');
    l_bfile := BFILENAME('BFILE_DIR', p_file);

    dbms_output.put_line('File is going to open');
    DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
    dest_handle := UTL_FILE.fopen(p_dir, dest_file, 'w', 32767);
    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(l_bfile) - 1 )/l_step) LOOP
    dbms_output.put_line('Inside encodeing :'||i);
    p_clob := p_clob||UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(l_bfile, l_step, i * l_step + 1)));
    END LOOP;
    dbms_output.put_line('Base64 encoded');
    p_clob:= '<img src="data:image/png;base64,'||p_clob||'" width="32" height="32">';
    dbms_output.put_line('Assignment completed');
    UTL_FILE.put(dest_handle, p_clob);
    DBMS_LOB.fileclose(l_bfile);
    UTL_FILE.fclose(dest_handle);
    exception
    when others then
    dbms_output.put_line('Error : '||SQLERRM);
    end;
    /


    Dbms_output

    Encoding starts
    File is going to open
    Inside encodeing :0
    Inside encodeing :1
    Inside encodeing :2
    Inside encodeing :3
    Base64 encoded
    Assignment completed
    Error : ORA-06502: PL/SQL: numeric or value error
  • 5. Re: base64 encode
    KeithJamieson Expert
    Currently Being Moderated
    Yep, thats what I told you.

    I have modified your code slightly.
    This definitely produces your output.

    Note that you were not using an oracle_directory for p_dir.

    You should have said create or replace directory p_dir as 'your_location';

    Instead you had p_dir varchar2(200) := 'your location';

    The problem with the use of utl_file.put is you have to output a byte at a time. You were trying to output an entire clob, which is why it fell over.

    Note: I also removed your exception handler as this just masks where the error is.
    DECLARE
    p_file varchar2(100):= 'test_img.png';
    p_clob CLOB;
    l_bfile BFILE;
    l_step PLS_INTEGER := 19200;
    dest_file varchar2(100):='test_image.html';
    dest_handle UTL_FILE.file_type;
    v_start number := 1;
    BEGIN
    dbms_output.put_line('Encoding starts');
    l_bfile := BFILENAME('BFILE_DIR', p_file);
    dbms_output.put_line('File is going to open');
    DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
    dest_handle := UTL_FILE.fopen('BFILE_DIR', dest_file, 'w', 32767);
    FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(l_bfile) - 1 )/l_step) LOOP
    dbms_output.put_line('Inside encodeing :'||i);
    p_clob := p_clob||UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(l_bfile, l_step, i * l_step + 1)));
    END LOOP;
    dbms_output.put_line('Base64 encoded');
    p_clob:= '<img src="data:image/png;base64,'||p_clob||'" width="32" height="32">';
    dbms_output.put_line('Assignment completed');
    for i in 1 .. dbms_lob.getlength(p_clob)
    loop
    UTL_FILE.put(dest_handle, dbms_lob.substr(p_clob,1,v_start));
    v_start := v_start+1;
    end loop;
    DBMS_LOB.fileclose(l_bfile);
    UTL_FILE.fclose(dest_handle);
    end;
    /

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points