Running an Oracle APEX application against MongoDB

This video demonstrates how to run an Oracle APEX application against MongoDB. The application contains a fully featured Interactive report and a form page running CRUD operations, both based on MongoDB data. 

In detail, it gives a brief introduction into the following techniques:

• extracting data from a website with Import.io. In this demonstration, the books about Oracle APEX are grabbed from an online store and will be used as example data for the application
• uploading the data to MongoDB and showing the easy handling of a JSON-style data store
• creating a RESTful API in Node.js using the modules Mongoose and Express.js
• testing the Web Services with a REST client (PAW)
• consuming the Web Services in Oracle APEX.  The data handling is achieved by using the latest JSON features shipped with Oracle Database 12c and Oracle APEX 5.

The unpleasant installation process of APEX5 in 12c

Since the early days of HTML DB the installation (or updating) process stayed almost the same. You could update your APEX database environment in just a few simple steps, this worked very stable for years.  With the releases of Oracle Database 12c and Apex 5 this easy handling changed for the worse.

This blogpost is about performing a new, clean APEX5 installation in a 12c database. This apparently simple task already contains some unpleasant surprises. This blogpost is not about migrating existing apex environments into 12c, these scenarios would go beyond its scope: the database architectures (11g, CDB 12c, Non-CDB 12c, PDB 12c), the different migration paths among each other, different APEX version levels and not to mention what happens when you plug it into another cdb environment. Administrators won't get quickly bored in 12c.

Make your choice, install APEX in a CDB or a PDB?
The latest database release 12.1.0.2 is shipped with APEX version 4.2.5, installed in the root container (CDB). Accordingly, one of the first steps will be an upgrade to APEX version 5. The "Non-CDB 12c"-architecture is already deprecated again, so you have to decide, if it's better to upgrade the existing CDB installation or to perform a new installation in a PDB.

If you have plans to actively use the multitenant option you should (at least) read the whitepaper Deploying and developing Oracle APEX with Oracle DB 12c and the Installation guide to make the decision. The recommended way is, according to the current 12c database documentation, to install APEX in a CDB. This enables centralized management and ensures every PDB runs in the same version. This recommendation has changed in the APEX 5 documentation - for the majority of use cases, now APEX should be better installed in PDB. Recently the blog post Why you should remove APEX from the CDB$ROOT also strongly recommends to install APEX in PDB, mainly to achieve higher flexibilty.


Installation in a CDB
You need DB patch no. 20618595 to upgrade APEX from version 4.2 to version 5 in a CDB. I won't go in detail here, the installation procedure is described in blogpost Upgrading to APEX 5.0 in your Oracle Database Release 12.1 CDB by Jason Straub. People with no greater affinity to DBA activities, will probably have some difficulties with the implementation of the necessary installation tasks.

The required patch is only available for Linux and AIX, although in the meantime APEX5 was released over half a year ago. In consequence, if you're on Windows, Solaris or HP-UX and followed conscientiously the advices from Oracle, this means you upgraded early to db 12c (premier support for 11gR2 ended in january 2015) and migrated your apex environment to CDB, you still have no upgrade path to apex 5. In this case, the only (undocumented) option is to deinstall your (productive?) APEX environment and perform a new, fresh APEX installation.

Installation in a PDB
At first the default apex installation has to be removed from the CDB before you can start with the APEX5 installation in PDB. Browsing the Installation guide brings you quickly to the section Uninstalling Application Express from a CDB. After executing the mentioned script "apxremov.sql", everything looks fine at the first glance. Though a message is displayed to scan all the logfiles for ORA-errors, the console output shows no errors. But when you examine the logfiles, you'll see that the script tries to remove an APEX5 installation. Of course this doesn't exist yet, your installation still resides under the schema APEX_040200.

