Wednesday, October 7, 2009

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.

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
, job_action=>'C:\WINDOWS\system32\cmd.exe'
, number_of_arguments=>4
, job_type=>'executable'
, enabled=>false);
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:

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

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;
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
else -- write in pieces
l_pos := 1;
while l_pos < l_blob_length
-- 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;
when others then
-- close the file if something goes wrong.
if utl_file.is_open(l_file) then
end if;

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

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

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
l_image_id my_image.my_image_id%type;
l_file blob;
l_mime_type apex_application_files.mime_type%type;
l_file_ext varchar2(255) := regexp_substr(p_file_name, '\..*$');
-- 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

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:
  • Web friendly image:
  • Thumbnail:

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.

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?


  1. Thanks for sharing,

    I hope they will not disable external tables in XE after your post. :)

    I think probably you don't need external procedure to scale image, you can use Oracle interMedia.

    Check this link:

    I wrote an application (just for fun) that calculates Mandelbrot Set - it builds and scales image. Everything is done in PL/SQL.

    I could find it if it's interesting.


  2. @Lev

    The lack of intermedia objects (ORDImage in particular) in Oracle XE was the reason behind this post in the first place. ORDImage invokes Java methods for image manipulation, and Java is not a part of Oracle XE.

    If you run Apex on any other database version, ORDImage IS included, and all this is not necessary :-)

    Storing (public) images outside the database is not such a bad idea anyway, in terms of performance. The same goes for images and files included with Apex.

    PS: External tables are probably going to stick around for a while yet, even in XE ;-)

  3. Great article!

    It's too bad that ORDImage (and Java) are missing from XE, but I don't expect to see it in XE 11g either...

    By the way, you did consistently misspell "IrfanView" as "IfranView"... (except in the URLs!) :)

  4. @Morten

    Hah! At least I was consistant :-)

    My bad, hopefully it is corrected now.

    Thank you for the heads up!

  5. Hi,
    Great solution,
    I did very similar one, but I didn't have time to write such a good article.

    Thanks for your time,


  6. @Algis

    Thank you, appreciate it :-)

    This post came as a result of not finding a "copy-paste"-solution myself.

    Hope it will be useful to other XE users as well.

  7. This subject re-posted by OraPedia
    OraPedia (Oracle Encyclopedia) is written collaboratively by largely anonymous internet volunteers who write without pay to collect any information from anywhere about oracle products how to install, how to do, how to solve problems in one place to be encyclopedia for oracle only.

    When you search to find a solution for any problem in Oracle Products in your work and got it.

    When you search to find how to do anything in Oracle Products and got it.

    When you do any workshop for anything related to Oracle Products.

    When you get new information about Oracle Products from anywhere.

    You need to document this information to be as a reference and publish this information to access easily to him again.

    For this purpose OraPedia launched.

    If you want to write on the OraPedia just send your email to orapedia@hotmail and OraPedia will send to you an invitation to be contributor in OraPedia then follow the following instruction.

    Signup for a account. You can also sign in with your existing account if you already have one.
    Accept the invitation.
    After create the account and accept the invitation you can log to OraPedia and add new posts.

    The new post must contain the information source link If you are not the owner of this information and your signature If you are the owner of this information.

    أورابيديا (موسوعة أوراكل) تكتب بشكل تعاوني من قبل متطوعين مجهولين من خلال الإنترنت دون أجر لجمع أي معلومات من أي مكان حول منتجات أوراكل كيفية تثبيت ….، كيفية القيام بـ….، وكيفية حل المشاكل في مكان واحد لتكون الموسوعة لأوراكل فقط.

    عند البحث لإيجاد حل لأية مشكلة في منتجات أوراكل في عملك وحصلت عليه.

    عند البحث للعثور على كيفية القيام بأي شيء في منتجات أوراكل وحصلت عليه.

    عند القيام بأي عمل للأي شيء متعلق منتجات أوراكل.

    عندما تحصل على معلومات جديدة حول منتجات أوراكل من أي مكان.

    فأنت تحتاج إلى توثيق هذه المعلومات لتكون مرجعا وكذلك نشرهذه المعلومات للوصول بسهولة إليها مرة أخرى.

    لهذا الغرض أطلقت أورابيديا.

    إذا كنت تريد أن تكتب على أورابيديا فقط أرسل بريدك الإلكتروني orapedia@hotmail وأورابيديا سوف ترسل لك دعوة لتكون مساهما في أورابيديا ثم اتبع التعليمات التالية:

    1- اشترك للحصول على حساب يمكنك أيضا تسجيل الدخول باستخدام حسابك على القائمة إذا كان لديك بالفعل واحدة.

    2- قبول الدعوة.

    بعد إنشاء الحساب وقبول الدعوة يمكنك تسجيل الدخول إلى أورابيديا وإضافة مقالات جديده.

    يجب أن تحتوي أي مقاله جديدة على رابط مصدرالمعلومات إن كانت غير منسوبه إليك وتوقيعك إن كانت هذه المعلومات منسوبه إليك