Auto REST with ORDS: An Overview and What’s Next?

In the current issue of Oracle Magazine, you can read all about how to get started with the Auto REST feature of Oracle REST Data Services…or in other words, how to automatically make Tables and Views available via REST APIs.

A table or view is very easy to consider as a resource in the REST model. What ORDS does is allows you to interact with them via GET, PUT, POST, and DELETE – without writing any code. That’s why we call it ‘Auto.’

This is Where Auto REST Starts

This concept of making database resources automatically available via ORDS has started with tables and views. I think that’s the natural place TO start, but it doesn’t end there.

And there is a very good case to be made about how one could better interface with their data in the Oracle domain. I’ll let one of our advocates speak for himself…

@OracleSQLDev making it as easy as possible to use REST with tables and views. But still good to build #PLSQL APIs around them! 🙂 https://t.co/dLtCIMnUPx

— Steven Feuerstein (@sfonplsql) March 23, 2017

So, how does a stored procedure fit into this world?

A stored procedure can do anything, or even nothing! So how do we map things like a GET or PUT onto a piece of code that just executes? For an upcoming version of ORDS, we will allow you to make your stored procedures available via POST calls. Your POST call will execute the ‘Auto’ enabled PL/SQL object.

If this sounds like RPC, then that’s probably because this is what that basically is.

• Execute via POST
• Pass parameters via BODY {json}
• Output returned as {json}
• REFURSORs? CHECK
• Complex types, SDO_GEOMETRY, Intervals, Custom types? Check-check-check, and CHECK

Here’s some PL/SQL I want to have at…

CREATE OR REPLACE FUNCTION refcursor_function
  RETURN SYS_REFCURSOR
AS
  c SYS_REFCURSOR;
BEGIN
  OPEN c FOR SELECT * FROM hr.job_runs; -- i know, don't do SELECT * FROM!!!
  RETURN c;
END;

So I’ll make this available via PL/SQL API to ORDS (ORDS.ENABLE_OBJECT) or via SQL Developer…and then I can make my POST call.

You can see in the response body the REFCURSOR that’s been returned – and you’ll notice it hands timestamp and interval data types with no problems whatsoever.

I’ll go into more details once this feature is officially available. But if you already had a PL/SQL package for managing your table or all tables, you’d simply enable ORDS access to this PL/SQL, and away you’d go.

Looking for some slides?

I walk the AutoREST feature start to finish, with examples of each bit of functionality here.

Ok, but what’s next after that?

We’re working on a few – ok, make that a LOT – of things. Things like making the entire database available via REST APIs. Want a list of sessions, an ASH report, or a list of RMAN backups? That kind of stuff. Or maybe you want to be able to just execute SQL via REST.

If you’re going to be in Cleveland, or San Antonio over the next few months, you can find people like me and Kris there at Oracle conference events talking about this stuff.