Dimitri Gielis

Subscribe to Dimitri Gielis feed
I created this Blog to share my knowledge especially in Oracle Application Express (APEX) and my feelings ...
Updated: 39 min 45 sec ago

Error!?! What's going in APEX? The easiest way to Debug and Trace an Oracle APEX session

Wed, 2018-06-20 13:55
There are some days you just can't explain the behaviour of the APEX Builder or your own APEX Application. Or you recognize this sentence of your end-user? "Hey, it doesn't work..."

In Oracle APEX 5.1 and 18.1, here's how you start to see in the land of the blinds :)

Logged in as a developer in APEX, go to Monitor Activity:


 From there go to Active Sessions:



You will see all active sessions at that moment. Looking at the Session Id or Owner (User) you can identify the session easily:


Clicking on the session id shows the details: which page views have been done, which calls, the session state information and the browser they are using.

But even more interesting, you can set the Debug Level for that session :)


When the user requests a new page or action, you see a Debug ID of that request.


Clicking on the Debug ID, you see straight away all the debug info and hopefully it gives you more insight why something is not behaving as expected.



A real use case: custom APEX app

I had a real strange issue which I couldn't explain at first... an app that was running for several years suddenly didn't show info in a classic report, it got "no data found". When logging out and back in, it would show the data in the report just fine. The user said it was not consistent, sometimes it works, sometimes not... even worse, I couldn't reproduce the issue. So I told her to call me whenever it happened again.
One day she calls, so I followed the above to set debug on for her session and then I saw it... the issue was due to pagination. In a previous record she had paginated to the "second page", but for the current record there was no "second page". With the debug information I could see exactly why it was behaving like that... APEX rewrote the query rows > :first_row, which was set to 16, but for that specific record there were not more than 16 records, so it would show no data found.
Once I figured that out, I could quickly fix the issue by Resetting Pagination on opening of the page.

Debug Levels

You can set different Debug Levels. Level 9 (= APEX Trace) gives you most info whereas debug level 1, only shows the errors, but not much other info. I typically go with APEX Trace (level 9).

The different debug levels with the description:


Trace Mode

In case you want to go a step futher you can also set Trace Mode to SQL Trace.


This will do behind the scenes: alter session set events '10046 trace name context forever, level 12’;
To find out where the trace file is stored, go to SQL Workshop > SQL Scripts and run

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';

It will return the path of the trace file. When looking into that directory you want to search for the filename which contains the APEX session id (2644211946422) and time you ran the trace.


In Oracle SQL Developer you can then look at those trace files a bit more easily. You can also use TKPROF or other tools.


When I really have performance issues and I need to investigate further, I like to use Method R Workbench. The Profiler interpretes the trace file(s) and gives an explanation what's going on.


And with the different tools on the left, you can drill down in the files.


I'm definitely not a specialist in reading those trace files, but the above tools really help me understanding them. When I'm really stuck I contact Cary Millsap - or I call him Mr Trace - he's the father of those tools and knows trace files inside out :)

A second use case: APEX Builder

I was testing our APEX Office Print plugin in APEX 18.1 and for some reason APEX was behaving differently than earlier versions, but I didn't understand why. I followed the above method again to turn debug and trace on for my own session - so even when you are in the APEX Builder you can see what APEX is doing behind the scenes.


Debugging and Tracing made easy

I hope by this post you see the light when you are in the dark. Let the force be with you :)

Categories: Development

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

Wed, 2018-06-06 15:37
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...

Facebook

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.


Google

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:
http://apexrnd.localdomain/graph.facebook.com/v2.10/me?fields=#USER_ATTRIBUTES#&access_token=#ACCESS_TOKEN#

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.

apex_json.get_varchar2('first_name')
apex_json.get_varchar2('last_name')
apex_json.get_varchar2('picture.data.url')


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.
Categories: Development

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

Tue, 2018-06-05 10:39

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 https://www.wc2018challenge.com

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!
Categories: Development

Safely Upgrading to Oracle APEX 18.1

Wed, 2018-05-30 05:37
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 apex.oracle.com 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 https://s3.amazonaws.com/apexRnD/website/maintenance.html
ErrorDocument 500 https://s3.amazonaws.com/apexRnD/website/maintenance.html
ErrorDocument 503 https://s3.amazonaws.com/apexRnD/website/maintenance.html


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
@apexins SYSAUX SYSAUX TEMP /i/

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
@apex_rest_config.sql

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 
./startup.sh

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 https://www.apexrnd.be/ords50/ or https://www.apexrnd.be/ords51/
https://www.apexrnd.be/ords/ 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.

Categories: Development

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

Tue, 2018-03-13 10:21
.gist-file .gist-data {max-height: 450px;}
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 apex.oracle.com.


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 apex.oracle.com 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.
Categories: Development

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

Tue, 2018-01-02 04:26
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.
Categories: Development

Visual Studio Code Extensions I use

Thu, 2017-12-21 07:36
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.auto-close-tag
  • formulahendry.code-runner
  • gerane.Theme-Blackboard
  • humao.rest-client
  • johnpapa.azure-functions-tools
  • kisstkondoros.vscode-codemetrics
  • mdickin.markdown-shortcuts
  • ms-vscode.azure-account
  • 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).

Categories: Development

Reverse engineer existing Oracle tables to Quick SQL

Fri, 2017-12-08 13:21
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.
Categories: Development

Meet me in Australia and New Zealand at the OTN Days 2017

Tue, 2017-11-14 08:32
Tonight I'll start my trip from Belgium to Australia and New Zealand. Although we have a company in New Zealand and Australia, which Lino is managing, I've never been there myself. It has always been my dream to visit the other side of the earth, so I look forward to it :)

