Apex f?p syntax with Flexible Parameter Passing enabled

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:

App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

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:


{proc}: SIGNATURE (parameter names) MISMATCHVARIABLES IN FORM NOT IN PROCEDURE:
NON-DEFAULT VARIABLES IN PROCEDURE NOT IN FORM: {param}


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


{proc}: SIGNATURE (parameter names) MISMATCHVARIABLES IN FORM NOT IN PROCEDURE: {param}
NON-DEFAULT VARIABLES IN PROCEDURE NOT IN FORM:

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

Example:

If you send the following URL:
http://www.acme.com/pls/myDAD/!scott.my_proc?x=john&y=10&z=doe

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.

   REVISIONS:
   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:
   ----------------
   f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

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

   results in:

   f?p=1:2:12345::::P1,P2,...,Pn:V1,V2,...,Vn:

   ******************************************************************************
   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);
BEGIN
   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);
      ELSE
         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;
   END LOOP;

   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'));
EXCEPTION
   WHEN OTHERS THEN
      RAISE;
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 BOOLEAN IS
BEGIN
   --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
      RETURN TRUE;
   ELSE
      RETURN FALSE;
   END IF;
END wwv_flow_epg_include_mod_local;
/