Create a Custom Authentication and Authorization Scheme in Oracle APEX

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 the https://www.apexrnd.be/ords/f?p=MTABLEmultiplication table app.

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:

	create or replace package mtl_auth_pkg
	as
	/**
	* Project: Multiplication Table
	* Author: APEX RnD - Dimitri Gielis
	* Description: Custom Authentication and Authorization
	* Note: None
	* @headcom
	*/
	/**
	* Create account
	*
	* @param p_username username
	* @param p_password password
	*/
	procedure create_account(
	p_email in varchar2,
	p_password in varchar2
	);
	/**
	* Custom authenticate
	*
	* @param p_username username
	* @param p_password password
	*/
	function custom_authenticate(
	p_username in varchar2,
	p_password in varchar2)
	return boolean;
	/**
	* Post authenticate
	*
	* @param p_username
	* @param out_user_id
	* @param out_first_name
	*/
	procedure post_authenticate(
	p_username in varchar2,
	out_user_id out number,
	out_time_zone out varchar2) ;
	/**
	* Request reset password
	*
	* @param p_email
	*/
	procedure request_reset_password(
	p_email in varchar2) ;
	/**
	* Verify reeset password
	*
	* verify the token of the password request and retun the id of the user
	*
	* @param p_token
	*/
	function verify_reset_password(
	p_id in number,
	p_verification_code in varchar2)
	return number;
	/**
	* Reset password
	*
	* @param p_id
	* @param p_password
	*/
	procedure reset_password(
	p_id in number,
	p_password in varchar2) ;
	/**
	* Authorization: administrator
	*
	* @param p_username username
	*/
	function authz_administrator(
	p_username in varchar2)
	return boolean;
	/**
	* Authorization: registered user
	*
	* @param p_username username
	*/
	function authz_user(
	p_username in varchar2)
	return boolean;
	end mtl_auth_pkg;

view raw mtl_auth_pkg.pks hosted with ❤ by GitHub

