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
I’ve not got your number, 400 Bad Request
Yes, we could just let this 404, but we’re not going to.
You’ve not got it, 400 Bad Request
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?
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.
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
Oracle Docs….SQLERRMreturns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value ofSQLCODE.SQLERRMwith no argument is useful only in an exception handler. Outside a handler,SQLERRMwith no argument always returns the message normal, successful completion.
There’s actually a more preferred method, and that’s discussed here – DBMS_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.
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;
14 Comments
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
Status code is an implicit bind, it’s available for any REST API.
Dear Jeff smith
how to handler username & password -basic authorized ords api inside pl/sql
for example : “errorMessage”: “ORA-20001: Invalid username or password.”
How do you mean “inside pl/sql?”
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.
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 know what my code does…but I can’t see your code. So…?
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).
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”