I'll present on how I build Oracle APEX apps today (and in the future) and how to make them available for others (cloud and others).

My schedule of the OTN Days 2017 (APAC Tour) looks like this:
If you are in one of those places, I would love to meet you and hear how you use Oracle APEX.
And I'm always up for showing you a live demo of APEX Office Print, you'll see our upcoming AOP 3.2 version as first! Just grab me by my arm and ask :)

In Perth there will also be a Q&A slot - so any Oracle APEX question can be asked there.
Categories: Development

APEX Office Print 3.1 released - support for Docker

Mon, 2017-10-16 04:18
Last week we release APEX Office Print (AOP) 3.1, our best release ever :)

AOP was already the easiest and most fully integrated printing and exporting solution for Oracle APEX, but with every new release we allow you to customise the way you use AOP a bit more and add more advanced functionalities.

As more and more bigger companies are using AOP, we focussed in this release more on enterprise features, for example, native HTTPS support, end-to-end and customisable debugging, a new queuing system for large amounts of prints and overall performance enhancements and general improvements.
You can read more about this release in our release history.

One other addition I want to highlight is the ability to run AOP in a Docker configuration.
The Docker image is available for our Gold and Enterprise license.

Docker is the world’s leading software container platform. If this concept is new for you, you can read more at What is Docker?



In the previous days Martin Giffy D'Souza blogged about How to Setup Oracle DB 12.2 Docker Container and Docker Oracle and APEX and Roel Hartman talked about Dockerize your APEX development environment. Those are some excellent posts how to get started with Docker in an Oracle Database and APEX context.

The most important reason for us to make an APEX Office Print docker image available was to ease the installation of multiple AOP instances even more and give the possibility to scale AOP in an enterprise way.

Here's a video how you are up and running with our AOP docker image in less than a minute:



You also find the detailed steps in the AOP documentation.

Juergen Schuster and Martin Giffy D'Souza did a podcast with me end of August, where I talk a bit about AOP and our development too.

If you are not yet on APEX Office Print 3.1, go and download the latest version, even when you are not enterprise, it's worthwhile the upgrade. We updated our AOP Sample Application with some new examples too.


Happy printing and exporting from Oracle APEX with AOP :)

Categories: Development

Talking about APEX Reporting and AOP @ Montreal Oracle Dev Day 2017

Wed, 2017-10-11 01:00
For those in Montreal and the surrounding area I encourage you to come out to the Montreal Oracle Dev Day on October 25th (8:30-4:30 at Centre for Sustainable Development).

Here’s a summary agenda of the presentations with the full agenda here:
Aside from the presentations you will have plenty of opportunity to network and share your Oracle development experiences. All speakers will be available all day so feel free to bring your APEX questions!

You can register now online.

As I'm not that much in this part of the world it would be great to meet in person. I would love to hear your thoughts on APEX Office Print (AOP) too.  If you have any questions, feedback or just want to talk how to use AOP in your environment, don't hesitate to come up to me. I'm more than happy to talk to you :)

Categories: Development

JavaScript, Node.js, JET and APEX day, 7-NOV-2017, Belgium

Mon, 2017-10-09 04:07
With great pleasure I can announce we have a special APEX Meetup in Belgium on Tuesday, November 7, 2017.


Dan McGhan is our special guest and will present on different technologies that will enrich your life as an Oracle APEX Developer too.

Unlike our other APEX Meetups, we decided to do a full day event, with food and drinks :) The event is sponsored by APEX R&D, iAdvise, ODTUG and Oracle.

Agenda:
• 8.30 am - 9 am: welcome coffee and registration
• 9 am - 10 am: Setup help for those that couldn't do it before (VM with Oracle & Node.js - optional)
• 10 am -11 am: Intro to JavaScript for PL/SQL and APEX Developers (language mechanics and DOM basics)
• 11 am -12 pm: Intermediate JavaScript for PL/SQL and APEX Developers (Ajax, promises, etc.)
• 12 pm - 1 pm: Lunch
• 1 pm - 2 pm: Intro to Node.js
• 2 pm - 3 pm: RESTful APIs with Node.js
• Coffee Break
• 3.30 pm - 4.30 pm: Intro to Oracle JET
• 4.30 pm - 5.30 pm: Using JET and Node.js with APEX
• Closing Drink

We have a capacity of 75 people, so if you didn't register yet, you find the link here.
Categories: Development

Choosing the right template and print to PDF from your Oracle APEX application

Fri, 2017-10-06 07:19
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

To motivate players to exercise more, I thought it would be a good idea to give the players a diploma (certificate) when they complete the multiplication table. They will be able to download or print this certificate if they have a specific score. I designed it that we have one official diploma of mtable.online, but there's also the ability so teachers or people at home can print their own certificate in the template or colours they want.

Creating the diploma

Creating a certificate in Microsoft Office is not that hard, in fact online you find many certificate templates you can just download. Mostly are in Word or Powerpoint format.



In our multiplication table project we have the concept of teams. One or more players can be part of one or more teams. A team could be for example a classroom and the students are the players within that team. I want the team manager be able to upload a template for the certificate (diploma) of the team.

How integrating those certificates in Oracle Application Express (APEX)?

With APEX Office Print, this becomes very easy!


