Monday, October 6, 2008

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

7 comments:

Patrick Wolf said...

Rutger,

nice posting about this great new feature of APEX!

Just one note about your APEX_UTIL.JSON_FROM_SQL call in your on-demand process. The example you show is vulnerable to SQL injections. Concatenation should not really used for dynamic SQL statement or checked with the DBMS_Assert package.

Regards
Patrick

Rutger said...

Hi Patrick,

Thanks for noticing. I'm just shooting in the dark here, but it would be great if JSON_FROM_SQL would accept a USING clause, or an extra parameter that is used as a bind variable.

I will look into this much deeper, I think it is a good subject for a next post.

Greetings,
Rutger

Anonymous said...

You are missing a ')' in the code
--
p_dname varchar2(20) := DBMS_ASSERT.ENQUOTE_LITERAL(DBMS_ASSERT.SIMPLE_SQL_NAME(wwv_flow.g_x01);

Anonymous said...

Hi Rutger,

it's nice feature to set items values dynamically from sql, but I have a problem whith json_SetItems function. Firebug said that function is not defined and I can't find solution how to solve this.

Please describe where this function placed and how it migth be described.

grateful Denis

Anonymous said...

Can someone explain the use of dbms_assert.simple_sql_name for cleaning up the input value. My current experience is that if your input value starts with a number then you are going to get an "Invalid sql name" error. So what should be used instead if your value is going to start with a number?

Anonymous said...

Help !!!!!!!!!

I am following Rutger's example
http://rutgerderuiter.blogspot.com/2008/10/update-multiple-items-from-lov-field.html

Here is my JavaScript
SCRIPT language=JavaScript src="JSON_CODE">

SCRIPT type=text/javascript>
window.onerror = function(msg, url, linenumber)
{
var logerror = 'Error message: ' + msg + '. Url: ' + url + 'Line Number: ' + linenumber;
alert(logerror);
return true;
}

function f_getHeatMap(p_MonthYear)
{
ajaxRequest = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=getHeatMap',0);


ajaxRequest.addParam('P0_TEMP', $v('P106_MONTHYEAR'));


ajaxResponse = ajaxRequest.get();


if (ajaxResponse)
{
html_GetElement('P106_X').value = '1';
var jsonobj= ajaxResponse.parseJSON();
$s('P106_PRODUCT', jsonobj.row[0].PRODUCT);
}
else
{
html_GetElement('P106_X').value = '2'
}
ajaxRequest = null;
}

Here is my Application process
DECLARE
V_SQL varchar2(500);
BEGIN
V_SQL := 'SELECT VHB1.PROCESS_DATE, VHB1.PROCESS, VHB1.PRODUCT, VHB1.CATEGORY, VHB1.C_1, VHB1.C_2, VHB1.C_3, VHB1.C_4 FROM V_HEATMAP_BASE_1 VHB1';
APEX_UTIL.JSON_FROM_SQL(V_SQL);
EXCEPTION
WHEN OTHERS THEN
HTP.p ('{"row":[]}');

END;

Problem
1. When ever java function is called. I get error message “ajaxResponse.parseJSON is not a function”
2. If I run application process at SQL Command prompt error message I get
ORA-06550: line 5, column 12:
PLS-00302: component 'JSON_FROM_SQL' must be declared
ORA-06550: line 5, column 2:
PL/SQL: Statement ignored
BEGIN
V_SQL := 'SELECT VHB1.PROCESS_DATE, VHB1.PROCESS, VHB1.PRODUCT, VHB1.CATEGORY, VHB1.C_1, VHB1.C_2, VHB1.C_3, VHB1.C_4 FROM V_HEATMAP_BASE_1 VHB1';
APEX_UTIL.JSON_FROM_SQL(V_SQL);
END;


- SAGAR DORIWALA

teresku said...

Rutger,

I know, it's a while ago, but your posting was, still is, very helpful.
Thank you.
I allowed me to link to you blog post if you don't mind: http://apexnotes.blogspot.com/2011/09/set-multiple-items-from-query.html

Best,
ter