Interactive Reports Not Working Properly on New Install

This is just a quick note to self, but may be useful to others experiencing similar problems. I would not recommend the Fix Two solution for production environments before conducting further research though...

Preface
Recently I installed Oracle Apex 3.2 in an existing database. Plain vanilla install on an Oracle 9.2.0.5 database with an Oracle iAS 9.2.0.3 in front. Installation went smooth, demo application works. Shift. Different site, same plain install, but this time on a 10.2.0.4 database, and 10.1.2 application server.

The Demo Effect
What is the first thing you show off when demonstrating Apex? The interactive reports, that is a no-brainer :-) But at both of these installations, something goes wrong. When clicking a report column, I just get the spinning wheel, no other response. What is amiss?

Dissecting the Problem
At the first location, I had no time and no Firebug. At the second location I had both, and two failed installations creating some sort of consistency.

In the Firebug console, I can see a javascript error pops up when a column is clicked. Sometimes it throws string not terminated error, sometimes some other cryptic message, but always the same javascript function. Examining the response in Firebug shows something odd; the response is cut short. Depending on the distinct values of the column I clicked, the response might be cut inside a string (string not terminated error), or in-between. When clicking numeric columns, it works. Hm... Special characters? NLS?

Fix One
Patching the Apex installation to 3.2.1 worked for the installation on the 10g system. IR's started working when the patch was applied.

One down, one to go...

Fix Two (The Dirty Fix)
Examining dads.conf for the 9i installation, I see previously configured dads has a different setting of PlsqlNLSLanguage. Both installs go against databases with NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 (don't ask me why, seems like a popular choice for older systems in Norway).

Changing from PlsqlNLSLanguage from AMERICAN_AMERICA.ALUTF8 to AMERICAN_AMERICA.WE8MSWIN1252 did the trick, IR's are now working as expected. I have not noticed anything else breaking (yet), but I am not at all comfortable with the workaround.

The documentation clearly states:
"The character set portion of the PlsqlNLSLanguage value must be set to AL32UTF8, regardless of whether or not the database character set is AL32UTF8."
or for older versions of iAS:
"The character set portion of the nls_lang value must always be set to AL32UTF8, regardless of whether or not the database character set is AL32UTF8."

Luckily, the 9i installation will not go to production in it's current state. Phew...

PL/SQL and Gmail (or UTL_SMTP with SSL)

In this post I will describe how to send mail from an Oracle database using UTL_SMTP over SSL using Stunnel. I conducted the test on Windows XP with Oracle database 11gR1, but it should work for nix-operating systems and database versions 9.2 and up. To be quite frank, this is nothing new, but it might be of use anyway.

Preface
I wanted to send emails from my database when some data changes. It was not a corporate solution with access to an internal smtp-host. A simple, accessible, ISP agnostic smtp-server would do. In my case, Gmail fitted the bill, only problem was that Gmail required SSL, which UTL_SMTP does not support. I am up for a challenge (meaning: I am good at complicating (ing, not ed :-)) things), so here goes...

Stunnel
Since UTL_SMTP does not support SSL, I will use a third party tool to "wrap" my connection. There are probably any number of tools which can do this, but Stunnel is quite often referred to, and very easy to install and configure. For nix systems, I suggest checking the Examples-page on stunnel.org, this is a Windows-specific explanation. This part of the post is based on a thread on ez.no.

Installing and configuring Stunnel

• Go to stunnel.org and download the latest Windows binaries
• Install Stunnel (take note of the installation path), in my example it is c:\stunnel
• Edit the file stunnel.conf located in installation folder to (just backup the original, and replace all the original text with the text below):

; Use it for client mode
client = yes

[ssmtp]
accept  = 1925
connect = smtp.gmail.com:465

Here I use port 1925 on my localhost (unused as far as I know) to connect to smtp.gmail.com.

Start Stunnel.exe, and test the configuration:

• Start cmd
• Write: telnet localhost 1925
• You should then see something like "220 mx.google.com ESMTP 5sm18031572eyh.34"
• Write: quit