APEX Office Print (AOP) is a print server for Oracle Application Express (APEX) which allows you to define your template in Word, Excel, Powerpoint, HTML or Markdown and merges it with your data in the Oracle database. As output you can select PDF, Word, Excel, Powerpoint, HTML or Markdown. APEX Office Print comes with an APEX Plug-in and PL/SQL API that makes it very easy to select the template and the data in any Oracle APEX application. AOP is also smart and understands Oracle APEX meta-data, so for example printing one or more Interactive Reports or Grids to Excel or PDF is done in a breeze. Till date, APEX Office Print (AOP) is the most integrated, easiest and flexible printing and data exporting solution for Oracle Application Express (APEX).
Full disclosure, my company APEX R&D is the maker of AOP, so I might be a bit biased, but you can check this youtube video where different APEX printing solutions are discussed.

Installation of the AOP plugin

When you go to the APEX Office Print website, you can download the cloud or on-premise version and try it out for 100 days. You just have to click sign up, provide an email go to Downloads and click the Cloud package.


 Extract the zip file that was downloaded and locate the db folder and plugin folder.



Import the APEX Plug-in by going into your APEX app, to Shared Components, Plug-ins, hit the import button and choose the dynamic_action_plugin_be_apexrnd_aop_da_51.sql file. Depending the version of APEX, you might need to choose the _50.sql file (in case of APEX 5.0). Follow the wizard to import the plug-in.



The plug-in calls a package, so there's one more step to do in order to make the plug-in work. Go to SQL Workshop > SQL Scripts and hit the Upload button and select the file aop_db_pkg.sql from the db folder.



Next, click on the Run button to run the script, which will install the AOP_API3_PKG PL/SQL package.

That's it - you successfully installed the AOP plug-in and are now ready to use it in your application.

Note: in the above example we installed the Dynamic Action plug-in. AOP also comes with a process type plugin, in case you prefer a process over a dynamic action.


Calling the AOP plugin from your page

Create a new dynamic action, for example Click on a button and as Action select the APEX Office Print (AOP) Plug-in:



The plug-in is very flexible and has many options, yet it's so easy to use. You first tell the plugin where your template is; in Static Application Files (Shared Components), in a table (define your SQL), a url, the filesystem, ... you just select where and tell it which one.

Next you have to tell which data you want to use, you can define SQL, PL/SQL, URL or even the static id of the region. AOP is so smart it will understand if you put the static id of an Interactive Report or Grid, a Classic Report or even a JET or other chart. Behind the scenes AOP is reading the meta-data, so it will use whatever is behind that region as source. This is one of the most liked features by our customers and something no other printing solution offers.



The plug-in has build-in help and examples, so it's more easy to know how to use the plugin.

You can define some other settings (which items to submit in session state, some special report settings etc.) and finally the output you want; PDF, Word, Excel, you name it.

Print the diploma

When you go to mtable.online, you can view the highscores for all players, or the highscores within a team.



Based on some rules you might be able to print a diploma. The logic which defines if you are allowed to get a diploma I wrapped in a view:


Now the interesting thing is that we can define the selection of the template dynamically in the AOP plugin. So I've some PL/SQL code that returns the correct template. If a team is selected, we will use that template of the team, if it doesn't have one, or if you are looking at all players, we will use the default template defined by us. As described in the first paragraph, the owner of the team can define his own template, they don't need to contact us (the developers) anymore. The just create their template in Word, Excel or Powerpoint and upload it.

From the very start when developing AOP, this was a main goal; we want the business users be able to create their own template. As a developer we just provide them the data, how it looks like the business people can define. With other printing solutions, I had to spent hours and hours redefining the template in XSL-FO, iReport, or other tools, but with AOP, as a developer my job is done, the moment I write my query :)

Back to the diploma in mtable.online; as default the template I used was a Powerpoint I downloaded from the certificate templates site. The only thing I changed was adding the substitution variables that AOP would understand: {player_name}, {mi_ss}, {play_date} (those are the columns in my query - see further)


 For my team, I went with a Word Template:


 And my plug-in looks like this:


Whenever the diploma link is clicked in the report, the AOP Dynamic Actions kicks in and generates the PDF, based on the template from the team (Word) or the default (Powerpoint). That's it :)

APEX Office Print is even more used in the Multiplication Table project; namely when you go to the  details of an exercise, which are shown in an Interactive Grid, AOP exports this Interactive Grid to our own Excel template. I'll do a dedicated post on that as there're more interesting steps to talk about with adding buttons and custom dynamic actions to an Interactive Grid.

If you didn't try APEX Office Print 3.0 yet, you can sign up for a trial for free. In a few days we ar also releasing AOP 3.1, our best version ever :)
Categories: Development

Multiple APEX Workspaces in one Oracle Exadata Express Cloud account now possible

Sat, 2017-09-23 05:18
Last night my Oracle Exadata Express Cloud account was updated to APEX 5.1.2, but there was more updated once I looked closer into the dashboard.


Before we could already create multiple Oracle schemas, but from now on within one Oracle Exadata Express account you can create multiple APEX Workspaces.

Go to Instance Administration under the Application Express setting in Manage:


Clicking on Manage Workspaces > Create Workspace


Just follow the wizard; you can attach the workspace to an Oracle database schema and once finished you will see.


Now when you login again in your Oracle Exadata Express account and go to the APEX App Builder it will ask you which workspace you want to login to:


The first time it will give you a welcome message and asks to set a password


As it's an Oracle cloud account with identity management, I don't really need to set a password, I just need to confirm my profile


That's it! You are now in your new Oracle Application Express (APEX) Workspace.

It's very nice to see Oracle Exadata Express getting better with every maintenance release. Thanks Oracle :)

Update 23-SEP-2017: another new feature is that you can export your Oracle schemas and data to the Oracle Storage Cloud Service.

Go to Export to Cloud Storage:


Add your Oracle Cloud storage details and select the schemas to be exported:


