Mostly Making oracle easy, mostly:
Search for:
In ORDS

Introducing the Database Management REST API for your Oracle Database

4 Mins Read

Oracle REST Data Services has for the past 5-6 years given you the ability to develop and deploy RESTful Services for your Oracle Database – by just writing some SQL and PL/SQL.

ORDS, in a Nutshell

Want to GET some table data out? Build a GET handler with a SELECT statement. Want to submit a sales order? Build a POST handler that calls your application PL/SQL package. And your responses are automatically transformed to {JSON} and obey all the rules of the HTTP road.

Now with ORDS version 19.1, we’re offering a new feature – the ability to manage your Oracle instances via REST calls.

This from the 19.1 README


Important Changes to Note
Database Management REST API
This release sees the introduction of the Database Management REST API. Please consult the ORDS documentation library for instructions on how to enable this feature, which is disabled by default.


https://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/ords-releasenotes-191-5458763.html

So, I don’t want our current ORDS customers to freak out, thinking by upgrading or deploying 19.1, that you’ll be exposing the guts of your database via this API. It MUST be enabled for it to be accessible, otherwise you’ll see 404’s when you attempt to call it.

The ORDS 19.1 Docs Page.

Enabling the Feature, Privileges Required

You need to add a line to your defaults.xml file:

<entry key="database.api.enabled">true</entry>

With ORDS started and this parameter enabled, you won’t get 404’s anymore. Now you’ll get 401’s.

You’ll need to REST enable a schema, and that schema needs the DBA role. Then, using Database Authentication, you can access an endpoint from the DB REST API.

Note, if you’re in a multitenant environment/Pluggable Database, your user will need the PDB_DBA role.

Note: that’s how it’s SUPPOSED to work. If you’re in a 12c of higher instance, you’ll need the PDB_DBA role – regardless of you being in a PDB or not. This will be fixed for 19.2.

Versions of the Database Supported

  • 11gR2
  • 12cR1
  • 12cR2
  • 18c
  • 19c

Obviously you can’t make calls to do pluggable operations on 11gR2, but any environment where you can configure and run ORDS, the DB API supports that database.

And speaking of PDB stuff, if you want to use the lifecyle management end points, there’s an additional amount of setup required – which I’ll cover in a follow-up post.

The API as Described by OpenAPI, Swagger Doc

You can find the entire API online here –
https://docs.oracle.com/en/database/oracle/oracle-database/19/dbrst/index.html

The tasks are spit into different functional areas:

For the moment, all but Fleet Patching are delivered via ORDS.

Let’s say I want to pull a list of objects…INDEXES…for a given SCHEMA. I’ll find a ton of GET end points under Data Dictionary – not to mention 100+ other end points available throughout the API.

A few of these could work, but I”m going to go with ‘Get all indexes’

My base URL for all my DB API REST calls will be:

 'http://localhost:8080/ords/hr/_/db-api/19.1.0/' or
 'http://localhost:8080/ords/hr/_/db-api/latest/'

Where:

  • HR – the REST enabled user the call will be ran as, also the DB user you need to use for DB authentication on the request
  • _/db-api/ – the ‘module’ or namespace for the API
  • 19.1.0 – the version of the API or
    • latest – the current version of the API that ships with ORDS install

For version 19.1, latest and 19.1.0 will be synonymous, as there’s only one version of the API today. If you want to ensure responses don’t change between end points, consider using the 19.1.0 in your URIs. If you’re cool with the latest and greatest, then latest is your cup of tea.

Making the call.

I want all the INDEXES for MDSYS.

So I’ll use the Query Parameters filtering feature to say, just give me indexes where OWNER = MDSYS

'/hr/_/db-api/latest/database/objects/indexes/?q={"$eq":{"table_owner":"MDSYS"}}'
Ta-da!

Note each item in the collection includes a link to said item, so I can traverse my collection by going to 

