Tuesday, December 22, 2009

Follow-up on my "Flexible Parameter Passing" post

I had some reactions on my post, describing an issue that occurred when using the DHTMLX library in combination with APEX:

From Bharadwaz (Bharat) Pappu , Subject: ODTUD- Oracle APEX + DHTMLX integration

Hello Christian,

The presentation at APEXPOSED will be similar to the one I did at ODTUG.

There is one huge change though…Instead of making changes to the .js files or the DHTMLX APIs I use a very powerful DHTMLX API to integrate it  with Oracle APEX.

As promised to you earlier here is a snippet of the code that will work without changing any APEX or DHTMLX APIs:

<link rel="STYLESHEET" type="text/css" href="/i/javascript/.../dhtmlxSuite/dhtmlxtree/samples/common/style.css">
<link rel="STYLESHEET" type="text/css" href="/i/javascript/.../dhtmlxSuite/dhtmlxtree/codebase/dhtmlxtree.css">
<script  src="/i/javascript/.../dhtmlxSuite/dhtmlxtree/codebase/dhtmlxtree.js"></script>

   lv_url varchar2(1000):='f?p='||v('APP_ID')||':31:'||v('SESSION')||'::::P31_ID,P31_UID:';

<div id="treeboxbox_tree" 
     height:600;background-color:#f5f5f5;border:1px solid Silver;overflow:auto;">

      tree.loadXML("'||lv_url||'"+id+","+(new Date()).valueOf());

   tree.attachEvent("onOpenStart", function (id, state) {
      return true

   tree.attachEvent("onOpenEnd", function(id, state) {

The highlighted piece of code is the substitution for my previous integration technique (which worked but not a classy way to do bussiness)…hope you like it.


Bharadwaz (Bharat) Pappu

From Peter Raganitsch, Subject: Your Blog posting about mod_plsql flexible parameter

Hi Christian,

I found your blog posting and learned something about flexible parameters.
Very interesting!

I just wanted to tell you, that most dhtmlx-components have a method called preventIECaching(false); to turn off sending of the additional parameter in IE.


Thursday, November 19, 2009

Book Review: Oracle Application Express Forms Converter

Oracle Application Express Forms Converter
A migration guide using the APEX conversion utility
Convert your Oracle Forms application to Oracle APEX successfully

by Douwe Pieter van den Bos

Packt Publishing 2009
ISBN 1847197760
ISBN 13 978-1-847197-76-4


Douwe Pieter van den Bos’s book “Oracle Application Express Forms Converter” covers all the aspects and tasks of your Forms conversion project, step-by-step, well explained and packed with screenshots.

The books tutorial-like organization guides you through the whole conversion process, from planning and preparation, through conversion and customizing your application, to deploying it.

If you are thinking of migrating your Oracle Forms and/or Reports to APEX, this book is a “must‑have” on your project teams reading list.

Nevertheless, you will need some knowledge of Oracle Forms and APEX to make this an easy read. Instead of going into details, in some cases you will find references to the appropriate (Oracle) documentation, which makes this book an easier read, but not the ultimate reference.

A very convenient, more practical aspect of the book is its size and number of pages, which seems to be optimized to fit laptop bags.

Wednesday, September 30, 2009

AJAX Bug in Firefox caused by Firebug 1.4

Well, another post on browser behavior. Actually it's about an Add-on. This time it's about a bug I ran into using the Firebug (current version: 1.4.3). Firebug is very popular under Apex Developers, so I thought this might interest you.

Firebug actually works fine, but if you are using AJAX calls in your Apex application (or any other web application), you will notice that the AJAX call is made, response received, but not processed. I tried all the other browsers, no problems; just Firefox did not behave correctly. I had no idea. A post on Firefox Support Forum ... another week of cluelessness ... finally I got a friendly hint, directing me to this blog-post:

Looks like this problem is caused by Firebug and solved in the upcoming release 1.5 (currently alpha).

Update 5-nov-2009: Firebug Add-in update 1.4.4 is available. I installed the update and the mentioned problem does not occur anymore.

Thursday, July 23, 2009

Session management within Internet Explorer

When developing web applications you sometimes want to develop/test your app with different users (sessions) simultaneously and not logout-login-logout-login every time to switch. When using Internet Explorer, you might have experienced some differences in session management between IE6/7 and IE8. I found this article on MSDN Blogs, which explains the differences and helped me to configure the behavior I wanted.

Friday, July 17, 2009

Apex f?p syntax with Flexible Parameter Passing enabled

url11 Apex is using its own syntax to pass URL parameters, called the f?p syntax. In fact, there is only one standard URL search-path parameter: "p". "p" accepts a string build as:


I am not going to explain all the individual arguments. They should be familiar to most APEX developers. I would like to discuss the itemNames:itemValues argument pair, which allows us to pass custom parameters to our page calls.
When using external libraries, like the "Yahoo! User Interface Library" (YUI), JQuery or (in my case) DHTMLX, you might find, that the f?p syntax is not always usable with these libraries. In some cases, URL’s get assembled by those libraries dynamically, expecting the standard search-path syntax ( ?P1=V1&...&Pn=Vn).

I came across this problem when using the xmlLoad functionality in the DHTMLX library. This method adds an additional parameter to the given XML-source URL (a random value to prevent caching, I believe; strange enough only when using IE and not in other browsers).

One could modify these libraries to work with the f?p syntax, and then make the modification every time you will receive an upgrade of the lib. Anyway, you probably loose support (if you have). I rather leave the lib untouched and add functionality to APEX to enable standard search-path syntax.

The Apex itemNames:itemValues argument pair allows us to add custom parameters in a very flexible way. But wait. "Flexible"? There already is a “flexible” way to pass parameters using PL/SQL Gateway (the very fundament of Apex http calls): "Flexible Parameter Passing".

Usually a PL/SQL Gateway http call has to provide exactly those parameter names of the database procedure handling the call (except those with default values of course). This method is called "Parameter Passing by Name". Not passing a mandatory parameter will result in an error:


Passing a parameter that is not in the procedures parameter list will return a different error (obviously):


With the PL/SQL Gateway’s Flexible Parameter Passing mechanism one can pass any number of parameters to a procedure. The called procedure has to be defined with a specific interface to handle these calls:

procedure [proc_name] is(name_array IN [array_type],value_array IN array_type])


If you send the following URL:

The exclamation mark prefix (!) instructs the PL/SQL Gateway to use flexible parameter passing. It invokes procedure scott.myproc and passes it the following two arguments:

name_array ==> ('x', 'y', 'z')
values_array ==> ('john', '10', 'doe')

Using Flexible Parameter Passing, I wrote a new “f” procedure supporting:

  • the f?p syntax arguments
  • passing of (custom) parameters the standard way
I called this procedure "ff" (flexible "f"). Here is the code:

CREATE OR REPLACE PROCEDURE apex_030200.ff (name_array IN OWA.vc_arr, value_array IN OWA.vc_arr) IS
   NAME:       ff
   PURPOSE:    "flexible parameter passing" enabled APEX f function

   num_entries   The number of name_value pairs in the query string
   name_array    The names from the query string (indexed from 1) in the
   order submitted.
   value_array   The values from the query string (indexed from 1) in the
   order submitted.
   reserved      Not used. It is reserved for future use.

   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        15-7-2009   C. Rokitta       Created this procedure.

   NOTE:  All existing f?p syntax parameters are mapped into f?p format
   All other name/value pairs are translated into APEX f call
   syntax itemNames:itemValues:

   APEX f?p syntax:

   This procedures ff syntax:
   !ff?App=1&Page=2&Session=12345&...&P1=V1&P2=V2 ... &Pn=Vn

   results in:


   C. Rokitta - christian[at]rokitta.nl

   TYPE f_param_array IS TABLE OF VARCHAR2 (32767)
                            INDEX BY VARCHAR2 (20);

   v_f_p_arr   f_param_array;
   v_f_p       VARCHAR2 (32767);
   v_inames    VARCHAR2 (32767);
   v_ivalues   VARCHAR2 (32767);
   v_f_p_arr ('app') := '';
   v_f_p_arr ('page') := '';
   v_f_p_arr ('session') := '';
   v_f_p_arr ('request') := '';
   v_f_p_arr ('debug') := '';
   v_f_p_arr ('clearcache') := '';
   v_f_p_arr ('printerfriendly') := '';

   FOR i IN 1 .. name_array.COUNT LOOP
      IF LOWER (name_array (i)) IN
            ('app', 'page', 'session', 'request', 'debug', 'clearcache', 'printerfriendly') THEN
         v_f_p_arr (LOWER (name_array (i))) := value_array (i);
         IF LENGTH (v_inames) > 0 THEN
            v_inames := v_inames || ',';
            v_ivalues := v_ivalues || ',';
         END IF;

         v_inames := v_inames || name_array (i);
         v_ivalues := v_ivalues || value_array (i);
      END IF;

   f (   v_f_p_arr ('app')
      || ':'
      || v_f_p_arr ('page')
      || ':'
      || v_f_p_arr ('session')
      || ':'
      || v_f_p_arr ('request')
      || ':'
      || v_f_p_arr ('debug')
      || ':'
      || v_f_p_arr ('clearcache')
      || ':'
      || v_inames
      || ':'
      || v_ivalues
      || ':'
      || v_f_p_arr ('printerfriendly'));
END ff;

This procedure is a wrapper/translater for the Apex f procedure. All f?p syntax arguments can be posted as named parameter (and then mapped into the f?p syntax) and all non f?p arguments will be placed into the itemNames:itemValues arguments. So basically the ff procedure does exact the same as the f procedure, but then with standard URL search-path syntax.

You have might noticed, that I created the procedure in the APEX schema. I thought this is a natural place to put, and I don’t have to worry about access right for the f procedure. Grant execute rights to PUBLIC and create a PUBLIC SYNONYM for "ff" , just like it is done for "f".

When testing "ff" I found, that I could not call my new procedure through the apex DAD. The DAD is secured to only allow certain (Apex) procedures to be called. Luckily one can register his own procedure in Apex, by customizing the Apex function WWV_FLOW_EPG_INCLUDE_MOD_LOCAL:

CREATE OR REPLACE FUNCTION wwv_flow_epg_include_mod_local (procedure_name IN VARCHAR2)
   --return false; -- remove this statement when you modify this function
   -- Administrator note: the procedure_name input parameter may be in the format:
   --    procedure
   --    schema.procedure
   --    package.procedure
   --    schema.package.procedure
   -- If the expected input parameter is a procedure name only, the IN list code shown below
   -- can be modified to itemize the expected procedure names. Otherwise you must parse the
   -- procedure_name parameter and replace the simple code below with code that will evaluate
   -- all of the cases listed above.
   IF UPPER (procedure_name) IN ('FF') THEN
   END IF;
END wwv_flow_epg_include_mod_local;

Tuesday, June 30, 2009

ODTUG Kaleidoscope 2009 - Monterey, CA

Last week I visited the ODTUG Kaleidoscope 2009 Conference in Monterey, California. Now, that I got over my jetlag and back to work, I'm trying to order and evaluate all the impressions, ideas and inspirations I got during this event.

I brought three green flashlight bouncing balls back home for my kids (got them from one of the exhibiting vendors). Of course they had lots of fun test-bouncing these goodies immediately. This is, kind of, what I feel right now: I really would like to start playing around with some of the techniques, tools and frameworks I learned about ("research", as I would describe it to my manager).

On second thought, evaluating the information I got, putting it into my work's context and then test driving it seems a better approach to me. Definitely when I saw the devastating effect of the bouncing balls on our livingroom's interior. ADF, APEX, jQuery, REST, SOAP, SOA, jXLS, ... when to use what and where? Still, the technique is not the driver, but the result that has to be achieved. So, I’m going to take a step back and take my time for a hype-free, closer look into some topics. I will try to share my findings in this blog.