The storm had now definitely abated, and what thunder there was now grumbled over more distant hills, like a man saying “And another thing…” twenty minutes after admitting he’s lost the argument.

Follow crokitta on Twitter

Source Article from
Session management within Internet Explorer
Oracle & Apex Geekery
And another thing …

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

   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]

   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;

Source Article from
Apex f?p syntax with Flexible Parameter Passing enabled
Oracle & Apex Geekery
And another thing …

In my current assignment I face users who would like to have their data and graphs in PowerPoint. How do you do that with Oracle tools when you do not want to spend a lot of money on a BI Publisher license? The answer is simple, by using Apache POI , an Java open source library for MS Office integration.
With the examples supplied with the software I was able to generate a prototype servlet in on afternoon! The result was a servlet that accepts a title and (the URL to) an image which it places in a PowerPoint slide. With some extra hours of effort I expanded the prototype to accept an array of URLs and titles. In combination with an Apex that generates parameter driven images the user can fill his shopping basket with images and generate a PowerPoint presentation by pressing a button.
When you want to generate PowerPoint output, do have a look at Apache POI!

Happy Apexing

Source Article from

Laltely I have done some projects that needed the implementation of Oracle pipelined functions. You would typically use pipelined functions for performance or flexibility (for example when using dynamic SQL).
It works great once you have got the job done, but before that there is a lot of typing involved. I myself always have to Google for an example of which objects to create and how it should be done. Then at least an hour further I have got my pipelined function running. This, as a real developer, made me wonder whether is would be possible to automate this process. And after some hours of thinking, I found a solution.

Each pipelined function is based on a table like structure, so we can start with a table or a view, say the known table DEPT. With the definition of this table we can build the rowtype object.

