Guru status on OTN!

About a week ago I recieved the double-golden-badge aka guru status on the OTN forums by hitting 2500 points. Hurray! I'm finally joining the elite! ;-)
I've only ever posted in the apex forums and the ascend was slow. Mainly in part because there are always periods of more and less activity at the job, leaving me with some more time and/or gusto to tackle problems there in my free time. I've also been active on StackOverflow in the oracle-apex tag, though the activity there is rather low (though as of late more people are starting to find their way there it seems).
Looking back at it though, it's undoubtedly one of the best things I've been engaging in for my self-development. Sure, there is quite a bit of time sunk in it. You usually don't fix someone's problem in just 5 minutes. It requires some dedication to it. Since getting guru I've flaunted my status at some collegues, mostly for fun of course. Though many don't really understand why I even do it, and I bet that is not limited to just my collegues.
Why then? Personally, I do it to challenge myself. I NEED a challenge, I can't put it any other way. I strive to keep learning. I don't want to get stuck in a rut. I don't want to keep doing the same thing over and over. And sometimes that is exactly what the job requires of you: do the same thing you've done twenty times before. Create a report with a form, and make it all perfect. Is that even fun anymore? Not really, and work isn't always about being fun all the time, and even a basic report+form is something that is simply necessary. I don't want to slam these things at all, they're just repetitive after a while.
Getting on the forums or SO and finding a problem I correlate with or deem interesting is something to keep myself going. Maybe it's just a slight variation on something that I've done 50 times over, but haven't encountered myself yet. Maybe it's pertaining to a part of apex that I haven't dealt much with. Maybe it's just pure javascript.
That's all good! I've spent much time in setting up examples, writing selects and pouring over documentation in order to get to the bottom of something. I'm happy Ive done all that! It's allowed me to not only flesh out my knowledge about apex, but also of the technology around it: HTML, CSS, javascript. I've managed to collect rich library of scripts and tips and tricks that I can regularly fall back on to solve a particular problem. I can more easily and faster get to the core of a problem than I used to, by being able to better identify the problem and knowing where to look, and asking the correct question. Most of all, I've learnt so much by forcing myself to set up examples, by reading documentation.
This goes hand in hand with ambition, by trying to improve and a drive to learn. By wanting to do better, by being able to stop and stand still and ask yourself whether what you've done is really the best solution there is, being critical in a constructive way.
Was it always fun? Ah, not always. My number one gripe would be when people simply don't credit someone's help with a simple click to acknowledge a helpful or correct answer on OTN. The StackOverflow structure is much better in that regard.
There is also a noticeable critical absence of knowledge with many, many developers. Especially when javascript is concerned. I often get very, very annoyed at some of the ignorance shown. To me, apex development is NOT only PLSQL, SQL and being able to navigate in apex. Valid HTML, CSS and selectors and javascript basics are, to me, core to apex and the development therein. When I see someone with a clearly copied construct which they don't understand I rage a little inside. How do you expect to grow your knowledge in such a way? How can it be oftentimes be clearly visible that documentation has not been read at all. How much would you want to abuse javascript to get something done and not understand what you've done? How can it be that seemingly still noone understand the f##-arrays and their use in tabular forms? Sometimes the first page on OTN is literally filled with questions in those veins! I admit I hardly find the resolve to react to those questions anymore. I tip my hat to Jari and Paul, the pillars of OTN apex.

I guess what I'm trying to explain here is that participating in this community is an enriching experience, for me and for those other participating. Getting that guru status is, in my eyes, a small mark of respect for the work I've been putting in, the amalgamation of it over the course of the last years. I -AM- proud of it! It IS (well, was) a milestone I've been working towards.

So, thanks, apex community! I have no intention of stopping to participate, I hope you won't either' :-)

(maybe I'll even write a blogpost again this year. I've halted it in part due to my job.)

Implementing an ajax tree in apex

So ajax trees. That sounds great right? To load the data on demand and not in one huge chunk? Honestly, I've noticed slowness/slowdown when there are even just a few hundred nodes around. And why? Often most of the data is never shown or needed, as people implement the link portion in the tree component. As a result, often there is only the selected node's branch and parent branches open. Yet I feel for the tree in apex. It is a component that wholeheartedly needs some love and some expanded options. For example the bundled version of jstree is plainly put ancient. It functions well but it suffers from clarity in documentation and location thereof and actual examples to be found on the net. The newer version, 1.0.0 has been released for a long time now, and while the look and feel can be exactly the same, this time around it has become a lot better on the developer's side of things.
Having said that, I looked at how to implement an ajax tree for both the current bundled version (which as of 4.2.3 is still 0.9.9a2) and using a 1.0.0 version.
Disclaimer though: there is a lot of javascript involved. Honestly, know what you're in for with this. If javascript and ajax aren't hurting your brain by just reading about it then you'll be fine, but otherwise steer clear. Do I say this because this is hard? Not really, personally I think that once you see an example it will slot down easily. But if you're only setting out in apex and only made baby steps in the way of javascript and html then for your own sake do not implement this in any demo or production system. Toy around all you like, but don't implement something for a customer if you can not support it.

And when? I'd say implementing an ajax tree has its uses and place. The downside is that I have not made this (yet?) into a plugin, so there is no declarative apex goodness. Using the standard tree component it will simply all be there. But if and when you start to feel the tree is slowing you and your users down, then the time may have come to look into your options.
So jstree then. I know there are other implementations around or workarounds or anything, but I decided to stick with jstree. It only makes sense that one day we'll have an upgraded version in apex. As I mentioned before: the newer version is tons better, and will allow someone with a grasp of what is going on and some knowledge of javascript to easily implement more features to his tree.

Basic setup

Basically an ajax tree setup is rather simple. To get data there need to be ajax calls. Those calls will require an application process. The process will need to return data in a certain form so that jstree can correctly parse it and create the html.
I also opted to load every node. I could have fed the tree some static data first, but I didn't do that here. You should know that is possible however, and that you could generate your apex page with an initial payload in it, and then do ajax calls from there on. I'll ask you refer to the docs though, and maybe later I may provide an example on that, but you should be able to find out if you understand what I did.

In both cases and on my two example pages I created a standard HTML region with the source set to:

