Install Oracle REST Data Services w/SQL Developer

thatjeffsmith ORDS 38 Comments

Tell Others About This Story:

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.

This will launch the ORDS installer.

This will launch the ORDS installer.

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.

So the default is to deploy the ORDS that SQL Developer ships with - but you can download a newer copy and still use SQL Developer to install THAT.

So the default is to deploy the ORDS that SQL Developer ships with – but you can download a newer copy and still use SQL Developer to install THAT.

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.

The database - I'm going to deploy ORDS to my PDB, and it will ONLY serve this PDB. You can go the other way, install to CDB and serve multiple PDBs.

The database – I’m going to deploy ORDS to my PDB, and it will ONLY serve this PDB. You can go the other way, install to CDB and serve multiple PDBs.

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…

From the Docs...

From the Docs…

To create users and do what the installer is going to be doing, you’re going to need a high privileged account (SYS!):

We'll talk about the data model in a bit.

We’ll talk about the data model in a bit.

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.

Couple of obviously important questions here.

Couple of obviously important questions here.

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.

These are the users you'll be logging in with to admin ORDS and configure RESTful services.

These are the users you’ll be logging in with to admin ORDS and configure RESTful services.

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.

So now what can I do?

So now what can I do?

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:

  1. 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.
  2. 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.

Proceed with caution: you are saying allow ORDS to operate AS THIS USER.

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.

Same warnings apply here.

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.

This Swagger page isn’t just a doc, it’s also a REST client you can use to TEST the API calls.

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:

I’ve created a SIMPLE module with a EMPLOYEES template and a GET handler.

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):

  1. ORDS gets the request
  2. Maps it to my database (one instance of ORDS can handle MANY databases)
  3. Finds the proper schema
  4. Proxy Connects as that USER (HR)
  5. Maps the template to a table or RESTful Services
  6. optionally authorizes the request – do you have the proper role/privilege?
  7. Finds the SQL or PL/SQL code behind the service module handler
  8. Runs said code
  9. Gets the results, transforms to JSON
  10. 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.

Related Posts

Tell Others About This Story:

Comments 38

    1. thatjeffsmith Post
      Author
      1. 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?.

      2. thatjeffsmith Post
        Author
      3. 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

  1. 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.

    1. thatjeffsmith Post
      Author

      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.

  2. 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

  3. 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

    1. thatjeffsmith Post
      Author
  4. 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

    1. thatjeffsmith Post
      Author
  5. 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.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  6. 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

    1. thatjeffsmith Post
      Author
  7. 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 ?

    1. thatjeffsmith Post
      Author
      1. 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.

      2. thatjeffsmith Post
        Author
      3. I have this same problem. In this case, just use sqldevrest user in RESTful Services Connection.
        Best regards.
        MZ

      4. 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

  8. 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.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
      1. 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

      2. thatjeffsmith Post
        Author
  9. 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

  10. 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 ?

    1. thatjeffsmith Post
      Author
      1. 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?

      2. thatjeffsmith Post
        Author
      3. 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

      4. thatjeffsmith Post
        Author

Leave a Reply

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