Categories: Development

Adding the game, the importance of the APEX Advisor, a lot of JavaScript and adapting for mobile

Wed, 2017-09-06 13:48
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

Over a year ago I developed the game to practice the multiplications. I quickly created an Oracle APEX page, added a bunch of JavaScript and that was about it. The last weeks you could read I've redeveloped the entire app from the ground up but I still have to add the game to it.

I thought to just copy the page I developed before with all the JavaScript in the new app.

How do you copy a page from one Oracle APEX environment to another? 

Export the app from the first environment and import the app in the second environment. Once both apps are in the same workspace, you can copy a page from another app by going to the Create button:


Click the Page as Copy option and follow the wizard:



So I followed the wizard to copy the page to my new app.

Now the tricky bit was that I changed page numbers, so all my references (in JavaScript, Processes etc.) to page items were wrong. To quickly identify the issues I used the APEX Advisor.

The APEX Advisor you find in App Builder > Utilities : Advisor


The Advisor checks for best practices, errors, security issues etc. I really like the Advisor as it will give your application some kind of quality control. And it's very fast to identify issues and navigate to them. Here's a screenshot what the Advisor found:


I would always run the APEX Advisor, even when you start from a Blueprint application as you will most likely modify the app, add pages, make changes etc. and it's easy to forget for example to put an authorization scheme.

A few years ago I also wrote an extension to the APEX Advisor that would check for our own standards. You can query an Oracle APEX application with SQL, so it's easy to check if naming conventions where followed, if deployments went fine or anything else you find important. Here's a screenshot of some of the checks in one of the projects:


In the community some other people did something similar, for example Oliver Lemm wrote a Quality Assurance plugin.

So back to my application - I fixed those issues and was able to play the game :)
(screenshot from the browser)


To build the table/grid of the game I programmatically generate the APEX items by using the API apex_item.


All the rest is build in JavaScript. I'm not going to put all the source code in this blog post, but if you right click on the page where the game is, and you look at the source, you see all the JavaScript that is used.

Whenever the Start button is clicked, a Dynamic Action is fired which calls some other JavaScript functions. The fireworks and stopwatch are libraries I found on the internet which I integrated in the APEX app. If you have any question on the JavaScript side, feel free to ask in the comments section.

A few weeks ago I released the app to test. Some people sent me an email with their feedback, but many also used the build-in feedback mechanism that came with our Blueprint app.


I typically leave a feedback mechanism in every app I build (yes, even in production), it's a great way to communicate with your users.

The result of the feedback was, it didn't run great yet on mobile devices. Except from using Universal Theme, I didn't really pay attention to mobile devices, as I thought it would be too small anyway, but apparently people were trying it on there. So I decided to make sure it would work on at least a tablet, for example I saw a use case for myself to let my son play on my iPad.

In the next section I will go in a bit of detail of things that I had to adapt to make it more user friendly on a mobile device and some other things that might be interesting to know in other applications.

So here's how the app looks like on an iPad:


Compare this image with the image before taking in the browser and you will see some differences.

I added some JavaScript library to check if we run on a mobile device and when so, we add some extra CSS to make the table fit on the screen. The breadcrumb will always be shrunk and the padding is less. Here's the code:


When starting the game the keyboard comes out. One of the first things I had to do, was to make the number keyboard the default and not the normal keyboard with the letters. The way you do that is in your input text item, you define a pattern and give it a type of numeric (see the PL/SQL code where we do the call to apex_item).


The keyboard that slides out hides a part of the table, so it's not ideal. I've an external keyboard for my iPad, so hooking that up, makes it a really nice experience:


Another issue on the iDevice was that the fireworks at the end of the game didn't work well. So I decided to add a setting, so you can choose what you want to see at the end when you finish the game and when you run on an iDevice, the Fireworks is hidden.


The other settings are based on feedback. Some people don't want to play all numbers yet, so you can now pick the numbers. Some others wanted to see the time or countdown, and for some others they got stressed by it. So I decided to make it customizable per player.

Those preferences are all normal APEX items, but the difference is the way they get stored. I only use one field (preferences column in the mtl_player table) to store all preferences. The way I do that was inspired by the APEX Multi-language translate plugin (JTL Item) from my friend Jorge Rimblas, who stored all translations in one field by using JSON. So, all the settings of the game are stored in one JSON object.


On page load I use following JavaScript to set the items:


There's also a save button in the settings section. There's a straight forward dynamic action to do the update. But once saved I show a notification which is done by adding a call to the APEX JavaScript API:

apex.message.showPageSuccess("Player settings saved.")

This results in:


While you play the game on every switch of the number, the timing is saved by calling an AJAX Callback process by apex.server.process (see the source on the page when you play the game). For now I've done it this way, but maybe in the future I might cache the results and only do a callback at the end.



The last thing I want to cover is the overlay you get when you end the game. You have the fireworks, an image or a message as on the below screenshot.


The overlay is done by adding a div on the page and some CSS.



By default it has display set to none, but once the game is complete it's set by some JavaScript.


Hopefully this gives you more insight how the game was created and some things I cover are also useful in your own project.

If you want to play the game, surf to http://mtable.online.

Happy playing!

Categories: Development

Create a Custom Authentication and Authorization Scheme in Oracle APEX

Tue, 2017-08-29 13:32
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

Before when creating the APEX application based on a Blueprint, we used the standard APEX Authentication, which means the management of users is done in Oracle Application Express itself. As we created our own user table, we want to manage our users there and have some custom authentication scheme for our multiplication application.

In this post I will show you exactly how I built the custom authentication scheme for my Oracle APEX application you see at mtable.online.

