Storing images outside Oracle XE with Apex

Although Oracle XE gives great value for money, 4Gb is not that much in this multimedia age. In this post I will describe how to store, resize and fetch images using Oracle XE and Apex, without having to worry about images eating of the precious 4Gb storage limit. If you don't run XE, you can use Ordimage data type and methods in the database to achieve the same result.

Parts of the solution is OS specific (due to third party image processing applications), and in this case based on a Windows operating system. Adapting the implementation for your favorite nix system should not be that different.

I will try to be structured in my explanation, but I am a developer after all... My pedagogical skills (or lack there of) lead me to explain this from the bottom up, breaking it up in uneven pieces:

• Resize images with IrfanView using CLI
  
• Create OS script for resizing images
• Create PL/SQL procedure to execute OS script from XE
• Create PL/SQL procedure to write blob to OS file system
• Create PL/SQL procedure for custom upload from APEX
• Create Apex page to upload image

You can download the full source code from my demo application.

Pre-requisites
To follow the example, you must have the following installed and working correctly:

• Oracle XE
• Oracle Apex 3.2
• IrfanView v4.1 or later
• Apache HTTP Server running as reverse proxy in front of EPG (if you want to view the images after uploading them...)

Some notes on the configuration: The process is only tested for Oracle Apex 3.2, but I see no reason why it should not work for older versions. You can change from Apache to your favorite web server, but some of the steps in this post will be different. This also applies to IrfanView, any other image processing application with command line interface will probably do the job just as well.

Resizing images with IrfanView
As mentioned earlier, I will use IrfanView in this example, it sports a CLI which is fast, and cover my needs. The CLI is also documented with examples, that helps... I am sure there are alternatives to IrfanView for Linux which has the same features, but I have not looked for any (yet).

The syntax for resizing images are quite straight forward:
<installDir>\i_view32.exe <originalImage> /resize=(x,y) /convert=<outputImage>

• instalDir is full path to where IrfanView was installed
• originalImage is full path and file name of image to be resized
• outputImage is full path and file name of resized image
• /resize=(x,y) - set pixels for resized image

I also add some other switches for good measure:

• /aspectratio - keep aspect ratio
• /jpgq=90 - drop quality to 90% of original
• /resample - for better quality

The command line now becomes:
<installDir>\i_view32.exe <originalImage> /resize=(1024,768) /aspectratio /jpgq=90 /resample /convert=<outputImage>

Try it out on an existing image to make sure it works.

Creating OS script for resizing images
Next up is packing the command lines into a .bat-file. The reason for this is twofold. First, I want to simplify the call from the database which will be executing the script later on. Second, I will resize each image more than once to get both thumbnails and "web friendly" versions of the images. Digital cameras today have an extraordinary amount of pixels, and way to much just for displaying it on a web page.

I will also create a folder structure to store the images in, this is the folder that will be used by Apache to serve the images. I will work with the following directory structure:

• c:\www\ttr\images\orig
• c:\www\ttr\images\thumb
• c:\www\ttr\images\web

Folder "orig" will contain the original images, web contains images resized to a web-friendly 1024x768, and thumbs will contain 150x150 images.

Create a script that looks like this:

set PATH=%PATH%;C:\WINDOWS\system32;
echo Start %date% %time% >> C:\Oracle\OraXE\images\test.txt
C:\Programfiler\IrfanView\i_view32.exe C:\www\ttr\images\orig\%1 /resize=(1024,768) /aspectratio /resample /jpgq=90 /convert=C:\www\ttr\images\web\%1
C:\Programfiler\IrfanView\i_view32.exe C:\www\ttr\images\orig\%1 /resize=(150,150) /aspectratio /resample /jpgq=90 /convert=C:\www\ttr\images\thumb\%1
echo End %date% %time% >> C:\Oracle\OraXE\images\test.txt

and save it as resize.bat in the images folder. Remember to replace the physical path names to your own structure. I have added setting of PATH environment variable to ensure the script can locate any additional files in the system32 folder, and echo Start/End to log how long time the conversion takes.

Place an image in the c:\www\ttr\images\orig folder, and run resize.bat to check if it works.

Execute OS-script from XE
I will cheat! In fact, I will cheat twice while I am at it.

