How to Keep the Response Body with Non-2xx Responses in UTL_HTTP

February 25, 2019

in Exceptions, PL/SQL, UTL packages

Keep the Response Body with Non-2xx Responses in PL/SQL

When UTL_HTTP web service calls nicely return 2xx status codes, all is well with the world. Things get tricky when a web service call encounters an error and sends back a non-2xx response. How should the calling PL/SQL code handle this scenario? Can we read the response body from PL/SQL in case of error?

Here’s an overview of web service error handling options available in PL/SQL UTL_HTTP, and how to make use of SET_RESPONSE_ERROR_CHECK to keep the response body with non-200 responses.

UTL_HTTP.SET_RESPONSE_ERROR_CHECK

SET_RESPONSE_ERROR_CHECK is a procedure in package UTL_HTTP. It determines whether or not UTL_HTTP.GET_RESPONSE will raise a PL/SQL exception when the web service returns an error status code  (4xx or 5xx).

  • If SET_RESPONSE_ERROR_CHECK is set to TRUE, UTL_HTTP.GET_RESPONSE raises a PL/SQL exception HTTP_CLIENT_ERROR (for 4xx) or HTTP_SERVER_ERROR (for 5xx).

  • If SET_RESPONSE_ERROR_CHECK is set to FALSE, UTL_HTTP.GET_RESPONSE does not raise a PL/SQL exception.

By default, SET_RESPONSE_ERROR_CHECK is FALSE. With this setting, it is possible to read the HTTP response from PL/SQL even if the HTTP status code returned from the web service indicates an error (4xx or 5xx).

SET_RESPONSE_ERROR_CHECK TRUE or FALSE: Which should be used?

We may choose to configure SET_RESPONSE_ERROR_CHECK as TRUE or FALSE depending on factors such as:

  • Knowledge of the web service being called: What are the possible return statuses? What would be the content of the response body in case of error?

    If a web service contains important error details in the response body, it would make sense to configure SET_RESPONSE_ERROR_CHECK as FALSE and let the PL/SQL program read and report the response.

  • Functional interpretation of return statuses: Does the web service error code truly represent an application error? “404 Not Found” may mean an invalid URL. It may also mean that a GET web service call returned no data and that’s 200…er, OK.

    Whether we propagate web service status of HTTP_NOT_FOUND as a PL/SQL exception or not would be guided by what it represents functionally.

  • The overall solution: Should the web service error be quietly handled and logged, letting the rest of the program proceed? Should the error trigger an alternate path in the code? Should it bring the entire processing to a halt?

    How we design exception handling for web service calls with SET_RESPONSE_ERROR_CHECK would be driven by how this piece of code fits into the larger picture.

The following demo shows how PL/SQL program flow changes based on the TRUE/FALSE value configured for SET_RESPONSE_ERROR_CHECK. This should help you decide the approach to follow in your solution.

Demo Description: REST API GET Call via PL/SQL

For the demo, we will use a simple GET call to the math.js REST service.

HTTP GET URL http://api.mathjs.org/v4/ 
Parameter “expr” = the arithmetic expression to be evaluated

Webservice expected responses:

Response Status Response Body
SUCCESS 200 Result of the expression e.g. if expr = 2+3, response is 5.
FAILURE 400 Error message

To test the behavior with different values in SET_RESPONSE_ERROR_CHECK, we will

  1. Write two variants of a procedure call_restapi to call the REST GET request from PL/SQL: one with SET_RESPONSE_ERROR_CHECK = TRUE, another with SET_RESPONSE_ERROR_CHECK = FALSE.

  2. Test SUCCESS scenario: Execute the procedure variants with a valid value of “expr” — this should show identical behavior regardless of the value of SET_RESPONSE_ERROR_CHECK.

  3. Test FAILURE scenario: Execute the procedure variants with an invalid value of “expr” — this should show that:

    • with SET_RESPONSE_ERROR_CHECK set to TRUE, the procedure raises a PL/SQL exception when return status is 400.
    • with SET_RESPONSE_ERROR_CHECK set to FALSE, the procedure completes successfully without a PL/SQL exception. The response body can be read in the procedure.