I first decided which kind of login page I wanted. A few years ago I blogged about pimping the logging page, which is what I use on the APEX Office Print login page. This type of page is a more traditional way of building a login page in Oracle APEX. For the registration page and forgot password page we used the same techniques.


For the multiplication table project I decided to do it a bit different. I started from the landing page and build the login mechanism into that page. But also the register and forgot password are on the same page, so not like what we did for APEX Office Print, using different pages for forgot password, register and login.

Here's how my page looks like in the Oracle APEX App Builder:


There are a few regions to help with the layout (Top, Left, Right). In the Right region, there are 3 sub-regions: Login, Register, Forgot Password, which will show one at a time. Dynamic Actions (Show/Hide) control which region is seen.

From the front-end this is what it looks like.
When clicking a button an APEX process is being fired, but all the logic is defined in a package.
The package to handle the authentication I typically call [project trigram]_AUTH_PKG. It doesn't only contain the authentication logic, but also the registration, reset password and authorization logic.

The specifications looks like this:

.gist .blob-wrapper.data { max-height:600px; overflow:auto; } And the body like this:

I typically use dbms_crypto to generate (hash) the passwords, but as that package is not supported on Oracle Exadata Express at the time of writing, I use another SHA256 PL/SQL implementation.

I'm not going into too much detail on the logic in the PL/SQL package. I hope it's kinda self explanatory, but if you have any question, feel free to ask in the comments field.

Now we will focus on creating a Custom Authentication Scheme in APEX.

Go to App Builder > Shared Components > Authentication Schemes and hit the Create button to add a new one:


Enter the custom_authenticate procedure from the package we created earlier:


By default the new authentication scheme will be current, so make sure you have some data in your tables, otherwise you won't be able to login.

Next I typically add some post authentication to fill some Application Items.
Edit the Custom Authentication and add the code and post_auth as in this picture:


We have now made our application accessible to people by defining our own custom authentication scheme.

Next, we want to define which rights you have in the application. To do this, we will create two Authorization Schemes, one for a normal user and one for an administrator.

In our package we already included a function with the logic. Every user has a role defined to him, and depending the role, it's a normal user or an administrator. An administrator can do everything a normal user can do, but can also access the administrator section where we maintain our application.

Blueprint actually already created our Authorization scheme for administrators, but we will adapt it to use our package. Go to Shared Components > Authorization Schemes and modify like this:


I hope it gives you all the components to build your own custom authentication and authorization schemes.

I also recorded a video which goes in more detail on the entire process of signing up, forgetting password and logging in and the different authorization schemes and code being used.

Categories: Development

Build the Oracle APEX application: the framework

Thu, 2017-08-17 05:11
Over the years while developing Oracle APEX application, I noticed in every application I was following the same steps. It was a routine I followed over and over. For example: in every application I created a global page (page 0), enabled Feedback, ran the Advisor multiple times a week, used certain naming conventions etc.

A few years ago I wrote those steps in our APEX R&D development guide, so within our team we would be consistent. Just like the database best practices, I thought it would be a good idea to share the APEX best practices with you as well. There are about 9 pages, here's a screenshot of the first page:
I don't like too many rules, but a few are good to have, especially when you get new people on board or when the customer is working together with you. As I said in my previous post where I described our database guidelines, the guidelines in itself is not meant to be "this is best and you have to follow", it's more something to start from which you can adapt.

Based on those guidelines, we created a "starter application", so you get a head start in your project. The app in itself wasn't spectacular in itself, it contained the global page, feedback, some administrator pages which give statistics of your app, some application items, error handling package etc.

If you know what you have to build, it wouldn't even take that long to build it from scratch. I just don't like to do repetitive things, I far rather concentrate on the real solution, so anything that can help, I will embrace :) I also won't hesitate to take some components or solutions from other projects and reuse those. But just one piece of advice, always check if what you've been doing before, is still valid today and "the best" way of doing it -for your current problem-. For example if you developed something in APEX 4.2, maybe in APEX 5.x there's a build-in package to do that. Or the other way, that a feature of APEX became deprecated (e.g. apex_plsql_job) and you should use a database feature (dbms_scheduler).

In 2015 I also gave a presentation at some Oracle conferences how I developed in APEX 4.2 vs 5.0, which you find here.

So I could use my starter app for this project, but just as with Quick SQL (to create your database objects), there's a new feature in Oracle APEX 5.2 that will make the starter app irrelevant, so I will cover the new way of doing something in 2017 and further.

The new way of building an application, is to start from a "Blueprint". The concept is very similar to my workflow previously, but now it's built-in the APEX Builder and it's more powerful as it allows to customize the features you want to include in your app.

Blueprint is at the time of writing only available in Preview mode (since March 2017) through apex.oracle.com and will probably undergo some more changes. I see a lot of potential in this feature. It's not only the wizard you can use, rumors are you can also use JSON syntax to define your app.

Here's the video I recorded where I give a bit of background and you see me using Blueprint:



At the time of writing Blueprint isn't finished yet. Not sure what the end result will be, but although there's still a lot of room for improvement, it already looks spectacular. As you could see in the video, I will use it as my starting point for my multiplication table project. I've exported the app of apex.oracle.com and imported in my Oracle Exadata Express environment. Next I'll make changes to this app and customize further.

Blueprint is (or will become) the fastest and most low code way to build an Oracle APEX application.
Categories: Development

Set up domain and launch page

