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:

You’re literally not doing it right, but…what exactly?

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 –

The same information you would see in your webserver/mid-tier logs for ORDS.

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.

I could have also caught this at ‘design time’ if I had tested my query before publishing it.

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.

I can see the query parameters, headers, the method, URI, the body, all kinds of good stuff.

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.

555 – your bad

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.

Ta-da.

This can also be set in the ORDS config (defaults or pool-specific) using the error.responseFormat setting (Docs):

This is in the “Understanding Configurable Parameters” section of the docs.

Hey, what’s that ecid looking string thingy?

So you can correlate errors from your end user/apps and your error logs in ORDS.

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.

Voila.
thatjeffsmith
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.

Write A Comment

RSS
Follow by Email
LinkedIn
Share