I looked through the Web for a simple way using a select list with dynamic data exchange inside my APEX application. What I found was just amazing (Thanks to Scott):
ajax-select-list-code-generator for APEX
Select list code generator
Output:
I linked a MySQL table into an OracleXE database (Short How to) and discovered a really strange behavior. When I tried an usual select about the MySQL table from my sqlplus client an error occurred: ORA-00904: "last_name": Invalid identifier
Here the whole description:
-- Error:
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as test_user
SQL> SELECT "last_name" FROM tbl_users@MYSQL_USER u;
SELECT "last_name" FROM tbl_users@MYSQL_USER u
ORA-00904: "last_name": ungültiger Bezeichner
SQL>
-- MySQL DDL TABLE:
DROP TABLE IF EXISTS 'my_sqldb'.'tbl_users';
CREATE TABLE 'my_sqldb'.'tbl_users' (
'u_id' int(10) unsigned NOT NULL auto_increment,
'last_name' varchar(50) NOT NULL,
'forename' varchar(50) NOT NULL,
'department_id' int(10) unsigned NOT NULL,
PRIMARY KEY ('u_id')
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;
Read More »»
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
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>
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;
Why German? I come from and live in Germany! ;D
I haven't really provided lot's of APEX based information in German. This just has to change. What to do?
Last year I created a small Oracle monitoring application with APEX and ExtJS. It was developed to collect CPU, Tablespace, Init.ora, Session and even more information about Oracle databases. In combination with some other monitoring tools it was quite a good (cheap) alternative to some expensive tools.
More details soon (in German)...
If you want to send data from your APEX application to an external website (like PHP) through the POST method. You can use the following example:
Add a new page (in my example it's page number 2)
Add a new button on the page where you want to send data from: BTN_SEND_DATE
With the following options:
Target is a: URL
URL Target: javascript:void(window.open('f?p=&APP_ID.:2:&SESSION.:::::', 'popup', 'toolbar=no,width=1024,height=768, resizable=yes,top=40,scrollbars=yes'));
On page 2: Add your variables
1 P2_ID Hidden -- Needs to be set before page load
2 T_NAME Hidden
3 T_DESC Hidden
3 T_USER Hidden
....
Set variable P2_ID before you open page 2
On page 2: Page Process - Before Header
BEGIN
IF :P2_ID IS NULL THEN
:T_NAME:= 'Error - No data selected';
:T_DESC := 'Error - No data selected';
:T_USER := 'Error - No data selected';
ELSE
-- Select data
SELECT t.t_name,
t.t_description,
initcap(:P1_USERNAME)
INTO :T_NAME,
:T_DESC,
:T_USER
FROM test_table r
WHERE t.t_id = :P2_ID;
END IF;
END;
<script>
html_GetElement('T_NAME').name = 'T_NAME';
html_GetElement('T_NAME').value= document.getElementById('T_NAME').value;
html_GetElement('T_DESC').name = 'T_DESC';
html_GetElement('T_DESC').value= document.getElementById('T_DESC').value;
html_GetElement('T_USER').name = 'T_USER';
html_GetElement('T_USER').value= document.getElementById('T_USER').value;
html_GetElement('wwvFlowForm').action='http://website/external.php';
html_GetElement('wwvFlowForm').method='post';
html_GetElement('wwvFlowForm').submit();
</script>