Mon, 2017-08-14 15:56
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017
Domain nameWhen you launch an app or service, you probably want a domain name. I register all my domain names with Godaddy. I'm a long time customer with them and I find them really reliable, not that expensive and they have good support. But I find it hard to come up with "the right" domain name. For example for our project I wrote on a piece of paper all different domain names: multiplicationtable.com, multiplicationstable.com, multiplications.edu, multiplicationtable.online, multiplicationtable.guru etc.

What domain would be perfect for our application? At one side you have to think about Google, a descriptive domain helps in your ranking, but on the other hand you want it to be easy to type and remember. Finally I decided to register mtable.online as my first choice multiplicationtable.com was taken. If you know a better domain that is still available, feel free to add in the comments field :)
Link domain name to Oracle APEX appOnce you have the domain name you want to link it to your Oracle APEX application.

In a future post I will cover Reverse Proxy and SSL, but for now, as I want you to see what is happening, I just configured in Godaddy to redirect to my Oracle Exadata Express url.


I don't want to let the world wait to know about our project, so I want to setup a landing or launch page. This page serves as a home while we are building our application. People can already register, so once we are live, we can let them know about it.
How do you build a landing or launch pageEither you have an idea about how you want the page to look like, or what I do as well, is look at other sites or templates created by others. For example for the APEX Office Print website we bought a template as starting point and integrated it in our APEX app.

I get often inspiration from following sites:
If you know some graphic design people, they can obviously help too :) It's easy(ier) to recognize good design, but it's hard(er) to create it yourself.

I like minimalist design or "less is more". If you look at the multiplication table project launch page, only the bare minimum is on the page: a title, a graphic, a text item to leave your email and a button (and a text to let everybody know it was built with Oracle APEX.


You find some more examples of minimalism here or just Google for it and you find plenty. Typically you will see a background picture and a text. If you find a template you like, you can look at the HTML and CSS and copy this in your own APEX page.

The font you use is really important and can make a huge difference. Google Fonts are a good starting place to pick a font. In our project I used the Raleway font.

In the last month (that I know) two other people showed how to build a landing page in APEX: Stefanie used a background picture and Richard used a video as background.
How long to build a landing pageWhen I look again at our launch page, I would probably tell you it would take me less than 30 minutes to build it in Oracle APEX. If you know what you will build, yes, but if I told you it took me well over 6 hours to build this landing page, would you believe me? It's the truth!


So where did I spend all my time then?

I first started to search for a design. I didn't really want to copy something from a previous project, but rather wanted to get a fresh new design, so I started to search, and search and search more. Finally I gave up on the idea and followed my own thoughts to do it very minimal and as close as possible to universal theme that comes with APEX.

So I added the regions, items and a button on the page:


This goes fast (if you know a bit of APEX). As I wanted the image on the left of the items and the items going down a bit, I used two regions next to each other.

I looked at the result and didn't like it, so I added some CSS and searched for another font. This was a lot of trial and error till I was happy with the result.

Once that was done, I looked how responsive it was. On a smaller screen, I didn't find it looked good enough, so I added a media query and custom CSS, so it would look better.


Oh, before I forget, whenever you build an app in Oracle APEX, include the plugin built with love using Oracle APEX.

Next up, I had to include a validation (to check if the email already exists), a process and a branch. Now here's a story too... I first started with adding a dynamic action on the Subscription button that would insert a record, but during testing I found I lost the value required and is email validation that you get for free when you submit your page, so I changed it to be a normal process.

Finally I changed on the page that duplicate submissions are not allowed.

So the end result is we have one very simple page in APEX, using standard components (regions, items, button), 1 plugin (built with APEX), Universal Theme and a bit of custom CSS.
Categories: Development

Create the Oracle database objects

Sat, 2017-08-12 13:49
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

In a previous post we defined the ERD of the multiplication table application we're going to build. Now I want to go into detail how I create the Oracle database objects like tables, foreign keys, sequences, triggers, etc.

Before I tell you what I do today, let me first walk you through my history so you understand why I'm using it.
Data Modeling ToolsData Modeling Tools allow you to visually create your tables, relationships, etc. If you work with Oracle for 10 or more years, you probably know Oracle Designer. This tool has been really popular, but today it's legacy and not maintained anymore by Oracle. Oracle moved forward with SQL Developer Data Modeler. Although I've been using those tools in the beginning, today I typically don't use them anymore, except for generating a visual diagram (ERD) of my tables, but that is now part of SQL Developer, so I don't have a data modeling tool installed anymore. The main reason for me was, it took me so much time to add the entities, that it was too slow for my process. As written earlier, I typically draw my entities on a whiteboard or piece of paper, after that I want to get going with real tables and data as fast as I can to verify the model.

If you have a big team and somebody is solely responsibility for modeling, I see a benefit of using a modeling tool or if you like the process of visually creating a model. There're probably more advantages using a modeling tool, so if you use it and like it, don't change because of this blog post, I just share how I typically do a project.
ManualSo if I didn't use a modeling tool, what did I do? I created the tables manually. I hear you think, yeah right, and that is faster? It was for me, because I had a certain workflow. I used strict naming conventions: 3 letters for the project, singular table names, meaningless id column in every table etc. Here's an overview screen:


You find the complete guide of my naming conventions here.
Those naming conventions were the base, but per project or customer we could adapt to their standards. The principle was that I created tables with only the relevant columns, a meaningful unique key and a comment.

Next I used a script that would generate all the primary keys and foreign key relationships, sequences, triggers, audit columns and everything else that I could automate for the particular project. You find the base of the script I used till two years ago here and a screenshot of a part of the script:


The only reason I could do this, was because I used strict naming conventions e.g. table_id column (fk) would be a reference to the id column of a table (pk). By doing this, I could really iterate fast on creating and adapting tables, it worked great for me.
The final step was to create a visual ERD of it in SQL Developer (or other tool) as it was easier to communicate with the team and we always include it as part of the documentation.

As I'm writing in the past, you probably figured that I stopped using this method. The reason is simple, because there came something I like even more :)

But before we move on, a final word on naming conventions; it's not which naming conventions you use that is important, it's more about being consistent within your project and make it easier for you to understand your model and have faster knowledge transfer. So whatever naming conventions you use is fine, there's not something like "this is the best" in my view.
Quick SQLSo now we come to today... this little tool is what I use to create a script for my database objects.

I can't explain Quick SQL better than what you find on the site:

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.
Quick SQL is a packaged app which comes with Oracle APEX, so you have it, without knowing :)