Troubleshooting: If you cannot reach smtp.gmail.com, there can be any number of things gone wrong.

• Try a normal ping to smtp.gmail.com
• Check to see if stunnel.exe is excepted properly in all firewalls (Windows native and other software firewalls)

Once stunnel is working, and if you are familiar with UTL_SMTP, don't bother reading on. This is the same as UTL_SMTP with any other smtp-host requiring authentication.

Setting up ACL (11g only)
This is more or less monkeyed from Arup Nandas 11g series.

To create an access control list for your application user, and enabling it to connect to localhost on port 1925, do the following:

-- create acl
begin
        dbms_network_acl_admin.create_acl (
                acl             => 'gmail.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
end;
/
-- add priviliege to acl
begin
  dbms_network_acl_admin.add_privilege ( 
  acl       => 'gmail.xml',
  principal    => '<YOUR SCHEMA USER>',
  is_grant    => TRUE, 
  privilege    => 'connect', 
  start_date    => null, 
  end_date    => null); 
end;
/
-- assign host, port to acl
begin
  dbms_network_acl_admin.assign_acl (
  acl => 'gmail.xml',
  host => 'localhost',
  lower_port => 1925,
  upper_port => 1925);
end;
/

And you are ready to use UTL_SMTP against smtp.gmail.com.

Wrapping UTL_SMTP
I have created a small test-package based on the old UTL_MAIL example from Oracle. Your schema user must have execute privileges on UTL_SMTP and UTL_ENCODE for this to work:

create or replace package apex_mail_p
is
   g_smtp_host      varchar2 (256)     := 'localhost';
   g_smtp_port      pls_integer        := 1925;
   g_smtp_domain    varchar2 (256)     := 'gmail.com';
   g_mailer_id constant varchar2 (256) := 'Mailer by Oracle UTL_SMTP';
   -- send mail using UTL_SMTP
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   );
end;
/
create or replace package body apex_mail_p
is
   -- Write a MIME header
   procedure write_mime_header (
      p_conn in out nocopy utl_smtp.connection
    , p_name in varchar2
    , p_value in varchar2
   )
   is
   begin
      utl_smtp.write_data ( p_conn
                          , p_name || ': ' || p_value || utl_tcp.crlf
      );
   end;
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   )
   is
      l_conn           utl_smtp.connection;
      nls_charset    varchar2(255);
   begin
      -- get characterset
      select value
      into   nls_charset
      from   nls_database_parameters
      where  parameter = 'NLS_CHARACTERSET';
      -- establish connection and autheticate
      l_conn   := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
      utl_smtp.ehlo(l_conn, g_smtp_domain);  
      utl_smtp.command(l_conn, 'auth login');
      utl_smtp.command(l_conn,utl_encode.text_encode('<your gmail account including @gmail.com>', nls_charset, 1));
      utl_smtp.command(l_conn, utl_encode.text_encode('<your gmail account password>', nls_charset, 1));
      -- set from/recipient
      utl_smtp.command(l_conn, 'MAIL FROM: <'||p_sender||'>');
      utl_smtp.command(l_conn, 'RCPT TO: <'||p_recipient||'>');
      -- write mime headers
      utl_smtp.open_data (l_conn);
      write_mime_header (l_conn, 'From', p_sender);
      write_mime_header (l_conn, 'To', p_recipient);
      write_mime_header (l_conn, 'Subject', p_subject);
      write_mime_header (l_conn, 'Content-Type', 'text/plain');
      write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
      utl_smtp.write_data (l_conn, utl_tcp.crlf);
      -- write message body
      utl_smtp.write_data (l_conn, p_message);
      utl_smtp.close_data (l_conn);
      -- end connection
      utl_smtp.quit (l_conn);
   exception
      when others
      then
         begin
           utl_smtp.quit(l_conn);
         exception
           when others then
             null;
         end;
         raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);   
   end;
end;
/