With the release of 12c the perl utility "catcon.pl" was introduced. It allows you to run SQL scripts in multitenant environments, the way the APEX removal script was also executed. Obviously the tool doesn't offer the option (or at least no script known to me used it yet) to hand over fatal errors to the console output. It's therefore all the more unfortunate that the tool creates already with one single execution not less than 34 different logfiles. It would have been desirable, Oracle had involved a better quality assurance for such an essential, new administration tool.

Back to the failed APEX deinstallation: so we need a script to remove APEX4.2 from the CDB. This can be found in the APEX distribution delivered with database installation at $ORACLE_HOME/apex. If you execute the "apxremov.sql" from this location now, your APEX_040200 schema will still stay alive. In Version 4.2 the scripting names are different. "apxremov.sql" also exists there, but it doesn't take any effect in a CDB. You'll have to execute a script called "apxremov_con.sql".

------------------------------------------------------
Update 04.12.15
There is no need to set the following undocumented parameter, just execute the correct script "$ORACLE_HOME/apex/apxremov_con.sql". Thanks to Jason for clarifying this.
------------------------------------------------------
When executing this script, you'll hit the error "ORA-28014: cannot drop administrative users". To avoid this error, you have to set the undocumented database parameter "_oracle_script". As you might know, using undocumented '_' parameters can violate your Oracle support contract. Those parameters should only be used when Oracle tells you to use them - but no words about that in the release notes, installation guide or at support.oracle.com. So the official way would be to create a service request at Oracle support. Otherwise, risking to violate your support contract and execute the following statement:
      alter session set "_oracle_script"=true;
Now your APEX4.2 installation should be removed successfully and you can start with your APEX5 installation.

Conclusion

APEX administrators are facing new challenges with the 12c database architecture. Just a simple upgrade from APEX 4.2 to 5 in 12c is anything but a quick and casual process. Probably not just a few guys might have thought "Let me try the latest releases, no big deal to install 12c and Apex 5!" - only to become quite frustrated after some time. Oracle should find a remedy, at least the documentation or release notes should point out the pitfalls. Of course the APEX development team, an absolutely passionate and outstanding team at Oracle, are rather dependent on the features (and patches) the Database development team delivers.
With the future release of DB 12cR2 the handling will probably get easier again - at least if it's shipped with installed APEX 5 in PDB$SEED then.

/* Example how to remove the default APEX 4.2.5 installation 
   from CDB and install APEX 5 in a PDB */

/* Navigate to APEX 4.2 directory (from database installation) */
oracle@srv> cd $ORACLE_HOME/apex

/* Open SQLPlus */
oracle@srv> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 27 19:05:41 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

/* Allow administration of common cdb users */
/* not necessary!
SQL> alter session set "_oracle_script"=true;

Session altered.
*/

/* Remove APEX 4.2 from CDB */
SQL> @apxremov_con.sql;

[...]

SQL> exit

/* Navigate to your APEX5 installation directory */
oracle@srv> cd /opt/oracle/install/apex5

/* Open SQLPlus */
oracle@srv> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 27 19:05:41 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

/* Switch to your pdb */
SQL> alter session set container=[pdb_name];

Session altered.

/* Install APEX5 */
SQL> @apexins.sql [apex_tablespace] [files_tablespace] [temp_tablespace] [images]

[...]

I won the ODTUG Apex Theming Competition!

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. 

Setup RESTful services with APEX Listener 2.0.x

Since version 4.2 it's possible to create database-driven RESTful services in Oracle APEX. But it may be somewhat bumpy to set up these services. 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

    [glassfish installation directory]/glassfish/domains/[domain name]/logs/server.log

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.

Spooky "ORA-01722: invalid number" errors when using Apex collections

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!

CSS conflicts between Twitter Bootstrap and Apex

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.

Oracle Apex with Modernizr in a nutshell

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):

   .borderradius .mycontainer {-webkit-border-radius: 6px; [...]}
   .no-borderradius .mycontainer {/*grmpf, need Sliding Doors .. */ [...]}

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");
   }