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, lewiscunninghamDuring 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!
