Monkey on Oracle

The Art of Self-preservation

2012-01-30T20:55:00.000+01:00

This post is aimed at all those poor souls that find themselves facing Oracle ADF coming from a PL/SQL background (Forms, APEX or plain old database development).

Normal First Reactions
Despair. Most of us have felt it in the beginning, and it is considered perfectly normal. The feeling of constant despair will diminish slightly over time, but relapses will be frequent.

You will feel as if you have stepped 20 odd years back in time; yes, it has to be this hard to make even the simplest CRUD application.

You will have to pass some fairly elevated hurdles; yes, you have to learn (or at least have some knowledge of) Java, JSF, Expression Language, Groovy, CSS, HTML, Javascript and last but absolutely not least ADF itself.

Start in the Right End
You (or more likely your superior) bought the sales pitch, and thought you arrived to a mature, complete development framework specifically suited to leverage your existing Oracle investments. What you got was the biggest jigsaw puzzle ever, and all the corners missing. To even attempt fiddling around with the pieces, you have to have some basic skills.

ADF is a Java framework (although Java developers would call it xml-based). Java is core. You have to learn the stuff. You will not get far without Java. It is that simple. After all, the kids all learn it at school these days, how hard can it be? The answer is like always; 10% talent, 90% practice, it will take time to be good at it.

As a starting point, I recommend Head First Java. Not only is it educational, it will also help you to realize just how bad all other introductory programming books actually are.

The Next Step
As Java frameworks go, ADF is pretty big. Full JEE stack with lots of bells and whistles, and an abundance of XML-files. The good news is that you can achieve some pretty sophisticated behavior by declaration, the bad news is that you have to know where to turn the knobs. There are a lot of knobs.

First you have to learn the grand picture of the framework;

The model, aka the meat, or business components
The other model. Err? This is the binding layer, or view backend model or some such
The controller thingy with task flows
The view, as in GUI, and not to be confused with the view object in the first model. Argh...

Grant Ronald will help you on the way with his book Quick Start Guide to Oracle Fusion Development: Oracle JDeveloper and Oracle ADF. Do not let the lengthy title put you off, Mr. Ronald understands where you are coming from, and will gently nudge you in the right direction.

Ready Yet?
Well, that depends. Do you want to create tutorial applications, or the real thing? You should not balk at tutorials, that is not what I am saying. It is just that real business users have never had any clue how to best work with <insert technology/framework here>, they just have needs (of which, you are of course grateful, as it gives you work).

Users do not want to have plain CRUD anymore, they want to have an extremely functional and visually appealing working environment delivered at lightning speed. Business users will push you to the edge, they are both educated within their domain, and most likely used to some seriously fast client server applications.

Nervous?

Relax...
Stay sane just a bit longer. Take a deep breath, there will eventually be a light at the end of the tunnel. Let us hope it is not an oncoming train ;-)

Shay Shmeltzer has a more elaborate (and infinitely better) approach on how to get started with ADF in this blog post.

To be continued...

Updated Statement of Direction

2012-01-26T21:33:00.000+01:00

...Of sorts. Actually, I did not even know I had one until now. But here goes:

My long absence from this blog are based on all the right reasons. Family. Life. You know, the things that matters. And some reasons slightly off. Like ADF. Yup, Oracle Application Development Framework. Just writing it out loud like that is probably going to make Dimitri's blog feed processor choke all over again, and get this blog instantly blacklisted from anything slightly PL/SQL related for all time.

The Old Road
My fascination with APEX has not diminished by far, and I hope to revisit extremelyproductivefundevelopingsmoothookingdatacentricapplications-land in the future. But for now, the "other" framework is more than enough.

The New Direction
Being a consultant will make you do strange things from time to time. This is one of those. Am I a Java-programmer? No. Do I know JSF? No. Am I a Groovy programmer? No. Am I at least proficient at Expression Language? No. How about ADF-skills? Well, some. I guess. Luckily, Oracle says I only need a week, or at least a day to become an expert. Hmm. I had to pass on OOW last year, due to a project using ADF... Catch 22 all over again.

I cannot say I am that sorry, actually. Learning new stuff (i.e. banging my head against a wall), and really trying to understand it (i.e. repeatedly), is part of my nature.

It was a professional change long overdue.

The Blog
I will continue to post my progress with ADF (or lack thereof) here, feel free to divert this blog from your favorite feed reader... Oh, and some APEX stuff will probably still make it here from time to time.

You can go wash your eyes now :D

Install, Rinse, Repeat

2011-10-22T21:37:00.000+02:00

Every time I start installing Oracle on Linux I always end up with the same dark thoughts: Why does it have to be so hard! An the worst part is: It isn't! But let me be precise with the last statement: It isn't hard if you know how! But in the beginning, you don't, so it is... You get the picture.

On my last endeavor to create a shiny new VirtualBox machine, I decided to take notes. Here is what I aimed at:

Yes, both Apache httpd and Apache Tomcat present. I needed a versatile development vm, with a known (to me) web server, and a known (again, to me) web container working with APEX, the APEX Listener and Oracle 11g XE database.

There will be typos, there will ~~probably~~ ~~most likely~~ of course be things I have forgotten to write down. On the whole, I hope it will save you some grey hairs (or in my case, loss of gray hairs :-))

Beware, the iframe controls will not be very responsive before the document has completed loading, and the document is quite large. Go for the direct access if it gets too troublesome...

Direct access to the Google doc is here: https://docs.google.com/document/pub?id=19TZ5Yqa-W6edWvPhWtH9d0D3iPEKFkGwckncUDKCaIY

Here goes:

Updated Hotkeys for APEX 4.1

2011-10-21T10:25:00.000+02:00

I finally got around to updating the hotkeys userscript for Application Express 4.1. There were some changes in the APEX Builder html that made an update necessary.

Cool image by Josef Stuefer, available by CC BY 2.0

I have to say, that after I started using shortcut keys in the builder I never looked back. These days I get confused when developing in a browser where the script is not installed.

The script can be downloaded here: http://userscripts.org/scripts/show/115495

Just click the big green Install-button, and you can start using it. The script is tested with Google Chrome, but should work equally well with Firefox.

The keys are the same old:

F8: Run Page
F9: Apply Changes
Alt + F9: Clicks the highlighted button
Shift + F9: Cancel
Alt + PageDown: Next
Alt + PageUp: Previous
Ctrl + Shift + S: Shared Components
Ctrl + Shift + E: Edit Page
Ctrl + Shift + Z: Toggle return to page on/off

If you disagree with the key mapping; then change them! The source is there for the changing :-)

If there are any grievous errors, then do not hesitate to call...

The old hotkeys userscript for APEX 4.0 is still available here: http://userscripts.org/scripts/show/81058

Multiple File Upload with jQuery and APEX Listener

2011-10-20T21:14:00.000+02:00

Did you ever want to do this?

Envious of the fancy multiple image upload functionality I saw from Google+, I set out to do the same. This is more of a log of observations than a full recipe. There is no APEX involved but the listener. To follow this you have to have an APEX Listener v1.1 (or higher) up and running against an Oracle 10g (or higher) database. This will not work with Internet Explorer or Opera (not multiple files), I have only tested on Google Chrome.

jQuery to the Rescue
jQuery has a lot (capital L type of lot) of plugins, one really nice I stumbled upon was Sebastian Tschan's jQuery-file-upload. Demo is here, download is here, documentation is here.

Setup Database Objects
The images has to go somewhere, and I created an images table in the hr-schema:

   --drop table images cascade constraints;
   --drop sequence images_pk_seq;

   create table images (
      image_id        number,
      content_type    varchar2(255),
      filename        varchar2(4000),
      image           blob,
      constraint img_pk primary key (image_id) using index)
   /

   create sequence images_pk_seq;

   create or replace trigger trg_images_bi 
   before insert on images 
   referencing new as new old as old for each row
   declare
      l_new_id number;
   begin
      if :new.image_id is null then
         select images_pk_seq.nextval into l_new_id from dual;
         :new.image_id := l_new_id;
      end if;
   exception
   when others then
      raise;
   end trg_images_bi;
   /

   alter trigger trg_images_bi enable;

   grant all on hr.images to APEX_PUBLIC_USER;

You have to grant insert/delete at the least to the user defined in the listener (normally APEX_PUBLIC_USER).

Setup Imagehandler Resource Template
This is where the actual data manipulation is going to happen. You can import all resource templates in this example by importing this file: resources.zip.

Log into listenerAdmin (http://myhost/apex/listenerAdmin).

Click Resource Template
Click Add Resource Template
Click Add Handler, and choose method POST
The Post handler will handle all uploads and inserts
Click Add Parameter, and set the following: status, X-APEX-STATUS-CODE, Header, Out, Integer
Click Add Parameter, and set the following: location, X-APEX-FORWARD, Header, Out, String
Click Add Parameter, and set the following: file, filename, Header, In, String (This should actually map to the filename, and be inserted with the rest of the data. For some reason, this does not work, and I'm too lazy to find out why...)
Add the data as seen below, and click Save

   declare
      l_image_id number;
   begin
      insert into hr.images (content_type, image, filename) values (:contentType, :body, :file) returning image_id into l_image_id;
      commit;
      /* This status will actually not be seen, as we have to redirect to get control of response */
      :status := 201;
      :location := 'imagehandler/response/'||l_image_id;
   end;

Click the imagehandler resource template
Click Add Handler
Choose method DELETE
Click Add Parameter, and set the following: imageid, <blank>, URI, IN, Integer
Click Add Parameter, and set the following: status, X-APEX-STATUS-CODE, Header, OUT, Integer
Add the data as seen below, click Save

   begin
      delete from hr.images where image_id = :imageid; 
      commit;
      :status := 200;
   end;

Click the imagehandler resource template
Click Add Parameter, and set the following: imageid, <blank>, URI, IN, Integer
Click the GET tabAdd the code as seen below, click Save

   select content_type, image
   from   hr.images
   where  image_id = :imageid

Setup Imagehandler Response Resource Template
I have yet to find a way to get a PL/SQL Block resource to respond with a json array, only single json attributes. It is a shame really, giving control of the response to the developer would have helped a lot. As it stands now, the client will not get a proper response (201 - created), and we have to add an extra resource template. If there is a way, please do tell, the docs gives nada here.

This jQuery plugin requires a json array, and in order to do that, we have to cheat.

Click Add Resource
Click Add Parameter, and set the following: imageid, <blank>, URI, IN, Integer
Template Add the code as seen below, click Save

   select 'application/json','[{"name":"'||:imageid||'.jpg","size":'||dbms_lob.getlength(img.image)||',"url":"\/apex\/imagehandler\/'||image_id||'","thumbnail_url":"\/apex\/imagehandler\/'||image_id||'","delete_url":"\/apex\/imagehandler/'||image_id||'","delete_type":"DELETE"}]'
   from hr.images img
   where img.image_id = :imageid

Looks a bit greek, I know. You can look at the server response for more details, but basically you are returning file name, download url, thumbnail url and delete uri/method in a json array.

Setup jQuery Plugin
Nothing to it really, download and unzip the archive somewhere reachable on a web server or appserver. In this example I unzipped and renamed the folder so that I could reach the example/index.html on http://myhost/js/multiupload/example/index.html.

You have to make one change in the example/index.html to make it work: Point the form action to your image handler:

<form action="/apex/imagehandler/0" method="POST" enctype="multipart/form-data">

Notice how we have to add {imageid} at the end, parameters are not optional (at least not in this version of the listener), so if it is left out, it will not match the resource templates URI pattern.

Ready for a test run!

Some notes on working with the Application Express Listener

The documentation is a bit lacking (to say the least)
Debugging is hard, 500 server error does not say much

This is a very simplistic example, there are no security involved, and it is not embedded into an APEX page. Anyhow, as a quick example, it (barely) serves the purpose.

Refresh Report Region and Pagination

2010-11-08T09:42:00.000+01:00

When using the refresh action to refresh reports in Oracle APEX 4.0, you inevitably will encounter the problem of lost pagination. The refresh action does really refresh, regardless of which page of the report the user was currently watching.

It Just Won't Tell Me
APEX knows the current pagination of a report. If you go to another page and back, or just press F5, you will see that APEX knows which page of your report you were on. Where can I get that user data? I have looked at the documented API's, and the closest thing I can see, is the apex_application.g_flow_current_min_row. The problem is that this is only available at render time and maps to pg_min_row request value. Otherwise, I only get the value 1 (first row).

It knows, but won't tell me!

The Dirty Approach
If there is a will, there is a way. I looked into the APEX tables, and especially wwv_flow_data. If you look, you will see a record containing the user session instance and report region id. Item value will be a colon separated value starting with the current min row, followed by current max rows (probably). That looks useful, but you need to keep your head about this. The value column is a CLOB data type, and a pretty special format, so if you attempt to use that data, be prepared to do a rewrite next time you patch your APEX installation.

If you notice in your pagination scheme, the pagination link executes a javascript called $a_report_Split (basically a wrapper for old (but rewritten) $a_report). So if I could use wwv_flow_data to get the second parameter of $a_report_Split right (<pMin>_<pMax>_<pFetched>), then I would be good to go..? For a while, anyway. $a_report_Split is not a documented API javascript function, and may also be subject to change in the upcoming releases

The Dirty Solution
Access to wwv_flow_data directly is not something you want to do in a normal APEX installation. Creating a very single minded function in a privileged schema (granted select on wwv_flow_data) and granting access to the function to my application schema was the best I could come up with.

create or replace function <privileged schema user>.get_current_pagination (    
         p_region_id    in       number
      ) return varchar2 is

         l_ret          varchar2(255);
         l_session_id   number := v('SESSION'); -- setting directly prevents unauthorized use
         l_value_tab    apex_application_global.vc_arr2;

         cursor c_region (
            b_session_id      in       number
         ,  b_region_id       in       number
         ) is 
            select item_value
              from apex_040000.wwv_flow_data
            where  flow_instance = b_session_id
              and  item_id = b_region_id;
      begin
         for r in c_region(l_session_id,p_region_id)
         loop
            l_value_tab := apex_util.string_to_table(r.item_value,':');
         end loop;

         if l_value_tab.exists(1)
         then
            l_ret := l_value_tab(1) || '_' || l_value_tab(2) || '_' || l_value_tab(2);
         end if;
         
         return l_ret;
      end;                            
      /

      grant execute on <privileged schema user>.get_current_pagination to <application schema user>;

That taken care of, the next thing is to get that value and use it in a call to $a_report_Split.

Follow these steps in your APEX builder:

Create two hidden page items called P1_REPORT_REGION_ID and P1_REPORT_SPLIT_PARAMS
Create a dynamic action to be called when you would like your report to be refreshed
Create three true actions:

First action to set P_REPORT_REGION_ID with the id of the report region:
Action: SetValue, Type: Javascript Expression, Javascript Expression: $('#<report region static id>').closest('div').attr('id').replace(/[^0-9]/g,'').
(If you set this item value as a computation before header in APEX, you can skip this action)

Second action is to set P1_REPORT_SPLIT_PARAMS with values corresponding to the current report page:
Action: SetValue, Type: PL/SQL Function Body, PL/SQL Function Body: return <privileged schema user>.get_current_pagination(:p1_report_region_id);

Third action is to call the $a_report_Split javascript function:
Action: Execute Javascript Code, Code: $a_report_Split($v('P1_REPORT_REGION_ID'),$v('P1_REPORT_SPLIT_PARAMS'));

That should be it.

Naturally, I have cannot create a demo page on apex.oracle.com for this, as access to wwv_flow_data is somewhat scarce in that environment (and rightfully so).

Why Didn't He Just...
If you know of a better way to do this (and better than refreshing the whole page :-)), then please let me know! And if it is possible through documented API's, that would be grand.

If this is the closest I get, and any of the APEX team is reading this, then please consider this an enhancement request :-P

For future reference: Oracle Application Express 4.0.1.00.03

Paranoia 101 - or protecting your investment

2010-11-05T15:23:00.000+01:00

In corporate application development, a versioning and backup strategy is pretty mandatory. But what about all those small personal projects? The only thing you can be really certain of when using a laptop/desktop for your pet projects, is that eventually your machine is going to die. How much work gets lost when it does?

I use SVN for version control as a rule. I also use the APEX export and splitter utility to capture my local Oracle APEX applications into subversion repositories. Sometimes I even remember to put some of my more important repositories on Amazon S3 for safe storage. The APEX export is (very) loosely based on a blog post by John Scott back in 2006.

A word of cation: This just my naive approach, use it at your own risk! Be sure to test whether a restore actually works, equally important as the actual backing up :-)

