Update multiple items from a LOV field with JSON

Ever wondered how great it would would be, if you could change or fill multiple item fields from a selected LOV.
Some people did, and came up with javascript solutions filtering out their own return strings, usually separated by a magic separator character (like ~).
In modern web design we'd actually want something where we can just fire away and retrieve some data, and be able to quickly (magically) retrieve separated data. Here's where JSON comes in. If you haven't heard of it, a lot of blog posts have been written about it as of late.
But in a "quicky", this is what it is: JavaScript Oject Notation (JSON) makes sure you can wrap and unwrap data in javascript. The power of JSON is that it is light weight. You are just passing strings of text. The format is making sure the right data is given the right name. In the calling javascript bit, you get an array that you can access. This array is created in an object notation. For more info on JSON itself, visit the site here.

=== Footnote ===

---

APEX is incorporating JSON in it's inner workings, even creating JSON calls in the APEX API. Carl Backstrom is really getting it together with the JSON calls, making communication between the APEX page and the database even easier. The known API calls are:
- APEX_UTIL.JSON_FROM_SQL (Returns a general JSON formatted text)
- APEX_UTIL.JSON_FROM_ITEMS (Returns a JSON string for "json_SetItems" command)

Both will be demonstrated in this post!

Getting it to work with a LOV field

JSON_FROM_SQL

Getting it working is fairly simple, we need javascript to get some event action going, and an application process to handle the API calls to the APEX_UTIL package. Since we want to do things dynamically, without any page loads, we'll use AJAX calls to trigger the application process code.

First off, I created a form on a table (I'll be using the DEPT table in this example). I changed the DNAME field to a Popup LOV, that selects all department names. (This could be a select list as well).

The second step is to create a callable javascript function that will make the AJAX call to the application process, parse our JSON text to a JSON object, and set the values to the items. Create something like this:

Now before you're going like, what is that JSON.js script doing there at the beginning. This is a JSON support script that gives some extra functions to help us making our lives easier (like parseJSON, instead of the eval function *See footnote for more info). You can ofcourse just download the script and upload it to your own web server, but this is easier for demonstration purposes.

The script is commented, so if there are any code questions, be sure to respond in this post comments.

The third step, is to create the application process that is called from the AJAX call in the javascript. The call looked like this:

ajaxRequest = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=DEPTLOOKUP',0);

So we need to create a Application Process called DEPTLOOKUP, looking like this:

Edit 13-10-2008 - Important if you are using this!
Thanks to Patrick Wolf, who noticed the SQL Injection vulnerability in the previous code. Be sure to use bind variables where you can, and if you can't use the DBMS_ASSERT package to filter out malicious characters.

DECLARE
 
-- We set wwv_flow.g_x01 in the javascript part
-- you can use this parameter as a temp APP ITEM

-- EDIT 13-10-2008: Thanks to Patrick Wolf, for noticing the SQL Injection threat
--                  Added DBMS_ASSERT.SIMPLE_SQL to check for weird user input
--                  And the ENQUOTE_LITERAL to prevent hardcoded quotations
p_dname varchar2(20) := DBMS_ASSERT.ENQUOTE_LITERAL(DBMS_ASSERT.SIMPLE_SQL_NAME(wwv_flow.g_x01);
 
BEGIN

--Use this to get a JSON string back in the javascript, based on the SQL inserted

APEX_UTIL.JSON_FROM_SQL('SELECT deptno, loc FROM dept WHERE dname = '||p_dname);
 
END;

As you can see, the parameter is picked up, and used in the JSON_FROM_SQL command. This command will automagically return a correct JSON string to it's caller. In this case the javascript.



The fourth and final step, is to trigger the javascript. For the purpose of the LOV, an "onchange" event does the trick perfectly. If you select another item from the LOV, the onchange event is triggered. So go to the details of the LOV item, and add to the "HTML Form Element Attributes" of the "Element" section:

onchange="lookupDeptno();"

If you did the above, you should get behaviour like this:
http://apex.oracle.com/pls/otn/f?p=33575:1

=== Footnote ===

---

The parseJSON function is the reason to incorporate the JSON.js "libary". The alternative of using the parseJSON function, is to use the javascript "eval" function. This function shares most of it's letters with another word that can describe this function: "evil"!!! Eval executes anything that is passed into it. So it's a potential security risk to use eval.

JSON_FROM_ITEM
This approach is similar to the previous one, but with one fundamental difference. With the JSON_FROM_ITEM call you set the items values from the APP process instead of the page javascript.

The first step is to have a popup LOV item again, that fires some javascript code. So create a form on a table (I used the DEPT example again).

Second is to create the javascript that is triggered, that will make an AJAX call and refresh the values.
Here's the code:

The third step is creating the application process "DEPTLOOKUP_ITEM". This app process will get the table record, set the new values into the session state and create a JSON string that is returned for the "json_SetItems" javascript call. And it looks like this:

declare
 p_dname  varchar2(200);
begin

 p_dname := DBMS_ASSERT.SIMPLE_SQL_NAME(wwv_flow.g_x01);
 FOR c_dept IN (select * from dept WHERE DNAME = p_dname) loop
  APEX_UTIL.SET_SESSION_STATE('P4_DEPTNO',c_dept.DEPTNO);
  apex_util.set_session_state('P4_LOC',c_dept.LOC);
 end loop;
 apex_util.json_from_items('P4_DEPTNO:P4_LOC');

end;

Finally, you need to create the onchange event again, in the "HTML Form Element attributes" to:

  onchange="getItemsFromJSON();"

This way you're able to change the values of the items automagically as well.
See the results here:
http://apex.oracle.com/pls/otn/f?p=33575:4

=== Footnote ===

---

Be sure to code the "ajaxRequest.get()" and the "json_SetItems" real close to each other, and make sure that there are no errors "in between" these lines of code. If there is an error generated between the "setting of session state" and updating it on the screen, you will get unexpected behavior. The session state has changed, and you have no idea that it did.

Conclusion

Using these JSON techniques really adds up to your page's usability. It can be an extra argument for customers that are considering APEX and are using Oracle Forms. A lot of Oracle Forms use this "LOV to fill multiple items" functionality. Usually with WHEN-VALIDATE-ITEM or POST-CHANGE triggers.

Credits must go out to Carl Backstrom, for integrating JSON with APEX, and also the examples he put forward (which are very similar to mine), so you should really take a look at his blog!

*PS
If this was helpful to you, your very welcome to share this in the post comments ;)