Tuesday, September 14, 2010

Adding a Context Menu to a Tree Region

One of the nice new features of Application Express is the new tree region. It is based on jsTree and supports features such as tool-tips. jsTree is the same tree used for the new tree view of the Application Builder Page Definition page. That page supports a right click context menu which is missing from the current version of the Application Express new tree region. With a little help from Patrick, I was able to add a context menu to a tree region I was working on for an update to the Document Library packaged application. The purpose of this article is to describe how to do this for your tree regions. I will use the EMP table as a simple example.

Patrick wanted me to mention that we may change the implementation of the tree in the future, so you may have to adjust the JavaScript code listed here in future versions of Application Express.

Let's start by creating a copy of the emp table, emp2, so you don't actually mess with the emp data. Use the SQL Workshop SQL Command Processor and the code in listing 1 to create emp2.

Code Listing 1

create table emp2 as select * from emp

alter table emp2 add constraint emp2_pk primary key (empno)

alter table emp2 add constraint emp2_fk foreign key (mgr) references emp2(empno)

Create Application and Tree

With emp2 in place you are ready to create an application. Simply run the create application wizard and create a form and report on emp2. You will replace the report on page 1 with a tree region. After the application is created, edit page 1. Delete the report region and then create a new tree region specifying the following options:

  1. Display Attributes, Title: Employees

  2. Tree Template: Default

  3. Table/View: EMP2

  4. Confirm that all values are defaulted on the Query step

  5. Tooltip: Database Column

  6. Tooltip Column: HIREDATE

Run the page and confirm that your tree appears. When you right click on a node, you should only see that standard right click options of whatever browser you are using.

Create Context Menu

Now for the magic! By sprinkling a little JavaScript on the page here and there, we will get a nice right click context menu. First edit the tree region and give the region a static ID of EMP2 as in figure 1. We need this static ID to make it easier to write JavaScript and select this region with jQuery.

Figure 1

Now you just need to sprinkle a little JavaScript on the page! Edit the page definition of page one and add the code in code listing 2 into the Function and Global Variable Declaration text area and the code in code listing 3 into the Execute when Page Loads text area.

Code Listing 2

