Wednesday, June 06, 2018

Facebook, Google and Custom Authentication in the same Oracle APEX 18.1 app

Oracle APEX 18.1 has many new features, one of them is called Social Login.

On the World Cup 2018 Challenge, you can see the implementation of this new feature. The site allows you to sign-up or login with Facebook, Google, and your own email address.

It's even nicer that if you register with your email, but later decide to sign-up with Google or Facebook, it will recognize you as the same user if the email address is the same.

To get the Social Login to work I had to do the following...


To enable Facebook login in your own app, you first have to create an app on Facebook. Creating an application is straightforward by following the wizards, just make sure you create a website app.


To enable Google login in your own app, you first have to create a project on Google. Adrian did a really nice blog post which walks you through creating your project and setup Google authentication in your APEX application.

To hook-up Google and Facebook to our own APEX app, we have to let APEX know which credentials it should use, namely the info you find in the previous screenshots.

Web Credentials 

Go to App Builder > Workspace Utilities > All Workspace Utilities and click on the Web Credentials link

I added the Web Credentials for Facebook and Google. Web Credentials store the necessary info (Client ID = App ID and Client Secret = App Secret) of the OAuth2 authentication. OAuth2 is a standard these days which most sites are using to authenticate you as a user. Web Credentials are stored on Workspace Level so you can reuse those credentials in all the APEX apps in the same workspace.

Authentication Scheme 

We need to create the different authentication schemes. The Custom Authentication is to authenticate with email, next we have FACEBOOK, and GOOGLE (and Application Express Authentication which is there by default, but not used in this app).

Custom Authentication Scheme

I blogged before about Create a Custom Authentication and Authorization Scheme in Oracle APEX. The package I use in that blog post is pretty similar to the one of the World Cup app. In the Authentication Scheme, you define the authentication function. I also have a post-authentication procedure that sets some application items.

Facebook Authentication Scheme

Normally the authentication scheme of Facebook would look a bit different as Oracle APEX has built-in Facebook authentication, but for that to work, you need to load the SSL certificate in the Oracle wallet. On the platform the World Cup is running, the database is 12.1 and unfortunately, there's a bug in the database with multi-site or wildcard certificates (which Facebook has). So I had to workaround the issue, but I still used a new feature of APEX 18.1, instead of Facebook Authentication I used Generic OAuth2 Provider.

This is how it looks like:

As we are using the Generic OAuth2 Provider, we have to define the different OAuth URLs manually. When you look at my URLs they look a bit strange...

To get around the SSL issue I set up a reverse proxy in Apache which handles the SSL, so anytime the database does a call to http://apexrnd.localdomain it goes through the reverse proxy.
The reverse proxy in Apache is configured like this:

Note that in Oracle DB 12.2 and above the SSL bug is not there, so you don't need to do the above. I've been using the technique many times before if I don't want to deal with the SSL certificates and configuring the Oracle wallet. Adrian did a post about APEX Social Sign-In without a wallet, which might be of interest if you are on Oracle XE for example.

So what else is happening in the authentication scheme? You have to give the scope of what you want to get back from Facebook. In our case, we use the email as username and for additional attributes, we also want to know the first name, last name and the picture. It's really important you set those additional attributes, otherwise, APEX won't pass the full JSON through and takes a shortcut as it just needs the email.

The User info Endpoint URL is special:

Special thanks to Christian of the APEX Dev team, without his help, I wouldn't have figured that one out. Thanks again, Christian!

The next big bit is the post_authenticate procedure which contains the logic to map the Facebook user to the World Cup app user. If it finds the user, it will set some application items again, just like in the custom authentication, but if it doesn't find the user (the first time somebody connects through Facebook), it will create a World Cup user. The most important part of that logic is the part to get the name and picture. Here we parse the JSON the authentication scheme holds in memory.


And then the final bit you have to be careful with, that in the authentication scheme "Switch in Session" is set to "Enabled". This setting is the magic bit to have your APEX application multiple authentication schemes and be able to use one or the other.

Google Authentication Scheme