And the body like this:

	create or replace package body mtl_auth_pkg
	as
	/**
	* Constants
	*/
	c_from_email constant varchar2(100) := 'no-reply@my.email';
	c_website constant varchar2(100) := 'my site';
	c_hostname constant varchar2(100) := 'my hostname';
	/**
	*/
	function custom_hash(
	p_username in varchar2,
	p_password in varchar2)
	return raw
	is
	l_username varchar2(100);
	l_password varchar2(100);
	l_salt varchar2(100) := 'my secret';
	begin
	apex_debug.message(p_message => 'Begin custom_hash', p_level => 3) ;
	-- This function should be wrapped, as the hash algorhythm is exposed here.
	-- You can change the value of l_salt, but you much reset all of your passwords if you choose to do this.
	l_username := upper(p_username);
	l_password := upper(p_password);
	l_password := sha256.ENCRYPT(l_salt || l_username || l_password);
	apex_debug.message(p_message => 'End custom_hash', p_level => 3) ;
	return l_password;
	end custom_hash;
	/**
	* Reset password email
	*/
	procedure mail_reset_password(
	p_email in varchar2,
	p_url in varchar2)
	is
	l_body clob;
	begin
	apex_debug.message(p_message => 'Reset password Multiplication Table account', p_level => 3) ;
	l_body := '<p>Hi,</p>
	<p>We received a request to reset your password in the Multiplication Table app.</p>
	<p><a href="'||p_url||'">Reset Now.</a></p>
	<p>If you did not request this, you can simply ignore this email.</p>
	<p>Kind regards,<br/>
	The Multiplication Table Team</p>';
	apex_mail.send (
	p_to => p_email,
	p_from => c_from_email,
	p_body => l_body,
	p_body_html => l_body,
	p_subj => 'Reset password Multiplication Table account');
	apex_mail.push_queue;
	exception
	when others
	then
	raise_application_error( - 20002, 'Issue sending reset password email.') ;
	end mail_reset_password;
	/**
	*/
	procedure create_account(
	p_email in varchar2,
	p_password in varchar2)
	is
	l_message varchar2(4000) ;
	l_password raw(64) ;
	l_user_id number;
	begin
	apex_debug.message(p_message => 'Begin create_site_account', p_level => 3);
	l_password := utl_raw.cast_to_raw(DBMS_RANDOM.string('x',10));
	apex_debug.message(p_message => 'verify email exists', p_level => 3) ;
	begin
	select password
	into l_password
	from mtl_user
	where upper(email) = upper(p_email) ;
	l_message := l_message || 'Email address already registered.';
	exception
	when no_data_found then
	apex_debug.message(p_message => 'email doesn''t exist yet - good to go', p_level => 3) ;
	end;
	if l_message is null then
	apex_debug.message(p_message => 'password ok', p_level => 3) ;
	l_password := custom_hash(p_username => p_email, p_password => p_password) ;
	apex_debug.message(p_message => 'insert record', p_level => 3) ;
	insert into mtl_user (email, password)
	values (p_email, l_password)
	returning id into l_user_id;
	else
	raise_application_error( -20001, l_message) ;
	end if;
	apex_authentication.post_login(p_username => p_email, p_password => p_password);
	-- no activation email
	apex_debug.message(p_message => 'End create_site_account', p_level => 3) ;
	end create_account;
	/**
	*/
	function custom_authenticate
	(
	p_username in varchar2,
	p_password in varchar2
	)
	return boolean
	is
	l_password varchar2(100) ;
	l_stored_password varchar2(100) ;
	l_boolean boolean;
	begin
	-- First, check to see if the user is in the user table and look up their password
	select password
	into l_stored_password
	from mtl_user
	where upper(email) = upper(p_username);
	-- hash the password the person entered
	l_password := custom_hash(p_username, p_password) ;
	-- Finally, we compare them to see if they are the same and return either TRUE or FALSE
	if l_password = l_stored_password then
	return true;
	else
	return false;
	end if;
	exception
	when no_data_found then
	return false;
	end custom_authenticate;
	/**
	*/
	procedure post_authenticate(
	p_username in varchar2,
	out_user_id out number,
	out_time_zone out varchar2
	)
	is
	l_id number;
	l_first_name varchar2(100) ;
	begin
	select id
	into l_id
	from mtl_user
	where upper(email) = upper(p_username);
	out_user_id := l_id;
	end post_authenticate;
	/**
	*/
	procedure request_reset_password(
	p_email in varchar2)
	is
	l_id number;
	l_verification_code varchar2(100);
	l_url varchar2(200);
	begin
	-- First, check to see if the user is in the user table
	select id
	into l_id
	from mtl_user
	where upper(email) = upper(p_email);
	dbms_random.initialize(to_char(sysdate, 'YYMMDDDSS')) ;
	l_verification_code := dbms_random.string('A', 20);
	l_url := apex_util.prepare_url(p_url => c_hostname||'f?p='||v('APP_ID')||':RESET_PWD:0::::P9999_ID,P9999_VC:' || l_id || ',' || l_verification_code, p_checksum_type => 1);
	update mtl_user
	set verification_code = 'RESET_' || l_verification_code
	where id = l_id;
	mail_reset_password(p_email => p_email, p_url => l_url);
	exception
	when no_data_found then
	raise_application_error( - 20001, 'Email address not registered.') ;
	end request_reset_password ;
	/**
	*/
	function verify_reset_password(
	p_id in number,
	p_verification_code in varchar2)
	return number
	is
	l_id number;
	begin
	select u.id
	into l_id
	from mtl_user u
	where u.verification_code = 'RESET_'||p_verification_code
	and u.id = p_id;
	return l_id;
	exception
	when no_data_found
	then
	raise_application_error( - 20001, 'Invalid password request url.') ;
	return null;
	end verify_reset_password ;
	/**
	*/
	procedure reset_password(
	p_id in number,
	p_password in varchar2)
	is
	l_username varchar2(100) ;
	l_hashed_password varchar2(100) ;
	begin
	select email
	into l_username
	from mtl_user
	where id = p_id;
	l_hashed_password := custom_hash(l_username, p_password) ;
	update mtl_user
	set password = l_hashed_password,
	verification_code = null
	where id = p_id;
	end reset_password;
	/**
	*/
	function authz_administrator(
	p_username in varchar2)
	return boolean
	is
	l_is_admin varchar2(1) ;
	begin
	select 'Y'
	into l_is_admin
	from mtl_user a
	where upper(a.email) = upper(p_username)
	and a.role_id = 2;
	--
	return true;
	exception
	when no_data_found then
	return false;
	end authz_administrator;
	/**
	*/
	function authz_user(
	p_username in varchar2)
	return boolean
	is
	l_is_user varchar2(1) ;
	begin
	select 'Y'
	into l_is_user
	from mtl_user a
	where upper(a.email) = upper(p_username)
	and a.role_id in (1,2);
	--
	return true;
	exception
	when no_data_found then
	return false;
	end authz_user;
	end mtl_auth_pkg;