This is NOT production-ready code: First of all, you do not want your credentials in the open, at least obfuscate the package body.

Some notes on the package:

• Parameters sender and recipient must contain e-mail addresses only, use the get_address function in the original Oracle example for more sophisticated use (you can also look at how to add attachments if you have the need).
• I had some trouble encoding my account name and password. My initial thought was to use utl_raw.cast_to_raw and utl_encode.base64_encode, but this did not work, so I ended up using utl_encode.encode_text
• Mime-type is set to "text/plain", set it to "text-html; charset=<something appropriate>" to enhance visual layout

Sending an E-mail
To test it all, try:

begin
   apex_mail_p.mail('<your gmail address>', '<recipient address>', '<Subject>', '<message body>');
end;
/

And you are done!

Well, if you don't get any error messages, that is. If you encounter any exceptions, first of all check your Gmail credentials. Next, check where (in the PL/SQL-code) it fails, and use your favorite search engine to do a combined search on smtp command sent and smtp-error received. Chances are others have worked through the same problems (even if they are not Oracle-related). Last resort is to use telnet and manually type the commands, a bit cumbersome but gives full control.

Happy coding :-)

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.

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:
WWV_CUSTOM-F_<some_id_here>_<APP_ID>
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
  </Directory>

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.

Secure?
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.

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?

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');
oEditor.InsertHtml(htmlSnippet);
}

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:
f?p=&APP_ID.:&APP_PAGE_ID.:&SESSION.:APPLICATION_PROCESS=getImage:::P_IMAGE_ID,P_THUMBNAIL:#IMAGE_ID#,Y
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);
   oEditor.InsertHtml(htmlSnippet);
}

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...

Dynamic RTF Documents Revisited

Yes, yes, this will be my last post on RTF for a while, but it's been a fun ride so far.

The (hi)story
In my posting RTF Documents in an Oracle Database I showed how to go about

• Creating RTF templates with substitution variables
• Generating RTF documents with substitution variables filled in run-time
• Data model and code to support the functionality in Oracle Apex
  

In the posting Converting RTF to PDF in an Oracle Database I,... Well the title is pretty self explanatory I guess :-)

Too simple!
Well, the first version of the code I showcased was a bit on the simple side, but it was just meant to give a hint of the possibilities. Anyway, I challenged myself to make a more complex RTF-component, so I set about wasting my time on coding a PL/SQL package that can return a RTF-table based on a query. With heavy use of dbms_sql the code can:

• Describe and store query columns
  This is used when defining the substitution variable. The columns are stored as rows in a table and have attributes such as title, text alignment, width.
• Running the query and return a RTF-table
  This is just a more advanced version of the simple substitution variables, but in stead of returning one column, one row as plain text, it now returns a RTF-table (but still as text).

In order for it to work, the query must have column aliases that can be resolved by dbms_sql.describe_columns into unique column names, and the columns must be of type varchar2 (yes, it is quite possible to return other data types, but in most cases text will suffice and I am lazy).

Sample Application
I have updated the sample application to show the function. I have included some images here to explain some of the elements. First a screen shot of the listing of all substitution variables in the uploaded template:


And a screen shot of the substitution variable attributes:
You can test it in my sample application, you can also download all necessary sample code there.

Now I soon leave for my summer holiday, five (!) weeks of leisure with my family :-)

XSL Transformation in Oracle Apex

I recently explored some of the XML capabilities of the Oracle database, and I must say it has matured quite a bit since I last looked at it (Oracle 8i).

Using XMLTRANSFORM-function
It is a powerful function which acts as a full XSLT processor. The syntax is simple:

xmltransform([xml_document],[xsl_document])

Both input parameters must be of type xmltype. By having XML/XSL stored as xmltype, or casting it to xmltype with the corresponding function, you in effect ensure the structure of the xml-documents are intact.

Read the documentation for more information. I know it can be boring, but also quite giving (from time to time, at least...). While you are at it, why not click "Previous" or "Next" on the documentation pages to view the other XML-functions?

