Thursday, October 15, 2009

Oracle Apex, Dead on Arrival?

Warning: Rant follows! The views and opinions expressed here are explicitly my own. Subjective, prejudiced and factless content ahead, best consumed with a pinch of salt (and some Tequila if you are in the mood).

Here in Norway, the Oracle community is just starting to open its eyes to Apex. I have been lobbying Apex internally in my company for quite some time now. Given the latest releases, this should have made my job easier (and the coming version 4.0 even more so). Apex has matured in many ways, but most noticeably in terms of functionality and community adoption. So what is driving me up the wall?

The long, long time ago
The major part of the customers I have worked with, started their relationship with Oracle by buying an Oracle database. To a greater or lesser degree, most have expanded their use of Oracle products. Having worked most of my time with rich back office applications in various domains and a number of development tools, I have witnessed the investments made by customers into their applications (both in terms of time and money).

Changing client technologies combined with a consistently performant database in the cellar, has made some pretty impressive database applications. Whole business processes have been modeled, implemented and evolved (and continue to evolve) in the database for quite some time now, and the companies have gained in-house database competence as a natural result.

Enter APEX
Along comes Apex, a PL/SQL based RAD tool , which actually seems to live up to the "RAD"-label and has the ability to flip code around to satisfy the most exotic  business needs. At the same time also embracing both KISS and the fat database paradigm.

In particular, companies heavily invested in database applications, often in combination with Oracle Forms clients, should see the benefits of Apex. Reuse of code, reuse of competence, and getting their pennies worth of already invested time and money. So what is stopping them?

The Case and the Choice
Based on one need or another, the business wants to expand their existing applications; what client tools are available in the Oracle sphere? There are several options, but most prominent (for the time being, anyway) is ADF and Apex. What to choose depends on a number of things, but I believe the most important in this phase is not to apply your silver bullet “just because”.

This is a topic in itself, so I will short circuit the discussion, and narrow it down to this:
A company wants to implement some back office system based on an existing database application. The application is of "medium" complexity, consisting of registration forms, case evaluation support, reporting and some integrating with other systems. About 10 concurrent users. The IT department has most of its skills in PL/SQL and SQL.

You cannot build a system based on that information, I agree. You cannot make a valid recommendation on a single tool based on that information, I agree. But this is the same amount of information available to Oracle sales representatives as well, and (in Norway at least) the answer is ADF nine out of ten times (actually, I have never heard Apex recommended, but I am giving the benefit of the doubt here).

What do they know that I do not?
How can they consistently answer ADF? Is Apex going to die soon? If I were a customer, I would shy away from Apex hearing this from Oracle itself. Heck, I might even shy away from Apex, I have to make a living too.

In many cases, I am convinced Apex is not just a viable choice, but also the best choice for the customer. But how can I justify that to the customer that Apex is the way to go, when the software vendor actually says something else? If I was a customer, I would be reluctant to use Apex, to say the least.

The road ahead
I do not know where to take it from here, hence the rant. That is the long and the short of it.

Any comments or suggestions would be greatly appreciated.

Monday, October 12, 2009

Protecting Apache Directories Using Apex Authentication Cookie

Regarding my previous post on how to place your images outside Oracle XE, you might want to ensure that only logged in users have access to the image folders. The technique described here applies for Oracle Apex applications running behind an Apache, there are probably similar configurations available for other web servers. It is tested for Apache 2.2, but will most likely work for versions down to 1.3.

There is security, and then there is Security with a big, fat, capital S. This falls in the category below the big S. I do not know how to circumvent this simple trick to protect your folders, but there are probably those that can. If you just want to keep out the general public from accessing content from your web server, this is a very simple, unobtrusive way of doing it.

Configure Cookie Attributes in Apex
When you log on to an application in Apex with native (and unedited) Apex authentication scheme, you will get a cookie named something like this:
It contains some mystic id. The attributes (not value) of this session cookie can easely be changed for the Apex application.

In the builder
  • Go to Shared Components for your application
  • Go to Authentication Schemes
  • Click your current Authentication Scheme
  • Scroll down to Cookie Attributes
  • Fill in the Cookie Name and Cookie Path values (see example below)

In my example, I named my cookie TTR_IMAGES, and the Path value ensures that the cookie is valid through my entire domain.

Configure Directory in Apache
This means getting your hands dirty with mod_rewrite. In my application I have an alias called "ttrimg" that maps to a physical directory where my image files are stored. Since I only want this directory open for users with valid Apex sessions, I include the rewrite rule in the Directory directive (as opposed to under VirtualHost). In my simple world, I imagine it must be better to evaluate the condition for this directory only, and not every request. Then again, my knowledge of Apache is a bit limited to say the least (there was some reference to re-injecting URL for server processing when substituting).

To achieve redirect based on TTR_IMAGES-cookie, include the following in httpd.conf (remember to back it up first!):
Alias /ttrimg "C:/www/ttr/images"
  <Directory "C:/www/ttr/images">
     ## Allow access if Apex session cookie is set and value not like -1
     RewriteEngine On
     RewriteCond %{HTTP_cookie} !TTR_IMAGES=([a-zA-Z0-9]{1})
     RewriteRule /(.*) /403.html [R=403,L] 

     Options Indexes FollowSymLinks MultiViews ExecCGI
     AllowOverride All
     Order allow,deny
     Allow from all