<div id="treecontainer">
</div>

This div will become the container for the tree later on.

Using the standard component bits, jstree version 0.9.9a2

I'll say right off the bat that setting up an ajax tree isn't too hard, but has its limits. I didn't bother with implementing a search functionality on this version because it's way to fiddly and hackery and plainly said a huge waste of time. If you want that then just push on and simply go with the newer version is my advice - you're writing javascript either way.

So let's get on with it. Code first, comments about it below it.

• file includes:

  #IMAGE_PREFIX#libraries/jquery-jstree/0.9.9a2/apex.jquery.tree.js

  This is the jstree file that is also used for the apex tree widget.
• javascript:

  function init_tree_custom(){
    var gTreeConfig = 
      {
        data:{
          type:"json",
          async:true,
          opts:{
            isTreeLoaded:false,
            method:"GET",
            url:"wwv_flow.show"
          }
        },  
        opened: [],
        selected: "",
        rules:{
          valid_children:"root",
          use_max_depth:true
        },
        callback:{
          beforedata: function(NODE, TREE_OBJ) { 
                        return {"p_request"      : "APPLICATION_PROCESS=GET_NODE_DATA",
                                "p_flow_id"      : $v('pFlowId'),
                                "p_flow_step_id" : $v('pFlowStepId'),
                                "p_instance"     : $v('pInstance'),
                                "x01"            : $(NODE).attr("id") || 0        
                               };
                      }
        },
        root:{
          draggable:false,
          valid_children: "folder"
        },
        folder:{
          valid_children: "file"
        },
        file:{
          valid_children: "none",
          max_children: 0,
          max_depth:0
        }
      };
      
      gTreeConfig.opened = ["1","2","5","6","39","43"];
      gTreeConfig.selected = "43";
      
      var gTree = $("#treecontainer").tree(gTreeConfig);
  };
  

  What I did here is just build up a tree configuration object. Plenty of it can just be gleaned from the documentation and also from the apex widget implementation of the tree. For example the settings for root, folder and file are not mine but some I simply copied over. Other parts I left out for practicality's sake.
  So first, "data". This is the basic ajax setup and tells jstree that the tree has to be filled asynchronously with ajax retrieved data in json notation.
  "opened" and "selected" I added more for show. Opened will tell jstree which nodes to open up (and awesomely enough will load as required, though you should take care that parent nodes come before their children!). Selected tells jstree which node is to be selected after all loading has finished (this includes the "opened" list of nodes!). The reason for not outright defining them in the object is to show you it does not have to be done there. You can set the values before the tree is initialized, and thus you could put out some data somewhere to initialize the array with relevant values.
  The callback part is also important. In this object you can define all callbacks that have to be reacted upon. The "beforedata" callback is called before any data is retrieved (ie ajax call). It allows manipulation of what is sent in the ajax call and is vital to the ajax setup. Especially the value in x01 as that is the node to be loaded!
• Execute on page load:

  init_tree_custom();

  This will initialize a jstree instance.
• on demand process: GET_NODE_DATA

  DECLARE
    l_json VARCHAR2(4000);
  BEGIN
    SELECT '['||listagg('{"attributes":{"id":"'||node_id||'"},"data": {"title":"'||name||'", "attributes": {}}, "state":"closed" }',',') within group(order by node_id)||']'
      INTO l_json
      FROM treedata
     WHERE parent_id = apex_application.g_x01;
  
    htp.p(l_json);
  END;
  

  This is another tricky part as you need to know the correct syntax for this. Jstree expects a certain syntax after all! But the core things are there: node id and display.
  The "state: closed" is CORE to the solution and what makes it tick. The reason for this is that these nodes are added to the tree but in a closed state. A closed state indicates to jstree that the node has not had its contents loaded yet - and jstree does not load an opened node again unless specifically asked to!
  A worthy point of attention is that listagg has a limit of 4000 characters. If your data will go over that (a real humongous tree with loaaaaads of nodes as children for example) there is no other option than either emitting a clob split in pieces or emit object per object.

  If you must know why the search is so annoying to get right it is because it uses the same data-fetching process used for node retrieval, and automatically adds a parameter to the query string. Unfortunately there is no way for us to access that extra parameter in plsql as we can only use the standard apex set of items, arrays and temp items. There is no real way to intercept and manipulate this call without outright hackering around. Which is not my goal. If you really must do with this component I advise to not use the tree search but simply implement an own solution and document that well, instead of something trying to get the tree component to play nice. Again: if you'd try to implement a search such as I'd described in a previous post then be aware that it will actually perform an ajax call and it just won't work.

• demo page

Implementing the newer version: jstree v1.0.0

Since apex 4.2 (I suppose, but do not remember, 4.1 too) uses jQuery version 1.7 we can not use the last version of the tree, since that has been adapted to jQuery 1.8+ (fun fact: jquery by now is 1.9 and 2.0!). So I went into the Github archive of jstree and fetched the version prior to the 1.8 implementation and used that, but you can just use rc2 of the tree.
For the example's sake on apex.oracle.com I had to upload some files to the static files of the application.
I also opted to use the jQueryUI styled theme of the tree instead of the default tree look and feel. I further enhanced it by implementing some used-to functionality: loading icon and the dots in front. I found the tree looked just a bit too bland without it.

• javascript file includes:

  #WORKSPACE_IMAGES#jquery.jstree.pre1.8.js

  This includes the jstree javascript, the version I fetched before the library got updated to 1.8.
