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
<entry key="debug.printDebugToScreen">true</entry>
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.
Our friend Oracle-Base made this quite simple with this great example/overview.
Similar Topics
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.
Wait, what about PerfHub/SQL Monitoring?
14 Comments
Hello Jeff,
Please I need your assistance in solving “404 Not Found”. I’m trying to use the ORDS url to view PDF in Apex
I followed a video published by the Oracle Developer channel on youtube (PDF Viewer in Oracle APEX) -Leah Bracken.
https://www.youtube.com/watch?v=PoAl_TA0TxA
My API works in Postman. I’m able to see the PDF. In Apex, I get ” 404 Not Found”.
Thank you.
Leah doesn’t work here anymore, but I just watched the video.
She’s having you use javascript and the fetch api to call the ORDS REST API to pull down the PDF doc from a BLOB in the database.
You say it’s works in Postman, but you get a 404 in APEX.
This tells me that the API is fine, but …
Have you opened your browser developer tools while the APEX app is going, and seeing exactly what GET request is being made by your app when the 404 pops up? You need to debug that, and make sure you’re calling the endpoint correctly.
Hi Jeff,
We are running ORDS standalone behind a NGINX reverse proxy. Calling applications can (optionally) send a custom header with a correlation ID that gets logged in our NGINX logs. If no correlation id is included in the request, NGINX creates a new one. How can we forward a correlation ID to ORDS so it uses the forwarded ID in the ORDS logs ?
I have tried to set the “X-Request-Id” header and forward it to ORDS but that did not work.
This is needed to easily track requests through the various layers. My questions are (1) is this possible ? (2) if not possible yet, is it possible to implement this feature ?
you just need to configure nginx to include/fwd the custom header(s), right? ORDS is just sitting there taking whatever you give it, right?
Thanks for your prompt response. Apologies that my question was not very clear. Yes, I can forward the custom header that contains the correlation id to ORDS. That is not the problem. What I would like to know is how can I make ORDS log this correlation id into its error log ? Replace the ECID with the custom correlation id or log both the ECID and the custom correlation id into the ORDS error log.
The purpose of this is if someone comes to me and says I made a request with correleation ID “abc” and got a response 555, what is wrong. Then I want to able to search the ORDS error log for “abc” to find the specific request.
I hope that the above clarifies and makes sense.
Kind regards,
Pieter
Hi Jeff,
Do you have an example of receiving a correlation/trace ID in ORDS so that ECID is populated with it?
Cheers
Nigel
Sorry we don’t include them for the actual query, could cause hard parses and hurt performance, so won’t be in the trc files.
Thanks for your prompt response Jeff.
We have an external integration server that creates a GUID to be used for correlation. Are you saying this 3rd party GUID can’t be passed into ORDS so it is recorded in DBA_HIST_ACTIVE_SESS_HISTORY.ecid? I understood from your ECID post that the ECID could be passed from layer to layer OR have I completely misunderstood?
Im not sure this is relevant but we are using ORDS standalone server if that makes any difference.
Cheers
Nigel
You asked if the ecid would show in a trace file, no.
Now you’re asking if you can inject a custom ecid or marker into your rest APIs dynamically?
Yes two questions based on “Hey, what’s that ecid looking string thingy?”
In the post it says
“So you can correlate errors from your end user/apps and your error logs in ORDS.”
and
“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.
”
Based on those two snippets my question is how do I pass a correlation ID from my end user app(.NET) through to ORDS so that ORDS logs it in the log file and/or ASH views as shown in the above post.
Thanks in advance.
Cheers
Nigel
Hi Jeff,
Thanks for the quick response. I really appreciate the pointers you gave me early on.
This is the error I get when running the page in developer mode.
Uncaught TypeError: Cannot read properties of undefined (reading ‘id’)
at Object.javascriptFunction (pdf-viewer?session=13223944939690:251:246)
at e.doAction (desktop_all.min.js?v=23.1.0:24:5085)
at e.doActions (desktop_all.min.js?v=23.1.0:24:3515)
at HTMLDocument. (desktop_all.min.js?v=23.1.0:24:2191)
at Function.each (desktop_all.min.js?v=23.1.0:2:3003)
at S.fn.init.each (desktop_all.min.js?v=23.1.0:2:1481)
at e.actions (desktop_all.min.js?v=23.1.0:24:2031)
at desktop_all.min.js?v=23.1.0:24:1164
at Array.forEach ()
at e.init (desktop_all.min.js?v=23.1.0:24:205)
:8080/ords/maya/resume/doc/#ID#:1
Failed to load resource: the server responded with a status of 404 (Not Found)”
It seems it can’t pass the ID .
This is a full URL with 404.
http://23c.kasuah.com:8080/ords/maya/resume/doc/#ID#.
When I replaced the “#ID#” with a valid ID in the browser, the PDF is previewed.
Thank you.
I’m guessing the problem is with how the apex form is sending the id value to the JS.
Hiya Jeff,
Thanks for the write up.
Is there a way to get ORDS to log everything it sees coming and sends back:
– method
– Status code
– headers
– payload
– response
– etc
This is logging for our API , not creating an API sending back that data… getting ORDS ( or Webslogic) do the heavy lifting , not have each API dealing with the OWA_UTIL data… I guess something similar to what laravel/telescope would do.
If standalone, then enabling logging (Jetty) would grab most of that.
WLS or Tomcat would do it otherwise.
See what you get for free before writing potentially expensive code.