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:
Installing ORDS
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.)
AUTO REST
Full discussion of AUTO REST here: slides, examples, Oracle Magazine article.
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:
GET /ords/hr/employees/
GET /ords/hr/demo/beers/
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
/ords/hr/employees/
If we were to alias the HR schema as personnel and EMPLOYEES table as peeps then we would access it via:
/ords/personnel/peeps/
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:
GET /ords/hr/open-api-catalog/employees/
We give you the Swagger Docs on your EMPLOYEES table API.
For more details on AUTO, see the aforementioned link.
RESTful Services
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
http://localhost/ords/hr/simple/employees/
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)
I go into much more detail on defining REST services in the SQL Developer tree here.
In Summary
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.
46 Comments
Is it possible to confire ORDS and Oracle database two different servers for oracle rest data service ? I am getting problem to configure this.
Please answer as soon as possible.
Thanks
You can have two ords for one database or one ords for two databases.
What are you looking for?
This is very useful website to me and works for me. But I can’t configure token authorization.If there have an article ,please share , It will be very helpful to me.
thanks
‘configure token auth’ – you mean OAuth2?
I want to run ords and main database different servers . My main database will separate from ords. I want to connect them remotely..Is it possible ? I know ords needs local database to install but my main database will be remote server.
I need help .. Is it possible ?
I tried to configure ORDS. And it is up and running. I am able to fetch the localhost page. But in my SQL developer, I am not able to get the ‘Rest Services’ option when I right-click on my schema. I am not able to figure out the problem here.
version of ORDS and SQL Developer?
If you look in your database, do you see both a ORDS_PUBLIC_USER and an ORDS_METADATA schema/user?
Nice
Hi Jeff, where can I find ORDS Metadata Table? I can’t find it. Is there a way to check stored JSON files on the DB?
It’s not table, it’s an entire schema: ORDS_METADATA.
As for the JSON files, can you go into more details?
Oh, so how can I connect to that Schema?.
I want to understand ORDS architecture, how it does things in the background. I know SODA works with ORDS but how?.
You only need a privileged user to be able to build a data model based on that schema.
Hi Jeff, I need some help. I already created 2 GET services, now I’m trying to create a PUT service to update an existing row and I can’t make it work. When I run the query on SQL Developer works fine, but from Postman I don’t get any response,
it stays loading infinitely. Can you help me please?
This is the guide I’m using: http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/ords/r30/Insert_Update_Delete/Insert_Update_Delete.html#section4
My Postman: https://prnt.sc/ijepzb
I have Oracle Application Express running from Oracle XE database installed on my desktop as well as SQL Developer installed on my desktop.
How can i enable REST and ORDS for this setup?
Do i need to have the Oracle Enterprise database rather than the XE database?
XE also uses the 8080 port.
How do i find out if REST services and ORDS already enabled on my Oracle XE databases and/or SQL developer?
Please advice.
Lots to say, but I’m on vacation. Basically, I doubt you have ORDS going. If you run the installer, it’ll tell you if it’s already there and config for your xe db. Also, you can run it in any port you want.
Jeff,
I tried that initially but the REST node tree under the DB Schema does not have the option to create the module and the handlers. It only has the Refresh option when I click on the Rest Data Services node.
Thanks,
PG
Jeff,
I tried that initially but the REST node tree under the DB Schema does not have the option to create the module and the handlers. It only has the Refresh option when I click on the Rest Data Services node.
Thanks,
Pani
I’d advise you to delete your ords schema and reinstall ORDS… something is wrong…you could check the ords log for the details on your 500 too
Jeff, Still the same error. This time tried to create the GET handler vi the REST Services node
Rest Developement->REST Data Services->Modules->->->GET handler
Please advise
No. No no.
Connect to your DB as the schema you rest enabled.
In the tree, expand the REST node. Create and save your handler from there.
I followed your instructions to the T but when I try to login to RESTful Services as sqldevrest the same way you did I get an error: Proxy list must not be empty.
Get v4.2 and go straight to the db and work on your REST Modules there.
how can you insert data into table which you have just got? using rest
if you REST enabled the SCHEMA/table – then you can POST new records up using JSON. It’s full CRUD support.
when i try to open the URI, it’s throwing below error
oracle.dbtools.common.jdbc.ConnectionPoolException: The pool named: apex_al does not exist
Sounds like the connection pool to the database isn’t going – did you check the output from when you started ORDS? Did the pools get created A-OK?
I have created a resource template with a pl sql stored procedure call and also defined what the service sends back and how as mentioned in this article, so I came upto that level and then when I right mouse click on module in order to deploy the module back to ORDS, that upload option is disable in the menu, so I can not upload, how can I get fix this issue ?
is your connection avail/valid? can you download a module?
no, I can not download a module as I have not uploaded any(couldn’t uploaded ).
Regarding my connection ,
in Rest Development view panel, when I try to make a Restful services connection, I am getting a authentication failed alert with “Cannot connect to local.
HTTP/1.1 404 Not Found” message.
I can connect to my database(xe) in connection view panel and DBA view panel.
connecting to ords != connecting to the database
I have this same problem. In this case, just use sqldevrest user in RESTful Services Connection.
Best regards.
MZ
Hi try This
BEGIN
ORDS.enable_schema(
p_enabled => TRUE,
p_schema => ‘DB-USER-NAME’,
p_url_mapping_type => ‘BASE_PATH’,
p_url_mapping_pattern => ‘testuser1’,
p_auto_rest_auth => FALSE
);
COMMIT;
END;
/
then try to connect it is working
Jeff, great article.
If you have existing PL/SQL procedures that used HTP/HTF to return HTML using the legacy mod_plsql, how would you configure access them using ORDS? Or is that even possible?
Thanks for any help/direction.
rest enabled PL/SQL and RESTful services that run PL/SQL blocks that return cursors will be supported in the next version of ORDS – stay tuned
I am getting following error during upload
error occurred uploading module expense. status code: 500 server error
You probably need to check the ORDS server logs, or if you’re running ORDS via SQL Dev, you can see the ORDS log right there in SQLDev.
Thanks for your reply. I created one that is working fine in localhost. When
1. Change localhost to public IP of aws ec2 it is not working
2. New rest data service module on new table not working. Giving 500 Internal Server Error
Please help to solve this 2 problems.
Thanks
ORDS is running on AWS, or your DB is, or both?
I installed REST stanndalone without the PL/SQL Gateway and I get 404 – not found. I tried to look in support and in forums and it looks like ORDs 3 is NOT working without the PL/SQL Gateway. I am asking you before I open SR. Thanks
When create a parameter I received : 500 Internal Server Error .
Looking in details i see error – primary key constraint failed – null.
No way to working with parameters with sql developers.
Could you agree or reject this ?
Kudos for having a real data model! Now we just need to work on those autolayout options…
lots of relationships there from a few driving tables, not sure there’s an easy math solution there
Agree – probably not an EASY solution as it would require box resizing to get things lined up neatly. But you should be able to at least imitate Barker notation and get the crows flying the same direction (ideally north and/or west).
If it was easy then the other guys would have done it by now. But your guys are better, right?
Everything’s easy if you’re properly motivated 🙂
Jeff, We have installed/deployed ORDS on the Weblogic server and using SQL Developer 4.2 to create the REST ful webservices on the database views. Created the module and added a template with GET handler and getting the below error while trying to upload the module/template to ORDS. Please advise
Error occurred uploading Module dashbaord.
Status Code: 500 Internal Server Error
1 counts of IllegalAnnotationExceptions
Don’t use the rest panel, use the connection tree and the REST Services node to add your GET handler.