function doAction(pNode,pTree,a){
var l_action;
var l_id;

l_id = pNode.attr("id");

if (a=="create") { l_action = "f?p="+$v('pFlowId')+":2:"+$v('pInstance')+":::2:P2_MGR:"+l_id }
if (a=="delete") { deleteEmp(pNode,l_id); }
if (a=="edit") { l_action = "f?p="+$v('pFlowId')+":2:"+$v('pInstance')+"::::P2_EMPNO:"+l_id }

if (l_action != null) {document.location.href=l_action; }

Code Listing 3

var lTreeContextMenu={
label:"Add Employee",
icon: "",
visible: true,
action: function(pNode, pTree){doAction(pNode, pTree, "create");}
icon: "",
visible: true,
action: function(pNode, pTree){doAction(pNode, pTree, "delete");}
icon: "",
visible: true,
action: function(pNode, pTree){doAction(pNode, pTree, "edit");}

// use jsTree to render the tree
var lTreeSel = apex.jQuery("#EMP2").find("div.tree");
var lTreeId = lTreeSel.attr("id");
var lDataId = lTreeId.replace("tree","");
var lTreeData = eval("l"+lDataId+"Data");
var lTree = lTreeSel.tree({
valid_children: "folder"
valid_children: "file"
valid_children: "none",
max_children: 0,

$.showTooltip = function(pEvent) {
var lAction = apex.jQuery(pEvent.target).attr("tooltip");
if (lAction && lAction != "") {
}; // showTooltip

// Bind Tooltips for tree nodes
apex.jQuery('a[tooltip]', lTreeSel).bind("mouseover", $.showTooltip);

// Hack for right click problem on selected node
apex.jQuery("#EMP2").find("a").live("mouseup",function() {apex.jQuery("#EMP2").find("a").removeClass()});

Now when you run the page you should see a right click context menu when you right click on a node in your tree, like figure 2. Note that the Add Employee and Edit actions are implemented and working. The delete action is not yet working, that will be added next.

Figure 2

Implement Delete Action

Technically the delete action is already implemented. You could edit the employee and just click delete on the next page. What sounds like more fun is to call an on demand process using AJAX from some JavaScript, and then use jQuery to remove the node from the tree, display a confirmation message, all without doing a page submit. First things first though, create a region that will hold the message specifying the following options:

  1. Region Type: HTML

  2. Title: Message Container

  3. Region Template: No Template

  4. Sequence: 5

  5. Region Source: See Code Listing 4

Code Listing 4

<div class="success" id="success-message" style="display:none;">
<img src="#IMAGE_PREFIX#delete.gif" onclick="apex.jQuery('#success-message').hide()" style="float:right;" class="remove-message" alt="" />
<div id="theMessage">This is a placeholder for messages</div>

Now we have a place for messages. Next, create a process on page 1 with the following options:

  1. Process Type: PL/SQL


  3. Point: On Demand – Run this process when requested by AJAX

  4. PL/SQL Page Process: See Code Listing 5

Code Listing 5

delete from emp2 where empno = apex_application.g_x01;
owa_util.mime_header('text/xml', FALSE );
htp.p('Cache-Control: no-cache');
htp.p('Pragma: no-cache');
htp.p('{result:"success",message:"Employee successfully deleted"}');
exception when others then
owa_util.mime_header('text/xml', FALSE );
htp.p('Cache-Control: no-cache');
htp.p('Pragma: no-cache');
htp.p('{result:"failed",message:"Error deleting employee: '||sqlerrm||'"}');

Finally, you add the JavaScript to do the AJAX call, display the result message, and remove the node from the tree. Edit the page attributes and add the code in code listing 6 in the Function and Global Variable Declaration text area before the existing doAction function.

Code Listing 6

function confirmSubmit(){
var agree=confirm("Are you sure you wish to continue and delete?");
if (agree)
return true ;
return false ;

function deleteEmp(pNode,pId){
var lTest = confirmSubmit();
if (lTest) {
var get = new htmldb_Get(null,$v('pFlowId'),'APPLICATION_PROCESS=DELETE_EMPLOYEE',$v('pFlowStepId'));
gReturn = get.get();
var j = eval("("+gReturn+")");
if (j.result == "success") {

get = null;

Now run the page and unroll Clark. Right click on Miller and choose delete. Confirm the delete and the result should be similar to figure 3.

Figure 3

Tuesday, July 6, 2010

Application Express 4.0 Web Services Evaluation Guide

I just returned from ODTUG Kaliedoscope 2010, (awesome event, best one I have been to), and promised during my presentation that the Application Express 4.0 Web Services Evaluation Guide would be posted on the Application Express Web Services Integration page on OTN. Well it now is, so I kept my promise. ;)

If you missed me at ODTUG, you can see me give a very similar presentation this year at Oracle Open World. The session is at 12:30 on Thursday September 23rd, so don't get too crazy at the appreciation event the night before!

Update 7/28/2010: A patch set exception was created for the issue the Roel reported in the comments below. Log on to Metalink and search for 9848562.

Friday, February 26, 2010

Application Express 4.0 Early Adopter Phase II (EA2) Available

The Application Express 4.0 Early Adopter Phase II (EA2) is now available. Like EA1, this instance is running on Amazon EC2. The beauty of Amazon's elastic IP feature is that we could work on the EA2 instance and get it completely ready, then associate the elastic IP with the new instance, making the switch in less than 60 seconds. You can still access EA1 for the next couple of weeks in case you need anything from that instance. You will need to sign up for a new workspace on EA2.

One nugget I would pass on is that if you run Oracle in the cloud and use Elastic Block Storage for your data files, you should also change the parameter diagnostic_dest to point to your EBS volume instead of the volatile volume that only lives with the instance. The reason is that you could fill up that volume pretty quickly if there is a lot of action on your instance and cause your database to shutdown.

Have fun with EA2 and especially kicking the tires of Websheets!

Friday, January 8, 2010

OTN Developer Days in NYC

Are you in the NY, NY area? If so you should definitely attend the free OTN Developer Day- Hands-on Oracle Database 11g Applications Development event at the New York Marriot Marquis on January 13, 2010. I will not be there, but much smarter colleagues of mine will like Mike Hichwa (VP of Development Tools, father of Application Express), Marc Sewtz, David Peake and Christina Cho.

Bring your own laptop for the labs.