Sample application
Well, "application" is just a bit overstated, what I do have is an Oracle Apex application page. I have created a quick and easy front end to the XSL transformation. Just paste your XML and XSL into the corresponding text areas, and click button Transform to view the result.

If the transformation fails, the error message will be returned in the display area.

Try out the transformation, and get a detailed description of the code involved in my sample application.

Converting RTF to PDF in an Oracle Database

There are a number of commercial products out there which converts RTF to PDF, but there is also a free alternative called Ted. Actually, calling this a converter is a gross understatement, it is a fully fledged RTF editor which also can convert RTF to PDF.

I will describe how to install Ted, and how to use it with RTF-documents stored in the database. This combined with generating dynamic RTF-documents can give a rich document solution for your Oracle Apex applications.

This is a Linux/Unix specific solution. So if you are stuck with some other operating system, go do something about it ;-)

Downloading and Installing Ted
Ted runs on Unix/Linux, and you can download the software here. Ted is released under GNU Public License, and restrictions may apply.

The easiest way of installing Ted is to use RPM package file.

• Download RPM package file to /tmp
• Install RPM-package as user root (or system user with sufficient privileges)
  rpm -ivh .rpm

If you get an error stating that packages are missing, do not despair, simply download and install the package indicated (or get it from your installation CD). The installation is pretty much the same as described in the bullets above.

Preparing os for RTF conversion
Download Ted the rtf2pdf.sh script which converts rtf-files to pdf-files.

• Create an os-directory:
  mkdir /u01/app/ted
• Copy the Ted conversion script to the directory
  cp /u01/stage/ted/rtf2pdf.sh /u01/app/ted/.
• Grant execute privileges on the script
  chmod +x rtf2pdf.sh

I have done the above operations with user oracle of group oinstall, and in this demonstration user oracle will be used to execute os-operations. This is probably not the best production solution, but will serve for my demonstration purposes.

Creating directory in the Oracle database
Defining a directory in Oracle is quite easy:

• Connect as user SYS
• Create directory:
  create directory RTF2PDF as '/u01/app/ted';
• Grant privileges to RTF2PFDF
  grant all on directory RTF2PDF to <schema_user>

How to execute os-commands from the Oracle database
There are basicly three alternatives; dbms_scheduler, Java and C. The three alternatives and use of dbms_scheduler is described by Steven Feuerstein here. He also has a good description of the Java and C approaches.

dbms_scheduler is the superior alternative to me, it's easy to use, and as opposed to Java, is compatible with Oracle XE (even though there are some issues with this release). The only beef with dbms_scheduler are the implicit commits, I have not investigated when or why any further as I have barely scratched the surface of dbms_scheduler. I'll get around to it some rainy day.

What actually made me revisit dbms_scheduler was this recent blog post by Tobias Arnold, following feeds like orna.info and apexblogs.info has proven invaluable for a monkey like me :-)

Dr. Tim Hall has an excellent site called oracle-base.com with loads of examples and articles on the Oracle database. My dbms_scheduler code and setup is based on his article Scheduler Enhancements in Oracle Database 11g Release 1.

Preparing for use of dbms_scheduler
Before your schema user can take advantage of dbms_scheduler and execute host commands, there are a few steps that must be done:

• Connect as user sys
• Grant create job to schema user:
  grant create job to <schema_user>;
• Grant the ability to execute host commands/external jobs to schema user:
  grant create external job to <schema_user>
• Create credential (this is new in 11g, and saves a lot of work):

begin
-- basic credential.
dbms_scheduler.create_credential(
credential_name => 'LOCALORACLE',
username        => '',
password        => '');
end;

• Grant credential to schema user:
  grant execute on LOCALORACLE to <schema_user>

The os-user specified must have execution privileges on rtf2pdf.sh, and r+w on the directory where the script resides for this example.

PL/SQL code to invoke conversion
I have written a sample package for converting a rtf-document to a pdf-document. Sadly, I can't use Oracle's hosted environment on apex.oracle.com to showcase the functionality. I do not have the privileges to perform the required steps described above, and rightly so. Feel free to download and test it yourself. I have uploaded the package to my sample application at apex.oracle.com, you can download the sample code here.

