Skip to Main Content

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

PhilMan2Feb 1 2015 — edited Feb 2 2015

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"
  -- Create a file based on the content of l_image_clob
  l_image_filename := 'image_' || p_event_pkey || '_' || i ||
  '.' || l_image_ext;
  l_file_handle := utl_file.fopen(l_dirname , l_image_filename, 'wb');
  -- wb is write byte. This returns file handle
  for i in 1 .. ceil( length( l_image_clob ) / chnksz )
  utl_file.put_raw( l_file_handle,
  utl_raw.cast_to_raw( substr( l_image_clob, (i-1) * chnksz + 1, chnksz )));
  end loop inner_loop;

This post has been answered by Anton Scheffer on Feb 2 2015
Jump to Answer
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Mar 2 2015
Added on Feb 1 2015