I’ve talked about trapping PL/SQL Exceptions in your REST APIs before – you can catch up on that here. But, you can stay RIGHT HERE – this post covers the same material, just in more detail.

Got a question from someone new to ORDS, let’s pretend their name is ‘Kartik.’ They wanted help figuring out why their ORDS Exception block wasn’t properly setting the Response Header STATUS code, in this case a 400.

Let’s look at a scenario.

Kartik want the API user to submit a value on their HTTP GET request. This can’t be done in the request body, because there is no such thing for GETs. So instead the user is left to using query string parameters or headers.

Kartik wants to use headers, and they’ll call it ‘id’ – and it will have a corresponding bind variable in the ORDS handler code as ‘:id’

This will be used in a SELECT statement – to find an employee.

If the employee is found, print a nice message.

If the employee isn’t found, print a a nice message to help the API user figure out ‘what is wrong.’

What could go wrong?

Why couldn’t Kartik’s API find an employee? Well, maybe the requested ID doesn’t exist. Normally a resource that can’t be found results in a HTTP 404 But Kartik instead wants to tell the requestor that their ‘request is bad.’

Or maybe..what if the user doesn’t send in a number at all? What if they send in a string like ‘ABC’? For sure the database won’t like that. But instead of puking out a 500 error response, Kartik wants ‘catch’ that exception. and send a nice message with a proper 400/bad request message.

Here’s what that looks like –

Working Request, 200 OK

Good data in, good data out.

I’ve not got your number, 400 Bad Request

Yes, we could just let this 404, but we’re not going to.

Jenny’s not answering to that number anymore, Tommy.

You’ve not got it, 400 Bad Request

I’m using Insomnia. It’s a prettier REST client than POSTMAN.

Our Handler Code

Yes, I’m going to share the actual code so you can copy and paste…but please read, understand, and use better code than me in PROD.

DECLARE
    favorite_emp CLOB;
    emp_id INTEGER;
BEGIN
    emp_id := :id;
    IF emp_id IS NULL
    THEN
        RAISE NO_DATA_FOUND;
    ELSE
        SELECT first_name || ' ' || last_name || ', ' || job_id
        INTO   favorite_emp
        FROM   employees
        WHERE employee_id = emp_id;
 
        OWA_UTIL.mime_header('text/plain');  
        HTP.print('And our favorite employee, is...');
        HTP.print(favorite_emp);
    END IF;
 
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            :string_out := 'Sorry cannot find who you are looking for, get with the program';
            :status := 400;
        WHEN VALUE_ERROR
        THEN
            :string_out := 'This ain''t even a number, do you even data?';
            :status := 400;
        WHEN OTHERS
        THEN
            :string_out := 'Something bad happened, but we are not sure what, here is the system response: ' || SQLERRM;
            :status := 400;
END;

So what’s important to understand in here?

I’m going to refer to the bind variable more than once in my code, so use a local variable to read it in.

Once I have ’emp_id’ set to :id coming in from my request, I can refer to it as many times as I need to.

Now setting the STATUS, we’ve done this before, many times. But here it is again. My advice – use a HTTP RESPONSE CODE that will make sense. Using improper status codes will confuse your API subscribers, and they won’t like using your API. That’s a lose-lose scenario.

I don’t want a 200 OK or a 500, I want 400 if something doesn’t work because of an honest mistake.

X-ORDS-STATUS-CODE is reserved for setting the Response Header status code in ORDS.

You’ll see I’m also trapping the ‘VALUE_ERROR’ exception. Why is that?

I’m not using an INTEGER for the ‘id’ bind variable on the header, I’m using a STRING. So if the user passes in a ‘1’ or an ‘A’, the code won’t fail out of the starter’s gate. Instead, it will fail when we try to pass that value into the SQL predicate, and the database can’t get ‘Jenny’ to translate into an error.

The ORA- code it would throw is an ‘ORA-06502’, which translates as ‘VALUE_ERROR’ for my exception.

And the WHEN OTHERS, what’s with that?

Well, maybe I didn’t account for every kind of problem in my code’s logic. Users WILL find your ‘holes.’ When that happens, help them.

Once way to do that is to trap any thing not otherwise accounted for with a ‘WHEN OTHERS,’ and since we’re in an exception block we can reference the ‘SQLERRM’ function.

The function SQLERRM returns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODESQLERRM with no argument is useful only in an exception handler. Outside a handler, SQLERRM with no argument always returns the message normal, successful completion.

Oracle Docs….

There’s actually a more preferred method, and that’s discussed hereDBMS_UTILITY.FORMAT_ERROR_STACK.

If we want to see the WHEN OTHERS THEN PRINT THER ERROR logic in play, I can just comment out one of my previous EXCEPTION HANDLER clauses.

I commented out my VALUE_ERROR so it falls to OTHERS.

Printing out the error stack when all else fails – not a HORRIBLE idea in your exception handling. If you don’t want your users to see it, log that into a TABLE with an ID that you return to your API subscriber. They can then contact support with the ID for them to look up and troubleshoot/debug.

The Complete Module Code, Handler with Parameters