First of all I will be using dbms_scheduler to execute host commands. In order to avoid any hassle setting up the OracleXEClrAgent and user rights, I will create a procedure in SYS schema to create and execute the job. Not using invokers rights here avoids the whole user rights shebang. See how easy it is shooting yourself in the foot?

Second, I will leave the script wide open any kind of host script. As an afterthought, I will grant schema user anything to get it going too! Feel comfortable putting this in production? If you do, go take a cold shower!

Log in as SYS and create a procedure to run arbitrary host scripts (...and such a big gun too...):

create or replace procedure resize_image (
p_script_name     in          varchar2
,  p_image_name      in          varchar2
,  p_directory       in          varchar2
) as
begin
dbms_scheduler.create_job(
   'imgres'
 , job_action=>'C:\WINDOWS\system32\cmd.exe'
 , number_of_arguments=>4
 , job_type=>'executable'
 , enabled=>false);
dbms_scheduler.set_job_argument_value('imgres',1,'/q');
dbms_scheduler.set_job_argument_value('imgres',2,'/c');
dbms_scheduler.set_job_argument_value('imgres',3,p_directory||p_script_name);
dbms_scheduler.set_job_argument_value('imgres',4,p_image_name);
dbms_scheduler.enable('imgres');
end;
/
grant execute on sys.resize_image to <app_schema>
/

To test the procedure, make sure there web and thumb OS-directories are empty, and an image is placed in the orig directory.

Log in as your <app_schema> user and execute the following script in SQL*Plus:

