Manage & Monitor Oracle Database with REST APIs.

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 –

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.

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’

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 won’t work until we sort the Authentication bits, but first, check out where it has the ‘base_url’ bit in the address bar.

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

Authentication

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

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

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!

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.

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.

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!

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.