Wednesday, March 20, 2013

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);
                               }
                    }
                   );

Tuesday, March 19, 2013

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.
overview
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:
excel - row
And is also in column B:
excel - column
The intersection of row 2 with column B is cell B2:
excel - cell
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:
tbody
As you can see, this element contains TR tags which are rows. The rows in turn contain TD elements:
tr - row
As is evident, there is no such thing as a column element. The tables are cells in rows.
td - cell
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.
input SAL - selected
When clicked you will be taken to the HTML tab in firebug, and the item will be selected in the structure.
td input SAL
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']")
td input HIREDATE
And now these inputs in the context of their row: inputs in 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+"")
select by id substr
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")
select by traverse 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"));
select multiple by traverse
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+""));
select multiple by substr

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.

Wednesday, March 6, 2013

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.