During Kscope14 last week the results of the ODTUG APEX Theming Competition were announced:
I won the first place, i'm the ODTUG APEX Theme Developer of the year - wow!!
Many thanks to ODTUG for this opportunity & the great price (free pass to Kscope15!). And especially to Adrian Png (@fuzziebrain) for the excellent organisation!
My contribution (including demo-url and source codes) is available here.
Since version 4.2 it's possible to create database-driven RESTful services in Oracle APEX. But it may be somewhat bumpy to set uptheseservices. So this is an overview to accomplish the provided Example RESTful Service Module run properly.
Oracle APEX postinstallation task Did you run the configuration script "apex_rest_config.sql" (on 12c: "apex_rest_config_con.sql") after installing Oracle APEX? You can verify this by checking if the users "APEX_LISTENER" and "APEX_REST_PUBLIC_USER" exist in your database. If not, execute this script from your apex installation directory. See also Oracle APEX Installation Guide. Tip: This script has recent modifications, but it's not part of the standard APEX upgrade procedure. So if you executed this script for example in apex 4.2.1, but you're are now on apex 4.2.3 - then it would be a good idea to run this script again. Configuring Oracle APEX Listener This is well-documented in the guide Installing Oracle APEX Listener, so I won't go in detail here. If your APEX Listener is already configured and you just want to add the connection information for the RESTful database users, then use the setup-option: java -jar apex.war setup This could also be a good reason to upgrade your APEX listener. Check if a newer version is already released (most presumably it will be the case :). Tip: You're having trouble to find out the version number of your current APEX Listener installation? Curiously this is not available in command-line interface, it's slightly hidden in APEX itself: login to your workspace, open the pulldown-menue "Administration" and select the item "About". Example RESTful Service Module This example module provides a good introduction in RESTful services. Open your APEX workspace and navigate to "SQL Workshop" - "RESTful Services". If no RESTful services are defined yet, then click on "Reset Sample Data" (right side of the screen under "Tasks").This function will create the example module named "oracle.example.hr".
Now your module is installed, but when you want to test a resource handler, you will receive the error message "Service unavailable". Just read on, the problem will be fixed in the last section.
Additional administration tasks While developing RESTful services in Oracle APEX, it's essential to get further information about the error details when something should fail. Per default the logging and debugging functionalities are turned off. One option is to enable the logging via the "Debug Tracing"-setting. Please consider that the APEX Listener hasn't got an own logging module, it uses the one from the application server. In case of glassfish application server the logs are located in the file
The second option is to enable the "PrintDebugToScreen"-setting. It displays the error message directly on your screen, this is of course very helpful during the development process. In APEX Listener Troubleshooting is described how to activate both parameters. But keep in mind that both parameters should not be enabled on production systems. The "Debug Tracing"-setting because of generating large amounts of data, the "PrintDebugToScreen"-setting due to security issues. Back to our problem with the Example Module. After enabling the debugging-setting we have a concrete error message now. It points out, that there are some user privileges missing: the REST Public User needs a proxy authentification to the APEX Workspace Owner ("parsing schema"). Certainly quite a basic task, when using RESTful services with Oracle APEX, but (as far as i know) it's still undocumented? Whatever the case, the grant-sql should look like this one: alter user [workspace owner] grant connect through apex_rest_public_user;
Now you should be ready to use the RESTful services with Oracle APEX. Besides the OTN resources, don't forget to have a look at Kris Rice's Blog to discover further possibilities with RESTful services.
This is a follow-up to Denes Kubicek's blogpost "APEX Collections and Joins". Denes wrote in his blogpost about receiving the error "ORA-01722: invalid number" when querying an APEX collection. What actually astonishing is, that the collection definitely contains just valid numbers and this error is not really reproducible, it just appears sporadic.
I also hit this issue after upgrading an APEX application, which heavily uses APEX collections. After upgrading (to apex version 4.2) the error appeared in all sorts of different places... sometimes... the only constant was, that it only showed up when querying APEX collections. But in my case the errors appeared more and more less and after 2-3 days the spook was over.
Denes blogpost gave me the inspiration to think over this issue again. Although I tried not to reproduce this error again (hell no!), i'm quite sure, that the following is a plausible explanation:
One problem is, that apex collections, due to the structure, are often used with implicit conversions. Number values are often stored in the varchar2-columns of the collection. This is not always immediately visible, especially when joining these columns with other table columns or when the query is encapsulated by a view. But have a look on this simplified example: [SQL 1]
select *
from apex_collections
where collection_name = 'ARTICLES'
and c001 = vArticleNo;
The datatype of the column "C001" is "varchar2", the datatype of the variable "vArticleNo" is "number". So Oracle must always convert internally the column value to a number ("implicit conversion"). This is (apparently..) no problem and works fine, as long as you insert only valid numbers in "c001" for your collection.
But keep in mind, that the column "c001" is also used in other collections and probably contains strings there. In this case the following sql would abort with the error "ORA-01722: invalid number":
[SQL 2]
select *
from apex_collections
where c001 = vArticleNo;
Of course this kind of sql (without filtering the collection name) would be never used in practice. But it's just for understanding the following fact: the first query [SQL 1] can only work, when the Cost-Based Optimizer (CBO) decides to filter the data with "collection_name = 'ARTICLES'" at first. Within this resultset it filters now the data with "c001 = vArticleNo". If the CBO would decide to filter the criterias vice versa, the query would fail with "ORA-01722: invalid numbers", because it will first convert the complete data of the column "c001" to number - including the strings from the other collections!
But when decides the CBO to apply the filter criterias vice versa? APEX stores the collection contents in the table "wwv_flow_collection_members$". If you already implemented some APEX collections, it will contain a lot of histograms for these user data columns:
[SQL 3]
select *
from dba_histograms
where owner = 'APEX_040200'
and table_name = 'WWV_FLOW_COLLECTION_MEMBERS$'
and column_name like 'C0%'
order by column_name, endpoint_number;
Depending on these table stats, the CBO could decide to apply the filter criterias vice versa. So if you hit this error and you won't touch the program code at first, just try to reanalyse the stats of this repository table as a quick fix. Best choice would be to completely remove the histograms from the user data columns ("dbms_stats.delete_column_stats"). It makes no sense to have histograms on these columns - the content is just temporary and the data characterics changes constantly. In my opinion the histograms for these columns could be completely deactivated ("dbms_stats.set_table_prefs").
If you are using implicit conversions and you're upgrading your apex version, it's more likely, that this error could occurs. The APEX repositiory is newly build and therefore the table stats are probably not optimal yet (see my personal error scenario above).
But back to the example sql. To avoid the implicit conversion here, the datatype of the variable has to be just converted to the datatype of the table column:
[SQL 4]
select *
from apex_collections
where collection_name = 'ARTICLES'
and c001 = to_char(vArticleNo);
Now the CBO can do whatever he wants.
The better way would be just to use the number columns (N001..N005) in APEX collection - like Denes described in his blogpost. Unfortunately only five of them exist.
So, take care not to use unintentionally implicit conversions in collections. Never use implicit conversions at all!
Twitter Bootstrap has become a hugely popular framework these days. It's lightweighted, elegant, responsive and released under open-source license - hooray! Furthermore the implementation into the apex templates is mostly straightforward.
But after implementing the first bootstrap components into Oracle Apex, you will notice that something has slightly changed... when examining the html elements it's getting obvious, that quite a lot of standard apex elements are now mixed up with stylings from bootstrap. You can see it most clearly when using input items: just resize your item, change the "Form Element Width"-setting in Apex - the value will be ignored! The width of your input item will be overwritten by the value from a generic Bootstrap-style.
Unfortunately Bootstrap contains css selectors which are simply too generic, and this causes the css conflicts with Oracle Apex.
One solution could be using the customizing feature from bootstrap. There you can create your own "personal bootstrap edition", including just those components you actually need. For example, if you start using bootstrap by implementing a navigation list, you could create your customized bootstrap including just the scaffoldings and the nav components. However, taking a closer look again and you will see that Bootstrap i.e. declares its "font-family" on the level of the common html-body.
There is no way around it: an clean integration of Bootstrap is only possible, if you modify the LESS sourcecodes depending on your needs to generate a new bootstrap.css. The approach would be to namespace the Bootstrap components and reference to this additional class name in your apex application. So the bootstrap components would be always surrounded by its own container within apex. It depends on the used bootstrap component, but commonly some modifications in the LESS-sources are necessary to avoid every css conflict between bootstrap and apex styles.
My conclusion
When implementing Twitter Bootstrap in Oracle Apex, it's unavoidable to get know about the stylesheets and dependencies of the bootstrap components. Exploiting the library and the LESS sourcecodes is essential.
Bootstrap is a fine, perhaps a little bit overhyped:) framework. I will continue using bootstrap - not as a complete framework, but to cherry-pick some parts of it.
You might hit on the library Modernizr while using the new responsive theme 25. Oracle Apex has implemented this library for the first time within this theme.
What is Modernizr?
Modernizr is a feature-detection library. It can currently detect over 40 HTML5- and CSS3-features, so you can adapt your application whether the feature is supported by the users browser or not.
Why should i use Modernizr?
The traditional way of checking the browsers capabilities is the integration of User Agents like this one:
<!--[if IE 7 ]> <html class="ie7 no-css3 no-js" lang="en"> <![endif]-->
This is an outdated practice, due to the wide variety of browser versions. Nowadays a developer could hardly knows which browser supports an specific feature. Or do you know the capabilities of Kindle Fire's Silk web browser?:-)
Instead of asking the question, is this Internet Explorer 7 or Firefox 18, the question should be: is there support for a particular feature? This is what Modernizr does.
How can i use Modernizr?
Modernizr will test your browser for the features that it may or may not support and makes the results available in two ways: as classes in the <html>-element and as a javascript object.
The test results in the <html>-element
To see Modernizr in action, you could create an apex application with theme 25 just including a dummy page. Open your web developer / firebug and have a look at the <html>-element:
Modernizr has included for each test result a seperate class in this tag. For example, including the class "borderradius" means, that your browser supports this "rounded borders"-feature. In case of non-support Modernizr would include the prefix "no-" to the class name, so it would be named "no-borderradius".
In your stylesheet you can easily now reference to these classes and adapt your application, for example like this (simplified code):
The test results in the javascript object
Modernizr creates a global javascript object named "Modernizr". Open the web developer in your theme 25 application and query this object in console:
Each test result is included as a boolean property. For example the property "borderradius" is set to true, so (obviously) your browser supports the "rounded borders"-feature.
In your javascript code you could query now these properties. For example the following code adds the class "round_borders", if the browser supports the "borderradius"-feature:
if (Modernizr.borderradius) {$("#mycontainer1").addClass("round_borders"); }
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.
