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
This is much better, or less bad than a 500.
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
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;
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.
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
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:
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
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!
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
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?
Hi Jeff,
Is there a chance to change HTTP response code to 400 in case of issues? ORDS status code works as described above.
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
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
Use stop_apex_engine to stop further processing and immediately exit to avoid adding additional HTML code to the HTTP buffer –
https://docs.oracle.com/en/database/oracle/apex/22.1/aeapi/STOP_APEX_ENGINE-Procedure.html
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
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
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!
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?
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