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.
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.
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:
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.
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"}}'
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
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.
13 Comments
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.
@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.
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.
Version of Oracle Database?
There’s an omission in the docs, that I’m working to fix now, that in a 12 or higher version database, you must also grant PDB_DBA to the REST enabled schema to access the DB API endpoints.
Which exact URLs are you trying to access and are getting 401s vs what are working?
Hi Jeff,
Sorry, I hadn’t spotted your reply. I’m using 19.3. I granted that privilege but there’s no difference. I’ve got detailed steps I’ve followed to build the whole thing that I can share if that’ll help? I suspect I’m missing something simple in the ORDS set up.
URL that works and returns the metadata catalog is:
http://:8080/ords//pdbadmin/_/db-api/stable/metadata-catalog/openapi.json
Example of URL giving 401:
http://:8080/ords//pdbadmin/_/db-api/stable/database/storage/tablespaces/
Thanks.
Tony.
The property is: jdbc.auth.enabled
It was in my previous post … but the xml got masked … including the property itself ;).
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!
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.
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.
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.
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.
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.
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.