Wednesday, March 20, 2013

Apex tree and form one one page

I had an idea to combine a tree and a form in one page. Actually, just because I never used a tree before (yes, really), and also because I couldn't find an example on internet. The Oracle Sample Trees application shows how you can use a tree node with a link to another form page. That is a waste of a lot of space on the right side of the page. What I wanted is this:

Basically, you need to have a tree, a form and a dynamic action that refreshes the form when you click on an employee node. A lot of it I picked from Scott Wesley's blog.

Create the tree region

The tree uses this query

select case when connect_by_isleaf = 1 then 0
            when level = 1             then 1
            else                           -1
       end as status,
       label||' '||name as title,
       null as icon,
       id as value,
       name as tooltip,
       'javascript:pageItemValue('''||id||''')' as link
select 'D' item_type,
       null label,
       to_char(d.deptno) id,
       null parent,
       d.dname name,
       null tooltip,
       null link
from dept d
union all
select 'E' item_type,
       null label,
       to_char(e.deptno)||'_'||to_char(e.empno) id,
       to_char(e.deptno) parent,
       e.ename name,
       null tooltip,
       null link
from emp e
start with parent is null
connect by prior id = parent
order siblings by name

Nothing special here as you can see (I included an item_type in the query for possible use later on).

Add the form region

Scott uses a classic report report region to refresh when you click on a node, where I needed a form region. My first attempt was to create a "Form on a Table or View" region, but I soon got stuck here. This kind of form uses a After Header process to fetch a row, and therefore only works on a page submit. I needed a partial page refresh. (Maybe it is possible to use this process in an Ajax call too, but I have no idea how to do that). So, I used a Tabular Form instead, based on EMP, where I would query just one record. After the region is created using the wizard (choosing Update functionality only in this case), slightly modify the query by adding a WHERE clause

from "#OWNER#"."EMP"
where "EMPNO"= to_number(substr(:Pn_SELECTED_NODE,4))

(I will get back on Pn_SELECTED_NODE. Include this item in the "Page items to Submit").

I've mainly used Apex 4.1, so with the new templates of Apex 4.2, I was a bit confused on how to get the form region to the right of the tree. You just have to set New Column = Yes in the Grid Layout of the form region.

Add a hidden item and some Javascript

As In Scott's example, add a hidden page item Pn_SELECTED_NODE to the page. It is important is to set Value Protected to No for this item.

Add the Javascript to to page header:

function pageItemValue(node)
  $s('Pn_SELECTED_NODE', node);

Create a dynamic action

Add the dynamic action to Pn_SELECTED_NODE, so that the form refreshes when you click on a node.

For this refresh to work, you have to to set Enable Partial Page Refresh to Yes in the Report Attributes for the tabular form region.

Get back to the same node after submitting the page

Now, this was actually the most frustrating part. On the tree, you have to set Selected Node Page Item to Pn_SELECTED_NODE to come back to the same node you selected before.

This just wouldn't work at all. I still have no idea why, though. I could see the correct value using the Session link when running the page, but after submitting the page (saving the updates in the form), the previously selected node just wouldn't highlight again.

At this point you just start to try about anything to get it to work. And voilà, the solution: on the branch back to the page, set Pn_SELECTED_NODE:

Monday, February 4, 2013

Apex and RESTful web services

You always read that it is so easy to consume RESTful or SOAP web services in Apex. Well, yes, when you know how it works. It took me some time to figure it out, googling for bits of information on every step. If you are struggling too, this may help you to get started.

The start lies in the database, and has nothing to do with Apex. You must make sure first that the web service you need can be reached from the database. For this, you have to configure a proper ACL (Access Control List). How to do this can be found in the Apex Application Builder User's Guide, Enabling Network Services in Oracle Database 11g. If you experience problems later on, read this blog by Joel Kallman for a possible explanation why it doesn't work.

To check if you can call the web service, first call it from within the database, before going to Apex. To test if it really works in Apex, you should probably run this logged in as the Apex schema owner (e.g. APEX_040100 for apex 4.1). Since that is usually not possible for developers, we will just assume (hope) it will work later on.
I have no idea what this web service at does, but you can use it as a demo. It is just a good example of a GET type web service with 5 parameters.

In the simplest test, you call the url directly using utl_http.request, as you would in your browser:

set scan off
select utl_http.request( '') weather
from dual;

Using Apex functionality, the URL, parameters and parameter values are split in different input parameters for procedure apex_web_service.make_rest_request:

set scan off
  v_result clob;
  v_result := apex_web_service.make_rest_request(
    p_url            => ''
   ,p_http_method    => 'GET'
   ,p_parm_name => apex_util.string_to_table('lat:lon:product:maxt:mint')
   ,p_parm_value => apex_util.string_to_table('39:-77:glance:maxt:mint')
This should return a big xml document. Later on, in Apex, I will extract the maximum temperature from this xml document. The maximum temperature can be found in this node:

<temperature time-layout="k-p24h-n7-1" type="maximum" units="Fahrenheit">
  <name>Daily Maximum Temperature</name>
  ... etc.

Now to Apex. I won't describe the basic steps that can be found in the User's Guide (like the fact that you create a web service reference in the Shared Components of the application).
You can use the url and parameters that you used when testing the web service in the database

The tricky part, for me anyway, was what to put in "Response XPath". This was new to me, so I had to take a crash course at XPath.Tutorial.
This is where the XML response you got when testing the web service in the database comes in. In this example, the XPath is:


which (sort of) means: select all value elements that are children of temperature elements that have an attribute named type with a value 'maximum'.

The REST output parameter path is now just "/*", since the XPath already goes down to the value element.

So, here is a trick to play with the XPath. You copy the part of the XML response you need and put that part as text in this query:

with c as
  (select xmltype('
                       Daily Maximum Temperature
                   ') xmltype001
   from dual               
select extractValue(value(t),'/*') "value"
from c
   , table(xmlsequence(extract(c.xmltype001,'//temperature[@type="maximum"]/value'))) t 

Now you can easily play around with the Response XPath ('//temperature[@type="maximum"]/value') and Output parameter Path ('/*') until it works.

Once this is all done it is easy to create a page for this web service.
Create a new page and select Form, Form and Reports on Web Service. For the rest, you just follow the wizard.

There is another version of RESTful web services that doesn't use parameters (or it uses a mixed version). An example is this URL:

This uses the airport code ('SFO') as part of the URL, and format as a parameter. So, how do you put the airport code in the Apex web service reference? This is simply done by substituting a page item in the URL, e.g.

In this case, you need to create a page item P5_AIRPORT_CODE manually. The wizard won't do this for you.