The code is for demonstration purposes only, so my usual disclaimer apply: I take no responsibility what so ever for what might happen when you use it and there is no warranty of any kind expressed or implied. Phew... I still hope for world domination when you compile the package though ;-)

The package contains procedures/functions for file handling and invoking rtf2pdf.sh, but can easily be adapted for a more generic use.

Converting RTF to PDF
Lets say you have a table called RTF_DOCUMENTS that looks like this:

create table rtf_document
(
rtf_document_id   number not null,
file_name         varchar2 (255) not null,
rtf_file          clob,
pdf_file          blob,
constraint rtf_document_pk primary key (rtf_document_id)
);

And you want to create a procedure to convert the contents of column RTF_FILE to a pdf-document in column PDF_DOCUMENT, you can use the following procedure:

create or replace procedure convert_rtf_doc (
p_rtf_document_id    in       rtf_document.rtf_document_id%type
,  p_dir_name           in       varchar2 default 'RTF2PDF'
) is
cursor c_doc
is
select rtdo.rtf_document_id
    , rtdo.file_name
    , rtdo.rtf_file
    , rtdo.pdf_file
from rtf_document rtdo
where rtdo.rtf_document_id = p_rtf_document_id;
l_pdf_file        blob;
begin
for r_doc in c_doc
loop
l_pdf_file := rtf2pdf.rtf2pdf(p_rtf_file_name => r_doc.file_name
                            ,p_rtf_file => r_doc.rtf_file
                            ,p_dir_name => p_dir_name);
update rtf_document rtdo
  set rtdo.pdf_file = l_pdf_file
where rtdo.rtf_document_id = p_rtf_document_id;
end loop;
end;
/

Beware of the implicit commit by dbms_scheduler! A select for update in c_doc cursor would result in "fetch out of sequence"-error.

Alternatives to Ted
There are always alternatives, this is not different. There are some Open Source projects out there, and this guy (that's sexist, but I still assume cfSearching is a he) has done some research on the topic. His angle was different from mine though, and he went the ooxml-way, which is not a stupid idea.

Open Office could be an alternative, and has a more active community surrounding it. The CLI surrounding the OO-libraries in server mode will probably do the job.

In conclusion
In all honesty, I have not yet investigated to which extent the rendering of pdf-files is correct based on the more complex elements of rtf. This, of course, I should have done first. For me the journey is the most fun when (as in this case) there are no expectations attached to the end solution.

Tightening the PL/SQl-code referenced here for production goes without saying!

Generating Table Collection API for Oracle Apex

...or "The Art of Laziness". The lazy programmer will always strive to get more for less, which is a good thing.

Why?
After looking at the collection API's i Oracle Apex, I could see their usefulness, but also get an idea on how labor intensive the operations were (for the programmer, not the database). I quickly got bored of the c001, c002, etc. coding, so for some time now I have been working on an API generator for creating and manipulating collections based on tables.

What is generated?
You get two packages and a view.

• The table API-package: This is very similar to the API packages generated by Apex, but with some changes to the cursor fetching table rows (all now share a global cursor) and the updates in my version does not attempt to update primary keys. Quite frankly, I could get by with the API's from Apex, but I have a bit of control freakishness about me, and it puts me in a better position to further customize the API when needed. I already have some ideas fro the latter part.
  
  
• The collection API package: This is the package that wraps both the Apex API's, and the table operations. Here you will find create, drop, ins/upd/del collection procedures, all with parameters that match the table, both in name and data type. You also get an apply-procedure to handle the actual dml operations against the actual database table. The package supports "overloading" of collections, so you can have more then one collection based on the same table at the same time. It also handles MD5 if you want it to
  
  
• The view: The view wraps the htmld_collections-view, and casts data types and column names from c### varchar2 format to their actual column names and data types from the database table it was founded on.

Statements for calling the collection API is also generated and included with the view-script.

Installation
Just download the generator package, and compile it into desired schema. Schema must have access to the following views: all_tab_columns, all_cons_columns and all_constraints. I deliberately left out any supporting tables (which certainly could have helped in some areas) to simplify things. This means that if you want to modify some parameters, you will have to do it in the package.

You can download the generator package here.

Generating code
Well, generating the code is quite easy. It is a "fire-and-forget" kind of operation; the packages and view will be generated in a PL/SQL-collection and can be queried and executed at will.

The following section shows how to generate code for an EMP-table located in schema NMS:

begin
gen_coll_p.gen_coll_package (p_owner        => 'NMS',
                          p_table_name   => 'EMP',
                          p_shortname    => 'EMP',
                          p_gen_type     => 'ALL');
end;
/

p_gen_type indicates what part(s) of code to generate, the available values are:

• TAB_API_SPEC for table API package specification
• TAB_API_BODY for table API package body
• COLL_SPEC for table/collection API package specification
• COLL_BODY for table/collection API package body
• COLL_VIEW for table/collection wrapper view
• ALL for all of the above

Viewing the generated code:

select text
from table (gen_coll_p.get_code)
order by line

Compiling generated code:
This part I have put NO amount of work in, after selecting the generated code, you are on your own :-) Well, it's not really that hard to select the code in SQL Developer or TOAD using the query above and copy/paste the result into a new window and choose "Run Script". Or lacking any "sophisticated" tools, you can even revert to spooling and running the result in SQL*Plus . I am sure you will find a way :-)