The Google authentication is simpler than the Facebook one, as we don't have to do the workaround for the certificate as Oracle understands the Google certificate. So here I use the standard APEX 18.1 feature to authenticate against Google. The username attribute is again the email, and the "additional user attribute" is "profile" as that holds the name and picture of the person.

The rest of the authentication scheme is very similar to the one of Facebook. Again don't forget to switch in session to enable.

Login buttons

To call the different authentication schemes on our login page we included different buttons:

The Login button is a normal Submit and will do the Custom Authentication as that is the default authentication (see - Current in Shared Components > Authentication Schemes).

The Facebook button has a Request defined in the link: APEX_AUTHENTICATION=FACEBOOK, this is the way that APEX let you switch authentication schemes on the fly. Very cool! :)

The Google button is similar, but then the request is APEX_AUTHENTICATION=GOOGLE
(note the name after the equal sign needs to be the same as your authentication scheme)

I hope by me showing how the Social Authentication of Oracle APEX 18.1 was implemented in the World Cup 2018 Challenge, it will help you to do the same in your own APEX application.

I really love this new feature of APEX 18.1. The implementation is very elegant, user-friendly and flexible enough to handle most of the OAuth2 authentications out there. Note that Facebook and Google upgrade their APIs to get user info, so depending on when you read this, things might have changed. Facebook is typically backward compatible for a long time, but know that the current implementation in APEX is for API v2.10 and the default Facebook authentication is v3.0. As far as I experienced, the user info didn't change between the API versions. I'll do another blog post how you can debug your authentication as it might help you get other info than the one I got for the World Cup app. Feel free to add a comment if you have any question.

Tuesday, June 05, 2018

The World Cup 2018 Challenge is live... An app created 12 years ago to showcase the awesome Oracle APEX

Since 2006 it's a tradition... every two years we launch a site where you can bet on the games of the World Cup (or Euro Cup). This year you find the app at

You can read more about the history and see how things look like over time, or you can look on this blog at other posts in the different years.

The initial goal of the app was to showcase what you can do with Oracle Application Express (APEX). Many companies have Excel sheets where they keep the scores of the games and keep some kind of ranking for their employees. When I saw in 2006 that Excel sheet, I thought, oh well, I can do this in APEX, and it would give us way more benefits... results straight away, no sending of Excel sheets or merging data, much more attractive design with APEX etc. and from then on this app lives its own life.

Every two years I updated the app with the latest and greatest of Oracle APEX at that time.

Today the site is built in Oracle APEX 18.1 and it showcases some of the new features.
The look and feel is completely upgraded. Instead of a custom theme, the site is now using Universal Theme. You might think, it doesn't look like a typical APEX app, but it is! Just some minimal changes in CSS and a background image makes the difference.

The other big change is the Social Authentication, which is now using the built-in capabilities of APEX 18.1 instead of a custom authentication scheme I used the previous years. You can authenticate with Google, Facebook and with your own email (custom).

Some other changes came with JET charts and some smaller enhancements that came with APEX 5.1 and 18.1.

Some people asked me how certain features were done, so I'll do some separate blog posts about how Universal Theme was adapted on the landing page and how Social Authentication was included and what issues we had along the line. If you wonder how anything else was done, I'm happy to do some more posts to explain.

Finally, I would like to thank a few people who helped to make the site ready for this year: Erik, Eduardo, Miguel, Diego, Galan, and Theo, thanks so much!

Wednesday, May 30, 2018

Safely Upgrading to Oracle APEX 18.1

Oracle Application Express (APEX) 18.1 has been out now for a couple of days.

I typically don't wait long before doing the upgrade, as with every new release you get many new features I want to use. Also if you want to stay on top of the game, you just want to move as fast as you can. I typically start testing the Early Adopter releases and then when gets updated, I do more testing, but having it on your own system with applications that are used day-in-day-out is a different level.

So I thought to share how we update our environment in a safe way.

The first thing we do is put our maintenance pages on. We use an Apache Reverse Proxy in front of Apache Tomcat with ORDS which is connected to the Database. By specifying some ErrorDocuments the maintenance pages are being used the moment there's an error.