The Start of an "Automated Approach"
While the manual steps described above will get you there, it is also a bit more work than I appreciate. In this post I will describe a more automated approach to ease your local APEX application backups (and anything else you put in your svn repository as well). A bit of pain to set it up, but once there, it is all plain sailing.

I do my development in Windows mainly, so all OS specifics described below are set in that context. This approach is viable for your favorite nix OS also. A tweaking of the batch scripts should do it.

Subversion
You need a command line tool to manipulate subversion repositories, download Subversion from http://subversion.apache.org/packages.html. I currently use the one from CollabNet. It should not matter much which one you choose. There are a number of online resources on how to install svn and create repositories (yes, even for Windows...).

Include the subversion bin directory to your path environment variable. After installation you should be able to execute svn commands from a cmd window:

svn help

svnadmin help

APEX Export Utility
This comes with the APEX installation files, and has the ability to extract single APEX applications (or whole instances) from a command line. An APEX splitter tool is also included, which together with a subversion repository enables you to track all the tiny changes in your applications.

In Oracle APEX 4.0, it accepts more modern Oracle jdbc libraries, in APEX 3.2 you had to locate classes12.jar (predates even the jdbc libraries that comes ships with Oracle XE). You can download all jdbc libraries here: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html

Since technetwork is constantly shifting about: If the link does not work, try to locate the Software Download Index, and search for JDBC.

Amazon S3
You need to set up an Amazon account before you can use Amazon Simple Storage Service (S3). If you have ever ordered books from Amazon, you probably already have an account, but it may be necessary to confirm payment methods.

Storage prices are not that high (at the moment). My reasoning is that I will suffer more if I don't backup my stuff to safe storage, than a tiny monthly bill could ever make me.

In order for this to work, you have to obtain an Acceskey:

Log on to http://aws.amazon.com/account/
Go to Security Credentials
Go to Access Credentials
If you do not have any previously generated accesskeys, then click Create a new Access Key
Copy both Access Key ID, and Secret Access Key

Beware to keep this information to yourself.

Create a bucket where you would like your backups to be stored, and take a note of the name.

The thing with Amazon S3, is its interfaces (REST style HTTP or SOAP) and storage objects organized in buckets. This basically means that you will require a third party tool (or do some serious programming yourself) to easily use the service.

JetS3t - Synchronize
I settled for JetS3t. I will not vouch for this tool in any way. I do not know who develops it, I do not know if it sends my credentials to other people or companies. I basically just hope and pray.

When credentials potentially can be exploited in a very direct manner to get at your most precious, and even conducting illegal activities on your behalf, it is time to draw a deep breath. Maybe uploading your file manually wasn't such a bad idea anyway? As I said, I just hope and prey. I hope "someone" will scan the source code, and "someone" will scream if anomalies appears. I hope.

I use a part of JetS3t called Synchronize. It has a very easy cli that suites my need for an automated backup process. Installation is just unzipping, but you have to tweak the properties a bit for it to work.

These are the changes I made in jets3t\configs\jets3t.properties-file:
s3service.default-bucket-location=EU (well, bacause it's the easiest to me)

And these are the changes in jets3t\configs\synchronize.properties

accesskey=<your accesskey>

secretkey=<your secret accesskey>

You can also point Synchronize to an encrypted file containing your access keys using the --credentials switch.

7-Zip
To zip my svn repository dumps, I use 7-Zip. Very easy, straight forward open source zipping tool with a command line interface. Make sure you can reach 7z from a command prompt before proceeding, else you have to modify the batch scripts below and add the path.

Java
Make sure you have java 1.5 or higher installed in you system, and that you can write java -version from a command prompt.

Batch scripts
This is where it gets interesting.

I start off by creating a directory where my backup scripts will reside. It will also serve as a staging area before files are uploaded to Amazon S3. This is the current structure I have:

backup

Apex (I need this directory for temporary check out/in)
oracle (this is where I copy the export/splitter utilities from <apex install dir>/apex/utilities/oracle)

apex

APEXExport.class
APEXExportSplitter.class

classes12.jar (jdbc drivers)

I then place four backup scripts directly into the backup directory.

backup_apex_app.bat
This script is the one responsible for checking out the APEX files from svn, extracting the current version of the APEX applications from the database, and checking it into svn again. It takes a parameter (%1%) containing APEX application id. All other necessary parameters will be set in the master.bat below.

REM ******** Start Backup APEX apps ************
set APEX_APP_ID=%1%

REM ** svn checkout
svn checkout file:///%APEX_REPOS_PATH% %APEX_SPLITTER_DIR%

REM ** Export APEX applications
set CLASSPATH=%CLASSPATH%;%APEX_BACKUP_DIR%\classes12.jar;
java oracle.apex.APEXExport -db %DB_JDBC_CONN_STR% -user %DB_USER% -password %DB_USER_PWD% -applicationid %APEX_APP_ID%
java oracle.apex.APEXExportSplitter f%APEX_APP_ID%.sql

REM ** Copy files to svn directory
xcopy %APEX_BACKUP_DIR%\f%APEX_APP_ID%.sql %APEX_SPLITTER_DIR% /Y
xcopy %APEX_BACKUP_DIR%\f%APEX_APP_ID% %APEX_SPLITTER_DIR%\f%APEX_APP_ID% /S /E /Y

REM ** Remove superflous files
rmdir %APEX_BACKUP_DIR%\f%APEX_APP_ID%\ /s /q
del %APEX_BACKUP_DIR%\f%APEX_APP_ID%.sql

REM ** Add and check in files to repository
cd %APEX_SPLITTER_DIR%
svn add * --force
svn ci -m "Automated backup"
cd %APEX_BACKUP_DIR%

backup_svn_repos.bat
This is the script responsible for dumping the svn repository and zipping the dump file. I suppose this could have been done with HOTCOPY as well. Old habits and such... You can include filters here to extract only the project you are interested in. All necessary parameters will be set from the master.bat script below.

REM ******** Start dumping and zipping repository ************

REM ** Remving old dumpfile
del %APEX_BACKUP_DIR%\%APEX_REPOS_DUMPFILE%.zip

REM ** Dumping svn repository
svnadmin dump %APEX_REPOS% > %APEX_BACKUP_DIR%\%APEX_REPOS_DUMPFILE%.dump

REM ** zipping repository dump
7z.exe a %APEX_REPOS_DUMPFILE%.zip %APEX_REPOS_DUMPFILE%.dump

REM ** Removing old dump file
del %APEX_BACKUP_DIR%\%APEX_REPOS_DUMPFILE%.dump

backup_svn_to_s3.bat
This script is responsible for sending the zipped repository dump to Amazon S3.

REM ******** Backup SVN repository to S3 ************

REM ** Synchronizing svn repository dumpfile
%JETS3T_PATH%\bin\synchronize.bat UP %BUCKET_NAME%/%BUCKET_PATH% %APEX_BACKUP_DIR%\%APEX_REPOS_DUMPFILE%.zip

master.bat
This is the script which ties it all together. If you want to test the separate scripts, just comment out the scripts (rem) you don't want to call. Chances are there is going to be some issues with the parameters the first run, so testing in small stages is key when debugging (it produces quite a bit of output during a normal run).

In this script it is necessary for you to adjust all parameters to your own environment. Example values are included in the sample below.

rem *********** Setting environment parameters *******************

rem ** backup directories
set APEX_BACKUP_DIR=<your backup dir, like c:\development\backup>
set APEX_SPLITTER_DIR=%APEX_BACKUP_DIR%\apex

rem ** path to apex application directory inside svn repository
set APEX_REPOS=<the actual path of your svn repository, like c:\SVN\myapprepos>
set APEX_REPOS_PATH=<Full path to your APEX folder inside your svn repository, note the forward slashes, like c:/SVN/myapprepos/trunc/apex>
set APEX_REPOS_DUMPFILE=<name of your repository dump file, like svn_repos, without post-fix/file type>

rem ** connect string format host:port:sid
set DB_JDBC_CONN_STR=<jdbc connect string, like localhost:1521:orcl3>
set DB_USER=<username, like scott>
set DB_USER_PWD=<password, like tiger>

rem ** S3
set JETS3T_PATH=<full path to your jets3t unzip dir, like C:\development\backup\jets3t>
set BUCKET_NAME=<name of the Amazon S3 bucket you created earlier, like mysafebackup.backup.svn>
set BUCKET_PATH=<name of path inside bucket, like svn_repos>

rem ************ Calling batch files ************************
call backup_apex_app.bat <application id>
call backup_apex_app.bat <other application id>
call backup_svn_repos.bat
call backup_svn_to_s3.bat

pause

Good to Go!?!
The only thing remaining to fully automated backups, is to schedule a regular execution of the script through the scheduler.

I know I felt a whole deal better after I started using this backup strategy :-)

Updated APEX 4.0 Builder Hotkeys Userscript

2010-09-03T14:11:00.000+02:00

I have updated my user script to include shortcut keys for navigating to Shared Components, Edit Page and Cancel.

Current shortcut keys are:

F8: Run Page
F9: Apply Changes
Alt + F9: Clicks the highlighted button
Shift + F9: Cancel
Alt + PageDown: Next
Alt + PageUp: Previous
Ctrl + Shift + S: Shared Components
Ctrl + Shift + E: Edit Page

It is tested with Google Chrome and Firefox (Greasemonkey must be installed).

You can download the script here: http://userscripts.org/scripts/show/81058

Press the big green Install-button to install the script in your browser :-)

Developing APEX Applications in Google Chrome

2010-09-03T13:18:00.000+02:00

Earlier this year I read Scott Wesleys post called Why I use Google Chrome for Oracle Apex Development, but it did not quite register then. Recently I started using Google Chrome as my main APEX development browser.

The sole reason for starting development in Chrome was the speed, but over time I have become more familiar with it's web developer environment. Here I will sum up some of the features I use, and my experiences so far.

Invoking Chrome Developer Tools
Besides the blistering speed of the browser, the native developer tools in Chrome is a great feature.

There are a number of ways to start the developer toolbar

You can click the icon in the upper right corner (tedious)
You can right click anywhere on the page (handy)
You can press <ctrl> + <shift> + I (handy indeed).

You can also press <ctrl> + <shift> + J to go directly to the javascript console.

The developer tools are feature rich, so I will only highlight some of the features.

Elements
This is the default screen when starting developer tools. You get what you expect from an element inspector (like Firebug):

Formatted HTML source code and instant view of element styles (also computed overview of all element style attributes in play)
Navigate through source code with keys or mouse to highlight page elements
Click the magnifying glass at the bototm and click page elements to inspect single elements
Double click element to change it (both in HTML document and element style attributes)

Resources
Resources gives you an overview of the complete page including external files. It sports an easy graphical view over how much time each part takes (like the Net tab of Firebug). Click any resource to view content and headers. It also gives you a nasty red dot with (preferrably a low) number in it, if the browser encounters any structural error in the documents it has to rewrite. If you click the document, the rewrites are highlighted so you can correct the issues.

You can filter the resources by clicking the buttons (like Stylesheets, Images, Scripts, etc.).

One of the features I liked most about Firebug, was the ability to easely track AJAX calls (from the console or Net). Resources tab in Crome has the same ability, just click XHR-button (XHR = XML HTTP Request) to view AJAX type requests and responses.

Console and Scripts
The console has it's own shortcut keys, <ctrl> + <shift> + J gets you directly there. The console will display all errors encountered by the browser to start with, and let's you write javascript to interact with the page.

The Scripts tab let's you view, alter and debug javascripts

Set/enable/disable breakpoints
Play/pause, step in/out/over, etc.
View and change variable values

Other Features

Timeline - Nifty feature to watch real-time javascript events as you interact with the page. Filtering options are available (Loading, Rendering, Scripting).
Profiles - Profile your pages (or actions on page) in view of CPU intensity or memory usage
Storage - The only real use I have for this is the Cookies overview.
Audits - This is like YSlow for Firefox, giving you advice on how to speed up your pages

Extensions and Userscripts
All the features mentioned above is integrated in the browser, in addition, there are a number of extensions you can download. I just have Web Developer installed, but you can browse Google Chrome Extensions for more. You can also see Creating a Web Development Environment using Google Chrome Extensions from Speckboy Design Magazine for more on related extensions.