• javascript:

  function init_tree_custom(){
    var lTreeConfig = {
      "plugins" : ["json_data", "search", "ui", "themeroller"],
      "core" : {
        "load_open": true,
        "initially_open": []
      },
      "ui" : {
        "initially_select": []
      },
      "json_data" : {
          "ajax" : {
              "type"           : 'GET',
              "url"            : "wwv_flow.show",
              "data": function(node){
                        return {
                          "p_request"      : "APPLICATION_PROCESS=GET_NODE_DATA",
                          "p_flow_id"      : $v('pFlowId'),
                          "p_flow_step_id" : $v('pFlowStepId'),
                          "p_instance"     : $v('pInstance'),
                          "x01"            : $(node).attr("id") || 0,
                          "x02"            : "LOAD"
                          };
              },
              "success": function (new_data) {
                  return new_data;
              }
          }
      },
      "search":{
        "ajax" : {
              "type"           : 'POST',
              "url"            : "wwv_flow.show",
              "data": function(searchvalue){
                        return {
                          "p_request"      : "APPLICATION_PROCESS=GET_NODE_DATA",
                          "p_flow_id"      : $v('pFlowId'),
                          "p_flow_step_id" : $v('pFlowStepId'),
                          "p_instance"     : $v('pInstance'),
                          "x01"            : searchvalue,
                          "x02"            : "SEARCH"
                          };
              },
              "success": function (nodelist) {
                  return nodelist;
              }
        }
      }
    };
  
    lTreeConfig.core.initially_open = ["1","2","5","6","39","43"];
    lTreeConfig.ui.initially_select = ["43"];
  
   $("#treecontainer")
     .bind("select_node.jstree", function(event, data){console.log(data);})
     .jstree(lTreeConfig);
  }
  

  Oh man - where to start!? I strongly advise to take a look at the official documentation: start with the core functionality. I really found it plenty helpful. It took a bit for me to figure everything out but not that hard.
  First of all, the plugins. "json_data" since I want to pass data in using json notation. "search" because I want to search the tree. "ui" because I want node selection. "themeroller" because I want the tree to use jquery-ui theming.
  The json data definition says it all: use an ajax call to retrieve node data. x01 will hold the node to look up data for (important: 0 if root node!). Since I've also defined ajax for the search plug-in, I decided to call the same process but with x02 set to a different value.
  
• Execute on page load:

  init_tree_custom();

• css file includes:

  #IMAGE_PREFIX#libraries/jquery-ui/1.8.22/themes/base/jquery-ui.css

  This includes the only apex-bundled jquery-ui theme, base. It's grey. But serves the example.
• css inline:

  /*Constrain the tree height a bit, helps make it less jumpy*/
  div#treecontainer{
  height: 400px;
  overflow-y: scroll;
  }
  
  #treecontainer ins{
  background-color: transparent;
  }
  
  div.jstree li > a.jstree-search{
  background: #CCCCCC;
  }
  
  /*
   * jsTree default theme 1.0
   * changed, adapted and cut, to work with workspace images for demo and improve look-n-feel
   */
  .jstree li, 
  div.jstree li > ins.jstree-icon
   { background-image:url("#WORKSPACE_IMAGES#d.png"); background-repeat:no-repeat; background-color:transparent; }
  .jstree li { background-position:-90px 0; background-repeat:repeat-y; }
  .jstree li.jstree-last { background:transparent; }
  .jstree .jstree-open > ins { background-position:-72px 0; }
  .jstree .jstree-closed > ins { background-position:-54px 0; }
  .jstree .jstree-leaf > ins { background-position:-36px 0; }
  
  .jstree a.jstree-loading .jstree-icon { background:url("#WORKSPACE_IMAGES#throbber.gif") center center no-repeat !important; }
  

• on demand process: GET_NODE_DATA

  DECLARE
    l_node treedata.node_id%TYPE;
    l_json VARCHAR2(4000);
  BEGIN
    IF apex_application.g_x02 = 'LOAD' THEN
      SELECT '['||listagg('{"data":"'||name||'","attr":{"id":"'||node_id||'"},"state":"'||CASE is_leaf WHEN 0 THEN 'closed' ELSE 'open' END||'" }',',') within group(order by node_id)||']'
        INTO l_json
        FROM (select level lvl, node_id, parent_id, name, connect_by_isleaf is_leaf
                from treedata
             connect by prior node_id = parent_id
               start with node_id = 1
             )
       WHERE parent_id = apex_application.g_x01;
  
      htp.p(l_json);
    ELSIF apex_application.g_x02 = 'SEARCH' THEN
      BEGIN          
        select '['||listagg('"#'||replace(ltrim(SYS_CONNECT_BY_PATH(parent_id, ','),','),',','","#')||'"', ',') within group(order by 1)||']' node_path
          into l_json
          from treedata t
         where upper(name) like '%'||UPPER(apex_application.g_x01)||'%'
        connect by t.parent_id = prior t.node_id
        start with t.parent_id = 0;
              
        htp.p(l_json);
      EXCEPTION WHEN NO_DATA_FOUND THEN
        htp.p('[]');
      END;
    END IF;
  END;
  

  Now here happens a lot of magic.
  • Start off with the LOAD part:
    First of all I'm always selecting from the tree structure as you can see in the sub-query. I do this because I wanted to leverage connect_by_isleaf because this will return 0 when there are children for this node. I use this in the query on it, where I check and set the state of the node to open or closed. The why to it is that if you load each node as closed, then each node will be expandable in the tree simply because it is not known whether or not there are children for it. It rather ticked me off to not be able to visually tell if a node has children or not, and thus would require me expanding each node to know. So I decided to drive this behaviour from the query, where I can easily pick this up. Leaf nodes will load in as actual leaf nodes and will not be expandable.
  • SEARCH:
    Performing a search on the tree with ajax will expect back a full path of nodes to open up. It also expects you to set up the search pattern. Here I just use a case-insensitive search on name of my nodes, but you can set it up any way you want obviously.
    Every node matching the search criteria is filtered out, and the path is returned by sys_connect_by_path. Note the specific mark-up: what is actually being returned is a list of id selectors ("#1","#2","#5" etc).
    Now the attentive reader will probably have noticed that this query will return the same nodes several times. Eg if 2 children of the same parent would need to be returned, then that parent node will be twice in the result set. To be fair, that is a concession I made to keep it clear and not to clash with how the tree opens nodes. As with the "initially_load" setting, nodes are opened as the structure expands. This means that that parent has to be loaded before a child [that has to be opened] can be loaded. However, already opened nodes will just be skipped. So paths to retrieved nodes have to be in descending order. But writing a query which would filter out doubles while actually keeping that sorting went a bit too far for me. I'm not phased by putting doubles in there, it's handled fast enough.
• demo page
• jstree documentation page: http://www.jstree.com/documentation
• Again I'd like to point out that the new version of the tree is a lot more flexible and gives a lot more ease of configuration than the current version in apex. Checkboxes? Plug it in. Drag and drop? Plug it in. Rename nodes? Yes, plug it in. Of course you'd need to bind to the events and use ajax to push it to the database - like before - but actually setting that up is a lot easier to do.

