Refresh Report Region and Pagination

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

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

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

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

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

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

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

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

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

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

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

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