Sadly, the APEX Builder Plugin does not work for Chrome. I use my own userscript to get shortcut keys working in the APEX builder. Many of the Greasemonkey userscripts also works in Chrome, see userscripts.org for a large amount of those.

Experiences so far
I now go days without resorting to Firefox and Firebug. (In truth, that is not just Chrome's achievement. Dynamic Actions in APEX 4.0 makes rich interfaces much, much easier than it was.) I find that most of what I need, most of the time is in place within the developer toolbar of Chrome. I can also work full working days without having to restart the browser because of memory leaks, etc (browser getting sluggish, you know what I am talking about...).

Oh, did I mention it is fast?

For future reference: This was written based on Google Chrome version 6.0.472.53.

Formatted Text with Dynamic Actions

2010-08-26T15:14:00.000+02:00

This is a quick note on how to use Dynamic Actions in Oracle APEX 4.0 to display unstructured and formatted text from the database based on user selection. This was a bit more cumbersome in the previous versions of APEX.

Image by Jesper Rønn-Jensen under Creative Commons License.

If you have ever let the users get at the Rich Text Editor items (HTML Editor in the previous versions), then you probably have som text with HTML-markup stored in the database. Below is a description on how to get and display the text based on browser events. The example below will display a description of a department when the user clicks department name in a SQL Report (quite stupid really, but serves the purpose).

Demo Application
I have updated my demo application with the example, see it in action here:http://apex.oracle.com/pls/apex/f?p=45420:5. Download the application here: http://apex.oracle.com/pls/apex/f?p=45410:DOWNLOAD.

Prepare the Report
Create a region of type SQL Report based on table DEPT:

select deptno, dname from dept

Modify the DNAME-column in the report:

Set Link Text to #DNAME#
Set Link Attributes to id="departmentName#DEPTNO#" (this will uniquely identify each anchor element, and give the ability of extracting DEPTNO from the department name)
Set Target to URL
Set URL to # (this really does nothing)

Add an item to the report region, this will eventually hold the DEPTNO of the chosen department from the report.

Right click the department report region and choose Create Page Item
Select Hidden
Give the item a name (P5_DEPTNO)
Leave the rest default (you might want to reconsider Source Used, if you set it to "Always, replacing any existing value in session state", and leaves Source Value blank, you ensure the item starts with no value when the page is displayed).

Create Region to Display Text
For the sake of simplicity, the text will be displayed in it's own HTML Region

Right click Regions and choose Create

Choose HTML
Choose HTML again
Give it a descriptive title: Department Description
Create a div to display the text, set Region Source to:
<div id="departmentDescription"></div>

Create the Dynamic Action
The first we need to do, is to create a Dynamic Action to get the DEPTNO from the chosen department name, and stuff it into P5_DEPTNO.

Right click Dynamic Actions and choose Create
Choose Advanced
Give it a name (onclick - Department Name)
Set the values as seen below, this means the Dynamic Action will fire when anchors with id's starting with departmentName is clicked

Set Action to Set Value, uncheck Fire on Page Load (we only want it to fire when clicked), set the Set Type to Javascript, and Javascript Expression to

this.triggeringElement.id.replace('departmentName','');

This will extract the DEPTNO from the id of the element clicked (by replacing departmentName with nothing).

Set Selection Type to Item(s), and transfer P5_DEPTNO to the right hand side

The final result should look something like this:

The next step is to get and display the desired text from the database. In order to do this we must create a new True Action to the same Dynamic Action.

Right click the Dynamic Action and choose Edit
Click Add True Action
Set Action: SetValue
Uncheck Fire on Page Load
Set Set Type: PL/SQL Function Body
Set PL/SQL Function Body to code below:

declare
   l_ret varchar2(32000);
begin
   for r in (select dep.*
               from dept dep
              where dep.deptno = :p5_deptno)
   loop
      l_ret := 'HTML formatted return value for <b>'|| r.dname ||'</b> located in <i>'|| upper(r.loc) ||'</i>.';
   end loop;
   return l_ret;
end;

Set Page Items to Submit: P5_DEPTNO
Set Escape Special Characters: No
Set Selection Type: jQuery Selector
Set jQuery Selector: div[id=departmentDescription]

Finally edit the Dynamic Action, and set Event Scope: live. This is to ensure the onclick event is attached to Department Name even after PPR refresh of the report.

A Word of Caution
Even though APEX takes care of escaping/encoding your HTML according to JSON specification, this will bloat your return message. There seems to be the standard PL/SQL 32k limit to the JSON-message (Large texts resulting in ORA-06502).

Take care what you return in your message, switching off escape special characters leaves it pretty much wide open to anything. HTML in JSON is a debated issue.

In short: Keep it small and neat :-)

More on Modal Pop Ups

2010-08-02T23:46:00.001+02:00

I previously wrote a post on how to use the new APEX 4.0 native features to conjure modal inline dialogs. It was followed by a brief (very brief on my part, to say the least) discussion on how to achieve the same functionality for a create button. In this post I will elaborate a bit on how to do exactly that.

The solution sketched out below definitely has potential for improvement, but can serve as a diving board for the interested.

Demo Application
If you are curious, or just down right bored with long posts, then I have a running copy of the demo application here: http://apex.oracle.com/pls/apex/f?p=45420:3. The demo application can be downloaded here: http://apex.oracle.com/pls/apex/f?p=45410:DOWNLOAD. To keep the examples as clean as possible, I have created a new page with the Create-button, so you have both alternatives available.

The Quick Solution
And what is wrong with a quick solution? Personally I feel the we programmers (me definitely included) are very good at complicating things. The rest of this post is dedicated to a more generic approach, but in my original comment I suggested this:

Create an anchor-tag (link) anywhere on the page
Set href attribute to the URL of your edit form page, let primary key item values remain blank (but be sure to pass them)
Set the id attribute to callModalDialog

In my sample application that would be: <a id="callModalDialog" href="f?p=&APP_ID.:4:&SESSION.::NO::P4_EMPNO:">Create Employee</a>.

This would work just like the edit links in my original example, but ready to insert a new employee. Like magic :-)

The Elaborate Solution
The elaborate solution uses more Dynamic Actions, a bit more javascripting and a couple of dirty tricks to accomplish the same as the quick solution. The goal is to make a normal create button to have the same behavior with inline modal dialog, as the edit links does.

The explanation below is based on the original modal dialog page in my sample application. To follow the example and repeat the steps, you need to start off with a copy of page 2 in my sample application.

To follow the example, start by creating a region button with the following values (mostly just accept defaults):
Button name: CREATE

The button should take you to the create/edit page, but without the fancy modal dialog.

A Note for Later
If you name your button to something else than Create (or Text Label/ALT-property for the page button in APEX), you must adjust the following JQuery selectors accordingly for it to work. You may also expect some issues when using an image button (the javascript onclick function extraction may fail).

Removing the Original onclick-Event
APEX uses a javascript function to redirect the browser (also when using anchor button template). There are two things that needs to be done when the page loads; store the original create link, and remove the original button onclick event. I use JQuery and javascript native regexp capabilities to achieve this.

To create a Dynamic Action which fires when the page has finished loading, do the following:
Create a new Dynamic Action at the page level

Choose Advanced

Give it a sensible name ("On Page Load")

Choose Event Page Load

Choose Action Execute Javascript Code, and paste in the javascript code below:

/* get original onclick event */
var origAction = $('button[value=Create]').attr('onclick').toString();
/* get link from original onclick event using regular expression */
var link = origAction.match(/(redirect\((\'|\"))([^\'\)|\"\)]*)/)[3];
/* Remove original onclick event */
$('button[value=Create]').removeAttr('onclick');
/* store link as title attribute of button */
$('button[value=Create]').attr('title', link);

Looks a bit Greek? Even if I have actually included comments? If you are not familiar with JQuery, it definitely will. If you are not familiar with regular expressions, even more so. If you are not familiar with javascript at all, you are allowed to test the code, but not use it in production unless you have truly understood what it means :-) There are very good sources on the web for all the knowledge required.

And why, oh, why store the link value in an element attribute definitely not meant to hold a link value!?! It will overwrite any existing title-values, and makes both setting and retrieving code hard to read. On the other hand, it will keep the value with it's element, and support more than one create button on any given page. There are more than one alternate way of doing this, but hey, feel free to bring suggestions :-)

Anyway, click Create and you are done.

If you run your page now, clicking the Create button will not do anything (the onclick event was removed, but not replaced).

That was the hard part!

Calling the Dialog
The edit dialog Dynamic Action is already in place, all that has to be done is to adapt the existing Dynamic Action to include the new create button.
Edit the Modal Dialog Dynamic Action
Include: button[value=Create] as the JQuery Selector expression (total expression will now be: a[id^=callModalDialog],button[value=Create])

Edit the True Action Javascript Expression to be:

/* prevent default behaviour on click */
var e = this.browserEvent;
e.preventDefault();
/* Find page link */
var link;
if (this.triggeringElement.tagName=='A') {
   link = this.triggeringElement.href;
} else if (this.triggeringElement.tagName=='BUTTON') {
   link = this.triggeringElement.title;
}
/* Trigger JQuery UI dialog */
var horizontalPadding = 30;
var verticalPadding = 30;
$('<iframe id="modalDialog" src="' + link + '" />').dialog({
   title: "Edit Employee",
   autoOpen: true,
   width: 700,
   height: 300,
   modal: true,
   close: function(event, ui) {apex.event.trigger('#P3_AFTER_MODAL','select',''); $(this).remove();},
   overlay: {
       opacity: 0.5,
       background: "black"}
}).width(700 - horizontalPadding).height(300 - verticalPadding);
return false;

The difference from the original javascript code is the extra bit extracting the link to use, which differs from a normal anchor element, and our button with the special id attribute.
Click Apply and you are done.

The Rest?
Is the same as detailed in my last post. I have updated the dialog page in the sample application to include Create and Delete buttons, but that is it.

Quick or Slow?
Quick sound promising! Simplicity rules! There are some draw-backs, of course. Like where to put the link to find it later (a Display Only item properly named perhaps?)? Like the need to style a link as a button? Like the need to place the link in a region template position? It all adds up.

The generic (more elaborate) approach will work with any create button (well, not with the anchor template). Drop the Dynamic Actions onto the page, and it will work. On the other hand, the generic approach involves more code, more code is harder to maintain and more prone to breaking. Generic code requires a delicate hand (ie takes time), and is generally harder to read than code created for a specific task. This is all in the eye of the beholder, but on more than one occasion I have had the pleasure of revisiting my own old code and though: I didn't need to do that... I digress, I know. Besides, that is too great a topic to just be delegated to a digression :-)

So, should you use the quick link (pun intended :-))? My answer (being a consultant) is a definitive: It depends!

Enjoy :-)

Quick Notes

2010-07-16T12:42:00.000+02:00

Just a bit of housekeeping.

APEX 4.0 Builder Hotkeys
This is just a quick note on the APEX 4.0 Builder Hotkeys. I have updated the script because not all previous/next buttons were included in the last version. Alt + PageUp/PageDown should now give the right response.

You can download and install the script here: http://userscripts.org/scripts/show/81058

Installation instructions can be found here: http://monkeyonoracle.blogspot.com/2010/07/apex-40-builder-hotkeys.html

Ye Olde Sample App
...is still working! I just went through the Application Upgrade and changed the theme while at it. I even updated the Google Maps example. ("Updated" sound so much better than "fixed a bug". Quick solutions will make you do that...) This application will probably not be updated anytime soon. There are a few more elaborate examples I had in mind, but then there is this "time"-thing all over again. Exploring APEX 4.0 is at the top of my spare time roster for now.

The application will be left alone for the time being, and can be found here: http://apex.oracle.com/pls/apex/f?p=28990:1

Summer Vacation
Finally, my last day of work before I start my summer vacation. One of the nicer benefits as a worker in Norway, is the length of paid vacation established by national law. I guess this is not so normal for Americans.

True story from the ODTUG Kaleidoscope this year. An American guy comes up to me with his friend, and after introductions the following conversation takes place:
Him: So, you're from Norway?
Me: Yep!
Him: Could you tell my colleague here how much paid vacation you guys have?
Me: Five weeks!
Friend: ... (going through the whole stunned jaw dropping improvised look)

Cheers :-)

Modal Pop Up with Dynamic Actions

2010-07-15T14:21:00.000+02:00

You have all seen it in the more "modern" web applications, pop-up windows displayed inline, and not in a separate window. With JQuery embedded into Oracle APEX 4.0, and JQuery UI equally at your disposal (at least in the new themes I have tested), creating this type of dialogs is well withing reach. The JQuery UI dialog is already in use in the application templates (the tool tip dialogs).

I wanted to use Dynamic Actions to create a modal pop-up dialog of APEX-pages, and this is what I came up with. It is not as streamlined as I would have liked it to be, but a big leap in the right direction nonetheless.

Demo Application
I have created a demo application which you can test here: http://apex.oracle.com/pls/apex/f?p=45420:2. You can also download the application here: http://apex.oracle.com/pls/apex/f?p=45410:DOWNLOAD.

Identifying Triggering Elements
The trick with the Dynamic Actions is again to identify which elements you would like to use to trigger the dialog. In my example I have a plain SQL-report, but I have edited the primary key column to display an edit icon, and under the Column Link properties, I have set Link Attributes to include id="callModalDialog#EMPNO#". This gives me the possibility to identify the links using a JQuery selector expression.

I created a Dynamic Action on the click event, with Selection Type JQuery Selector with value a[id^=callModalDialog]. This will identfy all items with attribute id starting with callModalDialog, which, incidentally, is the same id I used on my edit links in the report.

To ensure that the event triggers after refresh of the report, I set the Event Scope to live under Advanced (as opposed to bind, which is normal). This saves me the effort of re-binding the click-events when the report is refreshed.

Calling the Dialog
When the user clicks the edit links, I want to do two things: suppress the default browser behavior, and call the modal dialog. I achieve this by adding a true Action of type Execute Javascript Code.

This contains the javascript code to do both the things I want to do:

/* prevent default behavior on click */
var e = this.browserEvent;
e.preventDefault();
/* Trigger JQuery UI dialog */
var horizontalPadding = 30;
var verticalPadding = 30;
$('<iframe id="modalDialog" src="' + this.triggeringElement.href + '" />').dialog({
   title: "Edit Employee",
   autoOpen: true,
   width: 700,
   height: 300,
   modal: true,
   close: function(event, ui) {apex.event.trigger('#P2_AFTER_MODAL','select',''); $(this).remove();},
   overlay: {
       opacity: 0.5,
       background: "black"}
}).width(700 - horizontalPadding).height(300 - verticalPadding);
return false;

