Oracle APEX data embedded in your Website with an iFrame

You know when you want to include an iFrame in your website, and you come up against the dreaded CORS issues.
To those that are not familiar, modern browsers have taken the (sensible) approach of blocking includes from domains other than your own. You’ll spend hours tweaking HEADER settings and fiddling and getting nowhere, and be ready to chuck your computer out the window.

Well, here is a simple solution that allows you to make updates in a database and include them in your webpage without all the fiddling about.

There are many ways to do this properly, but unless you’re fully invested in Domain Names, DNS, SSL Certs all on top of the actual solution, then I have the answer for you! You can thank me later!

What is the need?

I want to be able to have updatable data in a simple web application, and then be able to include the updates on a webpage, perhaps embedded in Wordpress or so other platform.

What I am showing:
Use APEX to manage your data. Could be Oracle Free Tier, or apex.oracle.com. Either way, it’s a free way to have an Oracle Database with an APEX front end to manage your data.
APEX is an awesome low code, free data management tool to easily create reports, forms and dashboards, all built into (not onto) Oracle Database. Did I say it’s Free?

We’ll use ORDS (RESTful API) to serve some HTML that includes your data from the database. And, because we’re not returning a whole webpage full of compromise opportunities we don’t have worry about CORS issues.

Using an iFrame we’ll add that output to a webpage and voila.. Live data on your website.

Get yourself an APEX instance. Plenty of blogs on this. If you don’t have free tier (totally recommended), then head over to https://apex.oracle.com and sign up for a new Workspace.

If you’ve not used APEX before, you can get up and running in minutes. Perhaps take a look at some of the LiveLabs.

Once into your workspace, you’ll need to create a database table. I can thoroughly recommend QuickSQL

Quick SQL

Quick SQL allows us to create database objects without complicated(!) SQL. Ok, so this one is simple, but you get the idea.

webdata /insert 1
     web_message
     status_field num /values 0,1
     last_updated
     updated_by

The code above will create a table called “webdata”, with 4 columns. It will also insert 1 dummy row for us (we can do edits etc. later).
Quick SQL will convert the instructions on the left into runnable SQL in the right hand panel.

Generated SQL

Click “Save SQL Script” (give it a name) “Save Script”
Click “Review and Run”. If you’re happy, Click “Run”, Click “Run Now”
At this point you can could click the “Create App” button, and you will get a functioning data management app, so that you can make your updates whenever you want. If you’re brave and already have an authentication platform you can incorporate that as Social Sign On (like Microsoft or Google Authentication for instance).

Regardless, we now have a table with data that we can present inside a webpage.

So, how exactly do we get the data out? Let’s create a REST Api that will return some html with our webdata.

Whilst still in APEX, let’s navigate to RESTful Services.

If this is the first time in here, you’ll need to “Register the Schema for ORDS”. You can click the defaults. It’s good practise to change the Alias, but it’s not absolutely necessary.

We’ll need to create a Module, a template and a handler. Quite Simple:
Click “Modules” in the left Menu (Under RESTful Data Services)
Click “Create Module” button
Give it a name (keep it simple) “webdata”
Enter a path, this will be in your API path (see below) “webdata/” and click “Create Module”

Module name and path

You’ll be presented with a full url, ignore that for now.

Next we create a template. Click (unsurprisingly) “Create Template”
Enter a URI Template name “message” and click “Create Template”

Now to create our GET handler. Click “Create Handler”

Method will be “GET”
Source Type should be “MEDIA RESOURCE” (when I mess up, it’s because I forget this bit!)
At this point we have a space for some code. You can get the basic response using code builder in the APEX SQL Workshop, but I’ll give you the code to get the data, formatted as HTML from the Database.

select 'text/html', '<div><div>' || web_message || '</div><br /> Status ' || DECODE(STATUS_FIELD, 0, 'OFF', 'ON')  || '<br />' ||TO_CHAR(LAST_UPDATED, 'fmDD MON fmHH:MI PM') || '<div>' from webdata order by LAST_UPDATED desc FETCH FIRST 1 ROWS ONLY

This is our only real code, so to explain this, we’re SELECTing the data from the DB, but enclosing it in HTML (remember we’re going to put this in our webpage).

We select the response type as being “‘text/html” to tell the browser it’s just plain old html.
We’re changing the STATUS_FIELD to be readable (0 = Off, 1=On) and we’re formatting the LAST_UPDATED field to be a readable date.
For this exercise, we only want the most recent update, hence ordered by timestamp (descending, so most recent first), and only FETCH FIRST 1 ROW.
Feel free to play with the HTML here. You can include css tags and any formatting you require. I’ve kept this simple.

Paste the code in and Click “Create Handler”

You’ll now see a Full URL. That is our iFrame Source!
You can test this by pasting the URL into your browser.

HTML Output from your API

You can include this in your website with a simple iframe:

<iframe width="1000" style="border:none;" src="https://y12345abcde-orcldb.adb.ap-sydney-1.oraclecloudapps.com/ords/restalias/webdata/message"></iframe>

And that is it. Formatted HTML that includes data from the database without worrying about CORS and custom domain names and SSL certs.

Update: I notice that iFrame adds a set of <html> and <body> tags, which in my tests broke the style inheritance. To overcome this, you can use <embed> tag.

<embed type=”text/html” src=”https://y12345abcde-orcldb.adb.ap-sydney-1.oraclecloudapps.com/ords/restalias/webdata/message" width=”100%”>