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
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...)
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
- /aspectratio - keep aspect ratio
- /jpgq=90 - drop quality to 90% of original
- /resample - for better quality
<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:
Create a script that looks like this:
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
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
l_amount binary_integer := 32000;
l_pos integer := 1;
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;
when others then
-- close the file if something goes wrong.
if utl_file.is_open(l_file) then
To test the procedure, you can run the following as schema user:
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;
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);
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_file_ext varchar2(255) := regexp_substr(p_file_name, '\..*$');
-- get file from apex files
where name = p_file_name;
-- insert record into images table
insert into my_image ( filename
values ( l_name
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;
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)
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"
Options Indexes FollowSymLinks MultiViews ExecCGI
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:
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?