Some explanation for this is in order. The first part is just to prevent the default browser behavior (user click a ink, and the browser naturally wants to open that page). The second part is by far more complex, and contains all the code needed to start the JQuery UI dialog.

The edit link points to an APEX application page, and the code above invokes the page in an IFRAME, and uses the JQuery UI dialog to show the IFRAME inline in a modal dialog. I extract the link from the triggeringElement, set the title (this could also have been extracted from the triggeringElement), set various attributes for the dialog and IFRAME. In short the code above says open the HREF of the triggeringElement in an IFRAME, show the IFRAME in a modal dialog and open the dialog immediately.

Returning from the Dialog
If you notice the code above, the close configuration for the dialog looks quite complex. The reason for this is that I want to execute a Dynamic Action when the dialog is closed. There are two problems with this; (as far as I know) I cannot execute a Dynamic Action directly from Javascript, and I have nowhere to attach the Dynamic Event anyways. Write som javascript function you say? Yes I could do that, but the Dynamic Action is too good to pass up. Let us say that I want to refresh a report, set some item values and execute some PL/SQL when returning, with Dynamic Actions that is a walk in the park.

This is where I cheat :-)

The dialog does not exist when the page renders, so I have nowhere to attach my Dynamic Action to execute when returning from the dialog. My (kludgy) solution to this was to create a hidden item in the report region, and attach the Dynamic Action to the item. This way I can see all that happens in the Builder (easier to maintain), and if I give it sensible names, I can even discern what it does. In my case I created an item called P2_AFTER_MODAL, and attached a Dynamic Action called Refresh Report.

So, now I have defined what to do after returning, but how to trigger the event? And I cheat again... I attach the Dynamic Event to P2_AFTER_MODAL with the select event. The select event on a hidden item does not get triggered very easily. In effect this gives me a way to control that the Dynamic Action only executes when I programmatically tells it to. That is exactly what I tell it to when closing the dialog, using the APEX javascript API apex.event.trigger.

I also destroy the dialog after use, so I can create a new on the fly when the user wants to edit another employee, $(this).remove(); takes care of that (removing the IFRAME in effect, I could not get the dialog.destroy-method to work properly).

Closing the Dialog
When creating an APEX page that is to be a pop-up window, you need to take special care on the branching. In this case, you want to take care to close the dialog, both on cancel and after processing.

To handle cancel, let the cancel button redirect to URL, and set the URL target to: javascript:parent.$('#modalDialog').dialog('close');.

Notice the use of parent, the dialog is spawned by the original web page, and the dialog and its method belongs to the parent.

When branching after processing you have to be a bit more creative, create an unconditional branch to PL/SQL Procedure that runs the following code:

BEGIN
--closes this popup window
htp.p('<body>');
htp.p('<script type="text/javascript">');
htp.p('parent.$(''#modalDialog'').dialog(''close'');');
htp.p('</script>');
htp.p('</body>');
END;

This is in effect the same as the URL target behind the cancel button, but rendered by htp.p in PL/SQL.

Layout of the Dialog Page
In the sample application have have copied the Printer Friendly template, called it Modal Dialog, and used that as a page template for the dialog page. The modifications is basically just removing the navbar, and overriding the default "min-height" and "min-width" css attributes by explicitly setting style-attributes for the body-tag and div with id="body". This will vary, depending on the theme you are using. The goal is to strip the dialog of all unnecessary layout clutter.

Some Notes
I have created a few modal dialogs in the previous versions of APEX, and the javascripting involved is a pain. Maintaining the code even more painful. Even with the simple trick (or cheat, if you will) of attaching the Dynamic Action to an item in a declarative way, helps me getting control over the classic "what happens where, when and why".

Even though the basics is demonstrated in the sample application, there is a bit of work left. When you return from the dialog and refresh the report, you might want to take care to maintain the pagination, and if the user closes the dialog with cancel or window close, you might not want to refresh the report, etc., etc.

Enjoy :-)

APEX 4.0 Builder Hotkeys

2010-07-09T00:11:00.001+02:00

Until recently I only developed in Oracle APEX using Mozilla Firefox. Partly because of the add-ons (like Firebug), but most of all because of Patrick Wolf's Oracle APEX Builder Plugin. It can be really annoying at times to scroll and click to apply changes.

After upgrading to APEX 4.0, the APEX Builder Plugin stopped working. I tried a quick and dirty approach, and updated the config files to look for button-elements instead of input-elements, but that did not work.

Enter Google Chrome
I've been using this browser on and off for some time, and it is incredibly fast compared to Firefox (even without add-ons, I checked). I did a bit of research (sounds better than a quick google search :-)), and found that Chrome supports Greasemonkey scripts natively. How nice! If only I had a Greasemonkey script that worked, that is...

So, I had to make my own. It's an absolute first try, so don't hold your breath. I have probably made any number of rookie mistakes, feel free to correct me :-)

Greasemonkey?
Greasemonkey is a Mozilla Firefox add-on which can run scripts after page load, and do pretty much everything with it. Greasemonkey scripts are nothing but javascript with some "special" declarations at the beginning of the file, but runs in it's own sandbox.

Google Chrome understands and runs Greasemonkey scripts as add-ons (no install of Greasemonkey necessary). Chrome was my target after all.

APEX 4.0 Builder Hotkeys
This is nothing like the more sophisticated Builder Plugin from Patrick Wolf, I have only aimed at the dearly missed hotkeys (or shortcut keys if you will).

Hotkeys implemented so far:

F8 - Run page
F9 - Apply Changes
Alt + F9 - Click the orange button
Alt + PageUp - Click button named "<" or "< Previous"
Alt + PageDown - Click button named ">" or "Next >"

Install
Installation instructions for both Chrome and Firefox:

Point browser to http://userscripts.org/scripts/show/81058
Click green Install-button to the right
Click Install in the dialog (In Chrome: Click Continue at the bottom of the page, and then Install in the dialog)

Firefox users have to install Greasemonkey before installing the script above. I have hosted the script at userscripts.org. Have a look around while you are there, the number of scripts (and the crazy things people do to web pages) is just staggering.

Uninstall
Chrome:

Point the browser to chrome://extensions/
Click Uninstall under the "Oracle APEX 4.0 Builder Hotkeys" extension.

Firefox:

Right click the little monkey in the lower right corner of the window
Choose Manage User Scripts
Click "Oracle APEX 4.0 Builder Hotkeys" on the left
Check "Also uninstall associated preferences"
Click Uninstall

Some Notes
I had to revert to JQuery 1.3.2 to make it play nice with Greasemonkey. I know there are ways make it work for 1.4.x, but I am not using JQuery to it's full potential (only simple selectors and click-events). I didn't even bother trying. I also had to include the whole script to make it work with Chrome (no external references).

There might be some problems with namespacing and JQuery conflicts, but I have encountered none so far.

Feel free to modify the scripts, or to make enhancement requests (hey, I might even do something about them. It could happen! Really!)

So, will I retire Firefox now? Nope, Firebug, YSlow, WebDeveloper, etc. are all good reasons not to. I am not that comfortable with the Chrome developer tool.

Dynamic Actions

2010-07-07T09:53:00.008+02:00

One of the killer features of Apex 4.0 are Dynamic Actions. What a fantastic way to declaratively include JQuery and Ajax events into your application! I never really enjoyed placing javascript calls all over the place to achieve the rich functionality in the earlier versions of Apex. Too much work, and too hard to maintain.

Now you have Dynamic Actions, and all the little knobs and wheels show up in the page editor as any other kind of objects. The Apex-team has done a marvelous job of integrating JQuery into the framework.

How Dynamic are Dynamic Actions?
As I upgrade applications I want to get rid of all the little Javascript function calls that are all over, and replace them with Dynamic Actions. As I go along, there are a few challenges, but no show stoppers (yet). Below is a description on one of the hurdles (well, more like a small bump) passed along the way.

I find that Dynamic Actions are extremely versatile, but you should have some knowledge of JQuery selectors to realise it's full potential.

Invoking Dynamic Actions from Report Columns
In the editor there are no possibility of attaching dynamic actions to tabular forms columns. I wanted to invoke an Ajax-call to commit changes when the user changed a select list in a tabular form, here is a way you can do the same.

If you just want to inspect the code for yourself, I have created a small application you can download from apex.oracle.com: http://apex.oracle.com/pls/apex/p?n=1070334102765726226

First off, create a tabular form, in this example I have a select that uses the APEX_ITEM-api to create application items:

select apex_item.hidden(1, emp.empno) empno
,      apex_item.display_and_save(2, emp.ename) ename
,      apex_item.select_list_from_query(3, emp.deptno, 'select dname d, deptno r from dept order by dname', p_item_id => 'empno'||empno) deptno       
from   emp

Next, create two hidden items, one for the primary key (P1_EMPNO), and one for the changed column value (P1_DEPTNO).
Next, create a Dynamic Action on the page level to invoke the update statement:

Right click Dynamic Actions on the page level, and choose Create

Choose Advanced

Give the Dynamic Action a name

Set the event to trigger on Change, set Selection Type to JQuery Selector and set JQuery Selector to "select[id^=empno]". This is where we set up the Dynamic Action to fire on every change to all items where the item ID starts with "empno". If you notice the select statement above, I explicitly set the item ID when creating the select lists.

Now I have set up when the event is supposed to trigger, next I define what is actually going to happen once the Action is triggered.

Set the Action to Execute PL/SQL Code, and the PL/SQL Code to the update statement below.

update emp
   set deptno = :P1_DEPTNO
 where empno = :P1_EMPNO;

In addition you must set Page Items to Submit with the request (P1_DEPTNO and P1_EMPNO):

Click create and you are almost done.

Now the Dynamic Action is defined in essence, but there is one piece missing: When the user changes the values of the select list, the new DEPTNO-value and the current EMPNO must be reflected in P1_DEPTNO and P1_EMPNO before the PL/SQL-Code (in this case the update statement) is invoked. This is where the pure genious of the Dynamic Actions come into play; Apex allows you to create additional Actions to execute in the same event.

To set the values of P1_DEPTNO and P1_EMPNO, do the following:

Open the editor for the Dynamic Action

Under True Actions region, click Add True Action

Set the sequence to something lower than the PL/SQL-action (normally this means setting it below 10)
Set the Action to Set Value

Make sure the Fire on Page Load is unchecked
Set Set Type to Javascript Expression
Set Javascript Expression to this.triggeringElement.value, which basically means get the value of the triggering element
Under Affected Elements, set Selection Type to Item(s) and Item(s) to P1_DEPTNO

Now we have to do the same for P1_EMPNO, this almost the same as for P1_DEPTNO, but we have to get the EMPNO-value from the ID of the triggering element. To achieve this, Click Add True Action, and set it up as seen below:

The Javascript code gets the ID of the triggering element, and strips off the leading empno-string. The remainder is the actual EMPNO of the employee affected.

When you return to the Dynamic Actions editor, this is what the True Actions should look like:

Run the page to check that it works.

A Word of Caution
This was a special and simplified case, normally you should use the native tabular forms functionality in Apex. There are several reasons for this. There is no MD5-checking here (data has been changed by another user), and every little change will result in a round trip to the server (no batch submit and no way for user to cancel changes).

Either way, Dynamic Actions rock!

ODTUG Kaleidoscope 2010

2010-07-05T15:26:00.000+02:00

I attended the ODTUG Kaleidoscope this year. First off; this conference is truly a developers conference. The amount of knowledge shared (both within and outside sessions) is immense. The sheer number of profiles in Oracle development community speaks for itself. In short, if you are a developer in Oracle products, this is the conference for you.

The Goal
This year my primary focus was middle tier development, and ADF in particular. Being a consultant, you jump when customers says toad. While I remain a fan of APEX, it is part of my job to keep abreast with the alternatives.

ADF
The Oracle Application Development Framework (ADF) has been pushed by Oracle quite hard for a number of years. The market adoption has been a bit on the slow side (to say the least). That being said, I think it is one of the best frameworks out there based on Java to develop rich internet applications.

I think ADF sort of lives in a limbo; Traditional Java developers will not touch it (it's Oracle), and traditional (database) developers will not touch it (it's Java). It is not as simple as that, obviously, and it is being used out there, just not to the extent I expected.

ODTUG and ADF
I think Kaleidoscope reflects the limbo-effect, both in content and interest. Java developers obviously has other arenas, database developers are, well, database developers. I get that it is hard to combine the two, but look at the adoption of the conference by Essbase and Hyperion developers! They have really swarmed to the conference.

So, what did I get out of it? Related to ADF, not as much as I would have liked. There still seems to be considerably technical issues if you move out of the pure ADF sphere (like the SOA-suite). ADF on the other hand has matured considerably, and creating a slick looking, efficient application requires much less effort than it was in my last ADF project on 10g.

I still miss more to-the-point sessions on how to attack large implementations (like Andrejus Baranovskis).

The Meat
What really gave me value was the sessions by the big guns; Cary Millsap, Jonathan Lewis and Steven Feuerstein. Worth the trip alone to hear any of those speaking :-)

CKEditor Image Browser - APEX Style

2010-03-29T15:51:00.001+02:00

FCKEditor has been used with Oracle APEX for a long while, in APEX terms it is known as HTML Editor Standard and HTML Editor Minimal items. In APEX 4.0 it looks like the new CKEditor is included, called Rich Text Editor item. It actually looks like you are given a choice between the old FCKEditor and the new CKEditor, which is a nice touch for those that have customized FCKEditor in earlier versions.

If you already have FCKEditor configured with image browsing in you current install, I would not sweat through integrating CKEditor unless you have good reason. Editors are known to change, and this particular will be included in APEX 4.0.

The problem with CKEditor native image browser is the default parameters it includes when launching a pop-up window. Additional parameters does not combine very well with APEX, to say the least. In this post I will give an example on how to adapt the native CKEditor image editor to include your own APEX image browser. This is similar to the earlier article by Carsten Czarski found here in German, or a translated version here. I will not detail how to create your own image browser, but focus on how to change the CKEditor.

Beware: I am not a Javascript expert. I am not a CKEditor expert. This is basically just a documentation of my experience of adapting CKEditor.