Using the collection API
Te following examples shows how to use the API from an Oracle Apex application. The API in these examples are generated on the EMP table, and p_shortname parameter during generation is set to EMP as well.

Creating a new collection:

apex_emp_coll_p.create_coll( p_coll_name => 'EMP_COLL'
                           , p_include_md5 => 'Y'
                           , p_where => 'where upper(ename) like ''S%''');

Viewing the contents of the collection:

select seq_id,
    empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    comm,
    deptno
from apex_emp_coll_v
where apex_emp_coll_v.collection_name = 'EMP_COLL'

And please feel free to replace 'EMP_COLL' with a bind variable :-)

Inserting a collection member:

apex_emp_coll_p.ins(p_coll_name => 'EMP_COLL'
                   ,p_empno     => :p_empno
                   ,p_ename     => :p_ename
                   ,p_job       => :p_job
                   ,p_mgr       => :p_mgr
                   ,p_hiredate  => :p_hiredate
                   ,p_sal       => :p_sal
                   ,p_comm      => :p_comm
                   ,p_deptno    => :p_deptno);

Updating a collection member:

apex_emp_coll_p.upd(p_coll_name => 'EMP_COLL'
                   ,p_seq_id    => :p_seq_id
                   ,p_empno     => :p_empno
                   ,p_ename     => :p_ename
                   ,p_job       => :p_job
                   ,p_mgr       => :p_mgr
                   ,p_hiredate  => :p_hiredate
                   ,p_sal       => :p_sal
                   ,p_comm      => :p_comm
                   ,p_deptno    => :p_deptno);

Deleting a collection member:

apex_emp_coll_p.del(p_coll_name => 'EMP_COLL'
                   ,p_seq_id    => :p_seq_id);

Propagating changes to the database table:

apex_emp_coll_p.apply_changes(p_coll_name => 'EMP_COLL',
                              p_refresh   => 'Y');

See it in action
I have made some sample pages to showcase the generator and using the generated code. See http://apex.oracle.com/pls/otn/f?p=28990:8 for the code generator, and http://apex.oracle.com/pls/otn/f?p=28990:4 for viewing the generated code in action.

Some notes on the generator
It did not turn out as smooth as I had hoped. I would really have liked to include an "instead of"-trigger to the view, but this cannot be done without granting rights on WWV_FLOW_COLLECTIONS which I would rather not do. Even though the "instead of"-trigger uses the documented Apex API's, it does not change the "ORA-01031: insufficient privileges" from cropping up. This pretty much rules out the automatic DML process, which is a shame.

