Oracle APEX application with MySQL Data

Oracle APEX + OCI MySQL

In this blog, we will learn on how to use OCI MySQL table data to create an Oracle APEX application.

The topics we will be covering are as follows:

1. Install MySQL on OCI.
2. Create a simple table and insert data in MySQL.
3. Install ORDS for OCI MySQL. (Optional)
4. Create REST Enabled SQL for MySQL.
5. Create a simple APEX application on OCI using OCI MySQL Table data.

Pre-Requisites

1. Access to OCI Tenancy
2. Access to VCN, Subnets (public)
3. Access to create MySQL instance on OCI.
4. Access to a Compute on a Public Subnet.
5. Access to an APEX Instance on OCI.

Install MySQL on OCI

In OCI Console, go to Databases → MySQL

Create Database system here, by providing details for the below:

1. Name for your Database
2. Compartment where it has to be created
3. Choose the type of MySQL Database you need (Standalone, HA, HeatWave)
4. Username & Password for DB
5. VCN and Subnets where you want the Database to be provisioned (choose the subnet where you have a compute created).
6. What configuration machine you need (hardware) along with Data Storage size
7. Backup plan (as to how frequently you need backup and any specific time backup should be taken)

For the purpose of this blog, I have created a MySQL Standalone DB system on a Public Subnet.

You need a compute instance as you cannot access MySQL VM. MySQL instance is exposed through the endpoint of the DB system.

Now update your Security List to allow Ingress traffic on TCP ports 3306 & 33060 along with 22 & 443.

Create Table & Insert Data in MySQL

Now that you have MySQL DB created and a compute in the same public subnet. Let us try accessing the MySQL DB from local.

We’ll try to achieve this via MySQL Workbench which is installed on local machine.

Provide details as above in MySQL Workbench to create connection to OCI MySQL database.

In the above screenshot, SSH hostname would be your compute Public IP followed by Private key of “opc” user to login to VM/Compute Instance. Rest of the data is from your MySQL DB Systems screen as shown below

This completes the connection to MySQL on OCI. Now, let us create a database, table and insert data as below

MySQL Workbench

Install ORDS for OCI MySQL (Optional)

NOTE: This step is optional as we are going to create a connection to OCI MySQL in the next step, which helps with REST Enabled SQL service.

To access MySQL tables in Oracle APEX, you need to REST enable MySQL tables, for this let us start with Installing ORDS. We will be installing ORDS on the VM (which is created in the same public subnet as MySQL DB).

Before starting with ORDS installation, make sure your Java version is 11. Else install latest java version

As my Java version is 11, I’ve straightaway installed ORDS.

Once ORDS is configured, we need to do pool configuration which can be done on the same Compute from where we’ll connect to MySQL.

Run the below set of commands to do pool configuration

>> ords config — db-pool mysql set db.connectionType customurl
>> ords config — db-pool mysql set db.customURL “jdbc:mysql://<PrivateIP of MySQL DB>/?sslMode=REQUIRED”
>> ords config — db-pool mysql set db.username <username of MySQL DB>
>> ords config — db-pool mysql set db.credentialsSource request
>> ords config — db-pool mysql set restEnabledSql.active true
>> ords config — db-pool mysql secret db.password

Create REST Enabled SQL for MySQL

Login to OCI Console and create Connections under Database Tools

Create Connection to OCI MySQL Database by choosing the Database Cloud Service as “MySQL Database”, then choose your Database system name. Provide Username of MySQL DB, choose the vault where you have stored MySQL DB password and a Private Endpoint to connect to MySQL DB.

You can check if the connection is working or not, by going to SQL Worksheet under Database tools and by choosing your connection.

Now, let us create this as a REST Enabled SQL in APEX to create an Application.

Login to an existing APEX Instance (APEX Instance should be 22.1 or above)

Goto Workspace Utilities under App Builder

Workspace Utilities

In Workspace utilities we need to create Web Credentials (where we provide our details) and then use these credentials to create REST Enabled SQL for MySQL

Provide User OCID, Tenancy OCID, Private Key (used to generate fingerprint) and Fingerprint

Create a REST Enabled SQL Service

Endpoint URL required in REST Enabled SQL Service is to be formed as below “https://sql.dbtools.eu-amsterdam-1.oci.oraclecloud.com/20201005/ords/<Connection OCID>/_/sql”

The region mentioned in the above URL i.e., eu-amsterdam-1 is to be replaced with the region where your Connection is created.

In the next page, choose the Web Credentials you have created in the previous steps and click Create

Now, set your Default Database and Test REST Enabled SQL service

This completes creating a REST Enabled SQL Service connection to your MySQL DB.

Now, let us create a simple APEX application based on data from MySQL table we have created in previous steps.

Create a simple APEX application on OCI using OCI MySQL Table Data

Let us create a simple APEX application where we create a Reflow report with data from OCI MySQL Table data as below, by choosing

Location → REST Enabled SQL

Remote Server → The REST Enabled SQL Service you have created in previous step

Now, let us run the application.

OCI APEX Application

NOTE: This feature of creating APEX application using MySQL table data is available from APEX version 22.1 only and MySQL should be on OCI.

NOTE: You wouldn’t need a compute to be created if you are fine to access OCI MySQL directly on SQL Worksheet (Developer Services → Database Tools → SQL Worksheet). You would need a compute if you want to access OCI MySQL elsewhere. Also as mentioned above you need not perform Step3 i.e., installing ORDS on compute as we create connection to OCI MySQL and use that Connection OCID to REST Enable SQL service on OCI MySQL tables.

!!! Happy Reading !!!