Prerequisites
To follow the example you should:

Download and extract ckeditor_3.2.zip to a folder named ckeditor
Download ckpackager.exe and place it under the ckeditor directory
Map the ckeditor directory in your webserver so it can be reached with a web browser. I will use "/js/ckeditor" in this example.

CKEditor comes complete with examples, source code and documentation (albeit a bit lacking). CKPackager is not as streamlined, but browsing around the subversion repository you get the general idea. In short CKPackager takes care of putting together ckeditor.js and ckeditor_basic.js after you have fiddled with the source code. I chose the exe-version of CKPackager as I do my development in Windows, but there is a jar-file available as well.

Create an APEX page to serve as an image browser/picker (it does not have to do anything yet, we'll get to that).

Give the page an alias: IMAGE_PICKER
Create an APEX page with a text area item
Put the following code in the HTML-header of the APEX page:

<script type="text/javascript" src="/js/ckeditor/ckeditor.js"></script>
   <link href="/js/ckeditor/bouvetckeditor.css" rel="stylesheet" type="text/css" />

Put the following code in the Post Element Text of the text area item:

<script type="text/javascript">
   //<![CDATA[
      // Replace the <textarea id="editor"> with an CKEditor
      // instance, using default configurations.
      CKEDITOR.replace( 'P12_TEXT',
                        {filebrowserImageBrowseUrl : 'f?p=&APP_ID.:IMAGE_PICKER:&SESSION.::NO::',
                        width: '500'
                        });
   //]]>
   </script>

Replace "/js/ckeditor" to match your own configuration
Replace "P12_TEXT" to match the item name of your text area item.

The Problematic URL
You should now be able to run the page described above, and have CKEditor magically appear where your text area item was supposed to be. When you click the image button in the CKEditor toolbar, the image dialog appears, but when you click the "Browse Server" button, the fun begins. The pop-up window spawns with an URL that has three extra parameters attached.

CKEditor which contains the item name
CKEditorFuncNum which contains som mystic numeric ID
langCode which contains the language code set for the CKEditor instance

APEX will blow up when this URL is tried (as f has no parameters matching the three). Actually APEX won't blow, because f will never be reached as mod_plsql won't recognize any procedure with the signature required. You get the idea.

Changing CKEditor Source Code
To adapt the URL to APEX style, you must get your hands dirty with Javascript. Actually I think the code of CKEditor looks quite clean, it's just so much of it :-) The source code is located in the "_source" directory, open the file "ckeditor/_source/plugins/filebrowser/plugin.js" in your favourite Javascript editor (or TextPad in my case).

In this example I will keep the original parameters (and parameter names), but if you want to change them, they are located in the browseServer function.

The addQueryString function accepts the URL and parameters, and builds the complete URL. Change the function to look something like this:

function addQueryString( url, params )
        {
                var paramString = [];
                var queryString = [];

                if ( !params )
                        return url;
                else
                {
                        for ( var i in params ) {
                                paramString.push( i );
                                queryString.push( encodeURIComponent( params[ i ] ) );
                        }
                }

                return url + paramString.join( "," ) + ":" + queryString.join( "," );
        }

The function will now add the parameter names before the colon symbol, and the parameter values after. All in the same order, all separated by a comma. APEX style :-)

Re-Packing CKEditor
Changing the code is not enough, your changes have not reached ckeditor.js yet. This is what we need the CKPackager for. It collects the source, and builds a new ckeditor.js based on the current source.

Start a command line window and browse to the ckeditor directory
Execute CKPackager like this: "CKPackager.exe ckeditor.pack -v" (or: "java -jar CKPackager.jar ckeditor.pack -v")
A new version of ckeditor.js and ckeditor_basic.js will be generated for you

ckeditor.pack contains reference to the files to be included into the ckeditor.js and ckeditor_basic.js files. The switch -v will give a verbose execution.

Reload the APEX page containing the CKEditor item (a proper reload to ensure the new version of the ckeditor.js file is loaded), and this time the image picker APEX page should pop-up, so to speak.

Returning From APEX Pop-up Window
The only thing missing now is the value to return to the CKEditor image dialog. This requires som additional code in your Apex application:

First create three application level items (unrestricted) named:

CKEDITOR
CKEDITORFUNCNUM
LANGCODE

These items will contain the parameters recieved from CKEditor.

Next, modify your image picker APEX page:

Create a new submit button on the page, and name it ADD_IMAGE, accept the rest as defaults.
Create a new After Submit Page Process, type PL/SQL, and call it close_popup
Paste the code below into the PL/SQL Process code area:

