Browsing "Older Posts"

APEX-AT-WORK no image

SQL Developer 4 and APEX 4.2.4 are out

Von Tobias Arnhold → 12.13.2013
New version of the Oracle SQL Developer is out:

New features can be found here:


Besides the new version of SQL Developer also an update of the current APEX version got released:

Joel Kallman wrote about the updated / new features:

I bet this is the last release before APEX 5. ;)

Disable input elements in tabular form

Von Tobias Arnhold → 12.12.2013
There is a quite often requirement to disable a tabular form column based on the row information.

I got a customer request with the following requirement:
A select list column of a tabular form should be disabled (row based) when the value of another column is like SYSDATE.

There are 3 options to follow:
1. Create a manual tabular form and disable the column with a case statement inside the select
2. Deactivate the column with some jQuery code
3. Wait for APEX 5. I think there is some development ongoing about this problem.

Some thoughts about solution 2

I assume that the SYSDATE is: 12.12.2013
Our result should look like this:

How to achieve this?
First I need to extend the report with one extra column plus some column customization
Second is some jQuery code in form of two dynamic actions.

The SELECT statement:
SELECT -- Original columns:
       -- New column:
       to_char(SYSDATE,'') as CURRENT_DATE
Now I change the report attributes of column: LAST_DATE
Column Formatting >> HTML Expression:
<span class="tbl_date">#LAST_DATE#</span><span class="tbl_cur_date" style="display:none;">#CURRENT_DATE#</span>

Now I need to add two dynamic actions to change the appearance of our column: ANIMAL_ID
1. Deactivate the affected rows
Event: Page Load
Action: Execute JavaScript Code
$('.tbl_date').each(function( index ) {
 if ( $(this).html() == $(this).parent().find('.tbl_cur_date').html() ) 
    $( this ).parent().parent().find('select[name="f01"]').prop('disabled', 'disabled');
What am I doing here?
I search for all elements with the CSS class "tbl_date"
Now I compare the HTML value with the value of the class "tbl_cur_date".
If the result is TRUE then the select list will be disabled.
To find the select list I search for the name "f01". Actually because I only have one select element I  could use this code snippet as well: find('select')

2. Activate the affected rows before page submit. Otherwise I would get an tabular form error.
Event: Before Page Submit
Action: Execute JavaScript Code
$('.tbl_date').each(function( index ) {
 if ( $(this).html() == $(this).parent().find('.tbl_cur_date').html() ) 
    $( this ).parent().parent().find('select[name="f01"]').prop('disabled', '');
Now I have a huge security hole in my application. To close this one I need a tabular form validation. The validation must check for changed select elements. Of course only those which should not be able to change.
You can follow this blog post which is providing a hint how to achieve it:

Working with multiple browser tabs

Von Tobias Arnhold → 12.10.2013
When I develop APEX applications I use several browser tabs to navigate between the executed application and the application builder. There is one side affect which drives me crazy sometimes.

TAB 1: Executed application
TAB 2: Application Builder

How do I work?
I edit an item inside the application builder (for example the LOV select statement) and click on save. I immediately change the TAB and press F5 to update the application.

What drives me crazy?
If I have made a mistake in the select statement of my LOV I just get a error message inside the browser page. But I changed the TAB faster then the result appeared and I didn't recognize it.

It would be great if the TAB title would have changed too. So that I could see that an error occurred.

Update 12.12.2013:
New APEX feature request added on:
Text: Browser TAB error message

APEX-AT-WORK no image

Working with XML files and APEX - Part 2: Selecting data from XMLType column with XMLTable

Von Tobias Arnhold → 12.05.2013
After I described how to successfully import XML files into an APEX application. It's time to start to analyze them.

I still assume that this is the example XML file:
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
  <league id="1" name="2. Bundeliga">
        <name location="Dresden" stadium="Glücksgas-Stadion">SG Dynamo Dresden</name>
        <name location="Cologne" stadium="RheinEnergieStadion">1. FC Köln</name>
        <name location="Berlin" stadium="Alte Försterei">1. FC Union Berlin</name>
        <name location="Düsseldorf" stadium="Esprit Arena">Fortuna Düsseldorf</name>
The first select will find all team names and locations:
-- table: IMP_FM
-- XMLType column: XML_FILE

       IMP_FM FM,
       XMLTable('/leagues/league/teams/team/name' PASSING FM.XML_FILE
       COLUMNS  "TEAM"         VARCHAR2(255) PATH 'text()',
                "LOCATION"     VARCHAR2(255) PATH '@location'
                ) T
What am I doing here?
In the FROM-clause I define the XML table "IMP_FM" and right after that I generate a new XMLTable object. Inside this object I define the start entry "/leagues/league/teams/team/name" from where I want to select the data. In the PASSING-clause I define the XMLType column from where the data is coming.
Next step is to set the SQL columns based on the XML data.
To get the team names I need to select the text from the xml element "name". To do that I use the function "text()".
To select the attribute "location" I need to use an "@" in front of the attribute name.
 This newly created table will have the name "T" and can be selected in the SELECT-clause.

In the next example I will select league and team names. Actually they would be two tables with an 1-n relationship.
Because of that I have to implement two XMLTable objects in the select statement.
       IMP_FM FM,
       XMLTable('/leagues/league' PASSING FM.XML_FILE
       COLUMNS  "LEAGUE"     VARCHAR2(255) PATH '@name',
                "T_XML"      XMLTYPE       PATH 'teams/team'
                ) L,
       XMLTable('team/name' PASSING L.T_XML
       COLUMNS  "TEAM"         VARCHAR2(255) PATH 'text()',
                "LOCATION"     VARCHAR2(255) PATH '@location'
                ) T
What am I doing here?
In the first XMLTable object I generate a XMLType column with the data from path "teams/team". This column is named as "T_XML". The generated table is named as "L".
Now I create a new XMLTable object based on the data of the XMLType column "L.T_XML". I follow the same logic as in first select and just use a shorter PATH "team/name".
More information about XML sub-selects can be found in this blog post: New 12c XMLTABLE’s “RETURNING SEQUENCE BY REF” clause

In the last example I want to show how to work with XML namespaces. For that our XML changes a bit.
<?xml version="1.0" encoding="UTF-8"?>
<leagues xmlns:tt="" xmlns:xsi="" xsi:schemaLocation="" version="2.1" xmlns="">
  <league id="1" name="2. Bundeliga">
        <tt:name location="Dresden" stadium="Glücksgas-Stadion">SG Dynamo Dresden</tt:name>
        <tt:name location="Cologne" stadium="RheinEnergieStadion">1. FC Köln</tt:name>
        <tt:name location="Berlin" stadium="Alte Försterei">1. FC Union Berlin</tt:name>
        <tt:name location="Düsseldorf" stadium="Esprit Arena">Fortuna Düsseldorf</tt:name>
To select the data now is a bit more complicated:
       IMP_FM FM,
       XMLTable(XMLNameSpaces('' as "tt", 
                              default ''),
               '/leagues/league/teams/team' PASSING FM.XML_FILE
       COLUMNS  "TEAM"         VARCHAR2(255) PATH 'tt:name/text()',
                "LOCATION"     VARCHAR2(255) PATH 'tt:name/@location'
                ) T
What am I doing here?
Inside the XMLTable object I define a "XMLNameSpace" which is marked as "tt".
Inside the column definition I use "tt:" to select the data.
More information about multiple Namespaces can be found here:

That's it for today. Cheers Tobias

New beta version of the APEX Blog-aggregator is online

Von Tobias Arnhold → 12.03.2013
Check out the updated blog aggregator on

Finally some major usability extensions were integrated. Looks like APEX to me.

  - You can search the blog posts at least back into the year 2008.
  - Watch the last 100 posts
  - No more problems with duplicated posts or tweets
  - Seems really fast to me

Update 04.12.2013:
Got a comment from Buzz Killington and unfortunately deleted it (damn Smartphone). Here is the text:


Here are my thoughts:

1) They need to get rid of the double-scrollbar iframe thing. It is pretty unusable.

2) I'm pretty sure it's an APEX feature, but if you scroll to page 2 (11-20) and then close your browser, the next time you get back you're still on page 2. It's counter-intuitive - you should always go back to page 1 otherwise you'll miss new posts.


My thoughts:
1) I understand the handling issues you have. As far as I know there are not much options to change the behavior in including an iframe.

2) Right. I think this can be easily changed using a reset pagination in the iframe URL.

APEX-AT-WORK no image

Update an APEX tree dynamically

Von Tobias Arnhold → 12.01.2013
I was asked by an APEX developer if it is possible to dynamically update an APEX tree by changing an APEX item.

Example select:
select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
       ENAME as title,
       NULL  as icon,
       EMPNO as value,
       ENAME as tooltip,
       NULL  as link
from EMP
start with (:P1_MGR is null AND MGR is null OR :P1_MGR is not null and MGR = :P1_MGR)
connect by prior EMPNO = MGR
order siblings by ENAME

As far as I know APEX has no out of the box function for that. The dynamic action to refresh reports doesn't work with trees. Or am I wrong here?
You can follow different workarounds no one of them will fulfill all your hopes:

1. Wait for APEX 5.0 maybe the APEX team will include a dynamic tree update functionality?
2. Build some custom JS code to dynamically change the tree.
3. Don't make it dynamically. Submit the page after you have changed the APEX item.
4. Use a third party solution for example: dhtmlx tree
5. Build your own tree solution (plug-in).

I would tend to solution 3 and would wait for the next version of APEX. If the customer doesn't accept it and would pay the more efforts I would tend to a third party solution.  

Update 03.12.2013:
Take a look at Tom's blog he made a couple of really good tree based blog posts.