What do do when your beautiful REST API for inserting records into your Oracle Database isn’t working? Or maybe it calls a PL/SQL program or simply does a SELECT * FROM?
What you generally see when there are problems:
Detailed errors in your Responses
If you want easier to read errors, you can enable this in your defaults or pool specific XML file
With ORDS restarted, you’ll now see this in your error messages –
It’s very important that you do not use this production, else you risk leaking implementation details to your REST API consumers. They probably don’t need to know your TABLE names, the SQL behind your APIs, etc.
If you have access to where ORDS is running
I have ORDS running as a standalone process on my Mac, as I make bad requests to ORDS, I can see lots of fun information in my console.
Obviously this requires access to your mid-tiers where ORDS is running. If you’re in a managed environment, that might not be possible. One idea we have of making this easier for debugging ORDS issues in the Autonomous Database Cloud Services we have on the Oracle Cloud is adding a ‘debug’ mode for your schema or specific APIs…stay tuned on that.
What does ORDS see when requests come in?
This post germinated with this simple question. I’m currently helping a customer that is using a ‘man in the middle’ service where the request to ORDS gets filtered through another end point and then sent onto ORDS.
ORDS isn’t happy with the request, and if the customer makes the same request directly to ORDS, it works. So clearly something is getting ‘messed up’ in the middle.
So I asked, what exactly is ORDS getting? The webserver logs don’t record the POST request bodies…so what to do?
Creating a Debug/Fake Endpoint
I’ve created a REST API that does nothing but print back out what came in via the request headers and body. Basically a DBMS_OUTPUT for my HTTPS stuff coming into ORDS.
-- Generated by ORDS REST Data Services 21.1.0.b0901431 -- Schema: HR Date: Tue Aug 31 01:54:44 2021 -- BEGIN ORDS.ENABLE_SCHEMA( p_enabled => TRUE, p_schema => 'HR', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'hr', p_auto_rest_auth => FALSE); ORDS.DEFINE_MODULE( p_module_name => 'debug', p_base_path => '/debug/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'debug', p_pattern => 'echo', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'debug', p_pattern => 'echo', p_method => 'POST', p_source_type => 'plsql/block', p_items_per_page => 25, p_mimes_allowed => '', p_comments => NULL, p_source => 'DECLARE request_body clob := :body_text; vLENGTH integer; BEGIN vlength := dbms_lob.getlength(request_body); owa_util.mime_header(ccontent_type => ''text/plain'', bclose_header => true, ccharset => ''ISO-8859-4''); HTP.p(''List from PRINT_CGI_ENV including <br /> terminator:''); HTP.p(''=======================================================''); OWA_UTIL.print_cgi_env; htp.p(''Content-length: '' || vlength); HTP.p(''Request Body:''); HTP.p(''=======================================================''); htp.p(request_body); END;'); COMMIT; END;
Now, when I go run this, I get back the list of headers and the body.
Error Response 555 – we’ll return this when YOUR code is having issues. It’s at this point that looking at the SQL/PLSQL behind your API is very important.
JSON vs HTML error responses. All of the examples I’ve shown so far have the ‘pretty’ printed error responses. You might prefer not to see those in your text based logs.
Using the Accept header on your request, you can tell ORDS you only want JSON back.
This can also be set in the ORDS config (defaults or pool-specific) using the error.responseFormat setting (Docs):
Hey, what’s that ecid looking string thingy?
ECID = Execution Context ID. This can be passed from layer to layer. Or in this case, from the webserver/java servlet (ORDS) onto the Database! So when they’re a problem, you can correlate what’s not working, where.
So where is it in the database? If the session is still active, you could see it in V/GV$SESSION – but ORDS requests come and go quickly.
You may be more likely to see them in your Active Session History (ASH) views.