Creating an APEX App in the Oracle free database cloudPublished on: Author: Stefan van Glabbeek Category: Oracle
Creating an APEX App in the Oracle free database cloud
Oracle has a new service: a free database in the cloud. With the database comes a Linux environment, web-services and APEX. APEX is a low-code tool from Oracle to build your applications.
To get started, you go to one of the following links (or a variant in your own language):
When you follow the link, you’ll get this screen:
Just hit the button, fill in your details, like company, email address and phone number, and there you go. You can also give your free database a name. Then, you will go to the administration screen:
Here you can start and stop the database with the Start / Stop button.
On the second tab, you can find APEX:
Just open APEX and start building you application.
In APEX, you can create multiple users. Click on “Manage Users and Groups on the right”.
With the “create user” button, you can create as many as you need. Because I wanted to create an application for learning support, I needed 4 types of users: Pupils, teachers, parents and the hall porter.
In the SQL Workshop, you can run SQL scripts and SQL commands. This way, you can pass your create statements for the data model. There is also an object browser, so that you can see all the details of the objects you created. Then, when you have some tables ready, you can start building the screens with the App Builder. Click on the tab App Builder and simply click on create.
If you create a page, you can choose from a variety of predefined pages, or you can pick a blank page. I used a predefined one for every screen, because a lot of stuff is already done for me, so this saved time.
Most of the screens I created are Master-Detail. Take care that this must contain a parent table and a child table. The child table must have a foreign key relationship with the primary key of the parent table. When creating the Master-Detail page, you can choose between 3 layout styles, give the page a name, create a menu entry (in the root or somewhere nested). Then you fill in the Master (parent) table and the display columns, and the detail (child) table. You click on the button create and there it is! Master-Detail is one of the pages that you will need most often. I also made use of editable grids, just for data entry. For instance, I needed a form to fill in the classes you can take. So I picked Form from the predefined pages, then chose Editable Grid.
Another predefined page is the interactive report. I created the messages app, based on this one.
Teachers and the hall porter can send messages to individual pupils and to groups.
A very nice predefined page is the calendar. In my application, I needed two calendars. One for the schedule, to insert classes that are given on a certain day and a certain time, and details like the teacher, the subject and the location. The other calendar is to insert events that must be registered. For instance, a pupil is not present or gets sick and goes home, a student forgets to bring his book or didn’t do the homework etc.
The calendars are based on a table containing all of this information. You can just create a page, pick the predefined calendar and use the already created table to base the calendar on. The table must contain the date – time fields for the beginning and end of each event.
When clicking on the white space within the calendar, I can insert a new class. With a date picker, choose the date, begin time and end time. Choose the subject from a list, as well as the year/level, the teacher and the location. By clicking on an existing one, I can change it.
An important aspect is security. Within the app builder and within the application, you can go to Shared Components. Here, you can click on “Application Access Control” where you can assign roles to users. You can also click on “Authorization Schemes”. There, you can base Authorization Schemes on those roles. Every object of the application can have an Authorization Scheme, to make sure you can only see the screens and click on the buttons that fit your role. Meaning: Pupils can see the schedule calendar screen, but they cannot edit or insert classes. They also cannot see the screen where teachers are added or deleted.
There is also row-security. Because pupils only have to see the classes they’re going to attend. They won’t see classes of other years, or subjects they’re not subscribed for. Therefore, I created a Master-Detail screen with all the classes of a certain year and a certain subject as the Master Block. In the Detail Block, you can then enter the pupils following this class. There is another Master-Detail screen with all the pupils and the username they can login with.
Then, in the calendar for instance, I can change the SQL that retrieves what you see in the calendar. And I can add my conditions in the where-clause. So in the where-clause, I look at the user that is logged in and retrieve the pupil that belongs to this user. When I know the pupil, I can see which classes they are subscribed to, and in their calendar only show those classes with the adapted where-clause. The same goes for the messages, the scores on exams, etc.
It was also easy to include a picture. In the add student detail page, there is a field with database format “Blob-content”. In APEX App builder, this field is added to the page as a File Browser field. It allows you to add the picture. A PL/SQL procedure inserts the content, filename and mime type into fields in the table. And then the pupil screen contains a report based on this table, with some html code added to it to display the picture.
I didn’t have any previous experience with APEX, except for a three-day crash course by our colleague and experienced APEX consultant Wouter de Waard in 2017. It was really surprising how quickly you can create a nice application in APEX, within Oracle’s free database cloud. So making applications like this, on a free database, could be very cost effective for small applications with a limited amount of data.