'_/db-api/latest/database/objects/indexes/MDSYS,UNIQUE_LAYERS'

Which will give me this:

{
  "owner": "MDSYS",
  "index_name": "UNIQUE_LAYERS",
  "index_type": "NORMAL",
  "table_owner": "MDSYS",
  "table_name": "SDO_GEOM_METADATA_TABLE",
  "table_type": "TABLE",
  "uniqueness": "UNIQUE",
  "compression": "DISABLED",
  "prefix_length": null,
  "tablespace_name": "SYSAUX",
  "ini_trans": 2,
  "max_trans": 255,
  "initial_extent": null,
  "next_extent": null,
  "min_extents": null,
  "max_extents": null,
  "pct_increase": null,
  "pct_threshold": null,
  "include_column": null,
  "freelists": null,
  "freelist_groups": null,
  "pct_free": 10,
  "logging": "YES",
  "blevel": 0,
  "leaf_blocks": 0,
  "distinct_keys": 0,
  "avg_leaf_blocks_per_key": 0,
  "avg_data_blocks_per_key": 0,
  "clustering_factor": 0,
  "status": "VALID",
  "num_rows": 0,
  "sample_size": 0,
  "last_analyzed": "2017-01-26T20:11:48Z",
  "degree": "1",
  "instances": "1",
  "partitioned": "NO",
  "temporary": "N",
  "generated": "N",
  "secondary": "N",
  "buffer_pool": "DEFAULT",
  "flash_cache": "DEFAULT",
  "cell_flash_cache": "DEFAULT",
  "user_stats": "NO",
  "duration": null,
  "pct_direct_access": null,
  "ityp_owner": null,
  "ityp_name": null,
  "parameters": null,
  "global_stats": "YES",
  "domidx_status": null,
  "domidx_opstatus": null,
  "funcidx_status": null,
  "join_index": "NO",
  "iot_redundant_pkey_elim": "NO",
  "dropped": "NO",
  "visibility": "VISIBLE",
  "domidx_management": null,
  "segment_created": "NO",
  "orphaned_entries": "NO",
  "indexing": "FULL",
  "links": [
    {
      "rel": "self",
      "href": "http://localhost:8080/ords/hr/_/db-api/latest/database/objects/indexes/MDSYS,UNIQUE_LAYERS"
    },
    {
      "rel": "describedby",
      "href": "http://localhost:8080/ords/hr/_/db-api/latest/metadata-catalog/"
    },
    {
      "rel": "collection",
      "href": "http://localhost:8080/ords/hr/_/db-api/latest/database/objects/indexes/"
    }
  ]
}

Pulling up the Swagger Doc via ORDS

We have metadata-catalog and openapi end points for the DB API as well.

'http://localhost:8080/ords/hr/_/db-api/latest/metadata-catalog/openapi.json'

You can paste the response from this call into something like editor.swagger.io and generate a full test client for your DB API

Nicer than reading the raw JSON for sure.

What’s Next?

I”ll be doing more demonstrations and examples here of course. But you can also expect this API to grow substantially as each new version of ORDS is released. We already have a good start on the Database Creation Assistant, and the Data Pump team is going to build out a complete api for that, so it’s not all GET’s around 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.

Related Posts

