Forum Stats

  • 3,839,073 Users
  • 2,262,444 Discussions
  • 7,900,845 Comments

Discussions

How to use UTL_FILE to output a JPG image in a CLOB

PhilMan2
PhilMan2 Member Posts: 380 Bronze Badge
edited Feb 2, 2015 8:52PM in SQL & PL/SQL

I'm using Oracle 11.g.  I have an Oracle Apex application which generates automated eMails. In Apex, the user inserts a JPG image into a rich text field. That image is saved into a CLOB field. When it's time to to send the eMails, Apex calls a stored procedure which reads the JPG image and stores it into a local variable called l_image_clob. The procedure sends the embedded image along with the rest of the eMail body to a list of users. (note: this an embedded image and it is not an eMail attachment). All of this is working fine.

Now I'm attempting to save the contents of the JPG image stored in the variable l_image_clob to a JPG file on the server. The server is on a Windows 7 platform.  The following code produces a file, named properly and the size of the file is correct, but it isn't readable by the system. I get the error "this is not a valid bitmap file" when I try to open it with Microsoft Paint. The image data in the clob looks something like: /9j/4AAQSkZJRgABAgEAZABkAAD/4RXaRXhpZgAATU0AKg (a bunch more data) SSSSUpJJJJSkkkklKSSSSU//Z

How to I use utl_file to save the JPG image store in the variable l_image_clob to a valid JPG file?

-- Here's the code which creates the file that is "not a valid bitmap file"<br/>  -- Create a file based on the content of l_image_clob<br/>  l_image_filename := 'image_' || p_event_pkey || '_' || i ||<br/>  '.' || l_image_ext;<br/>  l_file_handle := utl_file.fopen(l_dirname , l_image_filename, 'wb');<br/>  -- wb is write byte. This returns file handle<br/>  <<inner_loop>><br/>  for i in 1 .. ceil( length( l_image_clob ) / chnksz )<br/>  loop<br/>  utl_file.put_raw( l_file_handle, <br/>  utl_raw.cast_to_raw( substr( l_image_clob, (i-1) * chnksz + 1, chnksz )));<br/>  utl_file.fflush(l_file_handle);<br/>  end loop inner_loop; <br/>  utl_file.fclose(l_file_handle);

Tagged:
Sven W.

Best Answer

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Feb 2, 2015 3:48AM Answer ✓

    procedure clobbase642file( p_clob clob, p_dir varchar2, p_filename varchar2 )

    is

      t_buffer varchar2(32767);

      t_pos number := 1;

      t_size number := nls_charset_decl_len( 32764, nls_charset_id( 'char_cs' ) );

      t_len number;

      t_fh utl_file.file_type;

    begin

      t_fh := utl_file.fopen( p_dir, p_filename, 'wb', 32767 );

      t_len := length( p_clob );

      loop

        exit when t_pos > t_len;

        t_buffer := replace( replace( substr( p_clob, t_pos, t_size ), chr(10) ), chr(13) );

        t_pos := t_pos + t_size;

        while t_pos <= t_len and mod( length( t_buffer ), 4 ) > 0

        loop

          t_buffer := t_buffer || replace( replace( substr( p_clob, t_pos, 1 ), chr(10) ), chr(13) );

          t_pos := t_pos + 1;

        end loop;

        utl_file.put_raw( t_fh, utl_encode.base64_decode( utl_raw.cast_to_raw( t_buffer ) ) );

      end loop;

      utl_file.fclose( t_fh );

    end;

    Sven W.PhilMan2

Answers

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Feb 1, 2015 3:46PM

    No direct experience, but I think BLOB should be used instead of CLOB

    Regards

    Etbin

  • PhilMan2
    PhilMan2 Member Posts: 380 Bronze Badge
    edited Feb 1, 2015 4:01PM

    Hi Etbin,

    The CLOB data works fine when I send the image embedded within the eMail body.  Are you saying that the CLOB must be converted to a BLOB in order to be written to file by UTL_FILE?

    Thanks for looking at this.

    Phil

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Feb 1, 2015 4:29PM

    Your clob is probably a base64 encoded image. If you want to store it as a jpg should have to decode it again. And while decoding you can write the parts (raw) direct to file.

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Feb 1, 2015 4:30PM

    No conversion should take place.

    Image data is binary data so it must be stored into BLOB as CLOB is reserved for character data (the contents of CLOB depends on database NLS settings).

    As already said I never worked with images and I don't know how Apex deals with them.

    I'm surprised images stored in CLOB work OK because of version 10g occasional troubles with texts containing our national characters coming from outside being stored in CLOBs.

    Regards

    Etbin

  • PhilMan2
    PhilMan2 Member Posts: 380 Bronze Badge
    edited Feb 1, 2015 5:34PM

    Hello ascheffer.  Thanks for responding.

    The image is indeed base64 encoded.  How do I go about decoding that when it's in a CLOB?

    Thanks.

  • PhilHerring
    PhilHerring Member Posts: 169
    edited Feb 1, 2015 5:47PM

    You need to use UTL_ENCODE.BASE64_DECODE. It's complicated by the fact that it uses raw input and output, and not everybody has experience with that data type. Read the manual and/or google "UTL_ENCODE.BASE64_DECODE example".

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Feb 2, 2015 3:48AM Answer ✓

    procedure clobbase642file( p_clob clob, p_dir varchar2, p_filename varchar2 )

    is

      t_buffer varchar2(32767);

      t_pos number := 1;

      t_size number := nls_charset_decl_len( 32764, nls_charset_id( 'char_cs' ) );

      t_len number;

      t_fh utl_file.file_type;

    begin

      t_fh := utl_file.fopen( p_dir, p_filename, 'wb', 32767 );

      t_len := length( p_clob );

      loop

        exit when t_pos > t_len;

        t_buffer := replace( replace( substr( p_clob, t_pos, t_size ), chr(10) ), chr(13) );

        t_pos := t_pos + t_size;

        while t_pos <= t_len and mod( length( t_buffer ), 4 ) > 0

        loop

          t_buffer := t_buffer || replace( replace( substr( p_clob, t_pos, 1 ), chr(10) ), chr(13) );

          t_pos := t_pos + 1;

        end loop;

        utl_file.put_raw( t_fh, utl_encode.base64_decode( utl_raw.cast_to_raw( t_buffer ) ) );

      end loop;

      utl_file.fclose( t_fh );

    end;

    Sven W.PhilMan2
  • PhilMan2
    PhilMan2 Member Posts: 380 Bronze Badge
    edited Feb 2, 2015 9:58AM

    Hello ascheffer,

    That did it!  When I pass the three variables to this procedure it creates a proper file in the directory.  You're awesome! 

    I changed the first line (the PROCEDURE) statement to the following so that it would compile as a separate procedure.

    create or replace procedure clob_base64_to_file(

      p_clob        in  clob,

      p_dir         in  varchar2,

      p_filename    in  varchar2

      )

    Again, thanks very much for all your help.

    Phil

  • Anton Scheffer
    Anton Scheffer Member Posts: 1,950 Gold Trophy
    edited Feb 2, 2015 4:42PM

    Then please mark this questions as answered.

  • PhilMan2
    PhilMan2 Member Posts: 380 Bronze Badge
    edited Feb 2, 2015 8:52PM

    Hi,

    I just did.  I didn't have that option before.  Naturally, I was looking for it.  Weird.  Thanks again!

This discussion has been closed.