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

3

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

image

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.

    image

    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:

    image

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

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

    image

    image

    image

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

    image

    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:

    image

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

    image

    image

    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:

    image

    more interesting are the menu entry for the page

    image

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

    image

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

    image

    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:

    image

    The page appears, as it will to end users:

    image

    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:

    image

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

    image

    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:

    image

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

    image

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

    SNAGHTMLdb29db9

    After submitting the offer

    image

    it can be reviewed:

    image

    and inspected in detail:

    SNAGHTMLdb859dd

      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.

      image

      About Author

      Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.

      3 Comments

      1. Apex is a good application development platform however oracle site provides free site for demo purposes. Your application along with data should be backed up before oracle refreshes those free instances.

      2. xtrailrunner on

        Hi Lucas,
        I had the same idea for sharing car rides from and to our village but at the end I was too lazy and opted for neben.de (a German platform comparable to nextdoor.com). Thanks !
        Juergen

        • Lucas Jellema on

          Hi J├╝rgen,

          I probably should check out nextdoor.com as well. However, I like to have control. And I like to get to know APEX – it seems a good solution in many situations. This article is intended more to demonstrate what APEX can do than necessarily what the best way is to address the ‘sharing society’.

          Lucas

      Leave a Reply

      This site uses Akismet to reduce spam. Learn how your comment data is processed.