declare
      l_file_url varchar2(4000);
   begin
      l_file_url := '/path/of/image/imagename.jpg';
      htp.p('<body>');
      htp.p('<script type="text/javascript">');
      htp.p('window.opener.CKEDITOR.tools.callFunction( '||:ckeditorfuncnum||', '''||l_file_url||''');');
      htp.p('window.close();');
      htp.p('</script>');
      htp.p('</body>');
   end;

This bit of code will call the appropriate CKEditor return function, and close the pop-up window. Modify the code to suite your needs, but beware not to put any code below the last htp.p-call.

Testing the Code
To test the code so far:

Run the page containing the CKEditor item.
Click the image button in the CKEditor toolbar
Click the Browse Server button
A pop-up window with your image picker APEX page should now appear
Click the Add Images button on your APEX page, and the CKEditor image dialog URL should now reflect the URL set from your APEX image picker page

Modifying CKEditor Image Browser Parameters
So far you have a complete working example with CKEditor image browser and Oracle APEX, but what if you want to modify the parameters themselves? Earlier you modified the function assembling the complete URL based on the defatult parameters, to change the parameters you must modify the browseServer-function in the same file as before ("ckeditor/_source/plugins/filebrowser/plugin.js"). The only parameter the CKEditor really needs when returning image source, is the image source (duh) and CKEditorFuncNum.

Here is an example of the browseServer-function when parameters CKEditor and langCode is removed, and the current APEX-page id added as a parameter called "callingPage":

function browseServer( evt )
   {
      var dialog = this.getDialog();
      var editor = dialog.getParentEditor();

      editor._.filebrowserSe = this;

      var width = editor.config[ 'filebrowser' + ucFirst( dialog.getName() ) + 'WindowWidth' ]
          || editor.config.filebrowserWindowWidth || '80%';
      var height = editor.config[ 'filebrowser' + ucFirst( dialog.getName() ) + 'WindowHeight' ]
          || editor.config.filebrowserWindowHeight || '70%';

      var params = this.filebrowser.params || {};
      params.CKEditorFuncNum = editor._.filebrowserFn;
      
      // New parameter to get APEX pageId
      params.callingPage = $v('pFlowStepId');

   var url = addQueryString( this.filebrowser.url, params );
      editor.popup( url, width, height );
   }

To generate a new version of CKEditor, use the CKPackager as described earlier in the post. The parameters in the URL uses the same (APEX friendly) construct as before (pName,pName:pValue,pValue), but the number and names of the parameters have changed. The new pop-up URL should now look something like this: "http://<yourserver>/pls/apex/f?p=<app_id>:IMAGE_PICKER:<sessionid>::NO::CKEditorFuncNum,callingPage:<X>,<pageid>".

A Word of Caution
When hacking the source like this, and not making your own plugin, guess what happens when you need to upgrade to the next version of CKEditor? When you push the toothbrush too far back, you have to start all over again.

This hack is in the filebrowser-plugin, which in turn is used by the Flash- and Link-dialogs, so pay attention to changes there as well.

Not as sleek as I would like it to be, but if moving to CKEditor is on your list, this is a way to achieve just that.

As always, use it at your own risk!

Enjoy :-)

DOCX Part II: How to Index Document Content With Oracle Text

2010-03-02T12:35:00.000+01:00

Here I will demonstrate how to index content of CLOB data using Oracle Text. Other than the database objects used in the example, the post addresses the use of Oracle Text in a general way, and will work for any CLOB column. There is no ground breaking code here, but it completes the example on how to handle Microsoft Open Office XML Format (DOCX) documents. The examples and content here are based on my previous post.

The code here is tested with Oracle database release 11.1.0.6, but should work with releases down to 10g. When I say "tested", remember I am a developer, so it basically means it does compile and it did give the expected result on one run trough...

See it in Action
I have included a search page in my demo application. Upload a DOCX document, and try it for yourself: Go to demo application.

About Oracle Text
Oracle Text (formerly Oracle interMedia) is Oracles full-text retrieval technology, and part of Standard, Enterprise and even XE editions of the database. See Oracle Text on oracle.com for more information.

In my case I will use it to index text content stored in a CLOB column, and utilize it's search capabilities to enable users to retrieve relevant results based on search phrases.

Indexing CLOBs
Or BLOBs, or BFILEs, for that matter. It is very easy to get up and running with a text index. The following code is all that is needed to get a text index up and running against the TEXT_CONTENT column of the SAMPLES_DOCX table:

create index samples_docx_ctx on samples_docx
(text_content)
indextype is ctxsys.context
/

That's it! If you want to get fancy, there are a number of options and ways to enhance the indexing, look at the documentation for more information on the subject (lexers, sections, stopword lists, etc.).

Searching with Oracle Text
With the text index in place, the next step is to use the index to retrieve search results. In my example I will use a technique called "Progressive Relaxation" as described by Roger Ford here. It uses a query template to implement a progressive relaxation of the search tokens.

The following script creates a package to handle and use the new text index:

create or replace package samples_docx_search_p
as
   -- result types
   type t_result_rec is record (
      score         number,
      filename      samples_docx.filename%type,
      snippet       varchar2(4000));
   type t_result_tab is table of t_result_rec;
   -- synchronize search index
   procedure sync_search_index (
      p_ctx_index_name     in          varchar2 default 'SAMPLES_DOCX_CTX'
   );
   -- search docx content
   function search_text (
      p_tokens    in          varchar2
   ) return t_result_tab pipelined;
end;
/

create or replace package body samples_docx_search_p
as
   -- synchronize search index
   procedure sync_search_index (
      p_ctx_index_name     in          varchar2 default 'SAMPLES_DOCX_CTX'
   ) is
   begin
      ctx_ddl.sync_index(p_ctx_index_name, '2M');
   end;
   -- search docx content
   function search_text (
      p_tokens    in          varchar2
   ) return t_result_tab pipelined
   as 
      l_max_rows         integer := 10;
      l_counter          integer := 0;
      l_ret_rec          t_result_rec;
      l_tokens           varchar2(4000) := lower(p_tokens); 
      l_query_template   varchar2(32000):='<query>'
                              ||chr(10)||'   <textquery> heregoesthetokens'
                              ||chr(10)||'     <progression>'
                              ||chr(10)||'       <seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>'
                              ||chr(10)||'       <seq><rewrite>transform((TOKENS, "{", "}", "AND"))</rewrite>/seq>'
                              ||chr(10)||'       <seq><rewrite>transform((TOKENS, "?{", "}", "AND"))</rewrite></seq>'
                              ||chr(10)||'       <seq><rewrite>transform((TOKENS, "{", "}", "OR"))</rewrite></seq>'
                              ||chr(10)||'       <seq><rewrite>transform((TOKENS, "?{", "}", "OR"))</rewrite></seq>'
                              ||chr(10)||'     </progression>'
                              ||chr(10)||'   </textquery>'
                              ||chr(10)||'</query>';
      l_query            varchar2(32000);
   begin
      if l_tokens is null
      then
         return;
      end if;
      -- create query temlpate based on tokens
      l_query_template := replace(l_query_template, 'heregoesthetokens', l_tokens);
      -- restructure tokens for use with snippet
      l_tokens := replace(l_tokens, ' ', ' | ');
      for c in (select rowid
                     , filename 
                  from samples_docx 
                 where contains (text_content, l_query_template, 1) > 0)
      loop
         -- crude custom score, just listing the results as they come
         l_ret_rec.score := l_counter;
         l_ret_rec.filename := c.filename;
         -- create snippet (with tokenbased highlight) of content to return to the user
         ctx_doc.set_key_type('ROWID');
         l_ret_rec.snippet :=  ctx_doc.snippet('SAMPLES_DOCX_CTX', c.rowid, l_tokens, '<b>', '</b>', false);
         l_counter := l_counter + 1;
         -- return row
         pipe row (l_ret_rec);
         -- exit when max number of rows exceeded
         exit when l_counter >= l_max_rows;
      end loop;
   end;
end;
/

Some parts of the code are more cryptic than others.

First up is the "SYNC_SEARCH_INDEX" procedure. It's sole purpose is to synchronize the search index with the TEXT_CONTENT column. When you perform DML on the TEXT_CONTENT column, changes to the text index does not automatically propagate to the search index (unless the index is explicitly told to do so). So, in this example, if you insert a new row, the TEXT_CONTENT will not show up through Oracle Text searches until you have told it to synchronize the index. Indexing can be a resource demanding operation, so it makes sense to separate table DML from the indexing job.

In short: Make sure you synchronize the text index after DML on the indexed column has been performed.

Next is the "SEARCH_TEXT" function, particularly the query template. In the past I have used Oracle Text in its most simple form, but query templates adds a new dimension to controlling your searches. Basically you define how you want to use the search tokens (search phrase) to find matches in the text index. It is written in XML-form, and the <seq>-tag states the order of which you want to process your criteria. For more information on query templates, take a look at the documentation.

A short explanation on the query template:

<rewrite>transform((TOKENS, "{", "}", " "))</rewrite>: Matches when whole phrase (tokens in order) is present within document
<rewrite>transform((TOKENS, "{", "}", "AND"))</rewrite>: Matches when each token is present within document
<rewrite>transform((TOKENS, "?{", "}", "AND"))</rewrite>: Matches when each token is present within docoument, allowing typos (fussy search)
<rewrite>transform((TOKENS, "{", "}", "OR"))</rewrite>: Matches when any of the tokens are present within document
<rewrite>transform((TOKENS, "?{", "}", "OR"))</rewrite>: Matches when any of the tokens are present within document, allowing typos (fussy search)

Although it will return matches according to the sequence, the "score" within the sequence will be random (as far as I could see at least), so the "score" column in the example above needs a bit of work. I also learned the hard way that structure of the XML in the query template is not validated, and it swallows typos without a word.

Test the Code
You can test the code directly from SQL*Plus or your favorite weapon of choice. The following code will search for "test":

select filename
,      snippet
from   table(samples_docx_search_p.search_text('test'))
order  by score
/

Wildcards
My query template does not support searching for parts of words, there are some reasons for this behavior.

Wildcard searches requires more resources
The results returned will be unpredictable. Imagine searching for "ora*", both "oracle" and "orange" will match.
Wildcard searches are more suitable for look-ups in structured data

What did I mean by that last statement? As I see it, users today are using a wide range of search engines and site searches every day, and wildcard matching are in most cases available only through the "advanced search"-link (that is rarely used, if ever). Let alone that people do not expect this kind of behavior anymore. If you want to help the user, add ajax autocomplete to your search field, and use the Oracle Text index. In the traditional back office application, it is another matter (look-ups and such).

Create a Search Page in Oracle APEX
I will not do a detailed explanation here, but creating a report based on a query will do the trick. If you choose "Yes" for "Enable search" in the wizard it will save you some work by creating the search dialog on the page. Clean up the query in the report region so that tour query looks something like this:

select filename
,      snippet
from   table(samples_docx_search_p.search_text(:PXX_REPORT_SEARCH))
order  by score

When keeping the order by, I assume that you will let the score column determine the result order. For good measure you can create a row template for the result representation in the APEX report so you can have heading and snippet spread over two rows.

Update Sample Upload Page
If you created the upload page described in my previous post, include a new page process (After Submit, and after the "extract_text"-process). The process should be of PL/SQL-type, and the code should look something like this:

samples_docx_search_p.sync_search_index;

It does not necessary have to be a page process, but it is important to synchronize the text index after DML.

How to Handle DOC and DOCX
...And PDF, XLS, PPT, etc. Well, the DOCX is the exception here. Oracle Text does not understand the format yet (unless you are running on release 11.1.0.7 or higher). If you create an Oracle Text index on a BLOB column containing other file types which it does understand, they will be indexed automagically. If you want to index the content of a DOCX document in the same index, use the technique described in my last post, convert the output (plain text) into a BLOB, and insert the BLOB into the table with the other documents. Or said in a different way: you except and handle DOCX documents differently than the known document types.

Querying an Oracle Text index works the same for BLOB and CLOB columns.

There is definitely room for improvement in the code, but it serves well enough for demonstration purposes.

Enjoy :-)

DOCX Part I: How to Extract Document Content as Plain Text

2010-03-01T15:48:00.000+01:00

In this post I will demonstrate how to extract plain text from Microsoft Open Office XML Format (DOCX) documents stored as a BLOB data type in an Oracle database. It is part one of two posts concerning how to extract and index unstructured content stored in DOCX files. I found snippets of solutions several places, but no complete copy-paste solution, so perhaps this will save some of you a bit of work.

The solution is tested on Oracle database Enterprise Edition release 11.1.0.6 with Apex 3.2.1 running on Windows XP, but should work down to 10g Standard Edition (not Oracle XE, since part of the code is in Java).

See it In Action
I have updated my demo application with a couple of pages so you can try it out yourselves: Go to demo application. There is a limited space available at apex.oracle.com, so please take care when uploading (and it is very easy to test in your own environment).

When will You Ever Need This?
If you have an Oracle database version prior to 11.1.0.7, Oracle Text is not able to index DOCX documents (as it does DOC documents). From version 11.1.0.7 and on, Oracle uses technology from Stellent, and DOCX is indexed as the other formats. So if you want to index DOCX text content in Oracle Text in a version prior to 11.1.0.7, then this could be a way to do it.

Oracle is working on a back-port to 10.x, but I have no status when (if) this will be available. Microsoft Office 2007 has been around since.. Take a wild guess! So these things obviously takes some time.

About Microsoft Open Office XML Format
As seen from the eyes of a PL/SQL developer, that is. First of all, XML sounds promising, you can do a ton of things with a valid XML in an Oracle database. You get the first nasty surprise when opening a DOCX-document in your favorite text editor; it is a zip archive! The next is when you realize that utl_compress can't help you uncompressing it either.

So Google next, and realizing this cannot be easily done in a pure PL/SQL solution, Google yields this gem from Ted Neward. It is a DOCX (Open XML) walk through as seen from the eyes of a Java developer. Very educational.

How to Unzip in PL/SQL
You probably can, but that would probably also involve a lot of work. The easy way is to take hold of a Java method that already does what you want. After searching the net I came up with this post from peterv6i which does exactly what I want (and more, it can also add files (BLOB) to an existing zip archive).

My short version of this (as I only need to extract content), follows below. The script creates a java class with a method to extract a file from a zip archive, and a PL/SQL wrapper to the getFileFromZip method. The database user must have JAVA_DEPLOY and JAVAUSERPRIV (I think) roles.

create or replace java source named "ZIPImpl" 
AS 
import java.io.*; 
import java.util.zip.*; 
import java.sql.*; 
import oracle.sql.*; 
public class ZIPImpl 
{ 
public static void getFileFromZip(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob[], java.lang.String name) { 
try { 
   OutputStream outBuffer = dstBlob[0].getBinaryOutputStream(); 
   InputStream inBuffer = srcBlob.getBinaryStream(); 
   ZipInputStream zip = new ZipInputStream(inBuffer); 
   ZipEntry entry; 
   byte[] tmpBuffer = new byte[2048]; 
   while((entry = zip.getNextEntry()) != null) { 
      if (entry.getName().compareTo(name)==0) { 
         int n; 
         while ((n = zip.read(tmpBuffer)) >= 0) 
           outBuffer.write(tmpBuffer, 0, n); 
      } 
   } 
   outBuffer.close(); 
 } 
 catch (SQLException e) { 
   System.err.println(e); 
 } 
 catch (IOException e) { 
   System.err.println(e); 
 } 
} 
}; 
/

alter java source "ZIPImpl" compile 
/      

create or replace package zip as 
   procedure unzip( 
      p_src       in          blob
   ,  p_dst       in out      blob
   ,  p_filename  in          varchar2); 
end; 
/

create or replace package body zip as 
   procedure unzip( 
      p_src       in          blob
   ,  p_dst       in out      blob
   ,  p_filename  in          varchar2) 
   as language java 
   name 'ZIPImpl.getFileFromZip(oracle.sql.BLOB, oracle.sql.BLOB[], java.lang.String)'; 
end; 
/

How to Extract Plain Text Content from DOCX
So, now you have a procedure to extract a file from a zip archive, next is to attack the content of the file. The DOCX file consists of several files, but the text content of the document resides in "word/document.xml". As the file is an XML document, I will perform an XML/XSL transformation of the file to be left with only plain text.

The script below creates a table SAMPLES_DOCX which will hold the original DOCX file stored as a BLOB, and a CLOB containing the plain text. It also creates a package with three procedures:

Store the DOCX (as uploaded into APEX_APPLICATION_FILES)
Download the original file (strictly not necessary for this exercise, but nice to have all the same)
Extract and store plain text from DOCX

The XSL used to transform the document.xml file is an (extremely) abbreviated version of the XSL posted here in the Oracle Technical Forums.

create table samples_docx (
   filename       varchar2(255) not null
,  mime_type      varchar2(255)
,  orig_file      blob
,  text_content   clob)
/

alter table samples_docx add constraint samples_docx_pk primary key (filename)
/

create or replace package sample_docx_p as
   -- get file from apex_application_files and store it in samples_docx table
   procedure store_docx (
      p_file_name    in       varchar2);
   -- download procedure for original docx file
   procedure retrieve_docx (
      p_file_name    in       varchar2);
   -- extract plain text from docx file (this is the meat)
   procedure store_text (
      p_file_name    in       varchar2);
end;
/

create or replace package body sample_docx_p as
   -- get file from apex_application_files and store it in samples_docx table
   procedure store_docx (
      p_file_name    in       varchar2)
   is
      l_file         blob;
      l_mime_type    apex_application_files.mime_type%type;
      l_name         apex_application_files.name%type;
   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 samples table
      insert into samples_docx ( filename
                               , mime_type
                               , orig_file)
       values   (   l_name
                  , l_mime_type
                  , l_file);
      -- delete file from apex files when done
      delete from   apex_application_files
           where   name = p_file_name;
   end store_docx;
   -- download procedure for original docx file
   procedure retrieve_docx (
      p_file_name    in       varchar2)
   is
      l_file         blob;
      l_mime_type    apex_application_files.mime_type%type;
      l_name         apex_application_files.name%type;
      l_size         number;
   begin
      -- get file from apex files
      select  filename
            , mime_type
            , orig_file
            , dbms_lob.getlength(orig_file)
       into   l_name
            , l_mime_type
            , l_file
            , l_size
       from samples_docx   
      where   filename = p_file_name;
      -- return file
      owa_util.mime_header( nvl(l_mime_type,'application/octet'), false);
      htp.p('Content-length: ' || l_size);
      htp.p('Content-Disposition:  attachment; filename="'||replace(replace(substr(l_name,instr(l_name,'/')+1),chr(10),null),chr(13),null)|| '"');
      owa_util.http_header_close;
      wpg_docload.download_file(l_file);
   end retrieve_docx;
   -- perform xsl tranformation of document.xml
   function get_text (
      p_docx_xml     in       xmltype
   ) return clob
   is
   l_clob   clob;
   -- xsl monkeyed from http://forums.oracle.com/forums/thread.jspa?messageID=3368284
   -- abbreviated quite a bit, check out original posting by "user304344" for the original
   l_xsl    xmltype := xmltype('<?xml version="1.0" encoding="utf-8"?>'
                     ||chr(10)||'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" '
                     ||chr(10)||'xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"'
                     ||chr(10)||'xmlns:v="urn:schemas-microsoft-com:vml"'
                     ||chr(10)||'exclude-result-prefixes="w v">'
                     ||chr(10)||'<xsl:output method="text" indent="no" encoding="UTF-8" version="1.0"/>'
                     ||chr(10)||'<!-- document root -->'
                     ||chr(10)||'<xsl:template match="/">'
                     ||chr(10)||'<!-- root element in document --> '
                     ||chr(10)||'<xsl:apply-templates select="w:document"/> '
                     ||chr(10)||'</xsl:template>'
                     ||chr(10)||'<!-- ****************************start document**************************** -->'
                     ||chr(10)||'<xsl:template match="w:document">'
                     ||chr(10)||'<xsl:for-each select="//w:p">'
                     ||chr(10)||'<xsl:apply-templates select="*/w:t"/> '
                     ||chr(10)||'<xsl:text>|¤¤</xsl:text> '
                     ||chr(10)||'</xsl:for-each> '
                     ||chr(10)||'</xsl:template>'
                     ||chr(10)||'<!-- get all text nodes within a para -->'
                     ||chr(10)||'<xsl:template match="*/w:t">'
                     ||chr(10)||'<xsl:value-of select="."/>'
                     ||chr(10)||'</xsl:template>'
                     ||chr(10)||'<!-- **************************** end document**************************** -->'
                     ||chr(10)||'</xsl:stylesheet>');
   begin
      -- "|¤¤" is just a hack to get linebreaks, should be an easier way to achieve this
      select replace(xmltransform(p_docx_xml, l_xsl).GetClobVal(), '|¤¤', chr(10))
        into l_clob
        from dual;
      return l_clob;
   end;   
   -- extract plain text from docx file (this is the meat)
   procedure store_text (
      p_file_name    in       varchar2)
   is
      l_docx            blob;
      l_docx_unzip      blob;
      l_doc             clob;
      l_dest_offset     integer := 1;
      l_src_offset      integer := 1;
      l_lang_context    integer := dbms_lob.default_lang_ctx;
      l_warning         integer;
   begin
      -- get original file
      select orig_file
        into l_docx
        from samples_docx
       where filename = p_file_name;
      -- create lob locators
      dbms_lob.createtemporary(l_docx_unzip,false);
      dbms_lob.createtemporary(l_doc,false);
      -- use java to unzip the docx file and retrieve document.xml
      zip.unzip(l_docx, l_docx_unzip, 'word/document.xml');
      -- convert blob to clob
      dbms_lob.converttoclob
               ( dest_lob => l_doc
               , src_blob => l_docx_unzip
               , amount => dbms_lob.lobmaxsize
               , dest_offset => l_dest_offset
               , src_offset => l_src_offset
               , blob_csid => nls_charset_id('AL32UTF8') --in my case, it is stored as UTF8
               , lang_context => l_lang_context
               , warning => l_warning
               );
      -- transform clob via xsl to get clean text
      l_doc := get_text(xmltype(l_doc));
      -- update the column containing document text
      update samples_docx
         set text_content = l_doc
       where filename = p_file_name;
      -- clean lob locators, should be repeated in exception block
      dbms_lob.freetemporary(l_docx_unzip);
      dbms_lob.freetemporary(l_doc);
   end;
end;
/

Bringing it Together in Oracle APEX
To test the code, you can create a simple upload page in APEX:

Create new page
Form
Form on a procedure
<schema name> where you installed the application
Choose "sample_docx_p.store_docx" as stored procedure name
Accept defaults (or change to your liking)
Choose desired tab options
Leave Invoking Page/Button Label blank if you do not want an invoking page
Choose branch pages (can be the same as the one you are creating)
Accept defaults
Click Finish
Click Edit Page
Choose PXX_FILE_NAME, and change "Display as" from "Text item" to "File Browse..."
Apply changes

To add post processing to extract DOCX file content:

Click Create under Processes
Choose PL/SQL
Give the process a name like "extract_text"
In the "Enter PL/SQL Page Process" dialog, paste "sample_docx_p.store_text(:PXX_FILE_NAME);" (XX being your page number)
Create process (and ensure the process comes after "Run Stored Procedure" process created by the page wizard

The last process could be implemented as a trigger on the samples_docx table.

Run the page and test by uploading a DOCX document (or test it in my demo application).

Building More
If you have a need to manipulate DOCX documents, this could probably be achieved by using the complete "ZIPImpl" as posted in this article, and using substitution variables in the same manner as I have described with RTF documents in an earlier post.

The zip/unzip may also come in handy when handling other office files (like Open Office ODF).

This is only a proof of concept, and the code is not production ready. Really! I'm not just saying that out of habit.

The next (and final) part of the DOCX "series" will address how to index the plain text content using Oracle Text.

Nice URL in a Public Facing Oracle APEX Application

2010-02-04T10:33:00.001+01:00

UPDATE: Try out Morten Bråtens excellent post on creating a REST web service with PL/SQL for the simplest answer to beautifying APEX URLs. The main difference between our solutions is that he uses a PL/SQL-procedure to actually do the rewrite, and I use an Apache map file. If I only had known... Ah, well, you live, you learn.

The Oracle APEX URL construct can be a bit cryptic for the uninvited, people and spiders alike. Here is a (a, not THE) technique to alleviate this. It is loosely based on a similar solution I made for an ancient Oracle Portal solution quite a few years back. You need an Apache (OHS/OAS or regular Apache) in front for this to work. The API is tested for Apache 2.2 and Oracle XE/11g, but it should run equally well on OHS/OAS (Apache 1.3-2.0).

Why Do You Need Nice URLs?
This is the place to start; Why would you consider rewriting the native APEX URL? There are a few good reasons why, but generalizations won't work here. The question is why do YOU need this for YOUR application? Consider it, and reconsider it, because there are no silver bullets here. Any path you choose will inevitably cause more work, add complexity and even more gray hairs when your carefully thought out assumptions suddenly changes. Not to mention adding overhead to your webserver when processing requests. Perhaps you will even start loosing hair for all I know. The more sophisticated application, the more work added for beautifying the URLs.

For an intranet environment I can see no good reasons to do this. None.

The Alternatives
There are already some designs out there to help you on your way. Patrick Wolf has a list of the more popular ones. Kris Rice's post Better APEX URLs also uses a map file. The <iframe> solution is also worth considering, depending on your need. There are also some very creative Apache rewrite solutions described in the OTN APEX-forum.

My Attempt
Let's just call it that, an attempt. It did not turn out as streamlined as I had hoped for, but for my need (and the effort I was prepared to put into it), it suffices.

I wanted to create content based nice URLs, and the content in question here resides in an Oracle database table. I also wanted to control the output from the database to reduce the number of moving parts.

This is how it works:

You can define a "page type" based on a table with a primary key, and create mapping between APEX URL and a nice URL based on that
You can manually create mapping between APEX URL and a nice URL to fully control both APEX and nice URL
The APEX to nice URL mapping will be written to an Apache map file
Apache checks the content of the map file for a potential rewrite
If the map file changes, Apache will cache the new version automagically

See examples below the first script to get a feel for it. The rewrites will not get in the way of the normal APEX URLs, so it will not break existing functionality.

The Database Code
The following script will create two tables, one to hold the mapping between source table (where content resides) and the APEX page(s) where content will be displayed. The other table is where the actual nice to APEX URL-mapping resides, the content of this table is what gets written to the Apache rewrite map file.

The script will also create a directory where the URL map file will be written, and a package to handle all the in-betweens. The schema user must have the appropriate privileges to create these objects for the script to succeed. Replace path to APACHECONF directory to desired location.

-- change path to suite your environment
create or replace directory APACHECONF as 'C:\Oracle\OraXE\Apache\conf'
/

create table app_page_type (
   page_id              number
,  source_table         varchar2(32)
,  source_table_pk_col  varchar2(32)
,  url_path             varchar2(255)
,  pk_page_parameter    varchar2(255)
,  name_column          varchar2(32)
)
/

create unique index app_page_type_uk
   on app_page_type (page_id, source_table)
/   

create table app_page_url_mapping
(
   page_id             number not null
 , source_table        varchar2(32)
 , source_table_id     number
 , apex_url            varchar2(4000) not null
 , nice_url            varchar2(4000) not null
 , nice_title          varchar2(255) 
)
/

create unique index app_page_url_mapping_uk
   on app_page_url_mapping (page_id, source_table, source_table_id)
/   

create or replace package app_page_p
as
   -- create url-friendly construct, 255 chars long
   -- based on a name.
   function get_nice_name (
      p_name               in     varchar2
   ,  p_source_table_id    in     varchar2 default null
   ,  p_url_end            in     varchar2 default '.html'
   ) return varchar2;
   -- get nice url based on source table, can be used
   -- directly from APEX application
   function get_nice_url (
      p_page_id            in    number
   ,  p_source_table       in    varchar2
   ,  p_source_table_id    in    varchar2
   ) return varchar2;
   -- get nice url from APEX page_id, will only return
   -- mappings without source table defined
   function get_nice_url (
      p_page_id            in    number
   ) return varchar2;
   -- create mapping based on page type and source table
   procedure create_mapping (
      p_source_table       in       varchar2
   ,  p_source_table_id    in       varchar2
   ,  p_name               in       varchar2 default null);
   -- create complete manual mapping  
   procedure create_manual_mapping (
      p_page_id            in       number
   ,  p_apex_url           in       varchar2
   ,  p_nice_url           in       varchar2
   ,  p_nice_title         in       varchar2 default null);
   --remove mapping when source is removed  
   procedure remove_mapping (
      p_source_table       in       varchar2
   ,  p_source_table_id    in       number
   );
   -- remove manual mapping to page_id
   procedure remove_manual_mapping (
      p_page_id            in       number
   );
   -- synchronize map file with mapping table
   procedure synchronize_mapfile;
end;
/
          
create or replace package body app_page_p
as
   -- global variables
   -- change variables to suite your environment
   g_app_id       number        := 1000; --APEX app_id for public application
   g_app_name     varchar2(255) := 'My test app'; --descriptive name of application
   g_dad_path     varchar2(255) := '/pls/apex'; --dad or location
   g_map_file     varchar2(255) := 'map.txt'; --name of map file
   g_tmp_map_file varchar2(255) := 'map_tmp.txt'; --name of temporary map file
   g_map_dir      varchar2(255) := 'APACHECONF'; --name of directory
   -- get nice url from source_table
   function get_nice_url (
      p_page_id            in    number
   ,  p_source_table       in    varchar2
   ,  p_source_table_id    in    varchar2
   ) return varchar2 is
      l_ret    varchar2(255);
   begin
      select tab.nice_url
        into l_ret
        from app_page_url_mapping tab
       where tab.page_id = p_page_id
         and tab.source_table = p_source_table
         and tab.source_table_id = p_source_table_id;
      return l_ret;
   exception
      when no_data_found
      then
         return null;
   end;
   -- get nice url from page_id, manually created
   function get_nice_url (
      p_page_id            in    number
   ) return varchar2 is
      l_ret    varchar2(255);
   begin
      for r in (select tab.nice_url
                  from app_page_url_mapping tab
                 where tab.page_id = p_page_id
                   and tab.source_table_id is null)
      loop
         l_ret := r.nice_url;
         exit;
      end loop;
      return l_ret;
   exception
      when no_data_found
      then
         return null;
   end;
   -- create url-friendly construct, 255 chars long
   function get_nice_name (
      p_name               in     varchar2
   ,  p_source_table_id    in     varchar2 default null
   ,  p_url_end            in     varchar2 default '.html'
   ) return varchar2 is
      l_ret    varchar2(4000) := p_name;
      l_sub    number;
   begin
      -- special national translation (excerpt)
      l_ret := translate(lower(l_ret), 'æøå ', 'aoa_');
      l_ret := regexp_replace(l_ret, '([^[:alnum:]|_])', '', 1, 0, 'i');
      l_sub := nvl(length(to_char(p_source_table_id)||p_url_end),0);
      l_ret := substr(l_ret, 1, 255-l_sub);
      l_ret := l_ret||p_source_table_id||p_url_end;
      return l_ret;
   end;
   -- lookup column value for source_table and source_table_id  
   function get_source_name (
      p_source_table       in       varchar2
   ,  p_source_table_id    in       number
   ,  p_source_pk_col      in       varchar2
   ,  p_source_name_col    in       varchar2
   ) return varchar2
   is
      l_sql    varchar2(4000) :=   'select '||p_source_name_col||' pk_col'||chr(10)
                                 ||'  from '||p_source_table||chr(10)
                                 ||' where '||p_source_pk_col||' = :1';
      l_ret    varchar2(4000);
   begin
      execute immediate l_sql into l_ret using in p_source_table_id;
      return l_ret;
   end;
   -- create the actual mapping
   procedure create_mapping (
      p_source_table       in       varchar2
   ,  p_source_table_id    in       varchar2
   ,  p_name               in       varchar2 default null
   ) is
      l_orig_name     varchar2(4000);
      l_nice_name     varchar2(255);
      l_apex_url      varchar2(4000);
      l_nice_url      varchar2(4000); 
      l_nice_title    varchar2(4000);
   begin
      -- delete previous entries
      delete from app_page_url_mapping
       where source_table = p_source_table
         and source_table_id = p_source_table_id;  
      for r in (select pt.*
                  from app_page_type pt
                 where pt.source_table = p_source_table)
      loop
         if p_name is null
         then
            l_orig_name := get_source_name(r.source_table, p_source_table_id, r.source_table_pk_col, r.name_column);
         else
            l_orig_name := p_name;
         end if; 
         l_nice_name := get_nice_name(l_orig_name, p_source_table_id, '.html');
         l_apex_url  := g_dad_path||'/f?p='||g_app_id||':'||r.page_id||':0::::'||r.pk_page_parameter||':'||p_source_table_id;
         l_nice_url  := r.url_path||'/'||l_nice_name;
         l_nice_title := substr(g_app_name||' - '||l_orig_name, 1, 255);
         -- insert new entry
         insert into app_page_url_mapping (
            page_id
         ,  source_table
         ,  source_table_id
         ,  apex_url
         ,  nice_url
         ,  nice_title)
         values (
            r.page_id
         ,  r.source_table
         ,  p_source_table_id
         ,  l_apex_url
         ,  l_nice_url
         ,  l_nice_title);
      end loop; 
      -- synchronize url-mapping file
      synchronize_mapfile;      
   end;
   -- create manual mapping  
   procedure create_manual_mapping (
      p_page_id            in       number
   ,  p_apex_url           in       varchar2
   ,  p_nice_url           in       varchar2
   ,  p_nice_title         in       varchar2 default null  
   ) is
   begin
      -- educated guess on delete
      delete from app_page_url_mapping
       where page_id = p_page_id
         and source_table is null;
      -- insert new entry
      insert into app_page_url_mapping (
         page_id
      ,  apex_url
      ,  nice_url
      ,  nice_title)
      values (
         p_page_id
      ,  p_apex_url
      ,  p_nice_url
      ,  p_nice_title);
      -- synchronize map file
      synchronize_mapfile;
   end;
   procedure remove_mapping (
      p_source_table       in       varchar2
   ,  p_source_table_id    in       number
   ) is
   begin
      delete from app_page_url_mapping
       where source_table = p_source_table
         and source_table_id = p_source_table_id;
      synchronize_mapfile;
   end;
   procedure remove_manual_mapping (
      p_page_id            in       number
   ) is
   begin
      delete from app_page_url_mapping
       where page_id = p_page_id
         and source_table is null;
      synchronize_mapfile;
   end;
   -- normal write to file procedure
   procedure write_to_file (
      p_file_name       in          varchar2
   ,  p_directory       in          varchar2
   ,  p_content         in          clob
   ) 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_file_content  blob;
      l_src_offset    integer := 1;
      l_dest_offset   integer := 1;
      l_lang_ctx      integer := dbms_lob.default_lang_ctx;
      l_warn          integer;
   begin
      -- converting from clob to blob
      dbms_lob.createtemporary(l_file_content, false);
      dbms_lob.converttoblob(l_file_content, p_content, dbms_lob.getlength(p_content), l_dest_offset, l_src_offset, 1, l_lang_ctx, l_warn);
      l_blob_length := dbms_lob.getlength(l_file_content);
      l_blob_left := l_blob_length;
      -- open the destination file.
      l_file := utl_file.fopen(p_directory,p_file_name,'WB', 32760);
      -- if small enough for a single write
      if l_blob_length < 32760 then
         utl_file.put_raw(l_file,l_file_content);
         utl_file.fflush(l_file);
      else -- write in pieces
         l_pos := 1;
         while l_pos < l_blob_length
         loop
            dbms_lob.read(l_file_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);
      dbms_lob.freetemporary(l_file_content);
   exception
     when others then
       dbms_lob.freetemporary(l_file_content);
       -- close the file if something goes wrong.
       if utl_file.is_open(l_file) then
         utl_file.fclose(l_file);
       end if;
       raise;
   end;
   -- synchronize map file
   procedure synchronize_mapfile
   is
      l_map    clob;
   begin
      l_map :=          '#       Generated file, manual changes will suddenly disappear'||chr(10);
      l_map := l_map || '#       Last generated: '||to_char(sysdate, 'dd.mm.yyyy hh24:mi:ss')||chr(10);
      for r in (select *
                  from app_page_url_mapping)
      loop
         l_map := l_map||r.nice_url||' '||r.apex_url||chr(10);
      end loop;
      write_to_file(g_tmp_map_file, g_map_dir, l_map);
      -- if everything goes well, we rename and overwrite
      utl_file.frename(g_map_dir, g_tmp_map_file, g_map_dir, g_map_file, true); 
   end; 
end;
/

Configuring Apache
Apache will handle the actual rewrites based on the generated map file. Include the following in your httpd.conf (after backing it up)

## Rewrite URLs that exists in map.txt
  RewriteMap nicetoapex txt:c:/oracle/oraxe/apache/conf/map.txt
  RewriteCond %{REQUEST_URI} /(([^/]+)(/.*)*)$
  RewriteCond %2 =articles.html [OR]
  RewriteCond %2 =article [OR]
  RewriteCond %2 =albums.html [OR]
  RewriteCond %2 =album
  RewriteCond ${nicetoapex:%{REQUEST_URI}|NOT_FOUND} !NOT_FOUND
  RewriteRule ^(/.*) ${nicetoapex:$1}%{QUERY_STRING} [P]

Note the forward slashes in the map file path (even for Windows installs). The code must be included directly into your VirtualHost-directive (if you have one), and not your default APEX Location-directive or some such. The reason is that the rewrite expects the nice-URL, not something that looks like '/pls/apex'.

Also note that I have included some criteria for when the map file will be checked, this is to exclude any number of requests not related directly to APEX pages. This is a very manual step, but can be minimized if you decide to prefix all your nice URLs with a common string. If you prefix your URLs you also will not force Apache to go through all the conditions before the attempted rewrite. A prefix kind of defeated the purpose of nice URLs for me, so I will update and reload Apache when changes are needed. A bit masochistic, I know...

The very cryptic "!NOT_FOUND" line above simply states that if the URL is not part of the map file, then don't rewrite (${nicetoapex:$1} will then have no value). There are probably easier ways to achieve this, but my knowledge of Apache is limited to what google serves me at the first page of the search result...

Apache will cache a copy of the map file for every start/restart/reload, and if the file is updated (mtime changed) it will re-read the file automagically. That is a nice feature, and saves you a ton of hassle.

You are not home free yet, APEX submits pages with relative paths (which is quite natural), but that will break your nice URL hierarchy. To fix this add:

## Handle calls to wwv_flow.accept
  RewriteCond %{REQUEST_URI} ^/wwv_flow.accept
  RewriteRule ^/(.*) /pls/apex/$1 [P]

This will rewrite all requests ending with wwv_flow.accept and point it to your dad-location. Depending on your application, it may be necessary to include more rewrite conditions for this rewriterule (like wwv_flow.show).

For good measure you can also include a rewrite to handle the default "start page" of your site with the following:

## Redirect server home page to Apex application
  RewriteRule ^/$ http://localhost:8080/pls/apex/f?p=1000:1:0 [P,L]
  RewriteRule ^/index.html$ http://localhost:8080/pls/apex/f?p=1000:1:0 [P,L]

Restart Apache for every change of httpd.conf for the changes to take effect.

Mapping URLs From Table Content
First off you have to define a relationship between the APEX-pages showing the content, and the database table where the content resides. The table APEX_PAGE_TYPE holds this information.

insert into app_page_type (
     page_id
   , source_table
   , source_table_pk_col
   , url_path
   , pk_page_parameter
   , name_column)
  values   (
     3                -- APEX page_id
   , 'ARTICLES'       -- Source Table
   , 'ARTICLES_ID'    -- Source table primary key
   , '/article'       -- Desired URL-prefix
   , 'P3_ARTICLE_ID'  -- APEX page item id that holds the primary key
   , 'TITLE')         -- Source table column that content title
/
commit
/

In the example above I want to generate a nice URL for articles in the ARTICLES-table. APEX page_id=3 will show the content, based on the value of page item P3_ARTICLE_ID. This bit you only have to do once for each page type/source table association.

When I insert/update a row in the ARTICLES-table with ARTICLE_ID=0, I can generate a nice URL with the following statements:

begin
   app_page_p.create_mapping( 'ARTICLES' -- source table name
                            , 0);        -- source table id
end;
/
commit
/

This is the code you normally would run following a publication of an article. If the TITLE-column has value 'APEX 4.0 - It is finally here!', your map-file should now contain the following:

#       Generated file, manual changes will suddenly disappear
#       Last generated: 03.02.2010 22:19:22
/article/apex_40__it_is_finally_here0.html /pls/apex/f?p=1000:3:0::::P3_ARTICLE_ID:0

You can have more than one page type associated with the same database table, and URLs for all pages will be generated from the statement above. If all works now, you should be able to point your browser to http://<yoursite>/article/apex_40__it_is_finally_here0.html

Manually Mappig URLs
Some APEX pages will probably not be directly linked to a database table, to accommodate this, the API offers a possibility to manually create page mappings with the following statements (note, there is no need for a page type to be defined first, as there is no content table to associate with that APEX page):

begin
   app_page_p.create_manual_mapping(2                          -- APEX page_id
                                   , '/pls/apex/f?p=1000:2:0'  -- APEX URL
                                   , '/articles.html'          -- Nice URL
                                   , 'Articles');              -- Nice name
end;
/
commit
/

If you ran this after the previous example, your map file should look like this:

#       Generated file, manual changes will suddenly disappear
#       Last generated: 03.02.2010 22:21:01
/articles.html /pls/apex/f?p=1000:2:0
/article/apex_40__it_is_finally_here0.html /pls/apex/f?p=1000:3:0::::P3_ARTICLE_ID:0

And likewise you should be able to see http://<yourhostname>/articles.html in a browser.

Pitfalls, Improvements and Considerations
What is the first thing you noticed about the API? No APP_ID-parameters! This version does not support more than one public application, but extending the code to accommodate this should be fairly trivial if the need should arise.

The second observation is the lack of support for more than one primary key column. The API must be modified to support this.

The third observation is the lack of APEX parameter support. Including RP or other such parameters will probably get onto your wish-list pretty soon. The API can be altered to achieve this, either through generating more mappings, or attaching parameters in a normal form to the nice-URL, and rewriting it to the correct APEX syntax in the Apache.

In the current version, there is no support for URL-hierarchy. If you want to incorporate an URL-path based on an hierarchy defined by the mapping API, you have to extend the API, or map it yourself by APP_PAGE_TYPE and APP_PAGE_URL_MAPPING.

You may also consider using the Apache httxt2dbm-utility to create a binary format DBM-file of the original map file, which is considerably faster than mapping with a txt-file. Adding a dbms_scheduler job to execute an external program should do the trick. Apache rewrite syntax is almost identical, you just have to tell it the map-type has changed.

I seriously wish there was an easy way to just delete a line from an existing file. I for one, could not conjure up a good way to do this from PL/SQL, hence the complete rewrite of the file. Maybe Perl could do a better job of it? Any old how, this portion of the API is well suited for a more asynchronous execution. In a normal production environment, the Apache is located in a DMZ, and not on the same server as the database. In that case the map file has to be moved from the database server to the webserver. The API does not support moving files through FTP/SFTP, but you can check out http://www.orafaq.com/scripts/plsql/ftpclient.txt for a plain PL/SQL FTP-solution, or be creative with an external procedure.

A Small Bonus
If you want to set the APEX page title according to the source table, you can modify the page template and include a function to return the nice_name column of the APP_PAGE_URL_MAPPING-table. The function will then be common for all pages in your application. The title of your webpage continually crops up as one of the more important tags for certain search engines.

In Conclusion
If you decide to use the API, then I suggest you start thinking about how to design your applications to use it, especially considering branching, tabs and other native APEX components that gravitate towards the normal APEX URL construct. If your application branches out with the old URL, there would be little point in attempting to rewrite the URLs. As I stated earlier, there are no silver bullets, and there will be more work than just using native APEX URLs.

If you are aiming at more sophisticated public applications, I would seriously consider NOT using it. This API (in it's present state at least) is aimed at more simple applications.

Oh, and did I mention you use this code at your own risk? Well, you do!

Enjoy :-)

Oracle Spatial, APEX and Google Maps

2010-01-07T15:52:00.000+01:00

In this post I will demonstrate how to use Oracle Spatial to store shapes, and utilize Oracle APEX and Google Maps API to visualize and manipulate the shapes.

The code is tested for Oracle 11g, but should work equally well for Oracle XE (no advanced Spatial operations), although I have noticed that Oracle XE is a bit more picky when it comes to valid shapes.

Demo Application
If there is too much text for you to read through, check out my demo Application on apex.oracle.com to see it in action :-)

The demo application is tested on Firefox ONLY, so expect issues when using other browsers!

Database objects
The code below will create a simple table with one column containing a sdo_geometry column called shapes, and a supporting package to mediate between APEX/Google Maps and the database table:

create table sample_polygon
(
   sample_polygon_id     number not null
 , name                  varchar2 (80) not null
 , description           varchar2 (4000)
 , zoom_level            number
 , shape                 mdsys.sdo_geometry
)
/

alter table sample_polygon add (
  constraint sample_polygon_pk
 primary key
 (sample_polygon_id))
/ 

create sequence sample_polygon_pk_seq
/

create or replace trigger trg_sample_polygon_bi
   before insert
   on sample_polygon
   referencing new as new old as old
   for each row
declare
   l_new_id       number;
begin
   if :new.sample_polygon_id is null
   then
      select   sample_polygon_pk_seq.nextval
        into   l_new_id
        from   dual;
      :new.sample_polygon_id   := l_new_id;
   end if;
exception
   when others
   then
      raise;
end trg_sample_polygon_bi;
/

create or replace package sample_polygon_p
as
   -- return string with coordinates
   function get_ordinates (
      p_shape                 in    sample_polygon.shape%type
   ) return varchar2;
   -- update table with shape
   procedure create_geometry (
       p_sample_polygon_id    in    sample_polygon.sample_polygon_id%type
   ,   p_name                 in    sample_polygon.name%type
   ,   p_description          in    sample_polygon.description%type
   ,   p_zoom_level           in    sample_polygon.zoom_level%type
   ,   p_shape                in    varchar2);
end;   
/

create or replace package body sample_polygon_p
as
   function get_ordinates (
      p_shape                 in    sample_polygon.shape%type
   ) return varchar2 is       
      l_poly   mdsys.sdo_ordinate_array;
      l_coords varchar2(4000);
      l_point  varchar2(20);
   begin
      l_poly := p_shape.sdo_ordinates;
      if l_poly.exists(1)
      then
         for i in l_poly.first..l_poly.last
         loop
            l_coords := l_coords ||','||replace(l_poly(i), ',', '.'); 
         end loop;
         l_coords := substr(l_coords, 2, length(l_coords));
      end if;
      return l_coords;
   end;
   procedure create_geometry (
       p_sample_polygon_id    in    sample_polygon.sample_polygon_id%type
   ,   p_name                 in    sample_polygon.name%type
   ,   p_description          in    sample_polygon.description%type
   ,   p_zoom_level           in    sample_polygon.zoom_level%type
   ,   p_shape                in    varchar2
   ) is
      l_sql       varchar2(32000);
      l_sample_polygon_id     sample_polygon.sample_polygon_id%type := p_sample_polygon_id;
   begin
      if l_sample_polygon_id is null
      then
         insert into sample_polygon
         (  name
         ,  description
         ,  zoom_level
         ) values
         (  p_name
         ,  p_description
         ,  p_zoom_level) returning sample_polygon_id into l_sample_polygon_id;
      else
         update sample_polygon
         set    name = p_name
         ,      description = p_description
         ,      zoom_level = p_zoom_level
         where  sample_polygon_id = l_sample_polygon_id;
      end if;
      -- 2003: Two dimentional polygon
      -- 4326: SRID
      -- 1,1003,1: one polygon (exterior polygon ring)
      l_sql := 'update sample_polygon'||chr(10)||
               'set    shape = sdo_geometry (2003'||chr(10)||
               '                            , ''4326'''||chr(10)||
               '                            , null'||chr(10)||
               '                            , sdo_elem_info_array(1,1003,1)'||chr(10)||
               '                            , sdo_ordinate_array( '|| p_shape ||')'||chr(10)||
               '                            )'||chr(10)||
               'where  sample_polygon_id = :1'; 
      execute immediate l_sql using in l_sample_polygon_id;
   end;
end;
/

You must have Oracle Spatial installed in your database (is part of default installation, including XE), and schema must have appropriate rights to invoke Spatial functions. If the above script delivers no error-messages you should be OK.

Why Spatial?
Well, why not? You already paid for it in you license, and quite frankly I can see no easier way to store your shapes. There are also a number of valuable functions that comes with Spatial, such as:

Location inside shape
Distance to nearest part of shape from location
Conversion between coordinate types
The ability to view shapes in other client tools like ESRI

I have very limited experience with Oracle Spatial, and the syntax for creating shapes as an SDO_GEOMETRY-data type was (is) a bit daunting.

Lessons learned are:
Take a close look at which coordinate system you are storing the points in. I have used the most common 4326, but if I understood correctly Google actually uses 3857 or 900913. This requires some further investigation.

The Google Maps API
There are already some examples on how to integrate Oracle APEX and Google Maps, and likewise, there are many examples on how to interact with Google Maps using the Google Maps API. I will list some of the resources on the subject:

Official Oracle APEX/Google Maps integration written by Jason Straub
Collection of map integrations with Oracle APEX by Patrick Wolf
Google Maps API Tool by Kjell Scharning
Official Google Maps API Reference

The first thing to do when trying out this code, is to sign up and get a Google Maps API Key!

A tiny bit of Javascript
The meaning of "tiny" being somewhat stretched here. Even if you are not familiar with Javascript, it is surprisingly easy to pick up. This script might not be the best place to start, but I encourage you to look at the source code from my demo application in detail to see what it does.

I will not do a full listing here, but highlight some of the more important functions and give a brief top-down view of what goes on.

On page rendering:

Javascript function buildMap is called
Invokes Google Maps API
Draws a map
Adds onClick-listener to the map to enable the user to create shapes

On click in map:

Draws a new marker on the map
Adds a new point to points array (this is what gets stored in the database)
Redraws the polygon on the map

On click on a marker in the map

Removes the marker
Removes the point from the points array
Redraws the map

Edit mode:

Redraws map to ensure completeness of polygon
Removes markers
Makes polygon editable
Removes onClick-listener on Map (Adding new points/markers in the map is no longer possible)

Exit edit mode:

Repopulates points array from polygon
Makes polygon uneditable (is that even a word?)
Redraws polygon on map with markers

Create new shape:

Removes all overlays from map (markers and polygon)
Resets all arrays and variables
Reattaches onClick-listener to map

For more details, you have to dig into the source code (view source in the demo application).

Merging Oracle Spatial and Google Maps
This not advanced mathematics, I have two functions in the database:

One which extracts ordinates (as they are called, don't ask me why) from the stored SDO_GEOMETRY-object, and return the ordinates as a comma-separated string
One which accepts a shape as a comma-separated string and creates a SDO_GEOMETRY-object

I also have two javascript functions:

One which accepts a string of comma separated coordinates, splits it into an array og "Google points"
One which accepts an array of "Google points", and returns a comma separated string (well, not exactly, it "returns" by setting an APEX item...)

Bringing it All Together
Oracle Apex is the glue between Google Maps and Oracle Spatial, but the actual coding in APEX to achieve this is quite little.

In my demo application I have:

Region containing a DIV and all necessary Javascrips to show and interact with Google Maps
Region based on a stored procedure to store shapes
Region containing a report of stored shapes

That's it, enjoy :-)

Interactive Reports Not Working Properly on New Install

2009-11-13T13:57:00.001+01:00

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)

2009-11-03T13:51:00.001+01:00

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?

2009-10-15T08:12:00.000+02:00

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

2009-10-12T14:27:00.000+02:00

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

2009-10-07T20:35:00.002+02:00

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

2009-10-04T23:15:00.014+02:00

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) """ 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

2009-07-09T21:32:00.008+02:00

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

2009-07-07T15:20:00.005+02:00

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

2009-06-26T13:42:00.014+02:00

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

2009-06-21T21:40:00.011+02:00

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

2009-05-26T08:37:00.007+02:00

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

2009-05-24T22:57:00.006+02:00

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

2009-05-24T22:46:00.006+02:00

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!