In closing

The demo application (link, again) can be downloaded (link on its homepage) and has everything in it you need. The table with seeding of data is in it, and the files for the tree are in the static files of the application. This will allow you to more closely inspect how I set it up and make it easier to reproduce.

Why is the tree slow?

An explanation of what goes on with the tree component in apex and why it grows increasingly slow as more data is presented.

I've speculated and written down some about this here and there, but haven't ever gotten into much detail of it. Partially because I didn't have the time and partially because I was confused about it. When people ask about the tree being slow, it always has to do with the amount of data in it. Let's say 6000 records. That is a lot of data though, and so beside the technical why-is-it-slow you have to consider that that is just a lot of data. Nevertheless, it's understandable to say that control over that can be a bit out of your hands. After all, you simply want to display hierarchical data and then preferably correct - so no arbitrary leaving out of records.

What actually happens

When you define a tree component you provide the query to it that will supply the data. The tree component is based on the jQuery plugin jsTree. It can work with several types of datastores, and the apex team has decided to work with a json container. This container is built up according to the query and settings in the tree component, and it will create one giant json object. This is then emitted in script tags in the tree region, and assigned to a global variable. The structure is not flat either, but objects nested in objects to represent the tree structure. Thus, a root node will have a children object, and those children will have children objects again etc.
Already you could imagine that having one such huge object in javascript would be taxing on the memory of the browser.

When the page has loaded however, you will not see any json objects. Rather, you will have a functional tree. JSON data is not just presented, that is what HTML is used for. Evident when looking at the code with dev tools: you will find an unordered list (UL) structure. This code is not generated (emitted) by apex however, it is generated at load (runtime) by the jstree plugin. Since the tree has to use the json-object with all nodes, it will transform that object into a presentable structure, and thus it will perform a lot of DOM manipulation: creating elements and attaching them to the document. This is probably the most taxing part, and why some browsers deal better than others with it.
Of course, what makes it worse is how undynamical this setup inherently is. The node selection will cause a page redirect, meaning that if the tree is to be shown on that other page it has to be rebuilt all again - even if all you want to do is refresh a report to the right of it. It is not out of the box refreshable of very interact-able with dynamic actions, but requires some understanding of html/javascript to get things working.

Apex so far has used version 0.9.9a2 of jsTree for its tree component, and this version has actually been outdated for quite some time. It does have documentation but nowadays it can be hard to find examples of implementations, which hasn't helped in deciphering everything. In hindsight, when I may have defended the tree component and said there is documentation, it is a bit obscured and put to the side and it could've all gone a bit clearer. The tree is, probably - in my book, one of those components that simply haven't been touched on for a while because it "may" be sufficient for what most want to achieve with it: simple data presentation with not too much data.

Basically, the best solution for when you want to use the tree but don't want an unnecessary huge load on that page then using an ajax-fed tree is ideal. But not very much out-of-the-box, although I found this to be pretty easy to implement. Personally I've looked at the 1.0.0 version of the tree plugin, found this to be a huge improvement, and then went on to implement it. It's rather easy and painless - but I'll handle that in the next post.

Performing a case-insensitive search on a tree in apex

