APEX-AT-WORK no image

Creating an ExtJS tree with PL/JSON

Von Tobias Arnhold 2.04.2010
I searched for a smoother way building ExtJS trees in APEX and found a great post from Anja Hildebrandt: Nice trees with ExtJS (Erstellung eines ExtJS-Baums…)

She used the PL/JSON Utility from Lewis Cunningham: PL/JSON by jkrogsboell, lewiscunningham

During the time now Lewis developed a new version of the utility and Anjas code didn't work anymore. I updated her source code with the new version of PL/JSON 0.8.6.

First I show how you build JSON output with the PL/JSON scripts:
Example - ex4.sql

SQL> declare
2 obj json;
3 procedure p(v varchar2) as begin dbms_output.put_line(null);dbms_output.put_line(v); end;
4 begin
5 p('you can also put json or json_lists as values:');
6 obj := json(); --fresh json;
7 obj.put('text', 'Audi');
8 obj.put('id', 100);
9 obj.put('leaf', json_bool(true));
10 obj.put('href', 'f?p=110:1:');
11 obj.put('children', json_list('[{"text": "A4"},{"text": "A5"}]'));
12 obj.print;
13 end;
14 /


you can also put json or json_lists as values:
{
"text" : "Audi",
"id" : 100,
"leaf" : true,
"href" : "f?p=305:1:",
"children" : [{
"text" : "A4"
}, {
"text" : "A5"
}]
}

PL/SQL procedure successfully completed

PL/JSON forum example
 
SQL>
SQL> DECLARE
2 resultset json;
3 row_list json_list := json_list();
4 columns1 json;
5 num_rows number := 2;
6 columns_length number := 3;
7 procedure p(v varchar2) as begin dbms_output.put_line(null);dbms_output.put_line(v); end;
8 BEGIN
9 p('Data:');
10 FOR i IN 1 .. num_rows
11 LOOP
12 columns1 := json();
13 columns1.put('rownum', i);
14 FOR x IN 1 .. columns_length
15 LOOP
16 columns1.put('column' || x, 'Testdata');
17 END LOOP;
18 row_list.add_elem(columns1.to_anydata);
19 END LOOP;
20 resultset := json();
21 resultset.put('ResultSet', row_list);
22 resultset.print;
23 END;
24 /


Data:
{
"ResultSet" : [{
"rownum" : 1,
"column1" : "Testdata",
"column2" : "Testdata",
"column3" : "Testdata"
}, {
"rownum" : 2,
"column1" : "Testdata",
"column2" : "Testdata",
"column3" : "Testdata"
}]
}

PL/SQL procedure successfully completed

SQL>

Updated tree package of Anja

create or replace package PKG_EXTJS_JSON is

-- Author : AHILDEBRANDT
-- : TARNHOLD
-- Created : 10.08.2009
-- Updated : 04.02.2010
-- Purpose : check if category has sub-categories
function hasChildren(i_cat_id in number) return boolean;

-- Purpose : create JSON-Object for category;
-- recursive
function getJsonObject(i_cat_id in number default 0) return json;

-- Purpose : function that calls getJsonObject and returns the result as string
function getTreeDataDynamic return varchar2;

end PKG_EXTJS_JSON;

create or replace package body PKG_EXTJS_JSON is
/*
-- Author : AHILDEBRANDT
-- : TARNHOLD
-- Created : 10.08.2009
-- Updated : 04.02.2010
-- Purpose : check if category has sub-categories
*/
function hasChildren(i_cat_id in number) return boolean is
v_count number:=0;
begin
select nvl(count(*),0) into v_count from tbl_categories where c_par_id=i_cat_id and c_active = 'YES';
if v_count>0 then
return true;
else
return false;
end if;
end;

/*
-- Author : AHILDEBRANDT
-- : TARNHOLD
-- Created : 10.08.2009
-- Updated : 04.02.2010
-- Created : 10.08.2009
-- Purpose : create JSON-Object for category;
-- recursive
*/
function getJsonObject(i_cat_id in number default 0) return json is
v_json json:=json();
v_row_list json_list := json_list();
v_arr_id NUMBER;
v_ele_id NUMBER;
begin

