Impress.js is a cool presentation framework based on the new features of CSS3 transforms and transitions. It makes me wonder about, how it will looks like, when integrating it in Oracle Apex. Don't take the following application too serious, it's just a fun implementation:
This demo is best viewed on a desktop pc with the latest chrome, safari or firefox browser (and probably IE10). Mobile Browsers are also supported, but the transformations are maybe not always optimal.
The focus of Impress.js is the presentation of slides, so it's not really appropriated for Oracle Apex. But perhaps it's now the time to start developing some nice Apex plugins with this new CSS3 features..
This is the sequel from my previous blogpost. Now the static local data store of the ExtJS combobox will be replaced with a remote data store.
To succeed the communications in JSON-format, i will use PL/JSON by Jonas Krogsboell. It's a great utility, which provides the creating and parsing of json objects in pl/sql. If you shouldn't be experienced with this utility yet, here is an example script for introduction:
declare
tabResultset json_list;
tabParameters json;
begin
tabResultset := json_dyn.executeList('select * from hr.employees where rownum < 5');
tabParameters := json();
tabParameters.put('Root', tabResultset);
tabParameters.print();
end;
This code dumps a json-object with records from the table "hr.employees" to the dbms-output-console. You can see how easily this can be accomplished. This code is also already the basis for the following data proxy package.
The intention is to create a universal procedure, where we pass the sql query as parameter and the json object including the recordset will be created. The json object should contain only those records from the current page and the total row count of the query as additional information ("displaying records 25 - 49 of 128").
create or replace package pkg_data_proxy AS
procedure pQueryJson(pSql varchar2, pStart pls_integer default null, pLimit pls_integer default null, pOutput varchar2 default 'htp');
end pkg_data_proxy;
create or replace
package body pkg_data_proxy AS
procedure pQueryJson(pSql varchar2, pStart pls_integer default null, pLimit pls_integer default null, pOutput varchar2 default 'htp') is
tabResultset adm.json_list;
tabParameters adm.json;
vSqlEnvelope varchar2(2000) := q'^
select * from
( select a.*, rownum as sql_rownum from
( select q.*, count(*) over () sql_totalrows from
( #QUERY#
) q
) a where rownum <= #LIMIT#
) where sql_rownum > #START#
^';
vTotalRows pls_integer;
begin
wwv_flow_utilities.fast_replace(vSqlEnvelope, '#QUERY#', pSql);
wwv_flow_utilities.fast_replace(vSqlEnvelope, '#START#', coalesce(to_char(pStart), '0'));
wwv_flow_utilities.fast_replace(vSqlEnvelope, '#LIMIT#', coalesce(to_char(pStart + pLimit), 'rownum'));
tabResultset := adm.json_dyn.executeList(vSqlEnvelope);
tabParameters := adm.json();
tabParameters.put('total', 0);
tabParameters.put('rowset', tabResultset);
vTotalRows := adm.json_ext.get_number(tabParameters, 'rowset[1].SQL_TOTALROWS');
tabParameters.put('total', vTotalRows);
if (pOutput = 'screen') then
dbms_output.put_line(tabParameters.to_char());
else
sys.htp.p(tabParameters.to_char(false));
end if;
end pQueryJson;
end pkg_data_proxy;
The parameters of the procedure "pQueryJson" are the sql query ("pSql"), the starting point of row count ("pStart"), the number of records ("pLimit") and the option to print the json object for debugging purposes to the dbms-output-console ("pOutput").
The query resultset is marked with "rowset" (line 38) and the total row count of the query is copied into the header of the json object (line 40-41).
The complete json string for one record should look like this:
This procedure will be invoked in every ajax-call of the combobox, here is the updated code for the package "pkg_plugin_ext_combobox" including the new function "fAjax":
create or replace
PACKAGE pkg_plugin_ext_combobox AS
subtype typeAttr is apex_application_page_items.attribute_01%type;
function fRender (p_item in apex_plugin.t_page_item,
p_plugin in apex_plugin.t_plugin,
p_value in varchar2,
p_is_readonly in boolean,
p_is_printer_friendly in boolean) return apex_plugin.t_page_item_render_result;
function fAjax (p_item in apex_plugin.t_page_item,
p_plugin in apex_plugin.t_plugin ) return apex_plugin.t_page_item_ajax_result;
end pkg_plugin_ext_combobox;
create or replace
PACKAGE BODY pkg_plugin_ext_combobox AS
-- Private declarations
function fJsonProxy (p_item in apex_plugin.t_page_item,
p_plugin in apex_plugin.t_plugin,
p_value in varchar2 )
return sys.dbms_sql.desc_tab2;
-- End private declarations
function fRender
(p_item in apex_plugin.t_page_item, p_plugin in apex_plugin.t_plugin, p_value in varchar2,
p_is_readonly in boolean, p_is_printer_friendly in boolean)
return apex_plugin.t_page_item_render_result is
pDisplayColumn typeAttr := p_item.attribute_01;
pValueColumn typeAttr := p_item.attribute_02;
pItemWidth typeAttr := p_item.attribute_03;
pEmptyText typeAttr := p_item.attribute_05;
pXTemplate typeAttr := p_item.attribute_06;
vResult apex_plugin.t_page_item_render_result;
vItemValue varchar2(32767) := sys.htf.escape_sc(p_value);
vItemName varchar2(200);
vJsCode varchar2(32767);
vColumns sys.dbms_sql.desc_tab2;
begin
if (apex_application.g_debug) then
apex_plugin_util.debug_page_item (p_plugin => p_plugin, p_page_item => p_item, p_value => p_value,
p_is_readonly => p_is_readonly, p_is_printer_friendly => p_is_printer_friendly );
end if;
vItemName := apex_plugin.get_input_name_for_page_item(false);
vColumns := fJsonProxy(p_item => p_item, p_plugin => p_plugin, p_value => p_value );
sys.htp.p('');
vJsCode := q'^
Ext.onReady(function()
{ var vCombo_#ITEM_ID# = Ext.create('Ext.form.field.ComboBox',
{ transform: '#ITEM_ID#',
store: vStore_#ITEM_ID#,
queryMode: 'remote',
queryParam: 'p_widget_num_return',
displayField: '#DISPLAY_COLUMN#',
valueField: '#VALUE_COLUMN#',
hiddenName: '#ITEM_NAME#',
emptyText: '#EMPTY_TEXT#',
#XTEMPLATE#
selectOnFocus: true,
minChars: 1,
listConfig:
{ resizable: true,
resizeHandles: 'all',
maxHeight: 400,
maxWidth: 800
},
width: #WIDTH#,
pageSize: 1
});
vStore_#ITEM_ID#.load(
{ params : { 'x01' : '#VALUE#'},
callback: function(records, operation, successful)
{ if (successful)
{ vCombo_#ITEM_ID#.setValue(records[0]);
}
else
{ vCombo_#ITEM_ID#.setValue("#EMPTY_TEXT#");
}
}
});
});
^';
wwv_flow_utilities.fast_replace(vJsCode, '#ITEM_ID#', p_item.name);
wwv_flow_utilities.fast_replace(vJsCode, '#ITEM_NAME#', vItemName);
wwv_flow_utilities.fast_replace(vJsCode, '#VALUE#', vItemValue);
wwv_flow_utilities.fast_replace(vJsCode, '#DISPLAY_COLUMN#', pDisplayColumn);
wwv_flow_utilities.fast_replace(vJsCode, '#VALUE_COLUMN#', pValueColumn);
wwv_flow_utilities.fast_replace(vJsCode, '#EMPTY_TEXT#', pEmptyText);
wwv_flow_utilities.fast_replace(vJsCode, '#WIDTH#', pItemWidth);
wwv_flow_utilities.fast_replace(vJsCode, '#XTEMPLATE#', pXTemplate);
apex_javascript.add_onload_code(p_code => vJsCode);
vResult.is_navigable := true;
return (vResult);
end fRender;
function fAjax (p_item apex_plugin.t_page_item, p_plugin apex_plugin.t_plugin )
return apex_plugin.t_page_item_ajax_result is
pValueColumn typeAttr := p_item.attribute_02;
vSqlHandler apex_plugin_util.t_sql_handler;
vResult apex_plugin.t_page_item_ajax_result;
vSql varchar2(32767);
vBinds dbms_sql.varchar2_table;
begin
apex_plugin_util.print_json_http_header;
vSql := p_item.lov_definition;
vBinds := wwv_flow_utilities.get_binds(vSql);
for i in 1..vBinds.count loop
vSql := replace(lower(vSql), lower(vBinds(i)), '''' || v(ltrim(vBinds(i),':')) || '''');
end loop;
if (apex_application.g_widget_num_return is not null) then
vSql := replace(vSql, '%%%', '%' || lower(apex_application.g_widget_num_return) || '%');
end if;
if (apex_application.g_x01 is not null) then
vSql := vSql || ' and ' || pValueColumn || ' = ' || apex_application.g_x01;
end if;
pkg_data_proxy.pQueryJson(vSql, coalesce(apex_application.g_widget_action_mod, 0), coalesce(apex_application.g_widget_action, 100));
apex_plugin_util.free_sql_handler(vSqlHandler);
return vResult;
exception
when others then
apex_plugin_util.free_sql_handler(vSqlHandler);
raise;
end fAjax;
function fJsonProxy (p_item in apex_plugin.t_page_item, p_plugin in apex_plugin.t_plugin, p_value in varchar2)
return sys.dbms_sql.desc_tab2 is
vSqlHandler apex_plugin_util.t_sql_handler;
vColCount number;
vColNames varchar2(2000);
vAjaxIdentifier varchar2(100);
vPageSize typeAttr := p_item.attribute_04;
begin
vSqlHandler := apex_plugin_util.get_sql_handler (p_sql_statement => p_item.lov_definition,
p_min_columns => 1, p_max_columns => 999, p_component_name => p_item.id);
vColCount := vSqlHandler.column_list.count();
for i in 1 .. vColCount loop
vColNames := vColNames || case when i > 1 then ',' end || '"' || upper(vSqlHandler.column_list(i).col_name) || '"';
end loop;
apex_plugin_util.free_sql_handler(vSqlHandler);
vAjaxIdentifier := apex_plugin.get_ajax_identifier;
sys.htp.p('');
return vSqlHandler.column_list;
exception
when others then
apex_plugin_util.free_sql_handler(vSqlHandler);
raise;
end fJsonProxy;
end pkg_plugin_ext_combobox;
The local data store from the former "fRender"-function was removed. Instead of that, a new function "fJsonProxy" is called, which creates the remote data store.
At first this function gets the column names from our sql query and creates the data model. The data store sets an proxy to the database procedure "wwv_flow.show" to trigger the apex ajax calls. It's a bit fussy to get the correct mappings in the proxy attributes, but (as always) the webkit developer tools / firebug are the irreplaceable assistents when debugging ajax calls. In case of error, Apex itself keeps quiet and just throws a "404 page not found" due to security guidance.
The proxy properties in detail:
startParam: the starting point for the records rowcount, used for pagination, corresponds to "p_widget_action_mod" in Apex
limitParam: the number of records per page, corresponds to "p_widget_action"
pageParam: the starting page number, unset
noCache: set to "false", caching has to be enabled. Otherwise ExtJS would add a random "_dc"-Parameter to the URL like "wwv_flow.show?_dc=1325682563764". But Apex can't interpret/ignore this and fails.
extraParams: Apex needs some more information like the app and page no. These are extracted from DOM and included in the "extraParams" section.
The create-command of the combobox in "fRender" stays almost the same, but some attributes are noteable. The attribut "pageSize" is no longer the number of records displayed per page. Now it just controls if the pagination toolbar should be displayed (yes/no)! The attribut "queryParam" contains the ongoing userinput of the item (for autocomplete) and is mapped to "p_widget_num_return".
You should also pay attention to the "hiddenName"-attribute. This creates a hidden input item with the value of the "valueField". Otherwise the value of the "displayField" would be submitted to database. ExtJs distinguishes between the value of an ExtJS-component and the corresponding DOM-element. By way of example, the getValue()-method in "Ext.getCmp" differs elementary from the one in "Ext.get".
In consequence, we have to load the value of the "displayField" also via ajax call and set this value in the ExtJS-combobox (this happens in lines 84-94). Otherwise the employee id would be displayed, when loading a record during page refresh. This solution applies to version 4.1 of ExtJS - in former releases it was quite easier, just by setting the "value"- and "valueField"-attributs in the combobox-config. Anyway, the primary key id for this ajax call is passed by using the apex parameter "x01". If this parameter is set, a further condition will be added in the where-clause of the ajax call. It contains the filtering on the basis of the primary key, so that only one record with required the "DisplayName" is returned (line 141-143).
In order that, we hit the new function "fAjax". The call of the function has to be included in the apex item plugin: add "pkg_plugin_ext_combobox.fAjax" in the "AJAX function name" of the callbacks.
In function "fAjax" the sql query for the ajax call is getting prepared. At first it binds the page item variables (line 132 - 135). For example the apex page contains a field with the department id and the query should be filtered including the selected department. The comboxbox sql would contain a bind variable like :Px_DEPARTMENT_ID, which will be simply replaced with the actual value here.
Furthermore the functions checks, if the variable "apex_application.g_widget_num_return" is set. It contains the ongoing userinput of the item. The user enters "david" and all employees containing "david" will be automatically displayed (autocompletion). To understand the "replace"-command in line 138, please insert the following sql in the "list of values definition" of your apex item:
select first_name || ' ' || last_name as name,
employee_id,
phone_number,
salary,
image
from hr.employees
where lower(first_name || ' ' || last_name) like '%%%'
The "%%%"-Symbols are the placeholder for the search string the user has entered. They keep the query valid and the condition is always true (except when null, but this is not relevant), when no search text is entered. Of course this mechanism is quite rudimentary, but can be easily extended to the search criterias the developer needs.
At last, the purpose of checking the variable "apex_application.g_x01" was already explained before.
After all you should have this nice enhanced selectlist avaible now - and a good foundation to discover the enormous potential of the ExtJS-Framework furthermore.
Select lists are still quite limited in Apex. The following blogposts give a tutorial how to implement an enhanced select list with the usage of the ExtJS-Framework.
This combobox will contains a freely html-formated (even with conditional logics) select list and features autocompletion and pagination. Here's an example screenshot:
Apex Item-Plugin including an ExtJS Combobox
The implementation will be described in two parts. The first blogpost contains the basics of integrating an ExtJS-Combobox as item plugin. The second post covers the implementation of the remote data store within this widget.
The code is tested with Apex 4.x and ExtJS 4.1.1a. Keep an eye on the version level of your installed ExtJS. Each release from Sencha is "kind of ground shaking".. for example the used parameter "hiddenName" was established in Ext3. With Ext4 it was firstly removed, later reintroduced again, but not working correctly. By now it handles like in Ext3 again. Unfortunately this is by far not a single case in the lifecycle of ExtJS.
Now let's get started with a ExtJS combobox based on a simple local data store. At first create the following package to provide the callback functions for the item plugin. When using the local data store we just need a render function at the moment.
create or replace package pkg_plugin_ext_combobox as
subtype typeAttr is apex_application_page_items.attribute_01%type;
function fRender (p_item in apex_plugin.t_page_item,
p_plugin in apex_plugin.t_plugin,
p_value in varchar2,
p_is_readonly in boolean,
p_is_printer_friendly in boolean) return apex_plugin.t_page_item_render_result;
end pkg_plugin_ext_combobox;
create or replace
PACKAGE BODY pkg_plugin_ext_combobox AS
function fRender
(p_item in apex_plugin.t_page_item, p_plugin in apex_plugin.t_plugin, p_value in varchar2,
p_is_readonly in boolean, p_is_printer_friendly in boolean)
return apex_plugin.t_page_item_render_result is
pDisplayColumn typeAttr := p_item.attribute_01;
pValueColumn typeAttr := p_item.attribute_02;
pItemWidth typeAttr := p_item.attribute_03;
pEmptyText typeAttr := p_item.attribute_05;
pXTemplate typeAttr := p_item.attribute_06;
vResult apex_plugin.t_page_item_render_result;
vItemValue varchar2(32767) := sys.htf.escape_sc(p_value);
vItemName varchar2(200);
vJsCode varchar2(32767);
vColumns sys.dbms_sql.desc_tab2;
begin
if (apex_application.g_debug) then
apex_plugin_util.debug_page_item (p_plugin => p_plugin, p_page_item => p_item, p_value => p_value,
p_is_readonly => p_is_readonly, p_is_printer_friendly => p_is_printer_friendly );
end if;
vItemName := apex_plugin.get_input_name_for_page_item(false);
sys.htp.p('<select id="' || p_item.name || '"></select>');
vJsCode := q'^
Ext.onReady(function()
{ Ext.define("vModel_#ITEM_ID#",
{ extend: "Ext.data.Model",
fields: ["NAME","EMPLOYEE_ID","PHONE_NUMBER", "SALARY"]
});
vStore_#ITEM_ID# = Ext.create("Ext.data.Store",
{ model: "vModel_#ITEM_ID#",
idProperty: "EMPLOYEE_ID",
data: [{ "NAME": "Ben Nebelt", "EMPLOYEE_ID": 10, "PHONE_NUMBER": '10-10', "SALARY": 4000},
{ "NAME": "Karl Toffel", "EMPLOYEE_ID": 20, "PHONE_NUMBER": '10-20', "SALARY": 5000},
{ "NAME": "Volker Nbrot", "EMPLOYEE_ID": 30, "PHONE_NUMBER": '10-30', "SALARY": 6000}]
});
var vCombo_#ITEM_ID# = Ext.create('Ext.form.field.ComboBox',
{ transform: '#ITEM_ID#',
store: vStore_#ITEM_ID#,
value: '#VALUE#',
queryMode: 'local',
displayField: '#DISPLAY_COLUMN#',
valueField: '#VALUE_COLUMN#',
hiddenName: '#ITEM_NAME#',
emptyText: '#EMPTY_TEXT#',
#XTEMPLATE#
selectOnFocus: true,
minChars: 1,
listConfig:
{ resizable: true,
resizeHandles: 'all',
maxHeight: 400,
maxWidth: 800
},
width: #WIDTH#
});
});
^';
wwv_flow_utilities.fast_replace(vJsCode, '#ITEM_ID#', p_item.name);
wwv_flow_utilities.fast_replace(vJsCode, '#ITEM_NAME#', vItemName);
wwv_flow_utilities.fast_replace(vJsCode, '#VALUE#', vItemValue);
wwv_flow_utilities.fast_replace(vJsCode, '#DISPLAY_COLUMN#', pDisplayColumn);
wwv_flow_utilities.fast_replace(vJsCode, '#VALUE_COLUMN#', pValueColumn);
wwv_flow_utilities.fast_replace(vJsCode, '#EMPTY_TEXT#', pEmptyText);
wwv_flow_utilities.fast_replace(vJsCode, '#WIDTH#', pItemWidth);
wwv_flow_utilities.fast_replace(vJsCode, '#XTEMPLATE#', pXTemplate);
apex_javascript.add_onload_code(p_code => vJsCode);
vResult.is_navigable := true;
return (vResult);
end fRender;
end pkg_plugin_ext_combobox;
I will focus on the characteristics and pitfalls of integrating an ExtJS combobox in Apex. So you should be already experienced with the basics of Apex Plugins and the ExtJS framework.
Mainly the function posts a select item containing only the id (line44), which will be transformed to the ExtJS combobox (line 62). So most features will come from ExtJS (but for example the item labeling is still apex standard).
The code basis is already adjusted for implementing the remote store. You can see that i.e. the "displayField" and "valueField" are set through the item plugin attributes although the (temporary) local data store is hardcoded. To keep the javascript code more readable, i include placeholders (#..#) for the dynamic attributes and replace them afterwards via "wwv_flow_utilities.fast_replace".
In general, mostly the standards for an apex item plugin respectively ExtJS combobox are used. The combobox is resizable (line 73-78) and includes the possibility to use a XTemplate (line 70). To get along with the combobox, create the apex item plugin now. I will only list the specific settings for the plugin, all other can remain to default (or your own choice).
Item plugin "extComboBox"
Render Function Name: "pkg_plugin_ext_combobox.fRender"
Standard Attributes: enable at least "Is visible widget", "Has source attributes", "Has list of values" to be prepared for the remote store. And, all-important, set the attribut "Maximum columns" to "999", so the forthcoming lov sql's can return more than 2 columns.
Component attributes:
Attribut "Display column" Type "Text", Required "Yes", Display Width "20", Maximum Width "40" Help text "column name from data store to be displayed in combobox"
Attribut "Value column" Type "Text", Required "Yes", Display Width "20", Maximum Width "40" Help text "column name from data store to be submitted from combobox"
Attribut "Item width (px)" Type "Integer", Required "No", Display Width "4", Maximum Width "4", Default value "250" Help text "Width of combobox in pixels"
Attribut "Records per page" Type "Integer", Required "No", Display Width "4", Maximum Width "4", Default value "25" Help text "Number of records showed per page"
Attribut "Empty text" Type "Text", Required "No", Display Width "20", Maximum Width "40", Default value "<please select>" Help text "Displayed text when item value is null"
Attribut "XTemplate" Type "Textarea", Required "No", Display Width "", Maximum Width "2000" Help text "ExtJS Xtemplate"
Now create an apex item based on the plugin. In the plugin attributes set the "Display column" to "NAME" and the "Value column" to "EMPLOYEE_ID". If the attribut "XTemplate" is not set, the select list is unformatted and only the display column is listed. To get an impression how powerful XTemplate's are, you could enter the following code:
With XTemplate you can loop through your records and format the output via html. Even conditional logic is possible, see line 5 where all salaries less than 5000 will be marked red. Mostly important for the apex integration is to include the class "x-boundlist-item", otherwise the list entries are not selectable!
Now then, the combobox should look like this:
The result of this blogposting
In the next blogpost we will replace the local data store with a remote one. Things are getting interesting..:-)