Some explanation is in order (well, at least I do try! :-)):
  • You must explicitly set RewriteEngine On for your Directory directive
  • %{HTTP_cookie} contains all cookies for your domain/path
  • TTR_IMAGES=([a-zA-Z0-9]{1} means that cookie TTR_IMAGES must have value starting with a letter or digit. When you log out of the Apex application the cookie gets value -1.
  • The exclamation mark in front of the last expression negates the result, meaning if it does not match TTR_IMAGES=([a-zA-Z0-9]{1}, the rewrite rule will be applied
RewriteBase is not necessary with Apache 2.2.

For my particular need; secure enough. Only you know your own needs (well, hopefully...).

Unencrypted cookies on an unsecured channel, can be prone to any number of things. Let me throw this at you: cookie hijacking (packet sniffing), cross site scripting cookie theft (send cookie to third party), cookie poisoning (tampering with cookie values)... Feel safer now?

Oh well, as I stated earlier: Safe enough for my particular application.

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?

Sunday, October 4, 2009

Interacting with HTML Editor Items in Oracle Apex

Oracle Apex ships with a rich text editor to enhance text area items; "HTML Editor Standard" and "HTML Editor Minimal". As you may know, this is actually FCKeditor (renamed CKEditor in the last release). This post is a short description on how to interact with FCKeditor API.

About the FCKeditor API
The API is documented on DKSource Docs. In my case, I only needed to paste something into the editor instance, but there are a number of things you can do. Basicly there are methods you can call to get/set values and properties from the editor instance, and there are events you can listen to, and override default behaviour.

My case
During creation and editing of articles, I wanted to be able to add images into the editor by the simplest means possible. And the images (when the article was displayed) should open in Lightbox2.

So, I had:
  • A table of images (ordimage data type)
  • An application process to serve both thumbnails and whole images
  • A report with thumbnails
  • A page item of type HTML Editor Standard
  • Lightbox2
A couple of notes here. The application process was based on an article by Carsten Czarsky on the German Apex community pages. There are more gems hidden among these community pages, but being written in German they don't show up in my web searches very often. Google Translate to the rescue, my German is not good to say the least... Just remember to keep the original page close by when copying code, as the code examples gets translated too :-)

Integrating Lightbox2 into Apex was described by Denes Kubicek (creator of the mother of all Apex sample apps), I have used a slightly modified version by Sébastien Grosjean (auto resize to browser window), but integrating with Apex is the same as the unmodified version.

Communicating with the FCKeditor
First of all I created a small javascript function in the html header. The function was to accept a string to be pasted into the editor.

function f_pasteEditor(htmlSnippet)
var oEditor = FCKeditorAPI.GetInstance('P12_TEXT');
P12_TEXT being the HTML Editor Standard item.

Next I created a report column, and edited the column link properties:
  • Link Text: Paste text
  • Target: URL
  • URL: javascript:f_pasteEditor('SomeTextToBePasted');
It actually works! No great accomplishment perhaps, but a step in the right direction :-)

Setting up the URL
...or The Great Escape. In my case, the pasted text would have a very specific syntax:
<a href="imgprocess" rel="lightbox"><img src="imgprocess" title="image name"></a>
and imgprocess is a call to an application process which looks like this:
Look at the URL in the link target above, what could possibly go wrong here? (or: what went wrong for me!)
  • If the name of the picture contains a single quote character, it will blow up. Single quotes has a meaning in javascript. Replacing it with unicode escape character "\u0027" will solve the issue.
  • Double quotes has a meaning in HTML, putting double quotes into target URL will have an impact. Replacing it with unicode escape character "\u0022" or (if the output is HTML) "&quot;" will solve the issue.
  • Apex substitution variables gets, er.. substituted! My resulting link in FCKeditor was supposed to contain substitution variables, when they are substituted in the report, that is a bit of a problem.
  • The Apex report would simply blank the column containing the URL (hm... this was probably a fault entirely created by me, but helps to understand my solution ;-))
I solved this the quick and dirty way, by extending the javascript function, and hardcoding the link there:

function f_pasteImg(p_item_name, p_image_id, p_alt)
var htmlSnippet = '<a href=\"f?p=&A'+'PP_ID.:&AP'+'P_PAGE_ID.:&S'+'ESSION.:APPLICATION_PROCESS=getImage:::P_IMAGE_ID,P_THUMBNAIL:'+p_image_id+',N\" rel=\"lightbox[myPicts]\"><img align="left" alt=\"'+ p_alt +'\" src=\"f?p=&AP'+'P_ID.:&A'+'PP_PAGE_ID.:&S'+'ESSION.:APPLICATION_PROCESS=getImage:::P_IMAGE_ID,P_THUMBNAIL:'+p_image_id+',Y\"></a>';
var oEditor = FCKeditorAPI.GetInstance(p_item_name);

Note how the Apex substitution variables are divided with "'+'" so the Apex engine won't replace it, and how the double quote are escaped by "\". Quotes in the alt-text are replaced with it's HTML/unicode counterparts in the SQL-query. I also cheat a bit by aligning the picture to the left, but this can easily be changed with FCKeditor later.The images will not show up in the preview window, because of the unsubstituted substitution variables (phew!).

As long as the images are served through an application process (to apply security) and not through a public procedure, this will be the result. But when the article is displayed, and session variables properly substituted, the thumbnails are properly displayed

So now my articles shows clickable thumbnails which displays the full picture in Lighbox2 :-)

The result?
Despite my shaky implementation, the users are left with a simple way to include pictures into their articles. That was pretty much the point. The users doesn't need to know it's held together with chewing gum and a piece of string.

As for me, I start to realize that javascript can do pretty much everything, and that sometimes it is a pain to get it to do anything...