view raw mtl_auth_pkg.pkb hosted with ❤ by GitHub

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.

Build the Oracle APEX application: the framework

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.

Update August 19th: on apex.oracle.com a new version of Blueprint is available with even more options than you see in the video.

Set up domain and launch page

This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

Domain name

When 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 app

Once 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 page

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

• HTML5 UP
• ThemeForest
• WrapBootstrap
• Instapage

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 page

When 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.

Create the Oracle database objects

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 Tools

Data 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.

Manual

So 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 SQL

So 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.

Set up an APEX development environment

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-premise

You 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.com

This 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 VM

Another 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 Service

This 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 OXAR

If 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).

Docker

Docker 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 companies

Next 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.

Cloud

As 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 Service

I'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.

List of the tools I use and why I use them

This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

I initially thought to only list the software tools I use, but tools are more than software alone. As a developer I find it important you have everything you need to be successful. For me that means a clean desk, a whiteboard, paper and some writing material (as explained in my previous post) and top notch hardware. Here's a picture of my desk:

So lets move on to the software part now, but before reading further, lets start with a quote I came up with ;)

The tools don’t make the developer - Dimitri Gielis

or another quote I like a lot, sent in by Alan Rintoul:

The single most important component of a camera is the twelve inches behind it – Ansel Adams

When doing development, it's not about the tools, it's about mindset. Tools can help achieve a goal, but which tool to use depends on you and how you work with them. I love to hear what and how other people are using tools to get the job done. Martin D'Souza showed in this podcast how he works with Atom (text editor) and why you should use it. I loved the podcast, Martin is a very smart guy and good developer, but you can look at this podcast in two different ways. You can look at it and say, I'm going to use Atom now, and work with Atom the same as you did in Notepad, or you can look at the principles he explains why he went with Atom and apply it to your editor of choice (which might be Atom, Sublime or another). I recently switched to Visual Studio Code as my main editor, because for me it worked better in the job I have to do a lot (read large JSON files, work with Markdown and Git). Does it mean everybody should switch? Not at all, whatever works for you.

So having said that, I thought it would be nice to list the (software) tools I use and order them by how frequently I use them. There're apps I use multiple times a day (daily), some I use multiple times a week (weekly) or just a few times a month (monthly). While compiling this list I also saw I still have apps installed I don't use that frequently at all. The below list contains only desktop applications I installed on my laptop, next to those programs I also use some webapps like Bitbucket and Trello to name a few, but I'll cover those in future posts as part of different sections. Same applies for plugins or command line apps.

Note: I've a Mac laptop with macOS, so the below applications might not exist for Windows.

Daily