When viewing the code in the generator itself, please remember that it has been evolving over time (and in my spare time too :-)), and the code reflects that fact.

I have some plans for features to include in the future, but as of now I do not know what and in what order they will be implemented, so I will let it lie.

Please leave a note if you find this useful or have any comments/questions, etc. :-)

RTF Documents in an Oracle Database

In this article I will outline how to use RTF templates to create documents with dynamic content.

Why RTF?
I'm lazy. RTF templates can easily be made of any user with some knowledge of MS Word, I don't have to lift a finger. Most users are comfortable with Word, and like the "freedom" it gives.

It's a proprietary standard, yes, but it's also documented, that helps... It comes in different versions, with subtle differences.

Is all rosy? No, there are pitfalls. In order to create templates with reasonable content and size, a structured approach i necessary. In a public application, I would not recommend RTF as the preferred format, but for an internal application in a controlled environment the approach can be quite effective.

In the past, I have used RTF in various projects, and found that it is very easy to develop and maintain fairly complex reports.

Creating a template
This example assumes working in M$ Word. First of all, create a document layout (with content) that you would like to use. When all is done, copy all content, click "Create new document" and choose "Empty document", paste all the content from the original document in one go and save as RTF.

You now have a clean RTF document, if you want to, you can examine the result in your text-editor of choice. Some elements are more cryptic than others, but you should get the general idea. For a more thorough investigation (depending on your temper), you can create RTFs from scratch. There are a number of sources online, check out the three tutorials located here, or this cookbook. If you are a bit more lazy (but still won't use MS Word), there are a number of tools that can do the job for you, including OpenOffice. Wiki is a good starting point, or use your favourite internet search engine...

Identifying dynamic elements
Some parts of the document are clearly candidates for dynamic substitution, identify these and tag them with descriptive names, I use §¤mySubstitutionVariable¤§ with "§¤" and "¤§" surrounding a descriptive name.

The substitution variable can contain any RTF-element, including tables and graphics. I concentrate on simple text strings in this article.

When you are satisfied with your substitutions, create a RTF template as described above.

Simple test application
Next you want to put the document in the database. I have a simple data model to support both RTF-templates and the substitution variables. It consists of three tables; one containing the RTF-template, one containing substitution variables and the last to bind them together.

I have created a package called rtf_p which pretty much handles all the fun, you can see it in action and download the source database objects here.

Remember that this is for demonstration purposes only, use it as you will, but I take no responsibility for what might happen when you do (your desktop melts, I achieve world domination, etc.).

Ready for production, or...?
So, the algorithm did not satisfy you? Well, it was not supposed to :-) Next you would want to implement some common function basis; which customer am I working with, what order number, etc. Using Apex collections is an easy way of achieving this. Create a collection (perhaps called "report"), and create rows of common use like "CUST_ID" or whatever you need and populate it before creating the finished document. Use the collection in your queries. If there are some substitution variables that are "always" included in a document; fill them with one query to improve performance.

The download part of rtf_p is not protected in my example (quite the contrary in fact), this you should rectify in a production environment; be sure the user is authenticated.

