Wednesday, 12 February 2020

Oracle APEX Geolocation 1: How to get your location

Some decades ago people used to work in an office. And occasionally they went on business trips. Nowadays users are much more mobile. They can work from home, form the office or from a field location. So their location varies and it can be useful information for the applications they use.The location might be tied to a customer or a fact that should be reported. This series of blogposts will deal with determining the location in Oracle APEX, and how to use and display it:

Part 2: Using Oracle Spatial
Part 3: Display location(s) on map
Part 4: Correct locations on map
Part 5: Getting location data from photos

The parts without link are not yet available.

There is an example application available at :

Getting your location in the browser

In order to retrieve your GPS location in the browser some JavaScript needs to be applied:

function getLocation() {
  if (navigator.geolocation) {
    navigator.geolocation.getCurrentPosition(function(position) {
        alert(`Latitude: ${position.coords.latitude} \n` +
              `Longitude: ${position.coords.longitude}`);
  } else {
    alert("Geolocation is not supported by this browser.");

The most important line is the call to navigator.geolocation.getCurrentPosition.
 This is the function to retrieve the GPS location. As this may take several seconds the function is asynchronous.  The function to be performed after retrieval of the location is the argument for the function. There also is a check when the function is not available.
After the call JavaScript will immediately continue with others tasks. Otherwise the page would hang until the function returns.

NB The function can only be called from a https web site.


In ideal situations the accuracy of the location is within a few meters. In less favorable situations this might expand to tens of even hundred of meters. Especially in an urban environment with many high buildings the visiblity of the GPS sattelites is less good and the accuracy is less accordingly.
More information on GPS accuracy can be found  here


Privacy can be an issue when you retrieve and store the location of employees, customers etc. The privacy legislation differs per country.
As of May 2018 the General Data Protection Regulation is issued by the EU. This law contains high penalties for offenders. It is wise to take notice of the privacy regulations in the countries where the application will be used.

Getting location using a APEX plug-in

For APEX developers it is not necessary to write JavaScript to retrieve the GPS location. You can use the Store location plug-in that can be found on

After importing the plug-in in your application, you can reference it in a dynamic action. All you need to fill in are the APEX items to store the latitude and longitude once the location is retrieved.

To define follow-up actions a custom DA can be defined acting on the event location-retreived.

Happy apexing ;-)

Saturday, 21 December 2019

Missing grid layout attributes for APEX items

Whilw preparing for a new presentation I created a new application using the APEX Create Application Wizard and started to create pages and adding items.
While doing so I noticed I missed the usual Grid layout attributes in the Layout section of the item attributes:

No column info etcetera. :-(.
I have spent at least an hour looking for a setting that governs this behavior. Without a result...
Also Google did not provide me with the right answers.

Until I created a new application with all the optional pages. When I set out to examine one of these pages I noticed that the region in the Breadcrumb position did not have the grid layout attributes either.
Going back to my other application I saw that all the regions were created in the Breadcrumb Bar position...
So moving the regions to the Content Body give me back my Grid Layout options (I really missed them).

Happy APEXing

Thursday, 7 November 2019

Internet Explorer does not show content of Modal Dialog

In my current assignment I develop using either Firefox or Chrome. But the organization also uses Internet Explorer 11. 

So this morning one of the testers calls me and shows an empty Modal Dialog in Internet Explorer. When using Firefox or Chrome the Modal Dialog shows the expected content. So where is the difference. 

So I reproduce the situation in the Development environment and there the same behavior shows when using Internet Explorer: an empty modal dialog. After opening the Developer Tools the cause shows in the console window: a Javascript error. 

Invalid character

The error points to the JS code below:

$( `[headers="${id}"] input[type="checkbox"]`).each(function() {
    if ( $(this).prop('checked') != checked_status ) {

In the above code snippet a template string is used enclosed with the backtick character (`). You can find more information on this technique on And at the end of the page there is a compatibility table, in which it is clear that Internet Explorer does not support this feature. 

It is clear that this feature is not supported by IE :-(. 

After a change of the code to: 

$( '[headers="'+id+ '"] input[type="checkbox"]').each(function() {

the modal window showed its content again in IE. 

So when coding for IE be sure not to use too modern JavaScript. Or use a tool like Babel that supplies backward compatibility. 


But still, it is quite tricky that a JavaScript error on startup prevents the whole APEX page to be displayed. 

Happy APEXing

Friday, 2 August 2019

Oracle APEX Plug-in to retrieve GPS location

Your smartphone is aware where it is using its GPS capabilities. From HTML it is possible to retrieve this GPS location. For this you will have to write some JavaScript.
The new Store location plug-in relieves you from writing the Javascript code. Use it by:

  • downloading the plug-in from
  • importing the plug-in into your application
  • create two items on a page for the GPS coodinates (lattitude and longitude). They may be hidden items
  • creating a Page Load dynamic action on the page referencing the plug-in in the action
  • filling in the names of the GPS coordinate items in the plug-in attributes
That's all! Run your page on a smart phone and the items will be filled with the GPS coordinates of your current position. For security reasons the smartphone might ask you for permission to access the GPS sensor. 

Acting upon the GPS location

Retrieving the GPS location is an asynchonous process. That means that the process is started and at some later point in time the result is stored in the items. This means that adding another in the Page Load dynamic action will access empty GPS location items because the process is not finished. 
To act upon the coordinates you need to define a custom dynamic action with the following specification:

In the actions you can for example define a PL/SQL action with the GPS coordinate items as inputs. 

In this blogpost you find a full example for applying the plug-in. 

Happy APEXing

Creating a mobile app with APEX - Part 10: Use your location

Your phone is packed with all kind of sensors. Many of them you can not use in JavaScript, but you can access the GPS sensor to retrieve your location. In this blogpost we will access the GPS location and use it in our application.

On the Activity page the GPS Location will be retrieved. When the record is entered when the user is still on the location of the activity she can check Use location. With this item checked the GPS location is stored with the activity.
When the page is opened to enter a new activity the GPS location is retrieved. It is compared to the GPS locations in the database and if possible it will provide a default value for the Location item.

We will:

  • update the database to add columns to store the location
  • import a plug-in to retrieve the GPS location
  • add items on the Activity page to store the location
  • add Dynamic Actions on the Activity page to act on the GPS location
  • use the stored location to provide a default value for the column Location

Update the database objects

There are some changes needed in the database to accommodate the location data. 
The columns act_lattitude and act_longitude are added to the table ttm_activity. We also add a column act_sdo_location which is of the type sdo_geometry to be able to use Oracle Spatial functions. 
The trigger on ttm_activity is changed to construct the column act_sdo_location from act_lattitude and act_longitude.
The view ttm_activity_vw and it's instead of trigger are changed to support the new columns. 
The package ttm_alg is expanded with a function to yield a default value for location

Download the change script from here and execute it. 

Import the Store Location plug-in

Download the Store Location plug-in from APEX World. (Go to the Plug-ins page and search for store location)

Load into the application plug-ins:
  • Go to Shared Components > Plug-ins
  • Press Import
  • Select the plug-in file
  • Hit Next
  • Press Next again
  • Hit the button Install Plug-in
The plug-in is installed and ready for use. 

Add items to the Activity page

Items are needed to store the GPS coordinates that are retrieved by the plug-in. 
Furthermore we need a check box to indicate whether the record is entered on the location of the activity. 

  • Go to page 15
  • Go to P15_ACT_LOCATION and press the right mouse button
  • Select Create Page Item
    • Name: P15_USE_LOCATION
    • Type: Checkbox
    • Appearance > Template: Hidden
    • List of Values:
      • Type: Static Values
      • Static Values: STATIC:Use location;Y
  • Create another item:
    • Name: P15_ACT_LATTITUDE
    • Type: Text Field
    • Label: Lattitude
    • Source:
      • Type: Database Column
      • Database Column: ACT_LATTITUDE
  • And another one:
    • Name: P15_ACT_LONGITUDE
    • Type: Text Field
    • Label: Longitude
    • Source:
      • Type: Database Column
      • Database Column: ACT_LONGITUDE

Adding the Dynamic Actions

Two dynamic actions will be added. One to retrieve the GPS location and another to react on the retrieval of the location. These are separate actions because the retrieval of the GPS location is an asynchronous process. 

  • Go to page 15
  • Go to the Dynamic Actions tab
  • Right click on Page Load and click Create Dynamic Action
    • Name: Page Load - Retieve GPS location
    • Select the Action
      • Change Identification > Action to Store Location [Plug-In]
      • In Settings:
        • Lattitude Item: P15_ACT_LATTITUDE
        • Longitude Item: P15_ACT_LONGITUDE
  • Right click on Page Load and click Create Dynamic Action
    • Name: Location retrieved
    • When:
      • Event: Custom
      • Custom Event: location-retrieved
      • Selection Type: jQuery Selector
      • jQuery Selector: html
    • Select the Action
      • Action: Execute PL/SQL Code
      • Settings > PL/SQL Code:
       , ttm_alg.default_location(:P15_ACT_LATTITUDE,:P15_ACT_LONGITUDE));
      • Items to Submit: P15_ACT_LATTITUDE,P15_ACT_LONGITUDE
      • Items to Return: P15_ACT_LOCATION
The last Dynamic Action retrieves a default value for the field Location based on the GPS location. The default value is retrieved in the function ttm_alg.default_location that uses an Oracle Spatial query to find activities that have been recorded close to the current GPS location. The query is discussed in the last paragraphs of this post. 

Eating the pudding

The proof is in..., well you know. 
So let's try it. 
  • log in to the application on your phone
  • press New activity
  • after the page has been opened your permission to use the location is asked. Confirm it.
  • now scroll down to the bottom
  • (after some time) you will see the GPS coordinates
  • fill in the items of the activity
  • enter in  Location: test GPS location
  • check Use Location, else the GPS location will not be stored
  • accept the changes
  • press New again
  • scroll down to the bottom
  • (after some time) you will see the GPS coordinates
  • Location should then contain the value you entered for the previous record: test GPS location
If things do not work as expected use the Chrome Inspector (or any other Developer tools) to examine what is wrong. 

Background on selecting the default value

Based on the GPS coordinates the default value for the Location field is determined. This is done using a spatial query:

      select act_location
      from   ttm_activities
      where  act_lattitude is not null 
        and  act_longitude is not null
        and  act_use_location = 'Y'
        and  sdo_geom.within_distance(act_sdo_location,0.2
                , sdo_geometry(2001,8307
             = 'TRUE'
        order by sdo_geom.sdo_distance(act_sdo_location
                , sdo_geometry(2001,8307

This query uses the Oracle Spatial function sdo_geom.within_distance to find all the records with a GPS location within 200 m's (the value 0.2) from the given GPS coordinates. This proves to be a reasonable distance to correct for the bias in GPS location. 
The records are sorted based on the distance to the coordinates which is retrieved using the function sdo_geom.sdo_distance. This way if the query delivers more than one row the one closest to the the given point is selected. 

Using Oracle Spatial functions

The Oracle Spatial functions expect arguments in the  form of a SDO Geometry objects. The column act_sdo_location is already stored as such an object. The GPS coordinates are numbers and need to be converted to a SDO Geometry object:


Without going to deep into the complex geometry subjects: 
  • the first parameter 2001 indicates that the shape is a point. You can also have lines, polygons etc. 
  • the second parameter 8307 references the coordinate system
  • you notice the values for longitude and lattitude in the call to sdo_point_type
  • the last parameter indicates that the unit used is kilometers. 
  • more detailed information can be found in the Oracle documentation
To be able to compare values the coordinate system and the unit used should be consistent in database and code. 

Tuesday, 30 July 2019

Creating a mobile app with APEX - Part 9: Put the app on your phone's home screen

Now you have a cool app. But to use it you still have start your phone's browser and type an URL or select a bookmark.
In this post I will show you how to put an icon for the app on the home screen of your phone. The steps are:
- get suitable icons
- prepare your application to use the icon(s)
- put your app on the home screen

For the last step only iOS is described.
Because of the lack of an suitable Android device I was not able to describe the process for Android.
However I expect the process on Android to be similar.

Get suitable icons

The first step is to get suitable icons. Apple sets strict standards around application icons. Among others they need to come in various prescribed sizes, like 16x16, 32x32 etc.
Luckily you can find many sites on the internet to do create such an icon set for you. I have used On this site you provide an image as base for the icons and the whole set is generated, even including the HTML code to add to your page.

  • go to
  • upload your image
  • set the options:
    • you can specify a background color
    • specify the location for the files: #APP_IMAGES#/img All the way at  the bottom of the page
    • generate the files
  • download the ZIP file with icons 
  • cut the HTML code and paste it into a file

Prepare your application

Now we will implement these icons to our application. First we load the icons in the Static Application Files:
  • go to Shared Components > Static Application Files
  • click on Upload Files and fill in the values:
    • Directory: img
    • Files: Choose the ZIP file with icons
    • Unzip File: Yes
    • Press the Upload button
  • all the icons are now available in the img directory in the Static Application Files
Then we can reference these files:
  • go to Shared Components > User Interface Attributes
  • fill Favicon > Favicon HTML with the HTML code from the previous paragraph
That's it. 
When running your application in the desktop browser, you will notice that the image in the tab next to the page name has changed to the new image. 

Put your app on the home screen (iOS)

To put your application on the home screen of your iPhone: 
  • open your application in Safari on you phone
  • log in
  • press the Share Button
  • chose Add to Home Screen
  • you will see a form with the icon. The title of the icon can be changed here
  • press Add  and the icon will be added to the Home Screen
This process is illustrated in the image below:

img 4

This way you will have easy access to your application to enter data fast. 

Happy APEXing

Wednesday, 24 July 2019

Creating a mobile app with APEX - Part 8: Implementing autologin

In the mobile app I usually enter only a few records of data like an expense or a few activities.
It is pretty annoying when I have to enter username and password each time I use the app for a few seconds. 

In most native apps the authentication is asked once and then stored safely to be reused at further use. The security lies in the presumption that the authorization has been done by unlocking the phone. For most applications this will be sufficient. Only apps with a high stake or high risk like banking apps will need additional authentication. 

So I want this scenario also for my mobile APEX apps: 
  • enter username and password the first time
  • checking the 'Stay logged in' checkbox
  • next time when starting the app the login process is done automatically and I will be led to the starting page
We can accomplish this by using cookies to store client side data. This idea has been described by Christian Rokita in this blogpost

I have created a bit different implementation without the need of an extra page:
  • a custom authentication scheme is created based on a tables with users and passwords
  • a sessions table is created to store tokens and user names
  • a package will accommodate the code needed to read and write the cookies and perform the autologin
  • on the login page the autologin procedure is called to read the cookie. If the cookie points to a valid user a session is created for this user and the session is redirected to the starting page of the application
  • on the login page a Stay logged in checkbox is added
  • an Before Header application process writes the token to the Stay logged in cookie and creates an entry for the token and the user name of the current user

Creating the database objects

You can download the file to create the tables here
Execute the script in your favorite SQL console. 
In your schema you should see:
  • the table aut_users
  • the table aut_sessions
  • the package aut_pck
The table aut_users  contains one record for a user user with a password secret. You can use this data to login to the application. Add your own users in this table.
The implementation of the authentication is very basic and just for demonstration purposes. For serious use at least the passwords should be stored encrypted!

Creating a new authentication scheme

To obtain autologin functionality we need to create a custom authentication scheme:

  • go to the Shared Components > Security > Authentication Schemes
    • press Create
    • chose Based on a pre-configured scheme from the gallery
    • press Next
      • Name: Custom
      • Scheme Type: Custom
      • Authentication Function Name: aut_pck.authenticate
    • hit Create Authentication Scheme
After creating the scheme it is automatically the current scheme.

Changing the login page

Now we will adapt the login page:
  • open the login page 9999
  • add a new process:
    • Name: autologin
    • PL/SQL code:
       ( p_app_id    =>  :APP_ID
       , p_page_id   =>  10
    • Executing Options > Point: Before Header
    • Server-side Condition:
      • Type: Request != Value
      • Value: LOGOUT
  • add a another process:
    • Name: autologout
    • PL/SQL code
    • Executing Options > 
      • Sequence: 0
      • Point: Before Header
    • Server-side Condition:
      • Type: Request = Value
      • Value: LOGOUT
  • select the Login Region
    • select the item P9999_REMEMBER
    • Change Label to Stay logged in
The last step in processing is clearing the page's session state. We need to limit that to the username and password items in order to have the value of the Stay logged in checkbox available on subsequent pages. 
  • Go to the processing tab
    • Open the Clear Page(s) Cache
    • In the attributes change Settings:
      • Type: Clear Items
      • Item(s): P9999_USERNAME,P9999_PASSWORD  This is done to keep the value of the P9999_REMEMBER in session state
  • Save the page

Adding the Application Process

We will create an application process that will fire on each page before the header on condition that the user is authenticated and P9999_REMEMBER = 'Y'. In this process the Stay logged in cookie will be written, unless there is a valid cookie.
Go create the application process:

  • go to the Shared Components > Application Processes
  • click the button Create
  • Enter 
    • Name: Write autologin cookie
    • Point: On Load: Before Header
    • Press Next
  • Enter the PL/SQL code:
  -- set autologin cookie
         ( p_username      =>  :APP_USER
         , p_remember      =>  :P9999_REMEMBER
  :P9999_REMEMBER := 'N';
    • Press Next
  • Enter the condition:
    • Condition Type: PL/SQL Expression
    • Expression 1: 
:APP_USER != 'nobody' and
:P9999_REMEMBER = 'Y'
  • Press Create Process
After writing the cookie the value of P9999_REMEMBER is set to 'N'. This ensures that the process only fires once after login. 

Enabling logging out

As all is set up now you will be automatically logged in until the cookie or the aut_session record expires. To give the user the possiblity to end the autologin we will adapt the logout URL. 

  • go to Shared Components > Navigation > Navigation Bar List
  • select Desktop Navigation Bar
  • click on Sign Out 
  • change Target:
    • Target Type: Page in this Application
    • Page: 9999
    • Request: LOGOUT
  • press Apply Changes
Now chosing Sign Out will result in navigating to the login page with the request LOGOUT.
Previously we have created a logout process on page 9999 which is triggered by the request LOGOUT. This process erases the cookie and removes the corresponding record from aut_sessions thus disabling the autologin. 

Testing the autologin functionality

Now you can test the functionality by logging in with the Stay logged in item checked. 
You can test the autologin by changing the session ID in the URL. Normally the session would be recognized and you would be returned to the login page. Now you just stay logged in. 

Behind the screen you can check on the existence of the cookie STAY_LOGGED_IN_xxx, where xxx is the application number using developer tools like the Chrome Inspector. Likewise you can inspect to content of the table aut_sessions, where a record should exist with the token stored in the cookie and the name of the user. 

Test the Sign out functionality. You should be returned to the login page.