• Mail: for my emails I use Mail that is included in OSX. I also use Google Mail for other mailboxes.
• Safari (Chrome, Firefox): I typically develop in Safari or Chrome. Safari is a bit more battery friendly, but Chrome has beter developer tools and plugins. Firefox I use when I need just another browser to see if something behaves the same.
• Visual Studio Code: My favorite editor, I plan to do a quick video how I work with Visual Studio Code and will update this post when done placeholder for video
• Oracle APEX: the obvious development tool of choice :) (technically not a desktop app, but belongs here anyway)
• SQL Developer: My favorite editor to have a window in my Oracle databases. I plan to record a quick video how I work with SQL Developer and will update this post when done placeholder for video
• TweetBot: a few times a day I check my Twitter account or the news of #orclapex
• Slack: at my company or with friends we use Slack to communicate with each other when we are remote. There's also an orclapex team where many people of the Oracle APEX community are in
• 1Password: with this little tool I can have all different and secure passwords, accessible with a click
• Dropbox, Google Drive, iCloud: most important documents are in the cloud with one of those services
• Pushbullet: sents me notifications of the server on all my devices

Weekly

• iTerm2: my window to the server or whenever I need a terminal
• SourceTree (Git): here I've all my connections to Git repositories and I can quickly see when and what was changed
• SQLcl: mostly used when I want to run scripts, or used behind the scenes with automation
• Node.js: mostly used by other programs like Visual Studio Code or APEX Office Print during development
• Gulp: I use it to automate some things, for example when I save a Markdown file in Visual Studio Code it will automatically build an html file for me
• VMware: when I need to have a Windows machine or want to test something in my OXAR VM
• MS Office 365: Word, Excel, Powerpoint: I use to create or edit documents for example with track changes or I'm using it to create templates for APEX Office Print
• GotoMeeting, Skype, Zoom, Webex, TeamViewer, Google: when connecting to customers, friends, we use any of those meeting tools
• Moom: a little window manager for OSX, which allows me to quickly see two windows next to each other

Monthly

• Keynote: to build or deliver my presentations
• Docker: to quickly get a test system or software up and running
• LICEcap: to create animated gifs
• Screenflow: to create videos
• Transmit: to transfer files to another system

Installed, but not using that often

• TextExpander: snippets manager, this one I actually plan to use more
• XCode: used when building native iOS apps or compiling Cordova apps
• VirtualBox: when testing Oracle Developer VMs
• Paw: a REST manager, but I'm using Visual Studio Code plugin for that now
• The Unarchiver: when getting files from customers to unpack them
• Letter Opener for macOS Mail: some people send windows mails, without this tool I can't read it on OSX
• Duet: enables my iPad as second screen, only used when travelling
• Kaleidoscope: to compare two files (if they are not in Git)
• Classeur: used when writing in Markdown for my Blog, but replaced it with Visual Studio Code
• BBEdit, Atom, Sublime Text: replaced with Visual Studio Code
• OmniPlan: used for planning
• Patterns: to try regular expressions
• MacDown: used to create Markdown files, for example most of the AOP documentation was written in here, but using Visual Studio Code now
• LibreOffice: used in combination with AOP
• Pages, Numbers: to exchange or read older files I wrote in Pages or Numbers
• MJML: to write responsive emails

The above are just some tools that help me doing my job. Over time I changed tools and will most likely use others in the future. So depending when you read this (after 2017) things might have changed.

Feel free to share your favorite tools in the comments section.

Create user stories and supporting ERD

This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017

In the first post we defined our high level idea of our application. Now what are the real requirements? what does the app has to do? In an Agile software development approach we typically create user stories to define that. We write sentences in the form of:

As a < type of user >, I want < some goal > so that < some reason >

Goal of defining user stories

The only relevant reason to write user stories is to have a discussion with the people you're building the application for. To developers those stories give an overview of what is expected before the development is started in a language that all parties understand.

Some people might like to write a big requirements document, but for me personally I just don't like that (neither to read or to write). I really want to speak to the people I'm going to build something for, to get in their body and skin and really understand and feel their pain. If somebody gives me a very detailed requirements document, they don't give me much freedom. Most people don't even know what is technically possible or what could really help them.