create or replace type dept_rowtype
as object
( deptno number(2,0)
, dname varchar2(14)
, loc varchar2(13)

On the rowtype object we need to define a tabletype object, that easy (and short):

create or replace type dept_table_type as table of dept_rowtype;

Then we need to write our function, that is declared returning the tabletype by in fact returning a lot of rowtypes. This is, even for a small table like DEPT quite a lot of typing.

create or replace function dept_pf return dept_table_type pipelined is
cursor c_cur is
select deptno
, dname
, loc
from dept;
r_rec dept_rowtype;
l_deptno number(2,0) := null;
l_dname varchar2(14) := null;
l_loc varchar2(13) := null;
open c_cur;
fetch c_cur into l_deptno
, l_dname
, l_loc
exit when c_cur%notfound;
r_rec := new dept_rowtype ( l_deptno
, l_dname
, l_loc
pipe row (r_rec);
end loop;

At last we need a view to show the data:

create or replace view dept_view as select * from table(dept_pf());

This code is all be generated automatically on the basis of a table definition. You can reach the Apex application to generate all this code with this button:

It’s really easy and fast!
Enter the name of the table, which is used to generate the names of the other objects. Then go to the Object Browser and download the column definitions. Cut the Name and Datatype columns in Excel and Paste them in Column Definition. Now hit the Generate button and presto you have got your scripts. Run them in SQL*Plus or TOAD and you have got your pipelined function working within a few minutes. Now you can query the view based on the function.
Off course you would like to perform some changes on the function itself, because you did not do all this just to get the same result as querying the table directly ;-).
But you did not have to do all the typing,

Hope you enjoy the generator, let me know what you think of it!

Dick Dral

Source Article from

As I am starting my own business I am building a new site ( My hosting provider provides a web site with static files, PHP and MySQL. The last two are not my cup of tea, so I decided to create a site with static HTML files for a start. I took my ASCII editor and set out to create the files. A large part of the pages show more or less then same on every page. Do I have to copy this theme on each page? How do I cope with changes in the design, that cann’t be implemented in CSS?

There must be tools available for this. But I am not in to Dream Weaver, Frontpage and I don’t want to invest time in a new tool if I don’t have to. So I decide to use Application Express to build the site. And it works!

I have implemented the basic theme in a page template. Most of the site is formatted using CSS so I have created a very basic templates for a Region (just showing a title and the HTML content) and some list template for the various menus througout the site. The general menus are placed on page zero.

This way I had my site up and running quite fast. There is only one problem. It’s run in Application Express and it should be static files. This can be solved by saving the webpages to files (use Show source and save the content of that!) and changing the links. Normally a Apex link has the format f?p=&APP_ID.:200:&APP_SESSION.. This is not very useful, because you cannot extract anything like a filename from this. So the links I created use application and page aliases resulting in a link like f?p=detora:about_detora:&APP_SESSION.. If you clip the part around the page alias and augment it with .html you have a perfectly usable link! Make sure you save the pages under there page alias name!

Off course this work is tedious and error prone, so I would not be an IT professional if I would not set out to automate this. It must be possible to write a PL/SQL procedure that reads the pages from the Apex dictionary, accesses each page through UTL_HTTP, replaces the links and writes the static file for the page. To be continued…

One nice thing about using Apex is the possibility to show objects conditionally. I use this feature more and more. It can be used for example to show an “under construction” image on pages that have not been filled yet:

  • create a region on page 0 containing a nice “Under construction” image
  • put a condition on it of type “NOT exists (SQL query returns no rows)”
  • add a query to this condition that returns the regions of the current page:

select, r.plug_name
from flows_020200.wwv_flow_page_plugs r
, flows_020200.wwv_flow_steps p
where p.flow_id = &APP_ID.
and r.flow_id = &APP_ID.
and = r.page_id
and = &APP_PAGE_ID.

In this way the “under construction” region will only be shown on a page that does not have any regions.Just make sure you create empty pages for all the links on the site!

Have fun creating your own site with Apex.

Source Article from

My new employer asked me to show messages from triggers more user friendly than they are shown by default by Apex, i.e. in the separate error screen. I had previously been investigating this, but I wondered there should be someone with a solution out there in the Apex user’s universe, represented by the Apex forum on OTN. So I posted my question, and got no response! This amazed me. Is there no one who came across this problem before?
Lot’s of organizations have databases with triggers to ensure data integrity, and sure some of them want to access their databases with Apex. Are their users content with the default error screen, or even a customized screen with only an error message and a Back button to return to the screen (having to memorize the error message).

I just saw there was a reply referencing Patrcik Wolff’s blog and his impressive solution for error checking in tabular forms. But I just want a simple solution without extra programming. So I took some of Patrick ideas together with some other code snippets to extract the relevant part of the error message form the whole error stack and started off…

Developing Javascript I think is a tedious process, I much rather work with PL/SQL, which can be easily debugged and traced. So I always work step by step, testing a lot along the way to make sure that everything keeps working. And at the end I had a working generic solution, which shows trigger error messages as notifications without any additional coding in Apex.

Apex: Show Database Error Message in calling form
The purpose is to show a neat error message in the notification area of the calling form.
Default Apex show the whole error stack raised by a DB trigger in a separate error page.
With acknowledgement to Patrick Wolf, I lend parts of his ApexLIB code to create this solution.


The error message is raised from a DB trigger using RAISE_APPLICATION_ERROR(-20000,’errormessagetext’).

  • The relevant part of the error stack is contained within the strings ‘ORA-20000’ and the next ‘ORA-‘-string, i.e. if the error stack is ‘ORA-20000 Value should not be null ORA-6502 …’, than the relevant string is ‘Value should not be null’ .
  • Cookies are enabled on the browser of the user
  • Explanation
    The solution relies heavily on the use of Javascript. On the template of the error page Javascript is added to identify the error stack and to extract the relevant error message. This message is written to a cookie, and then the control is passed back to the calling page (equal to pushing the Back button).
    In the calling page a Javascript onLoad process is added. This process determines whether an error message has been written to a cookie. If so the error message is formatted as an error and written to the notification area.
    The solution redefines two template pages, the two level tab (default page template) and the one level tab (error page template).
    Javascript is added to implement the solution. This Javascript is contained in a small library errorHandling.js:

    var vIndicator = "ApexErrorStack=";

    function writeMessage(vMessage)
    { document.cookie = vIndicator+vMessage+';';

    function readMessage()
    { var vCookieList = document.cookie;
    var vErrorStack = null;
    var vStart = null;
    var vEnd = null;
    var vPos = null;

    vPos = vCookieList.indexOf(vIndicator);
    // No cookie found?
    if (vPos == -1) return("empty");
    vStart = vPos + vIndicator.length;
    vEnd = vCookieList.indexOf(";", vStart);
    if (vEnd == -1) vEnd = vCookieList.length;
    vErrorStack = vCookieList.substring(vStart, vEnd);
    vErrorStack = decodeURIComponent(vErrorStack);
    // remove the cookie
    document.cookie = vIndicator+"; max-age=0";

    function getElementsByClass2(searchClass,node,tag)
    var classElements = new Array();
    if ( node == null )
    node = document;
    if ( tag == null )
    tag = '*';
    var els = node.getElementsByTagName(tag);
    var elsLen = els.length;
    var pattern = new RegExp('(^\\s)'+searchClass+'(\\s$)');
    for (i = 0, j = 0; i 0 )
    { errorText = errorElements[0].innerHTML;
    errorText = errorText.substr(errorText.indexOf("ORA-20000")+ 11);
    errorText = errorText.substr(0,errorText.indexOf("ORA")-1);
    // errorElements[0].innerHTML = errorText;

    function show_message()
    { var vCookieList = document.cookie;
    var vErrorStack = null;
    var vErrorName = null;
    var vErrorMessage = null;
    var vStart = null;
    var vEnd = null;
    var vPos = null;

    // get errorStack
    vErrorStack = readMessage();
    if (vErrorStack == -1) return;

    // search for our message section (eg. t7Messages)
    var notificationArea = document.getElementById("notification");
    if (notificationArea != null)
    { notificationArea.innerHTML = '

    '; }
    { alert(vErrorStack); }

    This code is loaded as a static file in Application Express (no application associated).

    In both templates a reference to this code file is added in the Definition Header section.

    This library is called from the two level tab page template to show the error message (if any) in het onLoad event of the body tag. Therefore the function show_message() is called in the onLoad event of the page. This function reads the cookie and displays the error messages stored in the cookie.

    <body onload="javascript:show_message();">#FORM_OPEN#

    This code checks whether a message has been written to a cookie and if found displays the message in the notification area.

    In the error template page the Error section has the content:

    <script language="javascript">
    </script><a href="javascript:window.history.go(-1);">Back</a>

    The call to processErrorText() looks for the error message, extracts the relevant part of it (between ‘ORA-20000’ and the next ‘ORA-‘), writes it to a cookie and returns to the previous screen.
    The link to the previous page is added should an error in the Javascript occur. It provides the user with a path back to the application.

    With these actions taken, the error messages issued from triggers are shown in the notification area of the form the user has entered his data in.
    The need for database driven messaging
    In some cases the need exists to process error messages in the database before presenting them to the user. This is the case, when the triggers return message codes, associated to error messages in a message table.

    This can be done by using a special Error Message Processing page in Apex.
    The error message page extracts the error message, redirecting to the Error Message Processing page with the error message as a parameter. In the EMP page a PL/SQL function can be called with the message as a parameter. This function returns the right message, which is written to a cookie using dynamically generated Javascript from PL/SQL. Than the contol is given back to the calling form.

    The redirect is implemented by location.replace, so that the error message page does not exist within the browsing history. The normal history(-1) will return to the calling page.

    Implementation of database driven messaging

    The solution redefines two template pages, the two level tab (default page template) and the one level tab (error page template).
    Javascript is added to implement the solution. This Javascript is contained in a small library errorHandling.js already listed in a previous paragraph.

    In the error template page the Error section has the content:

    <script language="Javascript">
    var errorText = null;

    function redirect2oracle()
    { window.location.replace("f?p=&APP_ID:500:&APP_SESSION.::::P500_MESSAGE:"+errorText); }

    function getError()
    { errorText = processErrorText(); }

    <a href="Javascript:redirect2oracle();">Go to Error Message Porcessing Page</a><br/><a href="javascript:window.history.go(-1);">Back to form</a><br/>
    <script language="Javascript">
    var errorText = null;

    function redirect2oracle()
    { window.location.replace("f?p=&APP_ID:500:&APP_SESSION.::::P500_MESSAGE:"+errorText); }

    function getError()
    { errorText = processErrorText(); }


    The call to processErrorText() looks for the error message, extracts the relevant part of it (between ‘ORA-20000’ and the next ‘ORA-‘), writes it to a cookie.
    Then the EPM-page (500) is called with the extracted message as parameter.

    The link to the EPM page and the previous page is added should an error in the Javascript occur. It provides the user with a path back to the application.

    We need to create an Error Message Processing Page.

    • Create a new page 500 with a empty HTML-region “Parameters”
    • Create an text-area P500_MESSAGE in this region
    • Create a PL/SQL region “Process Messages” with source:
    • Create a PL/SQL procedure convert_message like this example, that reads messages from a message table. If not found, the actual input message is returned.

    CREATE OR REPLACE procedure convert_message(i_message in varchar2) is
    v_id number := null;
    v_message varchar2(4000) := null;

    function get_message (i_message in varchar2) return varchar2 is
    v_return varchar2(4000) := null;
    select msg_text into v_return
    from messages
    where msg_code = upper(i_message);
    when no_data_found then

    v_message := get_message(i_message);

    // write the message for logging/debugging

    // write cookie and redirect to calling page
    htp.p('<script language="Javascript">');

    // enter return link just in case
    htp.p('<a href="javascript:window.history.go(-1);">Ga terug</a>');


    Note: The way the message is converted is just an example

    With these actions taken, the error messages issued from triggers are shown in the notification area of the form the user has entered his data in.

    Source Article from

    It’s been a while since I posted my last blog. I’v been busy in a new job as Apex coach for a large dutch bank. The department I work for used to be an Oracle Designer and Forms shop, but last year they discovered the possibilities of Apex by a proof of concept application.
    I have held several presentations for my collegues about Apex. They were impressed by the ease of application development, but they missed some features they use in Oracle Forms. One of them is spread tables. When you have a very large form, a spread table gives the the opportunity to scroll through a table both horizontally and vertically, while the heading and the row context stay in place.
    Standard Apex provides you with normal HTML tables. There is no such functionality as spread tables possible, is it…?

    When I was googling the internet on this subject I came across this site describing a technique to use spread tables using CSS and a bit of Javascript. It is possible to lock one or more columns to keep the context of the rows visible.
    Great solution! The only drawback is that it only functions in IE and from version 5 upward, but the bank I work with has standardised on IE for browsing. As long as this feature is used within the company there is no problem.

    But I want to use it in Apex. My idea is to create a new Report Template, that can be used to create spread table reports just like you create normal reports.

    I have based my new Report Template on the Alternating colors template. In the Before Rows section a DIV container is started, and the table is assigned the ID spreadtable:

    which is ended in the After Rows sec

    The format of the tablecells is controlled by the CSS. The class locked is assigned to table cells to be locked. The locking of the column headers to the top border of the div is done by :

    As you see the top position of the header cells is determined by a Javascript expression, which can also contain a custom Javascript function. The expression keyword in this syntax is only evaluated by Internet Explorer, it will not work in other browsers.
    The column cells that serve as row context are locked to the left side of the container with the folowing CSS.

    The full CSS can be found here
    The fact that it is based on an alternating colors table makes the CSS more complicated. Apart from this to obtain a clear separation between scrollable and locked columns I needed to create three new classes xxx_last, having a grey right border.

    Next we need some Javascript to assign width and height to restrain the table container and to actually assign the class locked to the table cells we want to lock. For a start we put this code in the pageheader.

    function lockColumns(regionDivID, width, height, noOfColumns)
    { // set DIV properties
    div = scrollDiv(regionDivID); = width; = height; = "auto";
    // look for table
    var tables = div.getElementsByTagName('TABLE');
    var table = tables[0];
    var tableRows = table.getElementsByTagName('TR'); //collection of rows
    var className = 'locked';
    for (i = 0; i { var tableRow = tableRows.item(i);
    for (j=0; j { if (j == noOfColumns - 1 ) { className = className + "_last"; }
    tableRow.cells[j].className = className; // last column get grey right border
    if (className == 'locked_last')
    { className = 'lockedalt'; }
    { className = 'locked'; }

    We call this funtion in a HTML region that we put behind the reports region so that we are sure that the region has rendered already.

    lockColumns ( "ST_REGION", "400px", "250px", 2);

    With this call we restrain the report in region with ID ST_REGION to a size of 400×250 px. A row context of 2 columns remains locked in position.

    The result is shown on this page – remember it only functions with IE 5 and up. In this page support is added for Ajax pagination. In the Reports template the pagination links are supplied with a call to the lockCols procedure:

    I am still working on adding this call to the sort-links in the column headers.

    Happy Apexing!

    Source Article from