Why "#OWNER#" in APEX interactive report ??

The default APEX generated interactive report has "#OWNER#" instead of the schema name...

Now, I'm not sure how that helps and this might be a bug as I cannot seem to be able to calculate any other columns (by using the COMPUTE feature of the interactive report). I kept getting this message:
"Invalid computation expression. ORA-00942: table or view does not exist".

When I removed the "#OWNER#.", miraculously the COMPUTE feature started working.

hmmm... this sure is one of the mysteries of APEX....

Linking in between tree #DRILL_DOWN#

This post is an addon to my previous post: Customizing APEX trees.

The previous post was pretty much complete EXCEPT (offcourse there's an exception) for the cases when the user was expanding or collapsing the tree !

To interfere with the process of expanding or collapsing the tree (we will generically define this as drilling from now on in this page) we need to take a look in the tree definition page at the Dynamic templates section.

We can find there, this tiny piece of info that looks like:

a href="#DRILL_DOWN#"

This is where the linking is made, and the #DRILL_DOWN# string contains everything we need: the method (expand or contract) and the node id that has to be applied that method.

So, as we did before, we can put a call to a Javascript instead of the direct link, append all our form field values to the link and only afterwards we can redirect the page...

Instead of the above, we can therefore have something like:

a href="javascript:treeDrilling('#DRILL_DOWN#');"

And in the treeDrilling we can easily redo everything we have done in the previous tutorial with the persistFormData function ...

Maybe the only thing that one may note there is the format of the #DRILL_DOWN# string, which is obviously a link like:

f?p=&APP_ID.:7:&APP_SESSION.:EXPAND,NODE_ID

OR

f?p=&APP_ID.:7:&APP_SESSION.:CONTRACT,NODE_ID

The expand and contract commands are given inside the APEX link, just after the session id, AND we can still append to the above the values of our form data !!

Once again - I hope this helps !

Customizing an Oracle APEX tree

The following have been tested and used in Oracle 10g rev2 with APEX 3.1 installed.

One may find it difficult to work with the standard Oracle APEX tree, but with a few small tweaks you can surely do what you want.

Prerequisites:
Please check how a standard Oracle APEX tree is done here:
http://www.oracle.com/technology/products/database/application_express/howtos/howto_tree.html


So, here's the hypothesis of the problem:
- We want to have a tree next to or inside an APEX form.
- There is one tree node that is currently highlighted and the user can select/highligth other nodes as he/she wants WITHOUT interfering and/or loosing the form data typed.



Lets proceed to the demonstration:

The second idea included in the hypothesis is pretty much easy to solve, provided that one may find this other link:
http://www.inside-oracle-apex.com/2007/06/highlight-current-tree-node.html

Now that we solved half the problem, we may want to solve this "nasty" bug: keeping/persisting the form data as the tree is browsed.

To do that, we first need to be perfectly knowledgeable of the format that APEX uses for its links:
http://gitb.it.lt/i/doc/concept_url_link.htm

But how does that help ?

Well, from the above page about APEX links, we can find out that we could pass on variables through the link (basicly using HTTP GET vars), but how to do that from the tree ?

We can use Javascript and do the following:

instead of linking the tree directly to a submit link, we could use an intermediary call to a Javascript that reformats the link and then submits the page with the form variables data included in the link ! This will give us the data persistance that we need.

A small example:

select CURRENT_TREE_NODE_ID id,
NODE_PARENT_ID pid,
CASE when CURRENT_TREE_NODE_ID = :CURRENT_FORM_ITEM THEN
''LEAF_INFO''
ELSE LEAF_INFO END name,
'javascript:persistFormData('CURRENT_TREE_NODE_ID');' LINK,
null a1,
null a2
from TREE_DATA

and we can now put the persistFormData function inside the header or even a separate region:

function persistFormData(passVal1)
{
var link1,link2;

link1 ='f?p=&APP_ID.:1:&APP_SESSION.::NO::CURRENT_TREE_NODE_ID;
link2 = ':'+passVal1;

link1=link1+',FIRST_FORM_ITEM';
link2=link2+',%5C'+encodeURIComponent(document.getElementById('FIRST_FORM_ITEM').value)+'%5C';

link1=link1+',SECOND_FORM_ITEM'; link2=link2+',%5C'+encodeURIComponent(document.getElementById('SECOND_FORM_ITEM').value)+'%5C';

/*and so on for the entire form*/

window.location = link1+link2;
}

The tricky part in the function above ?
- The link format that is completely described in the refferenced tutorial (note that I used page number 1 for this example).
- The usage of enclosing '%5C' (or /) for the variable values to be able to pass on commas or any special characters !
- The link gets appended all the name and values for the entire form !
- In the end, the javascript code forces a refresh of the page using window.location (tested ONLY with IE7)...

Thats about all,

I hope this will really help ppl as other blog tutorials really helped me.

Gl.