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

PhilMan2

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

Anton Scheffer

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;

Etbin

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

Regards

Etbin

PhilMan2

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

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

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

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

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

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;

PhilMan2

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

Then please mark this questions as answered.

PhilMan2

Hi,

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