This topic is so epic, it has a part two, deeper look (more examples, mostly..
In my Oracle REST Services Demos I always show things working exactly as planned.
But what happens when your user does something your program doesn’t expect?
Or what happens when you code does something you don’t expect? In the web (HTTP) world, you get a 500.
10.5.1 500 Internal Server Error
The server encountered an unexpected condition which prevented it from fulfilling the request.
Thankfully, PL/SQL provides EXCEPTIONS. We can say, when this bad thing happens, run this code instead. And really, as developers we should be good enough to expect certain problems to occur and to plan for them.
When something doesn’t work in your database code attached to a RESTful Service, you get a HTTP Status code of 500.
This isn’t friendly.
Let’s look at an un-handled exception in ORDS from the user perspective.
Bad
This looks quite unprofessional. Not the warm and fuzzy feel you want when doing business with a partner or vendor. What if that was the response I got when trying to buy the WiFi on my airline with my credit card? (A completely fake/did not really happen in real life scenario which happened to me yesterday).
Less Bad
We have a proper status code and a message returned from the server (ORDS).
Now, a perfect scenario would include having some client-side validation of the inputs, preventing me from sending bad values in the first place. But even then, it’s best to plan for the common scenarios. For example, what if your service is available OUTSIDE the intended application where there is no validation of inputs happening?
Anyways, let’s see how I made this happen. And it’s quite simple really.
The Exception
When you try to select a string into a number in Oracle, you get a ORA-06502. And if we look into the ORDS log, we can see this pop out when I make the bad call w/o handling the exception:
Caused by: Error : 6502, Position : 0, Sql = declare x integer; begin select :1 into x from dual; :2 := 'You passed in this number: ' || x; end;, OriginalSql = declare x integer; begin select ? into x from dual; ? := 'You passed in this number: ' || x; end;, Error Msg = ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 4
So, I need to account for the ORA-06502 in my handler code. Here’s how I’ve done that.
declare x integer; not_a_number EXCEPTION; PRAGMA EXCEPTION_INIT(not_a_number, -06502); begin select :num_in into x from dual; :string_out := 'You passed in this number: ' || x; EXCEPTION WHEN not_a_number THEN :string_out := 'That was NOT a number!'; :status := 400; end;
I’ve set the out going message/response to something a bit more helpful than a ¯\_(ツ)_/¯ and I’ve set the response to a HTTP 400, which means:
‘10.4.1 400 Bad Request – The request could not be understood by the server due to malformed syntax. The client SHOULD NOT repeat the request without modifications.‘
Note to pass the status back I had to set a parameter for my handler code for ‘X-ORDS-STATUS-CODE’
I’m Getting 500’s but I Don’t Know Why?
You need to get to the ORDS standard output logs. If you’re running ORDS, hopefully you’re logging that somewhere. If it’s Tomcat (Catalina) or WLS, they have places for that. If it’s in standalone mode, you need to redirect that out to a file yourself.
Or…per the troubleshooting guide (DOCS!), you can do this:
Restart ORDS, run your request again:
By the way, DO NOT DO THIS IN PROD. You will be exposing details of your database to people you do not want to. Like those mysql errors you see when you try to hit a website and it overloads their system. Oh, that’s MySQL, and they have a table named ‘XYZ’…ahhh. Yeah, that’s bad.
The Code
Here’s how I called it:
curl --request GET \ --url http://localhost:8080/ords/hr/exceptions/unhandled \ --header 'authorization: Basic Y29sbTpvcmFjbGU=' \ --header 'num_in: hello'
My ORDS handler pulls the value out of the header (num_in) and tries to convert it to a number (x) – which works just fine if your string just happens to be a number already. I then pass back a message saying, hey, thanks for passing me that number, and here it is again just so you know I got it correctly.
Here’s the module for my exception, handled and un-handled.
-- Generated by Oracle SQL Developer REST Data Services 18.3.0.276.0148
-- Exported REST Definitions from ORDS Schema Version 18.3.0.r2701456
-- Schema: HR Date: Fri Oct 26 11:14:42 EDT 2018
--
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 => 'exceptions',
p_base_path => '/exceptions/',
p_items_per_page => 25,
p_status => 'PUBLISHED',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'exceptions',
p_pattern => 'handled',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'exceptions',
p_pattern => 'handled',
p_method => 'GET',
p_source_type => 'plsql/block',
p_items_per_page => 25,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'declare
x integer;
not_a_number EXCEPTION;
PRAGMA EXCEPTION_INIT(not_a_number, -06502);
begin
select :num_in into x from dual;
:string_out := ''You passed in this number: '' || x;
EXCEPTION
WHEN not_a_number THEN
:string_out := ''That was NOT a number!'';
:status := 400;
end;'
);
ORDS.DEFINE_PARAMETER(
p_module_name => 'exceptions',
p_pattern => 'handled',
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 => 'exceptions',
p_pattern => 'handled',
p_method => 'GET',
p_name => 'num_in',
p_bind_variable_name => 'num_in',
p_source_type => 'HEADER',
p_param_type => 'STRING',
p_access_method => 'IN',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'exceptions',
p_pattern => 'handled',
p_method => 'GET',
p_name => 'string_out',
p_bind_variable_name => 'string_out',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
ORDS.DEFINE_TEMPLATE(
p_module_name => 'exceptions',
p_pattern => 'unhandled',
p_priority => 0,
p_etag_type => 'HASH',
p_etag_query => NULL,
p_comments => NULL);
ORDS.DEFINE_HANDLER(
p_module_name => 'exceptions',
p_pattern => 'unhandled',
p_method => 'GET',
p_source_type => 'plsql/block',
p_items_per_page => 25,
p_mimes_allowed => '',
p_comments => NULL,
p_source =>
'declare
x integer;
begin
select :num_in into x from dual;
:string_out := ''You passed in this number: '' || x;
end;'
);
ORDS.DEFINE_PARAMETER(
p_module_name => 'exceptions',
p_pattern => 'unhandled',
p_method => 'GET',
p_name => 'num_in',
p_bind_variable_name => 'num_in',
p_source_type => 'HEADER',
p_param_type => 'STRING',
p_access_method => 'IN',
p_comments => NULL);
ORDS.DEFINE_PARAMETER(
p_module_name => 'exceptions',
p_pattern => 'unhandled',
p_method => 'GET',
p_name => 'string_out',
p_bind_variable_name => 'string_out',
p_source_type => 'RESPONSE',
p_param_type => 'STRING',
p_access_method => 'OUT',
p_comments => NULL);
COMMIT;
END;
18 Comments
hi thatjeffsmith ,
I want to show the debug trace (SQL error) and to hide Stack Trace
how to modify ords 21 to do what I want above
21? That’s 3+ years old.
THe options are discussed in the docs, you can experiment with the parameters and see what happens as you turn things on and off.
Hi Jeff,
I’m learning more and more about the ORDS product and can see it being our main solution to expose a RESTful API to an existing Oracle system we have that is heavily PL/SQL driven.
One area that I can’t seem to find much information about is the benefit and usage of parameter definitions.
I have a service that takes a parameter (mpan) as part of the URI, e.g.
Select method,base_path, pattern from user_ords_services:
GET /dubs/v1/ address/:mpan/
http://mprsapp:8080/ords/dist/mprs-ws/dubs/v1/address/9910000100009
which invokes the following PL/SQL Package:
MPRS_REST_API_PKG.get_dubs_request_data(p_data_set=>’ADDRESS’,p_mpan_core=>:mpan); end;
This works fine without any parameters being defined for the template.
After changing the PL/SQL procedure p_mpan_core parameter type from VARCHAR2 to NUMBERI and invoking:
http://mprsapp:8080/ords/dist/mprs-ws/dubs/v1/address/ADSDSDS
it throws an data type conversion error, which is expected.
However, my question is this: What is the value of explicitly defining a parameter for the template as it does not seem to actually add any value. I would have thought ORDS could automatically trap this data type mis-match and return the appropriate error. At a minimum I would expect this to at least be a configurable option.
Can’t help get the feeling that I’m missing something here?
Thanks
Ian
your stored procedure will need to do the error checking, catch the bad input, then return the appropriate 3XX HTTP status code and message on the response
Hi Jeff,
I’m looking to use OWA_UTIL.REDIRECT_URL in my ORDS preHook but it does not seem to be working. If I call the re-directed URL directly from the browser all works as expected but not when re-directed from the preHook trigger. In my preHook function I’m using the following:
htp.init();
OWA_UTIL.REDIRECT_URL(curl=>OWA_UTIL.get_cgi_env(‘X-APEX-BASE’)||’error/?auditId=10′,bclose_header=>TRUE);
return FALSE;
I’ve tried with both including and excluding the htp.init() call. Am I missing something?
Thanks
Ian
Hi Jeff, please could you help:
logged in –> https://apex.oracle.com/pls/apex/f?p=4850:140:712782300120707:::140:P0_SELECTED_NODE,P140_HANDLER_ID:TH719284,719284
and calling https://apex.oracle.com/pls/apex/notix/hr/employees/ from web browser (Google Chrome Version 78.0.3904.108)
still getting the respose:
500 Internal Server Error
An unexpected error with the following message occurred: InternalServerException [statusCode=500, reasons=[]]
The detailed info (F12 debug console):
Request URL: https://apex.oracle.com/pls/apex/notix/hr/employees/
Request Method: GET
Status Code: 500
Remote Address: 23.4.252.132:443
Referrer Policy: no-referrer-when-downgrade
content-encoding: gzip
content-language: en
content-length: 6756
content-type: text/html
date: Mon, 02 Dec 2019 16:22:26 GMT
error-reason: error=”error”; error_description*=UTF-8”An%20unexpected%20error%20with%20the%20following%20message%20occurred%3a%20InternalServerException%20%5bstatusCode%3d500%2c%20reasons%3d%5b%5d%5d
status: 500
vary: Accept-Encoding
x-oracle-dms-ecid: 005aBfwEuPYFw0KimTtlWJ0005cK0005B7
x-oracle-dms-rid: 0:1
:authority: apex.oracle.com
:method: GET
:path: /pls/apex/notix/hr/employees/
:scheme: https
accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3
accept-encoding: gzip, deflate, br
accept-language: cs-CZ,cs;q=0.9
cache-control: max-age=0
cookie: ORA_WWV_APP_63621=ORA_WWV-SdhcwrSnw_Q-V4tcULbf4EvH; ORA_WWV_RAC_INSTANCE=8; ORA_WWV_REMEMBER_LANG=en; ORA_WWV_USER_63113759365424=ORA_WWV-JnJfuQIrv6i4IHFd6SInfZ8u; s_fid=69332DD202F89FB2-0AC823C294326A24; s_cc=true; ELOQUA=GUID=0F24EF50D1DD425F9BF29423D718FEE3; _gcl_au=1.1.731100591.1574352222; ORA_WWW_MRKT=v:1~g:902F3A7C49A1933BE050E60AD07F09EA~t:NOT_FOUND~c:LP05; ORA_WWW_PERSONALIZE=v:1~i:NOT_FOUND~r:NOT_FOUND~g:EMEA~l:cs~cs:NOT_FOUND~cn:NOTIX; ORASSO_AUTH_HINT=v1.0~20191122000413; ORA_UCM_INFO=3~902F3A7C49A1933BE050E60AD07F09EA~slavomir~ruzicka~slavomir.ruzicka@notix.cz; OAMAuthnHintCookie=1; _referrer_og=https%3A%2F%2Fwww.google.com%2F; _jsuid=2621332948; xdVisitorId=1002SBkuBRbyJZOaOKuqfBUzwOFRMHXrPXg-g2XyX2J-kfc6778; atgRecVisitorId=1002SBkuBRbyJZOaOKuqfBUzwOFRMHXrPXg-g2XyX2J-kfc6778; _abck=33D149B213F5CECFAB51D0F47070C235~0~YAAQTjwQAiIbjX1uAQAAtel8rAIB/kFYRP66xpvi/nIVSTSUMtAewf8VtmpxV0bdIaHmxmP4xwsZLFNxScyfG9I+34wtSP+5z7/ISngwsP3O6KfSOGAxTkgssknWk3UTZA9SNiiVDdlCFUr1JflWZz5zEFK1IqMpYkjaSR7cmtnyPKJTa32Gp7yLhHjIcBak1A92qBkKRYMtERhaH+Yoq/1UlpSTY+ptJ5H2YEcEzlvmH4AWCXpzHN4il0Fx5Zv+bBRh7gNRI5CCqEJ8R3phpgHrCekpkOQsffbDspLftQ/58IZcNojBWiZQcIc6OuhjDRtrp/9aAQ==~-1~-1~-1; bm_sz=24CF2C57CF21010E0524EDA013E08EB1~YAAQVzwQAme0/XduAQAAMTPBxgXGGnkJI+GRW2KTGpsILMEO05n7rZdmwhysmEntJWycNrZ+tVa1hq7MZaOJEK0PfXwi6LiTwhHE8hntyL1eNOZE0BzTwVX9EASOog5JFLlsHcJcPG5aQJA1RhGxlaUZcMubryOpVWIO9AlJiQzMh8gFWQpoyQjj3Wz+BgFe; mmapi.store.p.0=%7B%22mmparams.d%22%3A%7B%7D%2C%22mmparams.p%22%3A%7B%22pd%22%3A%221606838885405%7C%5C%22871854928%7CCAAAAApVAwCA4HQRaxI3nAABEgABQgDGZtGKAwBvZ6zQQXfXSD9e1tOndNdIAAAAAP%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FAAZEaXJlY3QBaxIDAAAAAAAAAAAA%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FAAAAAAAAAAFF%5C%22%22%2C%22bid%22%3A%221575303485061%7C%5C%22fravwcgus01%5C%22%22%2C%22srv%22%3A%221606838885469%7C%5C%22fravwcgus01%5C%22%22%2C%22uat%22%3A%221606838894715%7C%7B%5C%22Industry%5C%22%3A%5C%22NoValue%5C%22%2C%5C%22Role%5C%22%3A%5C%22NoValue%5C%22%2C%5C%22CompanySize%5C%22%3A%5C%22NoValue%5C%22%2C%5C%22CampaignID%5C%22%3A%5C%22NotSet%5C%22%2C%5C%22CategoryID%5C%22%3A%5C%22NotSet%5C%22%7D%22%7D%7D; mmapi.store.s.0=%7B%22mmparams.d%22%3A%7B%7D%2C%22mmparams.p%22%3A%7B%7D%7D; s_nr=1575303558106-Repeat; gpw_e24=no%20value; s_sq=%5B%5BB%5D%5D; notice_gdpr_prefs=0,1,2:cb8350a2759273dccf1e483791e6f8fd; notice_preferences=2:cb8350a2759273dccf1e483791e6f8fd; cmapi_gtm_bl=; cmapi_cookie_privacy=permit 1,2,3; atgRecSessionId=-0HHaMenJG1DZMWBHjW9i7V0phzZ7Jz0LkJFZTbs3F4BEQLs8dUa!-1367573188!-1615582263; RT=”sl=2&ss=k3omnddl&tt=lpj&bcn=%2F%2F686eb504.akstat.io%2F&dm=oracle.com&si=7b056f4a-d474-4fd4-b07c-69ec1aebb21b&z=1″
sec-fetch-mode: navigate
sec-fetch-site: none
sec-fetch-user: ?1
upgrade-insecure-requests: 1
user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36
? thank you
Slavomir
Hi Jeff, Is it possible to display a json file instead of an html? For errors 500, 404 and 400 for example, we try the modification in the defaults.xml but only accept * .html files
404’s come from your webserver, you can do custom error page responses via apache config for example.
Not sure if you can have a plsql handler exception have the response content-type set to application/json but I’m thinking it IS possible
Hi Jeff another question, in standalone would it be possible to answer with a json for 400 404 500 status codes?
For our GET endpoints we have build some views.
Is there any way to keep the “out-of-the-box” experience when using collection queries (pager, meta description) but als have the possibility to craft e custom error respons json object?
Cheers!
Like what sort of custom error?
The short answer is probably not.
For example when using the “q”-parameter with an unknown field:
?q={“unknownfield”: {“$null”: null}}
You will receive a 500 Internal Server Error.
In the stack trace the reason is shown: ORA-00904 invalid identifier
The error respons should have been 400: “unknownfield” invalid identifier
Version of ORDS?
Here’s a working REST address..
http://localhost:8080/ords/hr/abstract/peeps/?q={%22eq%22:{%22last_name%22:%22Smith%22}}
If I change this (instead of last_name which is valid to lname, which is not) to
http://localhost:8080/ords/hr/abstract/peeps/?q={%22eq%22:{%22lname%22:%22Smith%22}}
I get back a 403 and ‘The request could not be processed because a function referenced by the SQL statement being evaluated is not accessible or does not exist’
I’m on the version of ORDS we’re ABOUT to ship for 19.2…so it’s possible you’re hitting a bug we already fixed.
This is also what I would like to know. We would like the power and convenience of the query handlers (rather than pl/sql) so we benefit from the low-code pagination, ‘q’ search syntax and so on (which are really great). However, should anything go wrong, an incorrect date format is entered or anything unexpected, and you get a 500 error which is quite unprofessional as you state in this post.
It seems that these features are only good for a demo but not in a real production scenario. Unless there is some way to set the status via a GET request?
No, you should be getting 4XX errors back, so this would be a bug. Assuming I can reproduce it, will file a bug for you.
Thanks for reply. It works now. I already used X-ORDS-STATUS-CODE but my ORDS versions still requires ‘X-APEX-STATUS-CODE’
Hi Jeff,
Is there a chance to change HTTP response code to 400 in case of issues? ORDS status code works as described above.
Thanks
Reinhard
Yes, use exceptions, trap them, change the status code to something appropriate.
I’m sorry if I’m not understanding your question.