Deploying Oracle REST Data Services from Oracle SQL Developer

thatjeffsmith ORDS 33 Comments

Tell Others About This Story:

REST is Representational State Transfer for short. It’s how a lot of the Internet works for long, and I’m not going to tell that story. But.

When you see an application making PUT or GET requests over HTTP or HTTPS, that’s REST.

Anyways, before I make myself sound any more ignorant, let’s talk about Oracle REST Data Services (ORDS).

ORDS is a engine that accepts REST requests and marshalls those requests to the Oracle Database. It could be a SQL statement that’s called or a stored procedure that’s executed. The output, is sent back to the calling application in either JSON or CSV format.

So, what does this have to do with SQL Developer?

For a couple of releases now, you’ve been able to develop and deploy RESTful services to ORDS and also to configure ORDS.

Starting with version 4.1 of SQL Developer, you can now also:

  • Install and configure ORDS
  • Auto-REST enable a schema or selected tables

I’m mostly going to talk about the first of those bullet points to keep this in blog-post domain and not full-on whitepaper. Plus, I can talk about the REST enablement stuff in a follow-up post 🙂

Here’s a quick recap of REST and some slides on installing ORDS and auto-rest enabling tables…

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:

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. It’s great for DEV. It’s not supported for production. Kris talks here about where you can and maybe should deploy ORDS.

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 need them later.

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?

I’m Thirsty

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.

To define and deploy a RESTful Service, I need to open the Development panel.

Your Panel will be Blank, and that's OK

Your Panel will be Blank, and that’s OK

And now we need to connect to ORDS.

I told you to remember those username/passwords from the installer, right?

I told you to remember those username/passwords from the installer, right?

We’re going to login as the ‘ORDS RESTful Services User.’ Use the password you supplied in the installer on Step 8, see the screenshot above.

When you CONNECT, it pulls down the existing RESTful Services to the editor. If you have anything defined yet, that’s not been deployed to ORDS, you’ll lose it. So connect before you start building stuff.

Now we’re going to define a new Module and at least one Template.

Right click and say ‘New RESTful Services.’

You’ll now have a ‘Modules’ and a ‘Privileges’ tree node, we’re going to be looking at the Modules.

I am right-clicking on Modules, and adding my ‘Thirsty/’

This module will by default have at least one ‘Resource Template.’

You can add as many additional Resource Templates as you want per module.

In the screenshot below I’ve created a Resource Template called ‘untappd’ which I’ll use to pull back my Untappd beer checkins based on my review rating.

The default Method is ‘GET’, but we could also build ones for POST, PUT, and DELETE.

JSON, CSV? How many records?

JSON, CSV? How many records?

After you finish the wizard, you can double-click on the Resource Template -> Method to get the following editor.

I really like this part, if you know SQL or PL/SQL, you can create a RESTful Service.

I really like this part, if you know SQL or PL/SQL, you can create a RESTful Service.

You start by write a SQL statement. You get a fully-functional worksheet – syntax tips, object completion, and obviously you can ‘test’ your code to make sure you get back what you want to get back.

Now, you also need to define what the service sends back, and how.

You probably don't want to send back 10,000 JSON records to your iPhone all at once. So the default is batches of 25 records.

You probably don’t want to send back 10,000 JSON records to your iPhone all at once. So the default is batches of 25 records.

I’m lazy. So are our developers. They even put a neat little ‘copy to clipboard’ button you can use to test your service.

When you think it’s ready, deploy the Module back to ORDS.

Right-click on the Module to upload it to ORDS

Right-click on the Module to upload it to ORDS

You should see this, don’t blink, it’s fast!

Now we're cooking with gas.

Now we’re cooking with gas.

Remember that URI we copied to our clipboard?

Pop that into your browser and go at it.

I'm running a Chrome plugin, jsonview, to make the json 'pretty.'

I’m running a Chrome plugin, jsonview, to make the json ‘pretty.’

A key thing when working with results, is being able to navigate said result-set. So let’s scroll down to ‘the bottom.’

{
name: "Samuel Adams Winter Lager",
brewery: "Boston Beer Company (Samuel Adams)",
style: "Winter Lager",
abv: 0.06,
first_had: "2011-01-20T17:00:00Z",
rating: 5,
averagerating: 3.67,
totalcheckins: 3
}
],
hasMore: true,
limit: 25,
offset: 0,
count: 25,
links: [
{
rel: "self",
href: "http://localhost:8080/ords/demo/thirsty/untappd/"
},
{
rel: "describedby",
href: "http://localhost:8080/ords/demo/metadata-catalog/thirsty/untappd/"
},
{
rel: "first",
href: "http://localhost:8080/ords/demo/thirsty/untappd/"
},
{
rel: "next",
href: "http://localhost:8080/ords/demo/thirsty/untappd/?offset=25"
}
]
}

So my app knows how to get to the next 25 records, and go back to the previous set.

Cool?

It gets better, but we’ll go into the ‘automatic’ REST enablement stuff in a later post.

Make a table available via REST, or do an entire SCHEMA, your choice.

Make a table available via REST, or do an entire SCHEMA, your choice.

Oh, and our new ORDS user data model. This is where the services, etc. are stored.

We used foreign keys and everything :)

We used foreign keys and everything 🙂

Where’s our Thirsty module? It’s in the Modules table.

I split the editor, then pulled open the Data tab up top and the new Model tab on the bottom

I split the editor, then pulled open the Data tab up top and the new Model tab on the bottom

Tell Others About This Story:

Comments 33

  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.

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

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

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

          2. thatjeffsmith Post
            Author

Leave a Reply

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