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.
=== 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
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).
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.
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.
-- 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);
APEX_UTIL.JSON_FROM_SQL('SELECT deptno, loc FROM dept WHERE dname = '||p_dname);
If you did the above, you should get behaviour like this:
=== Footnote ===
Here's the code:
p_dname := DBMS_ASSERT.SIMPLE_SQL_NAME(wwv_flow.g_x01);
FOR c_dept IN (select * from dept WHERE DNAME = p_dname) loop
Finally, you need to create the onchange event again, in the "HTML Form Element attributes" to:
This way you're able to change the values of the items automagically as well.
See the results here:
=== 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.
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!
If this was helpful to you, your very welcome to share this in the post comments ;)