For example, you can add this to your httpd.conf:

ErrorDocument 404
ErrorDocument 500
ErrorDocument 503

When you update APEX you don't want any incoming connections, so we stop Apache Tomcat with ORDS. At that moment the Reverse Proxy gets an error and the ErrorDocument kicks in and serves the Maintenance page. This way if people want to use the system, they know we are working on it.

We use Oracle Database 12c container database and pluggable databases. We want to run different versions of APEX next to each other because we have to test APEX Office Print against all APEX releases. Our customers use different releases of Oracle APEX too, so when we do custom development we have to stick to their version, so we really need all supported APEX versions somewhere.

Our setup was like this before the APEX 18.1 upgrade:
- CDB: cdb
- PDB with APEX 4.2: apex42_pdb
- PDB with APEX 5.0: apex50_pdb
- PDB with APEX 5.1 (main - our most used one): apex_pdb

With every new major release of APEX we clone our main PDB and give it the name of the APEX release, so we keep the APEX release we are on.

The steps to clone a pluggable database in Oracle DB 12.1 (SQL*Plus or SQLcl):

alter pluggable database apex_pdb close immediate; 
alter pluggable database apex_pdb open read only; 
create pluggable database APEX51_PDB from APEX_PDB file_name_convert=('/u01/app/oracle/oradata/cdb/APEX_PDB/','/u01/app/oracle/oradata/cdb/APEX51_PDB/') PATH_PREFIX='/u01/app/oracle/oradata/cdb/APEX51_PDB'; 
alter pluggable database apex51_pdb open; 
alter pluggable database apex_pdb close immediate; 
alter pluggable database apex_pdb open;

After the above we have a situation like this:
- CDB: cdb
- PDB with APEX 4.2: apex42_pdb
- PDB with APEX 5.0: apex50_pdb
- PDB with APEX 5.1: apex51_pdb
- PDB with APEX 5.1: apex_pdb  - will be upgraded to APEX 18.1 (main - our most used one)

Note: if you use Transparent Data Encryption (TDE) you have to perform some additional steps.

The installation of APEX 18.1 on the database side are basically 5 steps:
1) download the software from OTN
2) unzip in /tmp folder and cd into the /tmp/apex directory
3) run SQLcl or SQLPlus as sys as sysdba and connect to the apex_pdb container
alter session set container=APEX_PDB;
4) run the apexins command

In my environment the script took about 23 minutes to complete:

Note: the APEX 18.1 scripts are in 3 phases and the wizard shows information and timings for all phases and at the end also a global timing for the whole. If you want to have less downtime you can run the phases separately - see the doc Maximizing Uptime During an Application Express Upgrade

5) run the apex_rest_config command

The pluggable database is ready now and contains APEX 18.1.

During the APEX upgrade and as we already have downtime, we typically make use of that time to upgrade the other components in a typical Oracle APEX stack, namely the web server (e.g. Apache Tomcat) and ORDS (Oracle REST Data Services). Another advantage of going with a new version of your middleware is that you have your working Apache Tomcat and ORDS untouched, so in case you have to rollback there's nothing to do. Note that you can prepare most of the following commands beforehand.

Upgrading the Application (web) Server:

Unzip in your folder of choice.
That is basically all you have to do (on Linux) :)

Unzip in your folder of choice and cd into it.
Run: java -jar ords.war install advanced
and follow the wizard to install ORDS in APEX_PDB
* make sure you use different config dirs for ORDS in order to run multiple versions of ORDS and APEX

Once done, copy the ords.war into /apache-tomcat-version/webapps
Next copy the images folder of the apex directory to /apache-tomcat-version/webapps:
cp -R /tmp/apex/images /apache-tomcat-version/webapps/i

Start Apache Tomcat:
cd bin 

Restart your Apache Reverse Proxy (and optionally take out the ErrorDocuments)
/sbin/service httpd graceful

It sometimes happens to me that APEX isn't working the first time when I run it.
Then I debug the connection and check the logs of the web server.

