Updated 28 May 2022
The ORDS installer has been rewritten for version 22.1, and will no longer be installable via SQL Developer going forward.
REST. Or, Representational State Transfer. It makes a lot of the INTERNET today possible.
When you see an application making PUT or GET requests over HTTP or HTTPS, that COULD be REST. REST is an architectural style. You perform actions (GET, PUT, POST, DELETE) against resources, like a TABLE.
So you do GET on employees.
Or you do a POST on employees.
What you do NOT do in REST is request a GET on DELETE_EMPLOYEES.
If you want to make your Oracle Database accessible via HTTPS, and you’re a proponent of REST, then you’re in the right place. I’m going to show you how to get started with Oracle REST Data Services (ORDS).
What is ORDS?
ORDS is a mid-tier java application that translates REST requests to database calls, and takes the database responses, result sets, and/or output and transforms them to JSON before sending them back to the client.
So, what does this have to do with SQL Developer?
SQL Developer makes it very easy to install ORDS, run ORDS, REST enable TABLES, and , develop RESTful Services.
The first part of this LONG POST (apologies), I’m going to talk about how to install and run ORDS as a standalone process using SQL Developer. The back-end of this post will very briefly show REST enabling a schema and using AUTO REST and doing your own custom RESTful Service.
If you would like to peruse some slides first, you may enjoy this:
So, if you haven’t guessed already, SQL Developer v4.1 actually ships with the ORDS bits. So now you can do a install without going back out to OTN to download something else.
Before you jump into it, you probably want to ask yourself a few questions, read the docs, and make sure you’re putting it where you really need it. But, if you’re just wanting to get something up and running, you can have it going on your laptop, just like me.
What I have on my machine is Windows 7 and a VirtualBox Oracle Linux appliance running Database 12c. I’m going to install ORDS on my Windows host, and have it configured to serve REST requests over to my VirtualBox database.
Let’s walk through a few key screens of the wizard.
The configuration files directory – that used to be the directory you’ve have to SSH into and edit a slew of XML files manually. Now SQL Developer has a few panels that allow you to configure stuff. When you deploy or upload your settings, they’ll get written to this directory.
You’ll notice finally exactly what version of ORDS you’re about to deploy.
We’re going to be possibly creating several new users. These are described in detail in the aforementioned docs.
Didn’t read the docs? Here’s a screencapture…
To create users and do what the installer is going to be doing, you’re going to need a high privileged account (SYS!):
There’s another screen for setting up default tablespaces for the new users, I’m going to skip that step.
I’m going to skip the step where we ask about the APEX_PUBLIC_USER and test its connectivity to the database.
By the way, I should mention that ORDS 3.0 is able to deploy WITHOUT Application Express. It has its own database schema now. You can optionally migrate your previously defined RESTful Services from APEX to ORDS. I’m not going to show those steps either, because again, I’m doing a quick and dirty ‘play’ install.
I’m running in Standalone mode. We also support running it with WebLogic and Tomcat. We technically support Glassfish too, but it’s deprecated, so expect that support to go away.
Write these accounts down somewhere, you’ll may them later.
These are the embedded Jetty user accounts. They get a ‘sql developer’ and ‘admin’ role – that allows you to login via HTTP and authenticate to deploy a REST Service or make a change to your ORDS configuration. In case you need to create these users later, read THIS.
There’s one final confirmation dialog that shows all of your settings and has the FINISH button.
If you click that, SQL Developer will do the database work, and startup ORDS. You can see it running in the log, and there’s a big ole STOP button in the toolbar.
Now Let’s go REST-ify Stuff! (TABLE data)
So in 5 or 10 minutes I was able to get ORDS up and running. Now I want to create a few services to test/show off the thing.
You have two options:
- Auto(matic) REST: CRUD support for tables and views. You REST enable a TABLE, we make GET, PUT, POST, DELETE handlers automatically available for querying the table, getting a single row, inserting a row, updating a row, or deleting a row.
- RESTful Services: You write all the code. ORDS does exactly what you tell it to do via the SQL or PL/SQL assigned to a Handler for your module (GET, PUT, POST, DELETE.)
First, you REST enable a SCHEMA/USER. This means you tell ORDS it’s ok to proxy-connect as THAT user to run the code behind your RESTful Services. You are also telling ORDS it’s OK to create RESTful Services and REST Enable objects in that schema. So when you run a Service or access a REST enabled table in HR – all of that will happen as HR. That means the security rules you’ve setup in the database for HR will be in play. Your code can do ANYTHING HR is granted to it.
Note two very important things here. The ALIAS. If you use the default, then everything in the URI for your REST call will EXPOSE your database user name. For example:
Now I know there’s an HR user in there somewhere. That could be bad. In fact, I would recommend you ALWAYS alias the rest enabled schema.
Second important thing, you should require Authorization. That means that any resource published under HR will be protected. ORDS will have to authorize your HTTPS requests. For demo/play mode, I leave this off. This should NEVER be disabled in real world scenarios though.
Second, you REST enable a TABLE/VIEW/PROCEDURE.
That’s done with another right-click.
If you do not alias EMPLOYEES, then accessing it via HTTPS will be done via
If we were to alias the HR schema as personnel and EMPLOYEES table as peeps then we would access it via:
Once you do this, you can do pretty much whatever you want with the table via HTTPS and GET, PUT, POST, and DELETE.
AND these calls are documented:
We give you the Swagger Docs on your EMPLOYEES table API.
For more details on AUTO, see the aforementioned link.
This is you defining the URIs and the code behind them. If you want to provide an API to query a table, then you get to write the SELECT code behind it. And, you can do all of this in SQL Developer. Just pull up the REST Data Services item in the connection tree.
You need to REST enable the schema FIRST.
Then you can do something like this:
The URI is simple/employees/ which builds onto the base /ords/hr/, and i’m running ORDS on my local machine as a standalone process, so that translates to
So when I access that URL, the following happens (more or less):
- ORDS gets the request
- Maps it to my database (one instance of ORDS can handle MANY databases)
- Finds the proper schema
- Proxy Connects as that USER (HR)
- Maps the template to a table or RESTful Services
- optionally authorizes the request – do you have the proper role/privilege?
- Finds the SQL or PL/SQL code behind the service module handler
- Runs said code
- Gets the results, transforms to JSON
- Gives the connection back to the connection pool (no longer connected as HR)
ORDS is built by the database team to support REST access for the Oracle Database. It’s included at no additional cost with your database license (FREE.) It’s supported by My Oracle Support (assuming you have a database covered by support.) It’s very powerful. It’s very easy. And if you know PL/SQL and SQL, then you can start building RESTful Services on Day 0.