I like this quote of Henry Ford which illustrates the above:

If I had asked people what they wanted, they would have said faster horses.

Now having said that, you have to be really careful with my statement above... it really depends the developer if you can give them freedom or not. I know many developers who are the complete opposite of me and just want you to tell them exactly what and how to build. Same applies to people who have a bright idea, they do know what would help them. I guess it comes down to, use each others strength and be open and supportive during the communication.

User stories for our project

In our multiplication table project we will write user stories for three different types of users: the player (child), the supervisor (parent/teacher) and the administrator of the app.

• As a player, I want to start a session so that I can practice
  
• As a player, I want to practice multiplications so that I get better at multiplying
  
• As a player, I want to see how I did so that I know if I improved, stayed the same or did worse
  
• As a player, I want to compare myself to other people so that I get a feeling of my level

• As a supervisor, I want to register players so that they can practice
  
• As a supervisor, I want to start the session so that player can practice
  
• As a supervisor, I want to choose the difficulty level so that the player gets only exercises based on their learning needs
• As a supervisor, I want to get an overview of the players progress and achievements
  
• As a supervisor, I want to get an overview of the players mistakes
  
• As a supervisor, I want to print a certificate so the player feels proud of it's achievement

• As an administrator, I want to see the people who registered for the app
  
• As an administrator, I want to add, update and remove users so that the database can be maintained
  
• As an administrator, I want to see statistics of the site so that I know if it's being used
  
• As an administrator, I want to notice feedback and suggestions from the users of the app

The above is not meant to be a static list, in contrary, whenever we think of something else, we will come back to the list and add more sentences. So far I took the role as administrator and parent, my son as child and my father as teacher to come to this list. I welcome more peoples ideas, so feel free to add more user stories in the comment field of things you think of. More info on user stories and how to write them you find here.

More on Agile, Scrum, Kanban, XP

Before we move on with what I typically do after having discussed the requirements with the people, I want to touch on some buzz-words. I guess most companies claim they do Agile software development. Most popular Agile software development frameworks are Scrum, Kanban and XP. I'm by far an expert in any of those, but for me it all comes down to make the team more efficient to deliver what is really needed.

My company and I are not following any of those frameworks to the letter, instead we use a mix of all. We have a place where we note all the things we have to do (backlog), we developed iteratively and ship versions frequently (sprints), we have coding standards, we limit the work in progress (WIP) etc.

When we are doing consulting or development we adapt to how the customer likes to work. It also depends a bit the size of the project and team that is in place.

So my advice is, do whatever works best for you and your team. The only important thing at the end of the day is that you deliver (in time, on budget and what is needed) :)

Thinking in relational models

So when I really understand the problem, my mind starts to think in an entity relational diagram or in short ERD. I don't use any tool just yet, a few pieces of paper is all I need. I start writing down words, draw circles and relations, in fact those will become my tables, columns and foreign keys later on. For me personally drawing an ERD really helps me moving to the next step of seeing what data I will have and how I should structure it. I read the user stories one by one and see if I have a table for the data to build the story. I write down the ideas, comments and questions that pop-up and put it on a cleaner piece of paper. This paper is again food for discussion with the end-users.

Here are the papers for the multiplication table project:

Our ERD is not that complicated I would say; we basically need a table to store the users who will connect to the site/app. I believe in first instance it will most likely be parents or teachers who are interested in this app. Every user has the "user" role, but some will have the administrator role, so the app can be managed. We could also use a flag in the user table to specify who's an admin, but I like to have a separate table for roles as it's more flexible, for example if we wanted to make a difference between a teacher and parent in the future. Once you are in the app you create some players, most likely your children. Those players will play games and every game consists out of some details, for example which multiplication they did.

While reading the user stories, we also want some rankings. In the above ERD I could create the players own ranking, or the ranking of the players of a user (supervisor), but it's not that flexible. That is why I added the concept of teams. A player can belong to one or more teams, so I could create a specific team where my son and I belong too, so we can see each others rank in that team, but I can also create a team of friends. The team concept makes it even flexible for teachers, so they can create their classes and add players to a specific class.