Another thing that often helps, is running ORDS in standalone mode as it will give me clear messages. e.g.

WARNING: *** jdbc.MaxLimit in configuration |apex|| is using a value of 10, this setting may not be sized adequately for a production environment ***
WARNING: *** jdbc.InitialLimit in configuration |apex|| is using a value of 3, this setting may not be sized adequately for a production environment ***
WARNING: The pool named: |apex|al| is invalid and will be ignored: The username or password for the connection pool named apex_al, are invalid, expired, or the account is locked
WARNING: The pool named: |apex|rt| is invalid and will be ignored: The username or password for the connection pool named apex_rt, are invalid, expired, or the account is locked

The above warning remembers me to change some parameters of ORDS. Or I could look-up my previous configuration and copy those parameters. The above warning also indicates our APEX_LISTENER user can't connect (apex_al), so we need to fix that by specifying the correct password. For example, for apex_rt I forgot which user it was, but it's easy to find by navigating to the ords config folder and view the apex_rt file. It will tell the user in the file.

Now we should have APEX 18.1 up-and-running :)

We also want to access the previous versions of APEX. So I copy the older ordsxx.war files to the new web server, but I name those ords51.war, ords50.war, so the URL I access to the different APEX versions becomes or is always the latest version of APEX. 
The images folder of the older APEX version (5.1) we map to /i51/ (instead of /i/ as that is of APEX 18.1 now). In order to have a different image folder you need to run in apex51_pdb following sql:
SQL> @\utilities\reset_image_prefix.sql

We upgraded our systems this weekend, the second day after 18.1 was released. We followed more or less the above procedure and things went fine. Make sure to test your own apps first before doing the upgrade. Most of our apps were running just fine, but for some, we had to replace some older plugins with new versions or remove the plugins and replace by built-in functionality.

Note: there are many different ways of updating your system. It comes down to see what works for you. What I share works for us, but for example, if you can't afford downtime you probably want to work with standby databases and load balancers. Or if you work with virtual machines or Docker, it might be useful to clone the machine and test things on the entire machine first.

Tuesday, March 13, 2018

My top 3 to gather user feedback in an Oracle APEX app

In every Oracle APEX application we create, we add a feedback mechanism. We're not only doing this during the development phase, but we also leave it enabled in production.

We want to give the users the ability to give feedback to the team in a structured way. It should not be an hassle to give feedback, one click for the user where he can say what it's going; if he likes it, if something can be improved, if there's a bug... but at the same time we want as much info as possible at the time the user enters this feedback. What app are they in, which page are they on, what session information was there...

Most of the users really like to be heard and the feedback mechanism in our apps helped us a lot to engage with our customers.

Here're my top 3 ways of providing such feedback mechanism in an Oracle APEX app:

Feedback link and Team Development (pre APEX 18.1)

This method I started to use with Oracle APEX 4.2 and beyond. I blogged about using this feature here and here before.

In APEX you can create a new page; called the Feedback page. It will not only create a page, but also a navigation bar entry. Some screenshots of the wizard:

Which will result in:

I prefere to use the Modal dialog option as it feels more integrated in the app. This page is a normal APEX page, so you can further customise. For example you can make the Application and Page fields hidden.

The feedback that is entered is stored in Team Development - an area in APEX where you can do your project management.

Feedback feature in Blueprint and the new APEX 18.1 app creation wizard

In Oracle APEX 18.1 the application wizard got an overhaul and is now inline with the blueprint feature which was enabled previously on

Enabling feedback is a matter of ticking a checkbox... and the result looks awesome to me :)

The user can share his feeling by selecting a smily, enter some feedback and include an attachment. When you use APEX 18.1, you will see the nice floating label (label inside box) for the feedback text item. Also when you submit feedback you get a nicer message that the feedback was submitted, instead that the dialog will automatically disappear as with the standard feedback page in 5.1 and earlier.

In Blueprint on and in APEX 5.2 EA1 (which became now APEX 18.1 EA2) the feedback was stored in its own table, but since APEX 18.1 EA2 the feedback is stored again in Team Development. In the Administration section there are a couple of screens that query the apex_team_feedback view. It looks like many of the Blueprint features that were showcased before, became native APEX features (e.g. email framework, feedback).