if i_cat_id = 1 then -- if category is root then set root-node
v_json.put(pair_name => 'id', pair_value => i_cat_id);
v_json.put(pair_name => 'text', pair_value => 'Root');
else -- else --> usual node; use category infos
for cat in (select * from tbl_categories where c_id=i_cat_id and c_active = 'YES') loop
v_json.put(pair_name => 'id', pair_value => i_cat_id);
v_json.put(pair_name => 'text', pair_value => cat.c_name);
end loop;
end if;

if not hasChildren(i_cat_id) then -- if node has no children
v_json.put(pair_name => 'leaf', pair_value => json_bool(true)); -- mark as leaf
v_json.put(pair_name => 'href', pair_value => 'f?p=110:1:'||v('APP_SESSION')); -- set a link if needed
-- v_json.put(pair_name => 'href', pair_value => 'javascript:show_cat('||i_cat_id|| ');'); -- set a javascript function
else -- sonst
v_json.put(pair_name => 'leaf', pair_value => json_bool(false)); -- mark as node with children
for child in (select * from tbl_categories where c_par_id=i_cat_id and c_active = 'YES') loop -- loop through all sub-categories
-- create JSON-object for sub-category using recursive call and the append to array
v_row_list.add_elem(getJsonObject(i_cat_id => child.c_id).to_anydata);
end loop;
v_json.put(pair_name => 'children', pair_value => v_row_list); -- add array for subcategories
end if;

return v_json;

end;

/*
-- Author : AHILDEBRANDT
-- : TARNHOLD
-- Created : 10.08.2009
-- Updated : 04.02.2010
-- Purpose : function that calls getJsonObject and returns the result as string
*/
function getTreeDataDynamic return varchar2 is
v_json json:=json();
begin
v_json:=getJsonObject(i_cat_id => 1);
return('['||v_json.to_char||']');
end;

end PKG_EXTJS_JSON;

Utilities like this makes developing much more faster!

6 Comments " Creating an ExtJS tree with PL/JSON "

Peter Raganitsch 05 February, 2010 08:54

Looks like a useful package for more complex queries.
For simple selects APEX_UTIL.json_from_sql can be used.

Do you know about performance of PL/JSON ? Is it fast or is it a big overhead?

Tobias Arnhold 06 February, 2010 00:19

Hi Peter,

in the applications I used PL/JSON it always felt fast. The overhead is quite low. Just 3 packages and a couple of types.

Anja Hildebrandt 06 February, 2010 15:27

Hi Tobias,

i was afraid, i had to do this update myself... so, thanks ;)

Anja

Anonymous

Hi Tobias,

I'm glad to hear that PL/JSON is being put into good use. For the record, Lewis has developed v0.5, v0.6 and v0.6.1, I developed v0.8.0 - v0.8.6 and v0.9.0. In the last version the code Lewis wrote is more or less gone, but it was a good starting point to develop from.

In the new version (v0.9.0), I have focused on speed and simplicity. To upgrade you will have to replace the to_anydata methods with to_json_value which is a new type (that hiddes anydata and makes it easy to work with). The simple types (json_bool, json_null, string, number) is now inside json_value, so instead of writting json_bool(true) you would write json_value(true).

I hope you will find the new version of PL/JSON easy to use.

/Jonas Krogsbøll

BTW: An extra package, json_dyn, has been included. It makes it possible to build json from select statements. Any feedback, suggestions or possible contributions in form of code would be highly appriciated.

vipul 08 November, 2010 08:04

hi..
as i am working with mysql i am having difficulties to make the same code in mysql procedure.
Is there any package of pl json in mysql or is there any other same hardcoded code for mysql dynamic parent child relationship

Anonymous

Hi,
unfortunately a new version of Json has been released and your code is not working either.

Keep up the great work and hope you can adjust your code as soon as possible.