13 Comments

  1. Deepak Reply

    ORDS is really cool and it cuts the middle layer of writing jax-rs or a spring boot project just to get the data out in a restful way. A lot of our customers use oracle and we use our product https://metamug.com/console to expose apis over multiple data sources. We can definately couple ords along with our services to our customrers.

    Thanks for the article jeff.

  2. Johan Louwers Reply

    @Dietmar
    on your question / comment; “What are the actual use cases for this functionality?”.

    I would argue that one of the most important actual use cases is the integration of the database in CI/CD pipelines. By having this option / interface you now enable DevOps teams to more easily interact with a database, deploy databases, gather information and interact with it during application deployment / database deployment from a CI/CD pipeline.

    It is true that all that is now available via the REST API can be done in other ways, however,…. this provides a level of interaction that is very close to the way most DevOps teams like to interact with a service (the database service in this specific case).

    In my opinion the creation of this REST API is one of the most promising new developments for DevOps teams and teams who maintain enterprise wide deployment pipelines. Very often not DBAs or database experts however in need of a way to quickly integrate an enterprise grade database as part of a (micro-service) application.

    Regards,
    Johan.

  3. Tony Reply

    Hi Jeff,

    I’m new to ORDS so I may be missing something basic but I’m struggling with this. Following the docs, I’ve granted DBA to PDBADMIN and ORDS enabled the PDBADMIN schema (incidentally, ‘exec’ is missing from the docs for this command).

    The example URLs in the docs use https which immediately fails for me. If I use http I get the 401 error you mention but no matter what I try I cannot get past this.
    The example URL from the docs that provides an OpenAPI V3 document does work successfully.

    I think I need a bit of an idiot’s guide for this. Is this the kind of stuff you hope to be posting in the near future or is there any chance the documentation will be improved further?

    I’m using these docs: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/19.1/aelig/enabling-ords-database-api.html

    Thanks.
    Tony.

  4. Dietmar Aust Reply

    The property is: jdbc.auth.enabled
    It was in my previous post … but the xml got masked … including the property itself ;).

    • thatjeffsmith Post Author

      Grrr, so yes, db auth has to be enabled, and there’s two ways to get that

      jdbc.auth.enabled or
      restEnabledSql.active

      I need to have the DOCS updated, thanks Dietmar, again!

    • Dietmar Aust

      Hi Jeff,

      thanks for clarifying it :).

      Enable dbauth with:
      jdbc.auth.enabled or
      restEnabledSql.active

      First I thought it might have been my testing environment. But even after a clean install it was still like this.

      I have another question, what kind of authentication is possible here, just db authentication or also first party authentication or oauth2?

      Thanks,
      ~Dietmar.

    • thatjeffsmith Post Author

      Authenticated mid tier users with the SQL Administrator role or database authentication. No oauth2.

      I’m submitting updates to the Docs team today to make this more clear based on your feedback ASAP.

    • Dietmar

      Got it. And now we also have the third option : Using the prehook functionality to authenticate a user and return the role “SQL Administrator” :).

      Another question, please.

      What are the actual use cases for this functionality? It makes total sense in a hosted environment. It can be connected to client side tools like SQL Developer to do remote administration over https instead of sqlnet. I get that. You could also script stuff using RESTful calls. Ok.

      But are there other use cases in an on-premise environment? What was the major driver and what do on-premise customers get out of it? Thanks again.

    • thatjeffsmith Post Author

      Yet another interface for managing your environments and automating things in a DevOps world.

      For some folks that starts AND ends with cURL, REST, and JSON.

      Also, not touching the db directly to see how the db is going is also a plus, no new connection to spin up to see why CPU is high…there’s already a connection there. But that’s just a benefit of connection pools vs REST I suppose.

  5. Dietmar Aust Reply

    Hi Jeff,

    thanks for the post. In the documentation it was REALLY hard to find how to actually USE the api.

    Also, I have struggled a bit because I was missing this requirement in defaults.xml
    true

    Without that, the db authentication with the basic authentication http header will not work.
    Yes, it is somewhat implicitly clear (and I got that after a while), would just be helpful if it was more transparent that this is a requirement.

    Another thing, your index query filter did not work for me:
    q={“$eq”:”table_owner”:”MDSYS”}}’

    I had to use:
    q={“$eq”:{“table_owner”:”MDSYS”}}

    There is a curly bracket missing.

    Thanks,
    ~Dietmar.

    • thatjeffsmith Post Author

      I think part of your comment got lost…

      ‘Also, I have struggled a bit because I was missing this requirement in defaults.xml
      true’

      Which property?

      I’ll fix that typo right now.

Write A Comment