So back to our multiplication project; the first thing I did was installing the packaged app in our APEX workspace. Go to App Builder > Create > Packaged App > Quick SQL. Next run the app and this is the screen you will see:


Next you start typing your tables and columns and some additional syntax to specify constraints etc. The power of Quick SQL is that it not only generates the tables, but it has built-in naming conventions, generates indexes, constraints, triggers, views and even sample data.

Here's a video of me creating the script for our multiplication table project:



The next thing I do is generate with SQL Developer the ERD, so I visually see it. Just follow the wizard in SQL Developer you find in File - Data Modeler - Import - Data Dictionary:


While reviewing the visual ERD, I saw I made a small mistake in Quick SQL. I didn't specify timezone with local timestamp (tswltz), but just timezone with timestamp (tstz). In the application for my son I used a date for that column, that is why I called that column start_date and end_date, but the more logical name is start_time and end_time, so I changed that too. I want to use timestamp with local timezone as this app is probably going to be used around the world, so it would be nice if you could see the time you actually played in your own timezone.

Here's the final version of the script in Quick SQL:


It's very easy to make changes in Quick SQL, and in settings I just included the drop statements and re-ran the entire script in SQL Dev and I was done :)

I really like Quick SQL and I hope it gets even more improved in the future. It would be really nice to version control the scripts and be able to generate the differences for the tables (alter statements instead of create statements) or do reverse engineering of a specific schema. Another improvement I hope to see in the future, is the ability to define unique constraints on multiple columns (or it might be already there, but that I don't know how to use it?).

The visual ERD is below:


In this post we went from the paper ERD to real Oracle objects. Now we are ready to build our app.
Categories: Development

Set up an APEX development environment

Thu, 2017-08-10 12:19
This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

A development environment can mean different things to different people. You can read the definitions on Wikipedia, TechTarget, Techopedia to name a few.

For me, it means on one side you need an environment where you can develop in. In case of an Oracle APEX project this means, you need at least an Oracle schema and an APEX workspace linked to the Oracle schema in which you can create your APEX application. Now if we take this a step further, it means you need an Oracle Database and APEX installed, which includes a webserver and ORDS (Oracle REST Data Services). And again one level higher it means you need a machine (that is most likely connected to the internet).

On the other side you have everything around it: some tools you use, something to plan and track the development, something to store your code (version control), something where you can collaborate with other team members. If we include this we talk more about software development in general which touches on application life cycle management (moving to Test, QA, Production).

Lets first focus om the first part; the infrastructure. Unless we get millions of concurrent users, I don't think our multiplication table project needs much infrastructure :)

I will walk over the different options we have when doing an APEX project.
On-premiseYou basically have your own machines and installed all software on there and you manage everything yourself. Many of my customers still have an on-premise infrastructure. Unless you see our laptops as on-premise, at my own company we never had on-premise, we've always been in the cloud. While running in the cloud you still have different options of the level of responsibility and flexibility you have yourself. We use cloud services from Amazon, Microsoft Azure, Digital Ocean and the Oracle Cloud. More on that further on.
apex.oracle.comThis is the fastest way to get started, we just sign up for an account on apex.oracle.com and get an APEX workspace and underlying Oracle schema. Apex.oracle.com also contains the latest version of Oracle Application Express (APEX) as the development team uses this service to roll-out and test new features and versions first. If you don't have an account yet, I recommend to create one. I've requested a workspace for this project too (takes less than 2 minutes), which you can see here:


Now we can use this service to develop and test, but we can't keep our application here forever, as we only have 25MB of space and you're not supposed to run production applications here. It's also not possible to connect to this service from SQL Developer or other tools, so it's a bit limiting. But in a future post "Build the Oracle APEX application: the framework" you will see why I still setup this workspace and I recommend everybody to have at least one workspace on apex.oracle.com :)
Oracle pre-built Developer VMAnother fast way of being up and running is to download the Oracle pre-built Developer VM for Virtual Box. Everything is already setup for us, but we would need to put it somewhere on a server where it can be accessible through the internet by more people. I typically don't use this solution to do my development, only to test something locally.
Free Oracle Database Cloud ServiceThis service from Oracle has been announced a few months ago, but is not yet available at the time of writing. The rumours are you get 1GB of data, have APEX 5.1 or higher, you're running in the Oracle Cloud and can run production applications there. This solution would have been ideal for our multiplication table project!
Cloud with OXARIf you're searching to build a low cost infrastructure based on Oracle XE (the free Oracle database), you really should use one of OraOpenSource projects, called OXAR (read "Oscar"). It sets up an entire machine by itself, it's completely scripted. The only thing you have to do is download the Oracle software (XE, ORDS, APEX), get a low cost virtual machine (for example at Digital Ocean or Amazon), clone the OXAR git repository on that machine, edit the config file to point to the downloaded files and run the build.sh script. That is it! Even the most popular print engine for Oracle APEX, APEX Office Print (AOP), comes installed with it :)