Demo Scripts: Procedure with SET_RESPONSE_ERROR_CHECK TRUE/FALSE, Success Scenario Test, Failure Scenario Test

1. Procedure with SET_RESPONSE_ERROR_CHECK TRUE/FALSE

PL/SQL procedure call_restapi uses UTL_HTTP to call the REST GET API. The code now has SET_RESPONSE_ERROR_CHECK = TRUE. We will toggle its value as TRUE/FALSE for the tests.

-- REST API call demo with exception handling.
-- Evaluate a mathemathical expression via GET
-- request from PLSQL
create or replace procedure calc_restapi 
    (expression in varchar2)
is
  url           varchar2(50) := 'http://api.mathjs.org/v4/';
  request       UTL_HTTP.req;
  response      UTL_HTTP.resp;
  response_clob CLOB;
  response_buf  varchar2(32767);

begin
  -- Begin the GET request
  request := UTL_HTTP.begin_request
           (url || '?expr='|| expression
         , 'GET', 'HTTP/1.1');
   
  -- Response error check configuration
  -- TRUE => PL/SQL exception in case of response error
  -- FALSE => no PL/SQL exception
  UTL_HTTP.set_response_error_check (true);
  
  response := UTL_HTTP.get_response(request);
  
  -- Flow reaches here if a 4xx/5xx error occurs when
  -- set_response_error_check is FALSE
  if (response.status_code = UTL_HTTP.HTTP_OK) then
    dbms_output.put_line('Success: Received OK response');
  else
    dbms_output.put_line
    ('Failure: Received non-OK response: '
    ||response.status_code||' '||response.reason_phrase);
  end if;
 
  -- Create temporary LOB to hold the HTTP response
  DBMS_LOB.createtemporary(response_clob, FALSE);
  
  -- Loop to read response body chunk by chunk into CLOB
  begin
    loop
      UTL_HTTP.read_text(response
       , response_buf, 32766);
      DBMS_LOB.writeappend (response_clob
       , LENGTH(response_buf), response_buf);  
    end loop;
    
    DBMS_LOB.freetemporary(response_clob);
  exception
    when UTL_HTTP.end_of_body then
      UTL_HTTP.end_response(response);
  end;   

  dbms_output.put_line('Response : '
    ||substr(response_clob,1,200));
exception
  -- Flow reaches here if a 4xx/5xx error occurs when
  -- set_response_error_check is TRUE
  when others then
    dbms_output.put_line('Error: '
    || response.status_code ||' '
    || UTL_HTTP.GET_DETAILED_SQLCODE ||' ' 
    || substr(UTL_HTTP.GET_DETAILED_SQLERRM,1,200));
end;
/

2. SUCCESS Scenario Test Script

PL/SQL script to execute call_restapi with a valid arithmetic expression as input: this should return status 200 OK. The response body should contain the result of the arithmetic expression.

The expected response body is the result of the expression — 8 in this case.

-- Success case: status code 200 OK
-- Response body: result of expression
exec calc_restapi ('2*(7-3)');

2. FAILURE Scenario Test Script

PL/SQL script to execute call_restapi with an invalid arithmetic expression as input: this should return status 400 Bad Request. The response body should contain the result of the arithmetic expression.

The expected response body is the details of the error in the input expression.

-- Failure case: 4xx error
-- Response body: error details
-- SET_RESPONSE_ERROR_CHECK = TRUE
exec calc_restapi ('a');

Let’s see the demo in action.

    PL/SQL REST API GET Call with SET_RESPONSE_ERROR_CHECK  = TRUE

    1. Compile procedure with SET_RESPONSE_ERROR_CHECK TRUE

    SQL> -- REST API call demo with exception handling.
