Why I think APEX should support OData (“SQL for the web”)

In today’s blog post I’ll explain what OData is about and go into details on my reasoning why Oracle in general and APEX in particular should support the OData standard.


NOTE: This is a blog post I prepared before I went to APEX Connect 2016 in Berlin. But I didn’t manage to verify and post in time. I’ve now slightly edited and added some details I learned about while being at APEX Connect.

OData is a new standardized protocol based on REST. It’s platform independent and uses HTTP, REST, JSON, XML. It basically allows you to do all operations you’d normally do in SQL in a transparent way.

So, if you want the short version why I think Oracle should support OData: Because Microsoft and SAP do and because it’s an open standard here to stay.

Microsoft Office 365,  Sharepoint, SQL Server, IBM DB2, SAP Netweaver and SAP HANA are a few of the products that either support OData natively or via a plugin.

Imagine defining an APEX Interactive Grid against a SAP HANA table without having to write any PL/SQL code or needing to write separate web-services for each of the operations / tables you work with.

We’re living in a distributed world, and this is even more true for the enterprise and the cloud era.

As much as I like the Oracle database and APEX in particular. Sooner or later you face the situation where you retrieve or store data in a non-Oracle database like Microsoft SQL Server or SAP HANA.

Basically I see it this way: APEX is an application server / development and runtime environment that happens to have it’s own repository and logic residing in an Oracle database. The data it receives, manipulates and stores can be somewhere else.
It’s all about connectivity and collaboration nowadays.

Ofcourse you can setup Oracle gateway, install ODBC and then define some database links. That is OK and even the best choice for some specific projects where you are relying on top performance with lots of data is being exchanged.

However it also really requires quite some effort until you have all that flying.  Go talk to your DBA’s and most of the time they won’t be very happy going that road. Also having a stable connection against a database on the other side of the world is kinda hard to do.

So handling the communication via RESTful services is a flexible and good way to deal with that. This is where ODATA comes into play. It’s now a standard that was originally worked on by Microsoft and SAP.

The official OData technical commitee currently includes: CA Technologies, Citrix Systems, IBM, Microsoft, Progress Software, Red Hat, SAP SE and SDL.

ODATA (V2) is the protocol used for exchanging data between SAP UI5 web applications and the SAP HANA database. That was my original motivation for looking into ODATA.

I personally like the idea using APEX as a frontend for SAP HANA, because APEX is king in rapid development. Implementing a SAP UI5 application from scratch still takes quite some effort.

Note: this blog post is not on bashing Oracle competitors.
As a matter of fact SAP HANA is really cool if you ask me. There I’ve said it 😉

Below is a good introduction video by Rainer Stropek from Austria, which explains ODATA realy well and also gives some good examples.

You can read more about the Odata protocol at http://www.odata.org/

I have opened an APEX feature request. So, if you think OData would be an interesting feature for APEX, then please vote for my request.

Filipfeature request

4 thoughts on “Why I think APEX should support OData (“SQL for the web”)

  1. Recently I have to tap into CRM 2015. It supports odata, but when we look at the risk, our skill set and cost, we picked gateway and db link. Would love to see odata support out of the box.

Leave a Reply

Your email address will not be published. Required fields are marked *