Quickly developing and hosting your friendly neighbourhood application – for free using … Oracle APEX

My challenge:

my wife wants to set up a system for some of our friends and relatives to let each other know about appliances and things they can share as well as objects they offer up [because they plan to get rid of them]. The specifications are fairly straightforward: users are known and should be authenticated, the app should be accessible through laptop and mobile device, offers can be placed along with at least one image of the object and anyone interested in an offer should have the opportunity to ask questions and indicate their interest.

Initially, I looked at Google Forms and a little later at Google Sheets (and custom sheets with Google WebScript for slightly more advanced stuff). I dug around a little, watched a pretty informative video and then decided to go a different route. Instead of no code I was quickly entering into low code at best (and medium code as my wife’s requirements start piling up, as I am sure they will). So if low code what I am facing with the option of getting more and more into coding, I am looking at a very small – in terms of everything: functionality, traffic, availability, performance – application and I will have to do some coding, isn’t Oracle APEX and the managed apex.oracle.com environment a very fitting way to go? Where coding is done in SQL, PL/SQL and JavaScript – technologies I master pretty well. Where all my other non-functional requirements probably can be taken care of rather easily. And where a managed environment for these small scale application is offered for free.

My biggest questions at this stage were:

• would it be easy to implement authentication on my application, to ensure only the people we know and love (and not even all of them) can enter the application
• would it be easy to create a user interface that is intuitive enough for some of the not so computer savvy users I am expecting
• would it be doable for me – with not much APEX experience to speak of – to quickly create at least an MVP with which my wife could get going

Let’s find out.

My steps will be:

• get my own Workspace set up at https://apex.oracle.com
• create the database tables to underpin my application (at least for the MVP)
• create an application and a first page to create and query data
• implement authentication and manage some pilot users
• publish the application and go live for some initial poking around by the pilot users

1. Getting Started

Getting started is easy enough – at https://apex.oracle.com/en/learn/getting-started/

After requesting my own workspace

it took no longer than a few minutes for the email with login details to arrive

2. create the database tables to underpin my application (at least for the MVP)

Using the Quick SQL facility in the APEX development environment, I composed my table definitions.

Via Review and Run, I can quickly execute the DDL in the Oracle database schema associated with my APEX workspace.

With the Object Browser, I can inspect the tables and constraints and start editing data:

3. Create an application and a first page to create and query data

From the App Builder tab, I click on Create New App

I provide the name, select a few application options – including Authentication using Application Express Accounts – and click Create Application:

This will create an APEX application with a Home and Administration page. This application will be accessible to any APEX user I set up. This user does not need to have an Oracle account, just use the credentials I provide them with.

To add pages to the application that are specific to the tables I have created, click Create Page:

Now I get to specify the style of the page – Form and Form on Table, :

And next I get to define the name of the page, the default navigation flow from this new page and whether a breadcrumb should be added:

more interesting are the menu entry for the page

and the table on which the form is to be based: table OFFERS  – as well as the columns to be presented in the page:

Finally I specify the primary key column(s) for the OFFERS records:

And the page is created.

If I want to, I can fine tune the page and the page items – but I do not have to. At this point I have a working page that I can test run right away, by clicking the run button:

The page appears, as it will to end users:

All fields can be entered, the record can be created and an image can be uploaded (one of the table columns is of type BLOB).

4. Publish the application to external users – implement authentication and manage some pilot users

I thought this would be a big step. To bring the application live – surely that would be a very involved operation? It turns out that it is not. I have created a user account for my wife:

and specified – very clearly – that she is neither and administrator nor a developer:

Upon creating the application, I set the authentication scheme to Application Express Accounts. After creating user accounts, I can send the application URL to these users along with their user name and password and they can get off to some initial poking around.

5. Initial User Experiences

After just a little tweaking and tuning, this is what my wife experiences on her mobile device:

Mobile friendly sign in – out of the box. After signing in:

And submit a new offer – register an object that is available for sharing and take over:

After submitting the offer

it can be reviewed:

and inspected in detail:

Note: this is just the MVP – a minimal set of features. Achieved in hardly any time at all.

Status at this point:

• cloud based data persistence (in relational database)
• web and mobile application experience
• authentication for centrally managed users (and no one else)
• record creation, manipulation and review
• upload images

This covers the core requirements from my wife. And I feel fairly confident that I can address most of her follow requirements as well.

Some very nice features in APEX I would like to mention:

• search through the entire application (for example to find that one text string that you want to have modified in the application)
• define reusable lists of values
• predefined page patterns (such as wizard, master-detail, interactive grid)
• built in user feedback mechanism – and option to ‘turn feedback into todo item’
• great look and feel on mobile, just out of the box – including ability to take and add photos as uploaded images
• free – not just the product APEX but also the light weight, fully managed environment

Conclusion

If you want to quickly create an application for a small group of known users that you want to make available on desktop and mobile devices from anywhere in the world, in which data can be entered, browsed and manipulated, and you want to spend no time on installation, administration and other non-functionality related stuff and you want it to look nice, then you probably should consider Oracle APEX as a solution. When Google Forms and SurveyMonkey are just too limited (by far) and low code platforms do not offer a freely available, managed  environment, when you prefer not to code but if you have to, SQL and PL/SQL are fine – then Oracle APEX can well be your friend.

I managed to get a simple four database table based application up and running – and accessed by my wife on her mobile phone in maybe three hours on a lazy Sunday afternoon – with no prior knowledge or skills regarding APEX. If I would have to do the same again, it would take less than one hour. From start to my wife and four of her friends live on a (mobile) web application where they create and share data.

APEX – for all your neighbourhood applications.

Resources

Request APEX workspace: at https://apex.oracle.com/en/learn/getting-started/ (this requires an Oracle account)

Current APEX documentation: https://apex.oracle.com/en/learn/documentation/

APEX Community Discussion Forum: https://community.oracle.com/community/groundbreakers/database/developer-tools/application_express 

Notes

Couple of things I had to find out:

– how to set a style on the image to have it contained within the page (set Advanced | Custom Attributes – style=”height:400px”

– how to derive a default value for a page item from one of the database tables using the user id for current user – see documentation on Session State Management, define default for page item of type PL/SQL Function Body, write a PL/SQL code block that returns value of the correct type; derive the value using the session state variable :APP_USER and perform a query against the MEMBERS table.