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
<<inner_loop>>
for i in 1 .. ceil( length( l_image_clob ) / chnksz )
loop
utl_file.put_raw( l_file_handle,
utl_raw.cast_to_raw( substr( l_image_clob, (i-1) * chnksz + 1, chnksz )));
utl_file.fflush(l_file_handle);
end loop inner_loop;
utl_file.fclose(l_file_handle);