When clicking on User Feedback and the pencil you see the details for every entry.

You can enter a response which will update Team Development by using the APEX API  apex_util.reply_to_feedback.

What I find interesting is that APEX 18.1 made a change in their pages; it catalogs the pages now as Component or Feature. A feature contains more than just a page, so the Feedback page became the Feedback feature and will create more pages than just the feedback page itself. Just like if you click the box with Blueprint, it can add the administration section and navigation bar entry.

The feedback mechanism we knew in APEX 5.1 and before, got a nice update in Oracle APEX 18.1, it might be worth doing an upgrade once 18.1 hits production.
One nice addition would be the ability to add a screenshot and annotate the screen like Martin and I build a long time ago. It would save the user creating a screenshot and uploading the file.

Feedback with REST API

If you are using another issue or ticketing system it might be worthwhile to gather the feedback there. You can still create the feedback page, but add some additional processes (or replace the team development process) so the feedback is stored in your favourite tracking system like Bugzilla, Jira, Redmine and others. In the next section I'll show how to integrate with two issue tracking systems we use.

Oracle Developer Cloud Issues

In the projects where we use Oracle Exadata Express, we use Oracle Developer Cloud service, which you get with your Exadata Express account to manage our project.

The nice thing is that you have a Git repo and Issues all available and all the other things around to mange and streamline your project. In the feedback page I added a call to a PL/SQL procedure, so an issue is created whenever feedback is given.

The PL/SQL package I wrote to create an Oracle Developer Cloud issue:

Bitbucket Git Issues

When we started in 2015 with the development of APEX Office Print, we used Team Development in Oracle APEX to manage the development and for version control we used Bitbucket (Git repo). We use different technologies like PL/SQL, APEX, Node.js, Markdown, CSS and HTML. A bit later we decided to use the issues in Git for our node.js code as it made it easier to track an issue/feature and a certain commit (we enter the issue number when we commit).

Bitbucket has also some nice features to integrate Trello boards and Bitbucket cards.

At one stage I wanted to add all our Team Development features as Bitbucket issues, so I wrote a small script that calls the REST API and creates the issues for you:

Nothing stops you to add a process on your Feedback page in Oracle APEX to create a Bitbucket issue automatically. If you copy everything inside the for loop, you are golden.

I hope this post helps you to get user feedback... and don't hesitate to put in the comments what you do to engage with your users.

Tuesday, January 02, 2018

Quick SQL: from Packaged App to built-in feature in Oracle APEX 5.2

I blogged about Quick SQL already a few times as I saw not many developers knew about it.

In Oracle APEX 5.1 you can install Quick SQL by going to the Packaged Apps section and install it from there:

I really love Quick SQL as it allows me to build my data model very fast, but also shows me the structure of the tables in a very efficient way. That is why I created a script that can reverse engineer existing tables into the Quick SQL format.

From Oracle APEX 5.2 onwards you won't find Quick SQL in the packaged app section anymore... but no worries, it's not gone, it's now built-in the APEX framework itself :)

Go to SQL Workshop - SQL Scripts:

Hit the Quick SQL button:

Here you have Quick SQL :)

You can run your script after you save, straight from this interface.

Note: the screenshots are taken from Oracle APEX 5.2 Early Adopter, so things might change in the final release of APEX 5.2.

Thursday, December 21, 2017

Visual Studio Code Extensions I use

In my post List of the tools I use and why I use them I already mentioned I use Visual Studio Code as my main editor. Before I used different editors, but VSC replaced them all (core + extensions), so it's easier for me to just use one editor. Next to that, VSC is controlled by a company, gets monthly updates and there's a huge community behind it that provide extensions.

Here's a screenshot of the updates in version 1.19

These are the extensions I've installed:

