Have you ever wanted to enable REST API debug messages for ORDS? Maybe you’re building or debugging an API, and you don’t have access to the back-end webserver where the ORDS logs are stashed. Wouldn’t it be nice if you could just print those errors ‘to the screen?’
Well, now you can! In this post I’ll show how this can be used for Oracle Autonomous Database Cloud Services, but this will work for any ORDS 22.3 and higher environment, Cloud or no Cloud.
Oracle Autonomous Database is an Oracle-managed service on Oracle Cloud Infrastructure (OCI). It comes with Oracle REST Data Services, which powers things like:
- SQL Developer Web
- APEX
- PL/SQL Web Gateway
- Data Studio
- SODA for REST (REST APIs for JSON Document Store)
- Mongo Listener for Oracle Database
- and so much more!
Since ORDS is managed by our Cloud Ops team, that means you don’t have to worry about it. But it also means you can’t really make any configuration changes to ORDS.
No, I want my cake! Ok, you can optionally setup a customer-managed ORDS on an OCI Compute node, and configure it to be your web listener for your Autonomous Database. At this point, you can make ANY ORDS configuration changes you want. We even updated the installer so it’s easier now in version 22.3 (DOCS)
But, what if I told you that you no longer need to run your own ORDS, to make SOME configuration changes?
That’s now the case for 22.3 – and 22.3 is now active in Oracle Autonomous Shared instances, worldwide in OCI.
If you see the release notes, you’ll find this line as a ‘new feature’ –
Provide PL/SQL API to force ORDS into debug mode
What this actually means
For a specific schema, I can set ORDS configuration settings, like
debug.printDebugToScreen
This is normally set at the ORDS or Pool configuration level, but now I can OVERRIDE that setting for a particular REST Enabled Schema with the ORDS PL/SQL API.
Here’s what it looks like on the response side of an ORDS REST API when this is set to ‘false’ –
That’s some info to go on, but not much. And in fact, we don’t really want to expose the deep, gory details of what went wrong on the back-end.
Except, in case when we do want to…because we’re doing development work in our Autonomous instance.
The Docs show how to enable ORDS Debug, but I’ll show how to do it it here as well.
Enable REST API debug messages for a specific schema
I have an HR application schema, and I have REST APIs there that I want to actively develop and debug. And my developer needs access to the error logs/stacks, which aren’t available in OCI for Autonomous Database.
So what I will do for them is run this code block, and then have them wait for, at most, about 10 minutes:
begin
ords_admin.set_property(
p_schema => 'HR',
p_key => 'debug.printDebugToScreen',
p_value => 'true'
);
end;
/To run this code block, or to use the ORDS_ADMIN PL/SQL API, you need the ‘ORDS_ADMINISTRATOR_ROLE.’ If you don’t, fall back to the Docs link, and you can see how to flip this switch for your own account.
Once the connections have expired their ‘reuse’ properties and I can be assured my HTTPS calls will get ‘fresh’ sessions in the database, I’ll see my response change quite dramatically when something goes ‘wrong.’
Now, the most important information, as in, why did my database code…not work??…can be found at the bottom of the Stack Trace.
Scrolling down, I can see this –
Now, when your developer is ‘done’ with their development or debugging, you should turn the ORDS schema level configuration off. You can use the UNSET command as shown in the Docs, you can just set the parameter to ‘false.’
Previously I was suggesting to folks they use a local docker/VirtualBox development instance to build/debug their REST APIs, but now you have more flexibility to do everything in Autonomous.
A few notes, caveats
This feature won’t just enable debug messages on ORDS REST APIs calls. It’ll enable Debug and Error stacks for ANYTHING going through that REST enabled schema.
I’ve done this post to demonstrate setting this for Autonomous Databases, but this is an ORDS configuration feature. In other words, it can be utilized for ANY ORDS version 22.3 and higher installations.
When it comes to debugging your REST APIs in general, I have some more tips. Like, did you know you can trace errors back via ECIDs?
If you turn something off, don’t be surprised when it quits working
One of the settings you have access to via this PL/SQL API is ‘feature.sdw’ – so you could turn off SQL Developer Web access for a REST Enabled schema/user.
That would mean their REST APIs would be available, but they wouldn’t have access to the SQL Developer Web app.
If you turn this off and try to login…you’re going to get 404s. All of the APIs that power SQL Developer Web will no longer be available.
You can check the status for a schema and any user defined ORDS properties with the ‘USER_ORDS_PROPERTIES’ view.
Note that if you turn off REST Enabled SQL but NOT SQL Developer Web, you’re going to have a Web App where some of the most important features are non-functional.