Someone mailed me recently about my previous posting about the apex tree, wondering how to implement a case-insensitive search. Indeed, the default search behaviour is case-sensitive. You might know how to do some toUppercase() magics, but how and where would you implement this?
I thought this question deserved some spotlight, so here we are. I do want to point out that my solution here is specific to the tree and the methods I used in my previous post. Other than that, it is still useful and can be used outside the context of the tree too.
Now, basically when you call the search function of jsTree, it will perform this search on the titles of the nodes in the tree with a jQuery pseudo-selector. By default this is the ":contains" selector, which will look for an occurence of a given string in the element. And this is not case insensitive.
Wait, pseudo-what? Don't fret, you've most likely encountered one of these beasts already. Some examples: ":first-child",":last-child",":hover",":visited",":enabled",... You can find some more info on them here (http://css-tricks.com/pseudo-class-selectors/) or just google them!
When you call the search on the tree, I have simply used search('somestring'). "search" however has another parameter!

search(needle [, compare_function])

Don't be deceived however by the name of the parameter. You do not actually pass on a function. The docs say this:

Optional argument. The jQuery function to be used for comparing titles to the string - defaults to "contains".

What is actually used is not a function per se, but a jQuery pseudo class selector. That is great actually. Pseudo classes can be created to extend the basic functionality of jQuery after all! This allows us to create a pseudo-class which performs the compare we require, and then pass this along to the tree search function.
You can find excellent documentation on how to create a pseudo-class by googling it of course, but this document helped me a lot: http://www.jameswiseman.com/blog/2010/04/19/creating-a-jquery-custom-selector/
I added the below code to my "treeOnload" function, so that jQuery is extended at the correct time. This means that once the document has finished loading, jQuery will be extended, and the custom selector will be available.

$.extend($.expr[':'], {
    ciContains: function(elem, i, match) {
        return $(elem).text().toUpperCase().indexOf(match[3].toUpperCase()) >= 0;
    }
});

In short, this will create a new pseudo-class "ciContains", which will look for the occurence of a given string in the element text while ignoring case. "match[3]" is the input text if you're wondering.
This selector can now be used anywhere on this page! For example, search for "tEsT" in each "td" element:

$("td:ciContains('tEsT')")

To get the search in the tree to work case-insensitive simply add "ciContains" as the second parameter to the search function.
For example:

$.tree.reference(l$Tree).search("d","ciContains");

I also built this feature into my tree demo page. By using the "case sensitive?" checkbox you can search either case sensitive or insensitive. One caveat though is using the same search string with different sensitivity. For example, searching the tree for "oliv" with no case sensitivity will highlight the "OLIVIA" node. Toggling the checkbox and searching again so that the search would be case sensitive will still highlight the "OLIVIA" node! This is because of how jstree performs a check on the provided search value. If the value is identical to the previous value, the already highlighted nodes will remain highlighted!

Tree demo page

Ajax calls in apex: examples

Just an example on the various ways to perform an ajax call in apex.

Setup:

I'm doing this on page 18. You can do this on any page of course, if you just adjust the page item name.
There is a tabular form on this page based on EMP, just to demonstrate getting the values from the ENAME column into an array.

On Demand process, called "demoProcess"

DECLARE
l_f01 VARCHAR2(200);
BEGIN
   FOR i IN 1..apex_application.g_f01.count
   LOOP
      l_f01 := l_f01 || apex_application.g_f01(i) ||',';
   END LOOP;
   l_f01 := rtrim(l_f01, ',');

   htp.p('P18_EMPNO: ' || :P18_EMPNO || 
         ' - X01: '    || apex_application.g_x01 || 
         ' - F01: '    || l_f01 );
END;

Javascript:

PLEASE NOTE: there are calls to "console.log" in the javascript code. These will write to the console. In Firebug this is found simply on the "console" tab. In IE however you might encounter javascript errors. If so, open up the "developer tools" with F12 and rerun the page. (I put developer tools in quotes because what passes for it in IE can hardly be called so. Don't dev in IE unless you really must.)

//To demonstrate using one of the fnn-arrays to get an array of data to the server.
//In this case all values in the ENAME column
var lArray = [];
$("td[headers='ENAME'] input:visible").each(function(){
   lArray.push($(this).val());
});
//---------------------------------------------------------------------
//htmldb_Get
//works in all versions, but has never been officially documented
var ajaxRequest = new htmldb_Get(null, 
                                 $v('pFlowId'), 
                                 'APPLICATION_PROCESS=demoProcess', 
                                 $v('pFlowStepId')
                                );
ajaxRequest.addParam('x01', 'Temporary Variable x01');
ajaxRequest.addParam('f01', lArray);
ajaxRequest.add('P18_EMPNO',$v('P18_EMPNO'));
//sync
//this is how this request is usually seen used
//a synchronous call will "lock" up the browser until the call has completed
var ajaxReturn = ajaxRequest.get();
console.log(ajaxReturn);

//async
//A method often overlooked, which will do the call asynchronous. 
//However, involves a bit more code and thus feels a bit more obscure than
// a jQuery alternative
ajaxRequest.GetAsync(function(pResponse){
   if(pResponse.readyState==4 && pResponse.status==200){
      console.log(pResponse.responseText);
   };
});
//---------------------------------------------------------------------
//jQuery post, async
//An alternative method which works where jQuery is included. 4.0 and up.
//By default this request is asynchronous, but if required can be made 
//asynchronous by adjusting the "async" param. See the docs!
//p_arg_names + values: for page items. Arrays!
var lArgNames = ['P18_EMPNO'],
    lArgVals  = [$v('P18_EMPNO')];

$.post('wwv_flow.show', 
       {"p_request"      : "APPLICATION_PROCESS=demoProcess",
        "p_flow_id"      : $v('pFlowId'),
        "p_flow_step_id" : $v('pFlowStepId'),
        "p_instance"     : $v('pInstance'),
        "x01"            : 'Temporary Variable x01',
        "f01"            : lArray,
        "p_arg_names"    : lArgNames,
        "p_arg_values"   : lArgVals
        },
        function(data){
           console.log(data);
        }
      );
//---------------------------------------------------------------------
//new apex.server namespace in 4.2, async
//This should be the preferred method starting from apex 4.2.
//It offers all the flexibility of jQuery, and it is well documented by the
//apex team. Here you do not have to specify parameters like p_flow_id, nor
//have to use p_arg_names/values. pageItems is a very useful addition aswell!
//Since you can freely change the parameters used for the jQuery call, you
//again can make the call synchronous if you would require so.
//Note that i provide the dataType parameter. apex.server.process will by
//default use "json", so if you just put out some text through your process
//you need to change this as otherwise you will encounter javascript errors.
apex.server.process('demoProcess',
                    {"pageItems":"P18_EMPNO",
                     "f01":lArray,
                     "x01":"Temporary Variable x01"
                    },
                    {"dataType":"text", 
                     "success":function(data){
                                  console.log(data);
                               }
                    }
                   );

Tabular form: select items on the same row

Introduction

Frequently I see people struggle with targetting things in a tabular form. While in a previous post i showed how to target some specific columns and find out how they relate to the fnn-arrays, I'd like to shed some light on how items on the same row of one can be targetted.
Some cherry picking:
https://forums.oracle.com/forums/thread.jspa?threadID=2479820
https://forums.oracle.com/forums/thread.jspa?threadID=2164344
https://forums.oracle.com/forums/thread.jspa?threadID=1117800
And there are plenty more of course.

The usual solution I see pass by is to target items by using their ID. I won't argue, this a valid method. The actual item's id is taken, and then the rowid suffix is extracted. This suffix then is concatenated with the desired item's array name, and an id targetting that item is constructed as such.
I don't like that. It has some shortcomings that are regulary forgotten or ignored, and these are called the fnn-arrays. But more on that further on.

Firstly I'd like to highlight another issue which is apparent: people do no understand, or even bother to look at, the html code and how to work it.

My setup

My example setup is still the same as in my previous post.
Tabular form with source sql:

select 
"EMPNO",
"EMPNO" EMPNO_DISPLAY,
"ENAME",
"HIREDATE",
"SAL",
"DEPTNO",
NULL checkme
from "#OWNER#"."EMP"

• EMPNO: hidden
• ENAME: popup lov, with query based lov:

  SELECT ename d, ename r FROM emp ORDER BY ename;

• HIREDATE: date field
• DEPTNO: select list based on a query:

  SELECT dname d, deptno r FROM dept ORDER BY dname;

• CHECKME: simple checkbox, with values "Y,"

(This form isn't meant to do anything but serve a demonstration purpose.)
Once again I'll use Firefox + Firebug, and this is what you'll see in the screenshots. It's my most valued and appreciated tool! If you're unfamiliar with it, take a look at my previous post.

Concept

What I'm going to show here is how to work in row 3, and specifically starting from the item in the SAL column.

Starting off with the concept of a table. Tables are always perceived by as a simply x*y-grid of cells, with x rows and y columns. Perhaps the most simple example is an Excel spreadsheet: say I want to target cell B2. B2 is on row 2:

And is also in column B:

The intersection of row 2 with column B is cell B2:

Of course, once an HTML table is displayed the principle is the same. To manipulate or retrieve from a table requires some knowledge of the HTML generated. So this is what you get when selecting the TBODY of the table:

As you can see, this element contains TR tags which are rows. The rows in turn contain TD elements:

As is evident, there is no such thing as a column element. The tables are cells in rows.

Luckily for us there is the headers attribute on the cells. With this header it is possible to target all cells with a given headers attribute, effectively providing a way to target all cells in a column.
Aside from that, this shows up that all td elements have a tr element as a parent, and the tr element in turn a tbody. So, targetting an item which is in the same row as another item should not prove to hard once you understand this structure.

Targetting on same row by substringing.

I want to select the input item in the SAL column on the third row. This is for demonstrative purpose. ":eq(2)" will target the 3rd item in the array of objects matching the selector (3rd, because of zero-index based arrays).

$("td[headers='SAL'] input:visible:eq(2)")

Executing this in the Firebug console will put 2 lines out to the console: one with the executed command and one with the result. You can hover over the result and it will show the actual element in the html.

When clicked you will be taken to the HTML tab in firebug, and the item will be selected in the structure.

You can see that the input item has been highlighted. This item is contained within a table cell element, and has 1 adjacent element in the form of a label element.
Take note of the item's id. This is what most people focus on when they work in a tabular form. It consists of the name attribute of the input element (and thus the associated fnn-array), and suffixed by the "rowid".
Most stop looking there. The rowid! THE ROWID!

$("input[name='f04_0003']")

And now these inputs in the context of their row:
So suppose that an event handler is bound to the input items in the SAL column, on change for example. When the change triggers, something has to be done in another column, on the same row.
To emulate this, I'll just select the input on the third row. (You could compare this to using $(this) in an actual event handler).
You'd then take the ID, and substring the rowid suffix. This can then in turn be easily suffixed to another array's name. For example, f04.

var rowid = $("td[headers='SAL'] input:visible:eq(2)").attr("id").substr(3);
console.log(rowid);
$("#f04"+rowid+"")

Another method would be to use replace on the id of the triggering item. This again requires you to know array names, of both the triggering item and the item you want to affect.

>>>> $("td[headers='SAL'] input:visible:eq(2)").attr("id").replace("f05","f04")
"f04_0003"

Considerations

Now I won't say that this method is bad or wrong. Obviously it works and if you're happy than that is fine by me. I however do not like it. Having to know which items map to which array is just a recipe for trouble to happen sooner or later. The problem is that the arrays can be switched so easily: a simple reordering of the columns or removing a column in the tabular form will outright break your javascript code when you used the arrays to target.
For example, say we have EMPNO and ENAME and both are editable. EMPNO is in array f01 and ENAME in f02. When you reorder these and ENAME comes before EMPNO, then ENAME will now be f01 and EMPNO will be f02. Now you will have to check your javascript code, and change it accordingly.
This is hellish if your tabular form has more than a few editable columns though. If it has 9 columns and 7 are editable and you have to make some change to it, like adding in a column in the fourth position, then you're out of luck. You will have at least 7 arrays to check up, and if your javascript is not littered with comments pointing out which array maps to which column, you will have to find out all over again.
And what if you have to revisit your code or form later on? Or the form? Or maybe not you, but a colleague? Oops. Chances are big that something will break.

Target by column headers

It really doesn't have to however, if you would target items not by array or rowid, but simply by using your knowledge of the html structure and employing some jQuery-fu. As I've shown above, all cells in a column have a headers attribute, and we can target a column by using this knowledge.
The input item has a cell as a parent. The cell has a row as parent. The row has the table body as parent.
For example, input item in column SAL, on row 3. The parent row element can then be retrieved by using ".closest"

$("td[headers='SAL'] input:visible:eq(2)").closest("tr")

Then from this row element we can traverse down again. Find the cell with headers=HIREDATE and then select the visible input in that column. All chained together:

$("td[headers='SAL'] input:visible:eq(2)").closest("tr").find("td[headers='HIREDATE'] input:visible")

Finding multiple items starting from one:
By traversing the dom:

var parentRow = $("td[headers='SAL'] input:visible:eq(2)").closest("tr");
console.log(parentRow.find("td[headers='HIREDATE'] input:visible"));
console.log(parentRow.find("td[headers='ENAME'] input:visible"));
console.log(parentRow.find("td[headers='DEPTNO'] select:visible"));

By using array + rowid:

var rowid = $("td[headers='SAL'] input:visible:eq(2)").attr("id").substr(3);
console.log(rowid);
console.log($("#f04"+rowid+""));
console.log($("#f03"+rowid+""));
console.log($("#f06"+rowid+""));

Practical example

In practice this will mostly be used on items that will have to change something in another column when they themselves have been changed. Thus, usually in change events.
Example, if DEPTNO changes, then change SAL

$("td[headers='DEPTNO'] select").change(function(){
   //get the input item in column sal
   var lSal = $(this).closest("tr").find("td[headers='SAL'] input:visible");
   //change the salary depending on department
   switch($(this).val()){
      case 10:
      lSal.val(1000);
      break;
      case 20:
      lSal.val(2000);
      break;
      case 30:
      lSal.val(3000);
      break;
      case 40:
      lSal.val(4000);
      break;
   };
});

This is easily translated to a dynamic action too. With change as event, and using "td[headers='DEPTNO'] select" as jQuery selector (without enclosing double quotes, mind you). A true action of execute javascript, and code:

var lSal = $(this.triggeringElement).closest("tr").find("td[headers='SAL'] input:visible");
switch($(this.triggeringElement).val()){
   case 10:
   lSal.val(1000);
   break;
   case 20:
   lSal.val(2000);
   break;
   case 30:
   lSal.val(3000);
   break;
   case 40:
   lSal.val(4000);
   break;
};

Now if you have to make a change to one of the columns or add one in, you'll be a lot safer. Of course, things will still break when you do certain things: removing a column, changing the type of a column (fe text to display only, text to select list), changing column headers.

Still I think this wins out. Code is a lot clearer to read when you can refer to column headers than having to find out associated array names.

Ajax in Apex: introduction and techniques

How to use ajax in apex? What exactly does it do? What can i do? Why doesn't it work? How can i get values from the database on my page? All are frequently asked questions and i just want to cover some of the basics and provide a headstart.

Built-in AJAX

You may already have encountered ajax functionality without really knowing. Plenty of functionality in Apex has ajax built into it!
A quick grab:

• paginating an IR will fetch the next or previous page from the server and replace the old source
• cascading LOVs: when you change the parent select list of another select list, the values have to be refreshed in the child select list.
• autocomplete items: when you enable lazy loading in an autocomplete item, the values will be refreshed as you enter characters.
• refreshing a region will replace the current html with an updated version fetched from the server

What can i use ajax for with Apex?

Usually you will want to do one of these things:

• Set session state
• Execute a block of PLSQL
• Maybe you just want to update a field of a record when a button is clicked, or a checkbox is changed.
• Fetch values from the database

In these cases you'd require either a process on the on-demand process point of a page or an application process. This process can contain any piece of PLSQL you'd want. Inserts, updates, deletes, validation code,... Returning values from this process to the ajax call is really easy: you do this by printing values to the http buffer, ie an htp.p call.
If all you want to do is to set the session state of some page items it is not absolutely necessary to use an application process. You might have seen examples of this as usually the process involved has no code except for "NULL;". Even the trick with a dynamic action which executes a PLSQL block and has "Page items to submit" specified has "NULL;" as code. The reason for this is to save on bandwith. The process really does nothing, and submitting to session state is done simply by including a value to a page item (or other variable) in the ajax call, and calling a process or not has no influence over that. However, without specifying a process which essentially does nothing, you will get the entire current page as a response. That's a lot of bytes there! Having an empty process will return nothing at all and thus save a lot of bytes.

Returning values to an ajax call

Returning values is done by making an htp.p call in the PLSQL process. How and if you return data is up to you. It could be just text, it could be JSON.

Session state

Before starting to talk about communication with the server, it is very important that you understand session state in apex. Let's say we have 2 page items. One is a select list with employees, where the display is ENAME and the return is EMPNO. The second item is a textfield, and it should fetch the JOB of an employee when it gets selected in the list.
This means that there should be a piece of code on the serverside that retrieves the job of a given employee. What is regularly seen is something like this:

SELECT job 
INTO l_job 
FROM emp 
WHERE empno = :P1_EMPNO;
htp.p(l_job);

And then people think "I'm all set up now! I select something, this code gets executed, and in my return value i'll have the job". Well, no.
You have to keep in mind that session state is the state of the item at the server side. This state can be the same as that on the client side, but i can also not be. When you change a value on the actual page, for example by selecting an employee from the select list, then this will not automatically update the session state of the item. If the page loaded and no value was present in the session state of the item, then there will still be no value in the session state when the selection changed on the page (given that there are no submits!). This means that just firing an ajax call and executing the above SQL will not work, as the session state of P1_EMPNO will likely be the session state as it was at rendering time. To make this work, the session state for P1_EMPNO would have to be updated.

You can always check the current session state by clicking the "Session" button on the developer bar. If session state is unfamiliar ground to you and all this sounds new, take a look at the documentation. This is important stuff!
Oracle Documentation on Session State

Dynamic actions

Execute PLSQL, submit and return items

Now before we get into using ajax in javascript, it is important to note that sometimes you can also solve talking to the server through dynamic actions. There is for example the 'Execute PLSQL' true action, which will obviously be run on the database. There is the option to submit page items to the session state so you can use them in the PLSQL block, and also page items to return so that the session state value can be set to the html. Reports also have a "Page items to submit" option where you can specify page items which have to be submitted to session state when the report is refreshed. If you'd have a report which includes a page item that is for example a select list, you can include the select list item in the "Page items to submit". By then defining an "on change" dynamic action on the select list, you can add a true action which refreshed the report region. This refresh will then submit the current select list value, and the report output will be correctly filtered.

Ajax calls in apex

Synchronous and asynchronous calls

Calls to the server can be made synchronous and asynchronous. In short, synchronous means that your logic will wait for the call to complete and then proceed with the execution, while asynchronous will fire a call and then proceed execution without awaiting a response. When using a asynchronous call, you will have to provide a callback to deal with the response when it gets there. This is important to understand, because in a synchronous call you will remain in the same context all along: variables used and filled in the same scope will remain available (ex: in a function block). This is different from an asynchronous call: since the callback may occur later on, things may have changed. For example, say that you specify in your callback to get the value from an input item. After the ajax code, you immediately remove the input item. The value of the item will only be retrieved when the callback fires, and the item will be gone already.

In apex you have 2 main ways to perform ajax calls to the server: there is the apex htmldb_Get object, or you can use jQuery.

htmldb_Get

Performing a synchronous call with htmldb_Get

var ajaxRequest = new htmldb_Get(null, $v('pFlowId'), 'APPLICATION_PROCESS=get_job', $v('pFlowStepId'));
ajaxRequest.addParam('x01', 'ALLEN');
var ajaxResult = ajaxRequest.get();

This is probably the most straightforward way, and will often be picked because of the small amount of lines and code and thus ease of use. All valid reasons, but remember it is synchronous and thus will make the browser wait for a response. This won't always be preferred or even necessary, and making you shouldn't make your user wait for something that is not required. An example could be a report with a button on each row that when clicked should do an update and set a flag to Y or N. There is no advantage to make the user wait for completion of this call, so why would you.
A more valid use would be to perform a validation of a field. When the field is changed or loses focus, you want to check if the value is valid by performing a select on the database. Here you would perform a synchrnous call to check this, and then possibly notify the user of an invalid choice. It wouldn't make much sense to let the focus slip and then after a second come telling them that the previous field isn't valid; rather make them wait.
Performing an asynchronous call with htmldb_Get: (works at least from 4.0 and up)

var ajaxRequest = new htmldb_Get(null, $v('pFlowId'), 'APPLICATION_PROCESS=get_job', $v('pFlowStepId'));
ajaxRequest.addParam('x01', 'ALLEN');
ajaxRequest.GetAsync(function(pResponse){
   console.log(pResponse.readyState);
   if(pResponse.readyState==4 && pResponse.status==200){
      console.log(pResponse.responseText);
   };
});

The GetAsync method takes a function as a parameter, and this function can take the response object as a parameter. You will need to check the response object to check when the ajax call has completed (readyState 4) successfully (status 200 HTTP OK), as shown above. (When you check your debug console you'll see 4 readyStates pass by if you put the console.log lines in as i've done in the example code).

Adding values to the request

Using the temporary items x##

ajaxRequest.addParam()

The f## arrays Name-value pairs uses p_arg_names and p_arg_values These are arrays.

ajaxRequest.add()

Source code

If you are wondering at how htmldb_Get works and you understand javascript, then you can always take a look at the code for this object in the apex javascript source file. For example, for version 4.1 this is in the file "javascript/apex_4_1.js" in the apex_images folder (or "/i/").

/**
 * @constructor
 * @param {Dom node | String} [obj] object to put in the partial page
 * @param {String} [flow] flow id
 * @param {String} [req] request value
 * @param {String} [page] page id
 * @param {String} [instance] instance
 * @param {String} [proc] process to call
 * @param {String} [queryString] hodler for quesry string
 *
 * */
function htmldb_Get(obj,flow,req,page,instance,proc,queryString)

You probably will never need to bother with obj, proc and queryString.
The callable application processes could be processes defined at the On Demand process point of a page, or as an application process. If you want to call an application process then provide 0 to the page id parameter.

apex.server.process (apex 4.2+)

Essentially replaces htmldb_Get and offers you more flexility because the jQuery functionality is readily exposed. I like the implementation and gives pure jQuery a run for its money, in my opinion :)
Oracle Documentation on apex.server.process

apex.server.process ( "MY_PROCESS", {
  x01: "test",
  pageItems: "#P1_DEPTNO,#P1_EMPNO"
  }, {
 success: function( pData ) { ... do something here ... }
  } );

Example usage:

apex.server.process('GET_TYPE_VALUE', 
                    {pageItems: '#P3_TYPE'}, 
                    {success: function(data){
                                 $s("P3_VALUE", data);
                              }, 
                     dataType: "text", 
                     loadingIndicator: '#P3_VALUE'}
                    );

Take note of "dataType": this is defaulted to JSON! When you use apex.server.process and do NOT return JSON values in your application process, you will get javascript errors if you did not set the dataType parameter accordingly.

jQuery

Performing an asynchronous call with jQuery

jQuery offers several methods to perform ajax calls, but the most base form is the $.ajax function. The $.post function is just a shorthand method which is based on the $.ajax function. It may look a bit more complex, but i think it is quite clear and not as unnerving once you know what does what.
It is important to read the documentation on the ajax functionality of jQuery. The documentation is very well done and will provide you with lots of information.
jQuery documentation on $.ajax
jQuery documentation on $.post

$.post('wwv_flow.show', 
       {"p_request"      : "APPLICATION_PROCESS=xxx",
        "p_flow_id"      : $v('pFlowId'),
        "p_flow_step_id" : $v('pFlowStepId'),
        "p_instance"     : $v('pInstance'),
        "x01"            : 'GET_EXISTING_FILTER'
        },
        function(data){
           //do something FUN with the return -> data
        }
        );

Which could be written to use the $.ajax functionality:

var ajaxData = {"p_request"      : "APPLICATION_PROCESS=xxx",
                "p_flow_id"      : $v('pFlowId'),
                "p_flow_step_id" : $v('pFlowStepId'),
                "p_instance"     : $v('pInstance'),
                "x01"            : 'GET_EXISTING_FILTER'
               };

$.ajax("url":'wwv_flow.show',
       "data": ajaxData,
       "settings":{"type":"POST","dataType":"text json"}
       )
       .done(function(data){
          //handle the successful return -> data
       });

Adding values to the request

Adding data to the request is not that hard. You simply add data to the data option of the ajax or post request. jQuery expects an object. There is again a difference in what you specify where using the temporary items x## The f## arrays Name-value pairs uses p_arg_names and p_arg_values These are arrays.

"p_arg_names"    : ['P1_EMPNO', 'P1_ENAME'],
"p_arg_values"   : ['9999', 'ROCKSMITH']

Valid parameters Lets say you mix up add and addParam on htmldb_Get. Would things still work? No. Putting X01 in .add() will cause a successfull return, but the return is the page html with a error embedded:

  ERR-1002 Unable to find item ID for item "x01" in application "299".
  Unexpected error, unable to find item name at application or page level.

You can always use the x## items and the f##-arrays however, but not in add():

  ERR-1002 Unable to find item ID for item "f01" in application "299".
  Unexpected error, unable to find item name at application or page level.

Meaning that only page and application items should be put in the p_arg_names and values arrays.

Example setup

This is just a really small example which demonstrates making a call to the server and return some values.
As a demonstration a value will be given to the x01 item, the f01 array and the P18_EMPNO page item. This should give you an idea on how to provide session state values to a serverside process.
Page item P18_EMPNO

Application process "bogus" at the AJAX Callback execution point.
DECLARE
   l_f01 VARCHAR2(200);
BEGIN
   FOR i IN 1..apex_application.g_f01.count
   LOOP
      l_f01 := l_f01 || apex_application.g_f01(i) ||',';
   END LOOP;
   l_f01 := rtrim(l_f01, ',');

   htp.p('P18_EMPNO: '||:P18_EMPNO||' - X01: '||apex_application.g_x01||' - F01: '||l_f01);
END;

var ajaxRequest = new htmldb_Get(null, $v("pFlowId"), "APPLICATION_PROCESS=bogus", $v("pFlowStepId"));
ajaxRequest.addParam("X01","xxxx");
ajaxRequest.addParam("f01",["test1","test2"]);
ajaxRequest.add("P18_EMPNO","8888");
var ajaxReturn = ajaxRequest.get();

Inspecting and debugging calls

firebug console tab How do you view and inspect ajax calls? Get Firebug for Firefox, or use the developer tools in Chrome. I don't develop in IE, i just use it run the sites as an enduser and fix IE-uses as i go along. Since i have to develop for IE8 i can't even bother with ajax calls since there are no inbuilt tools to inspect those. I have wireshark installed to do this, but i'm sure plenty of companies wouldn't allow use of it.