If you want to search for those extensions this list might be easier:
  • DavidAnson.vscode-markdownlint
  • DotJoshJohnson.xml
  • HookyQR.minify
  • PKief.material-icon-theme
  • PeterJausovec.vscode-docker
  • Shan.code-settings-sync
  • alefragnani.project-manager
  • anseki.vscode-color
  • apng.orclapex-autocomplete
  • buianhthang.xml2json
  • christian-kohler.npm-intellisense
  • christian-kohler.path-intellisense
  • dbaeumer.vscode-eslint
  • donjayamanne.githistory
  • eamodio.gitlens
  • eg2.vscode-npm-script
  • formulahendry.code-runner
  • gerane.Theme-Blackboard
  • kisstkondoros.vscode-codemetrics
  • mdickin.markdown-shortcuts
  • ms-vscode.Theme-MarkdownKit
  • ms-vscode.wordcount
  • msjsdiag.debugger-for-chrome
  • nodesource.vscode-for-node-js-development-pack
  • rafaelmaiolla.remote-vscode
  • robertohuertasm.vscode-icons
  • streetsidesoftware.code-spell-checker
  • wix.vscode-import-cost
  • xyz.plsql-language
If you install the Shell Command Line of VSC you can get the list of extensions by typing: code --list-extensions

In the Oracle APEX community many people are using Visual Studio Code, you can read a nice article of Morten; Using VS Code for PL/SQL development and Christope; Compile PL/SQL with VS Code using SSH. Adrian also created a nice extension for Oracle APEX. I highlighted the two extensions above.

Other editors that are often being used by Oracle APEX developers are Sublime Text and Atom, which have similar features than VSC, so if you read something nice that those editors can do, you can do it in Visual Studio Code most likely too e.g. Jorge's excellent post about Multi-Cursor Editing or Martin's truth about developing with Atom.

Just as a reminder for myself, As there were too many extensions to fit on one screen, I took two screenshots and glued the files together with following command:
convert -append vscode_1.png vscode_2.png vscode_extensions.png
If you wanted to glue them horizontally, you can use +append (I'm on a Mac).

Friday, December 08, 2017

Reverse engineer existing Oracle tables to Quick SQL

If you didn't hear about Oracle Quick SQL, it's time to read about it as it's something you have without knowing (it's a packaged app in Oracle APEX) and I believe you should start using :)

Quick SQL enables you to rapidly design and prototype data models using a markdown-like shorthand syntax that expands to standards-based Oracle SQL. You can easily create master detail relationships, check constraints, and even generate sample data.

In my blog post Create the Oracle database objects I go over the history how I created database objects and why I think Quick SQL is great and why I use it.

I guess most people typically use Quick SQL at the start of a new project, as it's the quickest way to create your data model and Oracle database objects. That is my primary use case too, but I started to use Quick SQL even on projects where database objects already exist.

In the project I'm currently involved in, the datamodel was generated by another tool, but as we iterate through the project, tables change, columns get renamed and added, row version were requested, triggers need to be made Oracle APEX aware...

Now we could do those changes manually, but I thought it made much more sense to create the data model in Quick SQL and use the features that come with Quick SQL. By clicking a checkbox we can include a Row version, Quick SQL generates the triggers automatically in an APEX aware form, we can generate as much sample data as we want by adding /insert and we can use all the other features that come with Quick SQL. For example when you want to include a history table in the future it's just another checkbox to click.

It's also easy to check-in the Quick SQL script into source control, together with the generated DDL.
If changes need to be done, we can adapt in Quick SQL and generate the DDL again and we see the changes immediately. It would be nice if Quick SQL could generate the ALTER statements too, but that's not the case yet. But it's easy enough to see the changes that were done by comparing the scripts in source control.

If you also want to reverse engineer an existing model into Quick SQL, here's a script that gives you a head start generating the markdown style format.

I tried the script on the Quick SQL data model itself - the result you see below:

Hopefully you see the benefit of using Quick SQL in existing projects too and the script helps you get there. Also Quick SQL gets frequent updates - in the upcoming release (17.3.4), which is already online, you can add a Security Group ID to every table (to make your app multi-tenant) and you can rename the audit columns to your own naming conventions.