I also added a note that instead of a custom username/password, it might be interesting to add a social login like Facebook, just so the app is even easier to be accessed. As I know in Oracle APEX 5.2 social authentication will be included, I will hold off to build it myself for now, but plan to upgrade our authentication scheme once Oracle APEX 5.2 comes out.

So my revised version of the ERD looks like this:

I hope this already gives some insight in the first steps I do when starting a project.

In the above post I didn't really go into the tools to support the Agile software development (as I didn't use it yet), that is for another post.

If you have questions, comments or want to share your thoughts, don't hesitate to put a comment to this post.

From idea to app or how I do an Oracle APEX project anno 2017

For a long time I had in mind to write in great detail how I do an Oracle APEX project from A to Z. But so far I never took the time to actually do it, until today :)

So here's the idea; I love building projects that help people and I love to share what I know, so I will combine both. I will write exactly my thoughts and things I do as I'm moving along with this project, so you have full insight what's happening behind the scenes.

Background

Way back, in the year 1999, I build an application in Visual Basic to help children study the multiplication tables. My father was a math teacher and taught people who wanted to become primary school teachers. While doing the visits of the primary schools, he saw the problem that children had difficulties to automate the multiplications from 1 till 10, so together we thought about how we could help them. That is how the Visual Basic application was born. I don't have a working example anymore of the program, but I found some paper prints from that time, which you see here:



We are now almost 20 years later and last year my son had difficulties memorizing the multiplication tables too. I tried sitting next to him and help him out, but when things don't go as smooth as you hope... You have to stay calm and supportive, but I found it hard, especially when there are two other children crying for attention too or you had a rough day yourself... In a way I felt frustrated because I didn't know how to help further in the time I had. At some point I thought about the program I wrote way back then and decided to quickly build a web app that would allow him to train himself. And to make it more fun for him, I told him I would exercise too, so he saw it was doable :)

At KScope16 I showed this web app during Open Mic Night; it was far from fancy, but it did the job.
Here's a quick demo:



Some people recognized my story and asked if I could put the app online. I just build the app quickly for my son, so it needs some more work to make it accessible for others.
During my holidays, I decided I should really treat this project as a real one, otherwise it would never happen, so here we are, that is what I'm going to do and I'll write about it in detail :)

Idea - our requirement

The application helps children (typically between 7 and 11 years old) to automate multiplications between 1 and 10. It also helps their parents to get insight in timings and mistakes of their children's multiplications.

Timeline

No project without deadline, so I've set my go-production date to August 20th, 2017. So I've about 2 weeks, typically one sprint in our projects.

Following along and feedback

I will tweet, blog and create some videos to show my progress. You can follow along and reach me on any of those channels. If you have any questions, tips or remarks during the development, don't hesitate to add a comment. I always welcome new ideas or insights and am happy to go in more detail if something is not clear.

High level break-down of plan for the following days

• Create user stories and supporting ERD
  
• List of the tools I use and why I use them
  
• Set up the development environment
  
• Create the Oracle database objects
  
• Set up a domain name
  
• Set up reverse proxy and https
  
• Create a landing page and communicate
  
• Build the Oracle APEX application: the framework
  
• Refine the APEX app: create custom authentication and authorization scheme
  
• Refine the APEX app: adding the game
  
• Refine the APEX app: improve the flow and navigation
  
• Refine the APEX app: add ability to print results to PDF
  
• Set up build process
  
• Check security
  
• Communicate first version of the app to registered people
  
• Check performance
  
• Refine the APEX app: add more reports and statistics
  
• Check and reply to feedback
  
• Set up automated testing
  
• A word on debugging
  
• Refine the APEX app: making final changes
  
• Set up backups
  
• Verify documentation and lessons learned
  
• Close the loop and Celebrate :)
  

So now, let's get started ...