The biggest benefit of an Oracle XE infrastructure, it's a very cheap solution, the downside is that Oracle XE is still Oracle DB 11g with many restrictions, so we can't use some 12c features (like JSON in DB, ...). Rumours are there will be an Oracle XE 12c version coming out in the future. Maybe even more important to know; there's no Oracle support for this database. Although at first sight you might not need it, it also implies you can't download Oracle APEX patches, as you don't have a CSI number. If you have a CSI number for another infrastructure and downloaded the APEX patches, you can obviously patch your APEX in Oracle XE. If not you would need to download and reinstall the full version of APEX every time, which becomes time consuming.

So lets look at our multiplication table project, we can definitely use OXAR for this as it fits within the XE limits. I subscribed at Digital Ocean for a CentOS Droplet for 10 USD/month and ran OXAR on there. It took me about 1 hour to be up and running (mostly the OXAR script was running by itself).


DockerDocker has gained a lot of popularity in the last years (since 2013/2014). Docker is a tool designed to make it easier to create, deploy, and run applications by using containers. Oracle provides official Docker Images for many of their products, see also the Docker store. If you're new to Docker, you can read more here. I also like this article which covers the main benefits of Docker. You can also watch this video how to build and deploy an Oracle Database Docker Image to the Oracle Container Cloud Service. So far I've used Docker only for test instances.

I typically use the docker image of Daniel Hochleitner (aka Mr. APEX Plugin). Daniel itself says his docker image isn't very "dockerish" because installing all components in one container is not the concept how it should work, so the benefits of microservices, security, single components and things like that are lost in a cloud environment with this particular docker image. But the nice thing about this image, just like OXAR, it includes all you need: ORDS, APEX, Tomcat, AOP, ... For me this image is ideal to test something quickly. A docker container has less overhead than a full VM and you still have some benefits of Docker.

Some pictures while building and creating the container:


For the multiplication table project I won't use this option. But I did want to cover it, as it might be a good solution for you, especially if your company is already into containers. Oh and did you know Docker has a competitor too? It's called rkt, we will probably hear more about that in the coming years.
Hosting companiesNext to Oracle, there're a number of hosting companies that provide Oracle APEX hosting, but I've no experience using them for my own projects, but it might be an option for yours. I know some AOP cloud customers that connect from their SkillBuilders, Revion and AppsHosting account.
CloudAs previously mentioned in the on-premise section, there're a number of options you have. You can just go with a (virtual) machine, or a machine that is pre-configured, a complete managed machine and database etc. They also call it infrastructure as a service (IaaS), platform as a service (PaaS) or software as a service (SaaS) or data as a service (DaaS). For an Oracle APEX project it's most important you have the Oracle database. Oracle put the last few years a lot of focus on the cloud, here you find the Oracle Database Cloud offering. There're also alternatives by many other providers for example Amazon AWS: EC2 (virtual machine), RDS (managed database) etc. In the next section we go in more detail with one of the Oracle's offering.
Oracle Exadata Express Cloud ServiceI'm a real fan of one of Oracle's cloud solutions named Oracle Exadata Express Cloud. I find this a great solution for many of my Oracle APEX projects. For 152 euro (excl. vat) you get an Oracle 12c pluggable database with many Enterprise Edition features turned on and ORDS and APEX 5.1 installed. This service is also fully managed by Oracle, so Oracle is taking care of all the patching, keeping it up and running, performant and secure. They let you know when a maintenance will happen and let you know when it's complete. Here's an example of a few days ago:



On top of this, it's running on Exadata hardware. To build this solution myself would require a lot of money. Oh and it can connect to our APEX Office Print cloud service, so you can do PDF printing and exporting to Excel too :)

But that is not all... you get more, you get a complete development infrastructure, meaning part 1 and part 2 I touched on at the start of this blog. So you can manage the full development life cycle with this service. You have a Git repository, can do the planning, capture issues, do automated builds etc.

Here's a quick screen cast logging in to Exadata Express and looking at the different pieces we covered:


On the management part of the development process; doing the builds, tracking the issues etc. you can definitely set this up yourself, and there are many options you have, from open source to probably most known, the Atlassian stack with Jira, Confluence, Bamboo etc. but this comes with a cost too. At APEX R&D we use different tools depending the project and customer. Two years ago, for AOP we started with Team Development which is part of Oracle APEX to define the features, gather feedback and define the releases. But we extended with other tools now like Trello and Bitbucket which stores our Git repository and we started to use the issue features there too. In some other projects we use Jira or Redmine.

There're so many tools to manage your development life cycle, at some point you just have to make a choice. At the end of the day most tools are good, it comes down to personal preference. Whenever our project involves Oracle Exadata Express, my choice will be to use the tools that come with the Oracle Developer Cloud Service, as that is included and I don't need to setup anything else anymore. From a maintenance and cost perspective I find it really appealing.

On different Oracle conferences this year, I've given a presentation how to move your APEX app to the Oracle Exadata Express Cloud.

Is this solution perfect yet? No, there's still room for improvement. For example I would love to have some more EE features available (RAS, Flashback data archive for example), an easier way to point to a custom URL and a customizable backup strategy. Oracle is improving every month and for many projects the current feature set is more than enough.

As the multiplication project has both an educational side to show you how I do APEX projects and is also a real use case, I'll use the Oracle Exadata Express service.

Ok, this post is getting close to 2000 words... if you read till here, great! I hope I gave you an insight in the different options you have to build your APEX infrastructure.

If you have any questions or remarks, don't hesitate to add them as comments to this post.
Categories: Development

Pages