ORDS, SODA & JSON in the Database

All

• DB: 19C
• ORDS: 23.1.0.r0861423

Following POST resource handler has been created:

DECLARE
  ec_parsing_ecode CONSTANT NUMBER := -31011;
  e_parsing EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_parsing ,  ec_parsing_ecode);
  --
  e_inbound EXCEPTION;
  --
  l_step VARCHAR2(180) := 'Initial';
  l_auth_user VARCHAR2(80) := :current_user;
  l_http_hdr_resp_key VARCHAR2(180) := :HTTPHeaderRespKey;
  l_shipment_payload JSON_OBJECT_T := JSON_OBJECT_T.parse('{}');
  l_new_shipment_num VARCHAR2(80) := NULL;
  l_reason VARCHAR2(4000) := NULL;
BEGIN
  l_step := 'Start';
  --  Documentation on the implicit parameters can be found here
  --    https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/23.1/orddg/implicit-parameters.html
  l_step := 'Parse received body';
  l_shipment_payload := JSON_OBJECT_T.parse(:body_text);
  --
  l_step := 'Process received body';
  BEGIN
    PACKAGE.PROCEDURE( p_auth_user        => l_auth_user
                     , p_http_hdr_respkey => l_http_hdr_resp_key
                     , p_payload          => l_shipment_payload
                     , p_shipment_num     => l_new_shipment_num
                     );
  EXCEPTION
    WHEN OTHERS THEN
         l_reason:= SQLERRM;
         RAISE e_inbound;
  END;
  --
  l_step := 'Setting the http-response parameters';
  :forward_location := './shipments/ack/'||l_new_shipment_num;
  :status_code := 201; --HTTP response code >created<
EXCEPTION
  -- https://en.wikipedia.org/wiki/List_of_HTTP_status_codes
  WHEN e_inbound THEN
       :status_code := 422; --HTTP response code >Unprocessable Content<
       :reason := l_reason;
  WHEN e_parsing THEN
       :status_code := 400; --HTTP response code >Bad Request<
       :reason := 'Invalid payload';
  WHEN OTHERS THEN
       :status_code := 500; --HTTP response code >Internal Server Error<
       :reason := 'Unexpected error occurred while "'||l_step||'"": '||SQLERRM;
END;

In the exception blocks you will see that there are 2 bind-variables: status_code and reason. The status_code is implicitly bound to the X-ORDS-STATUS-CODE (see link for documentation).

My question is on the reason bind variable: Is there a way to get this value in the https error reason, to give more detail information on the status_code (aka: X-ORDS-STATUS-CODE )?

***********************

The current implementaiton of the reason bind variable is mapped to a "resource handler parameter" towards the ‘RESPONSE’-source type.

I found some entries in this group. But it is not clear to me if this is possible or not.

Kind Regards

Olivier