5 Replies Latest reply: Nov 19, 2012 7:40 AM by Keith Jamieson RSS

    base64 encode

    user12223156
      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
          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
            Keith Jamieson
            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
              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
                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
                  Keith Jamieson
                  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;
                  /