ORDS logo
GET, POST, PUT, DELETE your way in and out of Oracle Database.

Monitor Oracle Database with REST APIs – but without writing any code. Sound too good to be true?

We at Oracle have a product/technology just for you – Oracle REST Data Services, also known as ORDS.

When installing and configuring ORDS, you have the option of enabling several features:

  • Database API
  • REST Enabled SQL
  • SQL Developer Web

IF you enable SQL Developer Web, you automatically get the other 2 as well. So if you know of an environment with SQL Developer Web, you know you have access to the other features.

What is the DB API? Well, it’s approximately 600 endpoints you can enable with ORDS for your database. It’s described using the OpenAPI v3 protocol at this endpoint –

https://your-server.com/ords/admin/_/db-api/latest/metadata-catalog/openapi.json

Reading that huge JSON document isn’t very fun, but thankfully there are a ton of great tools out there. One you need to become very familiar with is Swagger.

If I feed the contents of that JSON response to editor-next.swagger.io, I get this –

YAML or JSON on the left, documented APIs with built-in REST Client on the right

Do you have an Autonomous Database subscription?

If the answer to that is ‘yes,’ then it’s really easy to have a play with this!

Steps to get your Swagger Doc

  1. login to SQL Developer Web with your ADMIN account
  2. once you’re authenticated, replace the ords/… with this:
    ords/admin/_/db-api/latest/metadata-catalog/openapi.json
  3. save that to a file
  4. go to Swagger’s website
  5. File – Import
  6. That’s it!

The DB API Endpoints require database credentials. This is described in the API docs here.

Quick and Dirty API Call

If we take one of these GET endpoints and try it from the previously browser where you authenticated your SQL Developer Web Session..it’ll work.

Cookie-based auth makes this possible, but not super-useful.

What if you want to do a POST, PUT, or DELETE? That’s really hard in a browser. Or maybe you want to not have to copy and paste a million times?

Use a REST Client

I’m using Insomnia.

Step 1 – Click on where it says ‘Insomnia’

Postman supports this kind of thing as well.

Step 2 – Click the ‘Create’ button

Choose to Import from File.

Point to that file we created earlier when we got the json back from the OpenAPI URL.

Step 3 – Voila

All of our endpoints have been imported!

This is an overview of my APEX install in my Always Free Autonomous Database.

This won’t work until we sort the Authentication bits, but first, check out where it has the ‘base_url’ bit in the address bar.

It automatically fills that bit in whenever I want to make calls.

If you want to have this work with another ORDS server/install, you can simply update that variable in Insomnia.

I don’t ever have to type this in.

Authentication

We need database credentials, and for Autonomous, our administrator account is ADMIN. I can add those here –

NOW, we are ready to go.

GET an overview of APEX in my Instance, for last 5 days

If I go back to the Swagger editor, I can see in the APEX section, I have a /statistics/overview API, and with a parameter called ‘number_of_days’

When I imported that into Insomnia, it saw the parameter definition and made it available for me.

I just need to toggle over the Query page, and activate the parameter, and give it a value, say, ‘5.’

My response shows start date of Aug 25 and end date of Aug 30…for a period of 5 days.

Let’s try one more, a POST?

I’ve done this example before, but let’s try it again, let’s export a table using Data Pump.

If we scroll back to the Swagger Editor and look, we’ll find Data Pump. There’s an entry there to POST up a job, with a helpful example!

I can take that example JSON POST Body, and tweak it to get what I want. I want to export tables that look like JOB% but aren’t named JOBS, in my HRREST schema.

Look maw, no PL/SQL code to write!

This will take a few seconds to get a response…
ORDS is telling me we created the job.

There’s a few links there with my ‘201 Created’ link.

If I follow the ‘Related’ link, I can grab the log of my job.

Oh look, it worked 🙂

Of course, we built a UI to see these jobs in SQL Developer Web…

And it’s using the exact same REST API to display my job log.

We use our own code 🙂

Jeff I looked and looked and can’t find what I need.

We have 600+ endpoints and more on the way. But maybe you need to do something the DB API doesn’t offer yet. Like…creating a new Database User.

For that we have…

REST Enabled SQL

This is your ‘get-out-of-jail-free’ card. It let’s you do ANYTHING your user can do, via a POST endpoint.

The APIs we’ve talked about previously have predefined endpoints for very specific tasks. This endpoint is wide open, it’s a SQL prompt in your web server, more or less. It’s how we execute queries/scripts/plsql in SQL Developer Web’s SQL worksheet.

And you can use it too.

I can send JSON or SQL to the endpoint, and ORDS will run it, and send you the results.

The URL is

https://your-server.com/ords/<user>/_/sql/

Except this time I can use an OAuth2 client instead to authenticate!

Read the full post here.

Kris and I had a call yesterday with a customer that needed more automation for managing their 2500+ Oracle databases. They had 15 use cases they required at a minimum to evaluate an implementation.

We of course talked about ORDS, and between the DB API and REST Enabled SQL, we satisfied all 15 requirements.

And of course, if you want something more regimented and controlled than REST Enabled SQL, you could create your own Module. We’ve shown that before for working with AWR.

Getting a list of AWR Snapshots. Read how we did this here.

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

2 Comments

  1. Hi Jeff,

    just fyi and users without Insomnia client, you can import the openapi.json file into the Thunder Client plugin in Visual Studio Code too. Setting the baseURL as an environment and it works fine.

    Cheers,
    Carsten

Write A Comment