begin
sys.resize_image('imgres.bat', '<myImageFile>', 'C:\www\ttr\images\');
end;
/

If it comes up with a user rights error, try granting "create job", "create external job" and/or "manage scheduler" to your schema user. If it still does not work, check dbms_scheduler_job_log for any error messages. If you are unable to correct your job,search the web, there are plenty of people who has run into the same issue.

When this piece works, the rest is a breeze.

Writing BLOB to OS-file
There a ton of examples on how to do this, see Dr. Tim Hall's ftp-package, or the Extract BLOB Demo of Morgan's Library.
My version is in between those two, but will do the job:

create or replace directory IMAGES as 'C:\www\ttr\images\orig\'
/

create or replace procedure write_to_file (
p_file_name       in          varchar2
,  p_directory       in          varchar2
,  p_content         in          blob
) is
l_file          utl_file.file_type;
l_buffer        raw(32000);
l_amount        binary_integer := 32000;
l_pos           integer := 1;
l_blob          blob;
l_blob_left     number;
l_blob_length   number;
begin
l_blob_length := dbms_lob.getlength(p_content);
l_blob_left := l_blob_length;
-- open the destination file.
l_file := utl_file.fopen(p_directory,p_file_name,'WB', 32760);
-- read chunks of the blob and write them to the file
-- until complete.
-- if small enough for a single write
if l_blob_length < 32760 then
utl_file.put_raw(l_file,p_content);
utl_file.fflush(l_file);
else -- write in pieces
l_pos := 1;
while l_pos < l_blob_length
loop
  dbms_lob.read(p_content,l_amount,l_pos,l_buffer);
  utl_file.put_raw(l_file,l_buffer);
  utl_file.fflush(l_file);
  -- set the start position for the next cut
  l_pos := l_pos + l_amount;
  -- set the end position if less than 32000 bytes
  l_blob_left := l_blob_left - l_amount;
  if l_blob_left < 32000 then
     l_amount := l_blob_left;
  end if;
end loop;
end if;
utl_file.fclose(l_file);
exception
when others then
-- close the file if something goes wrong.
if utl_file.is_open(l_file) then
utl_file.fclose(l_file);
end if;
raise;
end;
/

To test the procedure, you can run the following as schema user:

declare
l_file         blob;
l_content      clob := 'This is soon to be a blob';
l_src_offset   integer := 1;
l_dest_offset  integer := 1;
l_lang_ctx     integer := dbms_lob.default_lang_ctx;
l_warn         integer;
begin
dbms_lob.createtemporary(l_file, false);
dbms_lob.converttoblob(l_file, l_content, dbms_lob.getlength(l_content), l_dest_offset, l_src_offset, 1, l_lang_ctx, l_warn);
write_to_file('testfile.txt', 'IMAGES', l_file);
dbms_lob.freetemporary(l_file);
exception
when others
then
 dbms_lob.freetemporary(l_file);
 raise;
end;
/

After running this, you should see a file called "testfile.txt" in the os-directory where the images will be placed later.

Note to self: this would probably be more elegant using BFILE.

Creating a custom upload procedure
Apex is goodhearted enough to take care of all the tedious bits of code to bring the image from your client into the database. If you want to do something more with it, you must create it yourself. Luckily it is not that hard. There are also some good examples of how to do this out there, including one from Oracle in the official documentation.

Here I will bring the pieces together, the procedure below calls on both write_to_file-procedure and sys.image_resize-procedure after inserting the image in a custom table. My table is called MY_IMAGE, take care to change this and other bits to your implementation.

create or replace procedure store_image (
p_file_name in varchar2
, p_description in varchar2
)
is
l_image_id     my_image.my_image_id%type;
l_file         blob;
l_mime_type    apex_application_files.mime_type%type;
l_name         apex_application_files.name%type;
l_file_ext     varchar2(255) := regexp_substr(p_file_name, '\..*$');
begin
-- get file from apex files
select   name
      , mime_type
      , blob_content
 into   l_name
      , l_mime_type
      , l_file
 from   apex_application_files
where   name = p_file_name;
-- insert record into images table
insert into my_image (   filename
                       , mime_type
                       , description)
 values   (   l_name
            , l_mime_type
            , p_description)
returning my_image_id into l_image_id;
-- insert file to os, use table pk as file name
write_to_file(l_image_id||l_file_ext, 'IMAGES', l_file);    
-- resize image, could check for mime-type here
sys.resize_image('imgres.bat', l_image_id||l_file_ext, 'C:\www\ttr\images\');
-- delete file from apex files when done
delete from   apex_application_files
     where   name = p_file_name;
end store_image;
/

In order to test this, you need to create an Apex page with a file upload form region. In the following example, make sure your Apex application uses the same parsing schema as above, or have been granted appropriate rights to execute them.

• Create a new empty page
• Create a form based on a procedure
• Choose procedure STORE_IMAGE
• Display PXX_FILE_NAME as a Browse item and PXX_DESCRIPTION as textarea
• Next-Next-Create (or something close to it)
  

And you are done.

When you run the page, choose an image, click Submit, three versions of the image should now appear in the three images folders (original, web friendly and thumbnail).

Viewing the images
For this, you need the Apache webserver. First of all, you must edit httpd.conf (AFTER backing it up first, of course!), include the lines:

Alias /ttrimg "C:/www/ttr/images"
<Directory "C:/www/ttr/images">
  Options Indexes FollowSymLinks MultiViews ExecCGI
  AllowOverride All
  Order allow,deny
  Allow from all
</Directory>

somewhere at the bottom of the file. If you use VirtualHost directives, be sure to include it inside the directive.

This is just a suggestion, options and access rights must be adapted to your needs (the Alias above is very public), the same goes for the location of the images directory. You must restart the Apache for the changes to take effect.

The images can now be reached with the following URL construct:

• Original image:
  http://<yourserver>:<port>/ttrimg/orig/<my_image.file_name>
• Web friendly image:
  http://<yourserver>:<port>/ttrimg/web/<my_image.file_name>
• Thumbnail:
  http://<yourserver>:<port>/ttrimg/thumb/<my_image.file_name>

Cool?
Well, isn't it?!? Not breaking native Apex upload functionality, and not eating of the precious 4Gb. Me like :-)

The whole source code including create user, grants, .bat, apex app, etc. can be downloaded from my demo application.

PS:
This was just an example, there are things I would do before moving on:

• Waiting for the image to be resized: Scheduler jobs are by nature asynchronous (unless you use the "use_current_session"-thingy), so in order to view your images immediately after upload, you must code your own "pause"-procedure (and probably check dbms_scheduler_job_log)
• The naming of the OS-files does not say much, there are room for improvement here. This also means my_image-table must be updated accordingly. BFILE again?
  
• Directory structure should be reconsidered if you expect a large amount of images, sub folders can be useful
• Extract and retrieve EXIF information back into the database?