SQL> -- Evaluate a mathemathical expression via GET
SQL> -- request from PLSQL
SQL> -- SET_RESPONSE_ERROR_CHECK = TRUE
SQL> create or replace procedure calc_restapi
  2      (expression in varchar2)
  3  is
  4    url           varchar2(50) := 'http://api.mathjs.org/v4/';
  5    request       UTL_HTTP.req;
  6    response      UTL_HTTP.resp;
  7    response_clob CLOB;
  8    response_buf  varchar2(32767);
  9
 10  begin
 11    -- Begin the GET request
 12    request := UTL_HTTP.begin_request
 13             (url || '?expr='|| expression
 14           , 'GET', 'HTTP/1.1');
 15
 16    -- Response error check configuration
 17    -- TRUE => PL/SQL exception in case of response error
 18    UTL_HTTP.set_response_error_check (true);
 19
 20    response := UTL_HTTP.get_response(request);
 21
 22    -- Flow reaches here if a 4xx/5xx error occurs when
 23    -- set_response_error_check is FALSE
 24    if (response.status_code = UTL_HTTP.HTTP_OK) then
 25      dbms_output.put_line('Success: Received OK response');
 26    else
 27      dbms_output.put_line
 28      ('Failure: Received non-OK response: '
 29      ||response.status_code||' '||response.reason_phrase);
 30    end if;
 31
 32    -- Create temporary LOB to hold the HTTP response
 33    DBMS_LOB.createtemporary(response_clob, FALSE);
 34
 35    -- Loop to read response body chunk by chunk into CLOB
 36    begin
 37      loop
 38        UTL_HTTP.read_text(response
 39         , response_buf, 32766);
 40        DBMS_LOB.writeappend (response_clob
 41         , LENGTH(response_buf), response_buf);
 42      end loop;
 43
 44      DBMS_LOB.freetemporary(response_clob);
 45    exception
 46      when UTL_HTTP.end_of_body then
 47        UTL_HTTP.end_response(response);
 48    end;
 49
 50    dbms_output.put_line('Response : '
 51      ||substr(response_clob,1,200));
 52  exception
 53    -- Flow reaches here if a 4xx/5xx error occurs when
 54    -- set_response_error_check is TRUE
 55    when others then
 56      dbms_output.put_line('Error: '
 57      || response.status_code ||' '
 58      || UTL_HTTP.GET_DETAILED_SQLCODE ||' '
 59      || substr(UTL_HTTP.GET_DETAILED_SQLERRM,1,200));
 60  end;
 61  /

Procedure created.

    2. Run SUCCESS scenario test script

    SQL> -- Success case: status code 200 OK
SQL> -- Response body: result of expression
SQL> exec calc_restapi ('2*(7-3)');
Success: Received OK response
Response : 8

PL/SQL procedure successfully completed.

    3. Run FAILURE scenario test script

    SQL> -- Failure case: 4xx error
SQL> -- Response body: error details
SQL> -- SET_RESPONSE_ERROR_CHECK = TRUE
SQL> exec calc_restapi ('a');
Error:  -29268 ORA-29268: HTTP client error 400 - Bad Request

PL/SQL procedure successfully completed.

    You can see that the FAILURE test script did not execute the code after the call to UTL_HTTP.get_response, but jumped straight to the EXCEPTION section.

    PL/SQL REST API GET Call with SET_RESPONSE_ERROR_CHECK  = FALSE

    1. Compile procedure with SET_RESPONSE_ERROR_CHECK FALSE

    SQL> -- REST API call demo with exception handling.
