Advanced APEX trees

TREE

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:

CREATE TABLE  "LOG_SYSTEMS"
( "S_ID" NUMBER NOT NULL ENABLE,
"S_NAME" VARCHAR2(100) NOT NULL ENABLE,
"S_DESCRIPTION_SHORT" VARCHAR2(100),
"S_PARENT" NUMBER NOT NULL ENABLE,
"S_RESPONSIBLE_DEPARTMENT" VARCHAR2(5),
"S_CONTACT_PERSON" VARCHAR2(100),
"S_TYPE" VARCHAR2(20)
CONSTRAINT "PK_S_ID" PRIMARY KEY ("S_ID") ENABLE
)

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
from "#OWNER#"."LOG_SYSTEMS"

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
CASE
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;
}
</script>

-- 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()}
close();
window.opener.doSubmit('POPUP_RELOAD');
}
</script>

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:
http://apex-at-work.blogspot.com/2011/09/logbuch.html
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:
http://apex.oracle.com/pls/apex/f?p=36648:27

APEX_MAIL with UTL_TCP under XE database

APEX examples

APEX_MAIL with UTL_TCP under XE database

Von Tobias Arnhold → 9.24.2008

If you want to use the features of the UTL_TCP package you need to publish it to your APEX application user. (I tested it under APEX 3.1.2)

How to:

-- sqlplus
GRANT EXECUTE ON "SYS"."UTL_TCP" TO "APEX_USER"

-- sqlplus
create or replace synonym UTL_TCP for SYS.UTL_TCP;

-- an APEX process procedur
-- apex_mail procedure call
apex_mail.send(
p_to => 'user@company.com',
p_from => 'info@company.com',
p_body => 'New message cerated from ' || :p1_user || '.' || utl_tcp.crlf ||
'Description: ' || utl_tcp.crlf || :p1_description,
p_subj => 'New message! ' utl_tcp.crlf);

-- push the e-mail queue for immediate delivery
wwv_flow_mail.push_queue(
P_SMTP_HOSTNAME => 'ip',
P_SMTP_PORTNO => 'port');

Update (08.09.2009):
There was a interesting question about the APEX_MAIL function and pushing the email queue in the Oracle forum which you may be interested on: apex mail - mail queue

APEX DoSubmit button with js before pl/sql processes

APEX examples

APEX DoSubmit button with js before pl/sql processes

Von Tobias Arnhold → 9.17.2008

Sometimes you need to use javascript after click on a button before the APEX validations and processes starts.
(For example you have a report with editable fields and maybe a save button on every row. In these special cases you could need something like that.)

Go on edit Button > URL Redirect > change/add
Target is: URL
URL Target: javascript:BUTTON_ACTION();

Edit Page > HTML Header > add the new javascript

<script language="JavaScript" type="text/javascript">
function RR_ACTION_UPDATE() {
// P1_ACTION field into JS variable
var l_field_id = document.getElementById("P1_ACTION");
// Now you are able to put in every type of code
// example: set value for field l_field_id.value =
// 'UPDATE';
// calculate: l_field_id.value = 5 + 5;

// doSubmit action
doSubmit('DOSUBMIT');
}
</script>

APEX Report with changing icons on row level

APEX examples

APEX Report with changing icons on row level

Von Tobias Arnhold → 9.10.2008

If you want to show different icons based on a column value in your report.
Create a selection set for your report which works with the decode function (I used the example table emp):

Source code:

SELECT
e.empno,
e.ename,
decode
(e.job,
'PRESIDENT',
'<a title="President"><img src="#WORKSPACE_IMAGES#president.png" border="0" alt="president"></img></a>',
'MANAGER',
'<a title="Manager"><img src="#WORKSPACE_IMAGES#manager.png" border="0" alt="manager"></img></a>',
'ANALYST',
'<a title="Analyst"><img src="#WORKSPACE_IMAGES#analyst.png" border="0" alt="anaylst"></img></a>',
'<a title="Others"><img src="#WORKSPACE_IMAGES#other.png" border="0" alt="other"></img></a>')
AS only_with_job_icon,
decode(e.job,
'PRESIDENT',
'<a href="javascript:PopUpPage2_REPORT(''' || e.job ||
''');" title="President"><img src="#WORKSPACE_IMAGES#president.png" border="0" alt="president"></img></a>',
'MANAGER',
'<a href="javascript:PopUpPage2_REPORT(''' || e.job ||
''');" title="Manager"><img src="#WORKSPACE_IMAGES#manager.png" border="0" alt="manager"></img></a>',
'ANALYST',
'<a href="javascript:PopUpPage2_REPORT(''' || e.job ||
''');" title="Analyst"><img src="#WORKSPACE_IMAGES#analyst.png" border="0" alt="anaylst"></img></a>',
'<a href="javascript:PopUpPage2_REPORT(''' || e.job ||
''');" title="Others"><img src="#WORKSPACE_IMAGES#other.png" border="0" alt="other"></img></a>') AS link_via_js_and_job_icon
FROM emp e

Column description:

• only_with_job_icon - Only show an icon without linking to any page

• link_via_js_and_job_icon - Show an icon with linking to another page via javascript

Create the javascript for the link column: link_via_js_and_job_icon

Go into Page Attributes > HTML Header and add a javascript like:

Source code:

<script language="JavaScript" type="text/javascript">
function PopUpPage2(v_job){
url = "f?p=&APP_ID.:2:&SESSION.::::P2_JOB:" + v_job;

w = open(url,"winLov","resizable=yes,
scrollbars=yes,status=no,width=1152,height=768");

if (w.opener == null)
w.opener = self;
w.focus();
}
</script>

Using CSS in APEX pages and regions

APEX examples

Using CSS in APEX pages and regions

Von Tobias Arnhold → 9.02.2008

If you want to use your own style sheets in your application here an example how to do it:

• Go Edit Page Attributes --> HTML Header --> Add css text and Apply Changes

• Go Edit Region --> Title: <h1 >Headline </h1 > and Apply Changes

In my case I made a select list item with a list of values to change the css in the page title dynamically.
In my example application you can see how it would look: http://apex.oracle.com/pls/otn/f?p=25472

Edited 07.10.2008:
I had the problem in one of my headlines that I wanted to use some text with bold and some without. It always put in a line break in between the title.
How to avoid that:

• Go Edit Page Attributes --> HTML Header --> Add css text for the span command
  

• Go Edit Region --> Title: <h1>Headline: <span>(Detailed information)</span> </h1>