Preventing 500 Status Codes with Oracle REST Data Services

thatjeffsmith ORDS 0 Comments

Tell Others About This Story:

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

Boo!

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

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’

This is new for 18.3 of ORDS, the X-APEX codes are being deprecated.

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:

DO NOT DO THIS IN PROD.

Restart ORDS, run your request again:

DO NOT DO THIS IN PROD.

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;

Related Posts

Tell Others About This Story:

Leave a Reply

Your email address will not be published. Required fields are marked *