SQL> -- Evaluate a mathemathical expression via GET
SQL> -- request from PLSQL
SQL> -- SET_RESPONSE_ERROR_CHECK = FALSE
SQL> create or replace procedure calc_restapi
  2      (expression in varchar2)
  3  is
  4    url           varchar2(50) := 'http://api.mathjs.org/v4/';
  5    request       UTL_HTTP.req;
  6    response      UTL_HTTP.resp;
  7    response_clob CLOB;
  8    response_buf  varchar2(32767);
  9
 10  begin
 11    -- Begin the GET request
 12    request := UTL_HTTP.begin_request
 13             (url || '?expr='|| expression
 14           , 'GET', 'HTTP/1.1');
 15
 16    -- Response error check configuration
 17    -- FALSE => no PL/SQL exception
 18    UTL_HTTP.set_response_error_check (false);
 19
 20    response := UTL_HTTP.get_response(request);
 21
 22    -- Flow reaches here if a 4xx/5xx error occurs when
 23    -- set_response_error_check is FALSE
 24    if (response.status_code = UTL_HTTP.HTTP_OK) then
 25      dbms_output.put_line('Success: Received OK response');
 26    else
 27      dbms_output.put_line
 28      ('Failure: Received non-OK response: '
 29      ||response.status_code||' '||response.reason_phrase);
 30    end if;
 31
 32    -- Create temporary LOB to hold the HTTP response
 33    DBMS_LOB.createtemporary(response_clob, FALSE);
 34
 35    -- Loop to read response body chunk by chunk into CLOB
 36    begin
 37      loop
 38        UTL_HTTP.read_text(response
 39         , response_buf, 32766);
 40        DBMS_LOB.writeappend (response_clob
 41         , LENGTH(response_buf), response_buf);
 42      end loop;
 43
 44      DBMS_LOB.freetemporary(response_clob);
 45    exception
 46      when UTL_HTTP.end_of_body then
 47        UTL_HTTP.end_response(response);
 48    end;
 49
 50    dbms_output.put_line('Response : '
 51      ||substr(response_clob,1,200));
 52  exception
 53    -- Flow reaches here if a 4xx/5xx error occurs when
 54    -- set_response_error_check is TRUE
 55    when others then
 56      dbms_output.put_line('Error: '
 57      || response.status_code ||' '
 58      || UTL_HTTP.GET_DETAILED_SQLCODE ||' '
 59      || substr(UTL_HTTP.GET_DETAILED_SQLERRM,1,200));
 60  end;
 61  /

Procedure created.

    2. Run SUCCESS scenario test script

    SQL> -- Success case: status code 200 OK
SQL> -- Response body: result of expression
SQL> exec calc_restapi ('2*(7-3)');
Success: Received OK response
Response : 8

PL/SQL procedure successfully completed.

    The result for a SUCCESS scenario is the same regardless of the value of SET_RESPONSE_ERROR_CHECK.

    3. Run FAILURE scenario test script

    SQL> -- Failure case: 4xx error
SQL> -- Response body: error details
SQL> -- SET_RESPONSE_ERROR_CHECK = FALSE
SQL> exec calc_restapi ('a');
Failure: Received non-OK response: 400 Bad Request
Response : Error: Undefined symbol a

    You can see that the FAILURE test script executed the code after the call to UTL_HTTP.get_response, regardless of whether the web service raised an error or not. There was no PL/SQL exception – the code in the EXCEPTION block, which was executed in the previous case of SET_RESPONSE_ERROR_CHECK = TRUE, did not get executed with SET_RESPONSE_ERROR_CHECK = FALSE.

    Summary

    This article provides an overview of web service error handling options available in PL/SQL UTL_HTTP. It gives working examples of calling a REST GET API with a parameter, and shows:

    • how to make a successful REST GET API call from PL/SQL
    • how to handle failure cases in the web service call from PL/SQL, with different settings for SET_RESPONSE_ERROR_CHECK
    • how to read the response body when the web service returns an error status (non-2xx response)

    References

    { 1 comment… read it below or add one }

    1 Timothy P March 18, 2019 at 12:10 pm

    Great explanation for managing web service errors. Thanks!

    Leave a Comment

    Previous post:

    Next post: