APEX-AT-WORK no image

Advanced APEX trees

Von Tobias Arnhold 9.26.2008
Last couple of weeks/days when i went through the Oracle APEX forum there was quite a lot of questions about the APEX trees.
I really worked a lot with them and want to describe how to use them.

1. This how to will base on the following table ddl:
2. How does the standard SQL selection of a APEX tree look like

select "S_ID" id, -- Primary key
"S_PARENT" pid, -- Parent key
"S_NAME" name, -- Displayed value (node description)
'f?p=&APP_ID.:1:&SESSION.::NO::P1_SYSTEM:'||"S_ID" link, -- linked page
null a1,
null a2

3. How to improve the displayed value (node)
You can concat several columns:
-- name with description
"S_NAME" || ' (' || "S_DESCRIPTION" || ')' AS name,

-- name with html (big)
'<b>' || "S_NAME" || '</b>' AS name,

-- name with icon in front of it
'<img src="#WORKSPACE_IMAGES#tree_group.png" border="0" style="vertical-align:middle" alt="Group">&nbsp ' || "S_NAME" AS name,

-- Using of the case command to see different node values
WHEN "S_TYPE" = 'GROUP' THEN '<b>' || "S_NAME" || '</b>'
WHEN "S_TYPE" = 'SYSTEM' THEN "S_NAME" || ' (' || "S_DESCRIPTION" || ')' END AS name,

4. How to use the link column
-- Standard link column would link to page 1 and overwrite the
-- value of :P1_SYSTEM
'f?p=&APP_ID.:1:&SESSION.::NO::P1_SYSTEM:'||"S_ID" link,

-- Using several values to overwrite in page
'f?p=&APP_ID.:1:&SESSION.::NO::P1_SYSTEM,P1_S_NAME,P1_FROM_TREE:'|| "S_ID"||','||"S_NAME"||',YES' AS link,

-- Link to a specified page via node value
'f?p=&APP_ID.:' ||"S_ID" || ':&SESSION.::NO::' AS link,

-- Link to a specified page via node value and javascript
'javascript:change_page(' || "S_ID" || ')'

Go Edit Page > HTML Header
<script language="JavaScript" type="text/javascript">
function change_page(v_page) {
var v_url = 'f?p=&APP_ID.:' + v_page + ':&SESSION.::::';
window.location.href = v_url;

-- If you use your tree as a pop up tree and want to give back a node
-- value to the parent page with submit and close the pop up
-- page automatically afterward
-- Info: I used it as a self made pop up tree item
'javascript:close_page(' || "S_ID" || ',''' || "S_NAME" || ''')' AS link,

-- I also used a hidden item in the pop up page to fill with the parent
-- page id &P101_WHEREFROM.
Go Edit Page > HTML Header
<script language="JavaScript" type="text/javascript">
function close_page(v_id,v_value) {
var v_page = 'P' + '&P100_WHEREFROM.' + '_S_ID';
var v_page_display = 'P' + '&P101_WHEREFROM.' + '_S_NAME';
var l_field_id = opener.document.getElementById(v_page);
l_field_id.value = v_id;
if(l_field_id.getAttribute('onchange') || l_field_id.onchange) {l_field_id.onchange()}
var l_field_value = opener.document.getElementById(v_page_display);
l_field_value.value = v_value;
if(l_field_value.getAttribute('onchange') || l_field_value.onchange) {l_field_value.onchange()}

I used these examples in my application TIA: http://apex.oracle.com/pls/otn/f?p=25500:1
Login: guest Password: apexuser
Download it from: http://www.oracle-apex-award.de/TIA-Technische-Infrastruktur.70.0.html
The application is in German but the developments (used variables, hints) are made in English.

Hope you can use it in your application and of course you are welcome http://www.blogger.com/img/blank.gifto give me some feedback.

Update 12.09.2011:
The example application doesn't work anymore. I currently work on a new version which includes the new APEX tree as well:
I will try to extend the view of the standard APEX tree as well as I did with the old one. Of course I will write about it. :)

Update 26.09.2011:
I just saw a nice example application about APEX trees:

Post Tags:

13 Comments " Advanced APEX trees "


Very nice read,Tobias.
I need this tree structure to display application data. Still have to understand how the pid and id thing is interpreted by apex ...


Hallo Tobias,

ich habe versucht, mit "guest" und "apexuser" einzuloggen, aber geht nicht.

Und vielen Dank, die Anwendung scheint, ein Problem eine meiner Anwendungen zu loesen.

Steve Stanley

Tobias Arnhold 29 October, 2009 08:15

Hallo Steve,
wo hast du dich denn versucht einzuloggen? Wenn du die Anwendung gerne downloaden möchtest gib einfach bescheid.




Hi Tobias,

I cannot login using the information provided by you. (guest/apexuser)


Tobias Arnhold 06 November, 2009 12:19

Hi Steve, hi Benz!

I just saw that somebody deleted the guest user. Anyway now it should work again.

Thanks for the hint.

Laahari 02 September, 2010 17:40

Hi Tobias,

I cannot login using the information provided by you. (guest/apexuser).I guess some1 deleted again.Please update.

bcarlis_2000 18 October, 2010 13:04

Nice Tree example.. good work!
What User name and password is needed to download the app?
I tried guest/apexuser and it did not work for that.
I did get in to view the app with guest/apexuser though.
Thank you, bcarlis_2000@yahoo.com

bcarlis_2000 18 October, 2010 13:20

Tobias.. Have you ever searched a tree?
If you have 50 root/trunks with many branches and leaves to each, then search on key word that is found in the center of the 14th root/trunk.
I want to show only that trunk including all it's branches and leaves. Also if 4 of the 50 are a match then the results would be just those 4 trunks including all their branches and leaves.
Seemed easy but I could not do it.
thanks for any help,
Bill bcarlis_2000@yahoo.com

Albert Roth

Hi Tobias,

I would like to check the tree sample and to download it.

I assume the account is locked or something else.
When trying to download i am asked for User and Password.

I didn't find a valid combination.
What must I do.

Please contact me, my email is albert.roth@siemens.com

mnaegler 23 August, 2011 14:36

Schade weder der Download noch die Demo laufen da der Login nicht akzeptiert wird :(

Wäre nett wenn Du das nochmal korrigieren würdest.

Tobias Arnhold 23 August, 2011 19:48

Ich arbeite im Moment an einer neuen Version des Logbuchs. Diese wird die besten Plug-Ins beinhalten und auch den aktuellen APEX Tree.
Die alte Anwendung ist aufgrund der vielen APEX Updates nicht mehr aufrufbar und würde zu viel Aufwand verursachen diese jetzt noch mal anzufassen.

Ein Update kommt in den nächsten Monaten.




Hi Tobias,

I am unable to login with guest/apexuser.


Denzel 12 March, 2012 14:21

Hello ,
I've also trouble with conncting with guest/apexuser.
Please any new pwd