Depending on the need (you never know which way the customer jumps...), you might implement functionality for storing the finished document, or substituting more advanced RTF objects (like tables). You should try hard (well, harder than I did) to assist the users for filling substitution variables and such in the application (or: do you think it's fun to write the advanced "I wan't the last name first!"-type of queries?).

As always; the sky is the limit! (...and time, money, social life, quality of coffee, mood, etc...)

Producing documents with Oracle Apex

While in a start-up of a project, I delved into the whole document administration and generation world from an Apex perspective. I'm not talking about the typical accounting reports, but more like formal letters and such to send to customers.

My requirements list goes like this:

• Produce nice looking documents with some advanced elements (graphics, fonts, tables, header/footer, etc.), preferably in PDF
  
• Easy to develop and maintain, both static and dynamic content
  
• Low or no license cost

First off, Oracle Apex does not produce PDFs on its own. If you want to use the native Apex functionality for producing reports in PDF, you have to connect it to a print server. You can however generate PDFs with tools that does not integrate as well with Apex, and the list of possible solutions increases.

Here are the alternatives I dug out on short notice. Now, this is by no means an exhausting list, so feel free to guide me to other solutions.

Apache FOP: Open source XSL:FO implementation. Pure Java solution, can be used "as-is" with command line interface, or embedded in a servlet running in a Java container (eg. Tomcat). Requires skills of XSL:FO (or a commercial tool that produces this format) to customize output. Apex ships with pre-configured implementation for running in OC4J, so it's very easy to integrate.

Apache Cocoon: Spring-based open source Java framework that can do a lot more than producing PDF. If I understood correctly, it uses Apache fop libraries to generate PDF. Has to run in a Java container (eg. Tomcat). Very easy to integrate with Apex, Carl's (may he rest in peace) example proves the point. Requires skills of XSL:FO to customize output.

PLPDF: A pure PL/SQL alternative, and thus requires PL/SQL skills to define documents. I have tested the api, and the amount of code required to produce a document is not overwhelming. I really miss a GUI for the tool though. Cheap license (per database). Keeping it in the database appeals to me, and it comes recommended from some prominent members of the Apex community. Easy (but not without programming) integration with Apex.

JasperReports: Open source Java framework for report generation. Can be embedded in a servlet running in a Java container (yes, yes, Tomcat again...). Requires adaption of both JR and Apex for integration. There are some examples of integrating the two on forums.oracle.com. As opposed to the Apache fop-based tools which integrate quite easely into Apex, both the queries and layout have to be configured in JR. Customizing and layout of reports are supposedly quite easy with iReport. I have seen som fairly exotic documents generated with this tool, so advanced layout is no show-stopper. JR will require skills in a separate product from Apex. ReportChunker (based on JasperReports) comes pre-packed as a war, this might be an easy way of integrating, but I have no hands-on experience with it.

Oracle BI Publisher: King of document design and Apex integration. If you already have a license for Oracle EBS/BI Publisher, or are going to produce painfully many advanced reports (and have an arm and a leg to spare), this is the way to go. It is really, really not cheap, but depending on how many hours you plan spending on developing documents and customizing them, I imagine there must be a break-even limit somewhere. I imagine... Requires skills in a separate product from Apex.

Any more out there? Of course there are, but as the project headed for RTF, I did not dig any further.

The monkey-side of me is still baffled by the fact that there still are no point-and-click solution for this, coding is so much, well, work... And quite a bit of fun ;-)

Monkey see, monkey do

So, what could possibly possess a "normal" person to start a blog at this time? Everyone knows there are far more blogs than readers out there.

I think the reason is simply that I am a monkey! Well, at least a biped of sorts. You have to hand it to the monkeys, they have no restrictions what so ever about "copying" other peoples (or bipeds, whatever) work. In that sense, I am a monkey (probably more than just that sense, but I won't go there now). I sift through the internet to find an example matching my current task. I am lazy, I am good at searching the net, and I am good at adopting code. At least good enough to make a living of it.

And the blog? It's time for this monkey to give something back. Even if people don't read blogs like they used to (everybody busy writing their own and all), the text becomes public and just a few keystrokes away in your favorite search engine. Even my own work for myself, which is not such a stupid idea for someone like me.

What really prompted me to do this was this thing called Oracle Application Express (Apex). Working with the product (which is great!) proved significantly more easy than I am used to. Why? The community surrounding the product! Let's face it, Apex does not get the support from sales that it deserves, so the community surrounding it has risen to the occasion. This is what we expect to see from the Java community, but seldom see in the more closed world of Oracle (even if that has changed these past few years). I like the "make each other better"-approach!

So, this blog will ramble on about this Apex thingy and technology surrounding it.

Think you could learn something from a monkey? Happy reading!