The only thing you’ll need is a table that looks like ‘HR.EMPLOYEES’ and a REST Enabled Schema. Oh, and don’t forget to secure your APIs before you publish them to prod.

 
-- Generated by ORDS REST Data Services 21.1.0.b0901431
-- Schema: HR  Date: Wed Sep 01 09:11:21 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    => 'header.exceptions',
      p_base_path      => '/header_exceptions/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);
 
  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'header.exceptions',
      p_pattern        => 'kartik',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => NULL);
 
  ORDS.DEFINE_HANDLER(
      p_module_name    => 'header.exceptions',
      p_pattern        => 'kartik',
      p_method         => 'GET',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => '',
      p_comments       => NULL,
      p_source         => 
'DECLARE
    favorite_emp CLOB;
    emp_id integer;
BEGIN
    emp_id := :id;
    IF emp_id IS NULL
    THEN
        RAISE NO_DATA_FOUND;
    ELSE
        SELECT first_name || '' '' || last_name || '', '' || job_id
        INTO   favorite_emp
        FROM   employees
        WHERE employee_id = emp_id;
 
        OWA_UTIL.mime_header(''text/plain'');  
        HTP.print(''And our favorite employee, is...'');
        HTP.print(favorite_emp);
    END IF;
 
    EXCEPTION
        WHEN NO_DATA_FOUND
        THEN
            :string_out := ''Sorry cannot find who you are looking for, get with the program'';
            :status := 400;
        WHEN VALUE_ERROR
        THEN
            :string_out := ''This ain''''t even a number, do you even data?'';
            :status := 400;
        WHEN OTHERS
        THEN
            :string_out := ''Something bad happened, but we are not sure what, here is the system response: '' || sqlerrm;
            :status := 400;
END;');
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'header.exceptions',
      p_pattern            => 'kartik',
      p_method             => 'GET',
      p_name               => 'X-ORDS-STATUS-CODE',
      p_bind_variable_name => 'status',
      p_source_type        => 'HEADER',
      p_param_type         => 'INT',
      p_access_method      => 'OUT',
      p_comments           => NULL);
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'header.exceptions',
      p_pattern            => 'kartik',
      p_method             => 'GET',
      p_name               => 'message',
      p_bind_variable_name => 'string_out',
      p_source_type        => 'RESPONSE',
      p_param_type         => 'STRING',
      p_access_method      => 'OUT',
      p_comments           => NULL);
 
  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'header.exceptions',
      p_pattern            => 'kartik',
      p_method             => 'GET',
      p_name               => 'id',
      p_bind_variable_name => 'id',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);
 
 
 
COMMIT;
 
END;
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.

14 Comments

  1. jerome Millot Reply

    Hi Jeff,

    When I’m using a bind parameter like :status_code, I see it in the swagger in the input parameter list !
    What is missing ?

    thanks

  2. Dear Jeff smith

    how to handler username & password -basic authorized ords api inside pl/sql

    for example : “errorMessage”: “ORA-20001: Invalid username or password.”

  3. I’m generating a swagger doc from a module that uses htp.p as above.

    When I use this, the open_api_catalog shows the 200 response as
    responses:
    ‘200’:
    description: The queried record.
    schema:
    type: object
    properties:
    message:
    type: string

    is it possible to get the detailed properties for the 200 response when it is generated by htp.p ?

    • How would we do that? I’d like to have more flexible/customizable swagger doc in general.

  4. Hi Jeff,

    Thank you for that great post. I have a problem with the response text. If I return status 200 my custom response text is shown correctly. If I pass status 400 then the response “Bad Request” and not my custom error message is shown. I use the Insomnia client. Is this a problem of Insomnia?

    • 400 Bad Request is correct.

      You have to look on the BODY of your response, which there has your message.

    • Hi Jeff,

      That’s a misunderstandig. I know than 400 = Bad Request. But in Insomnia I don’t see my custom error code in the body window (preview). There is just the message “Bad Request” instead of my custom message.

    • I’m sorry.

      ORDS Source Code:

      declare
      l_status number;
      l_response varchar2(4000);
      begin

      begin
      my_package.do_request(utl_raw.cast_to_varchar2(:body), l_status, l_response);
      exception when others then
      l_status := 400;
      l_response := substr(dbms_utility.format_error_stack(), 1, 256);
      end;

      :response := l_response;
      :status := l_status;

      end;

      ORDS Parameters:
      – status = X-ORDS-STATUS-CODE (OUT, HTTP HEADER, INTEGER)
      – response = message (OUT, RESPONSE, STRING)

      And now in the body of the Insomnia client there is not :response but “Bad Request”.

    • Hi Jeff,

      I solved the problem. I had to change some settings in the Microsoft IIS. On the “Error Pages” of the website I had to change the “Error Responses” behavior to “Detailed errors” (https://forums.iis.net/t/1213176.aspx).

  5. Hi Jeff,
    Thank you for sharing this valuable information. This helped me a lot !
    Everything works for me except the below portion of the code.

    IF emp_id IS NULL
    THEN
    RAISE NO_DATA_FOUND;

    I still get 404 NOT FOUND if I do not pass any value for :id in the URL. Can you help me on this

    • We’re using headers to pass values not the URL, right?

      If I do a GET on ‘http://localhost:8080/ords/hr/header_exceptions/kartik’ and I don’t include the ‘id’ Header with a number, I get a 400 Bad Request with my custom message
      “Sorry cannot find who you are looking for, get with the program”

Write A Comment