Reusing Common REST API Handler Code

Reuse common REST handler code to send JSON responses, errors, and optional debug details.

Every PL/SQL handler performs the following set of steps:

• If necessary:

  • processes incoming request by referencing bind variables

  • prepares response to return to the client

• Then always:

  • Sets the MIME Type of the response, and any other response headers

  • Sets the status code if the default 200 status is not appropriate

  • Closes the header

  • Writes the response to the output buffer using HTP.P or HTP.PRN

To simplify handler implementation, consider using a package like APP_COMMON_REST below that provides procedures for:

• commit_and_send_response – committing the transaction and writing out the response

• handle_error – rolling back the transaction, signalling an error, and returning error details

• set_debug – to enable including additional debug info useful to developers.

package app_common_rest as
    --------------------------------------------------------------
    -- Set debug mode for error reporting
    --------------------------------------------------------------
    procedure set_debug(
        p_value in varchar2);
    --------------------------------------------------------------
    -- Commit and write out JSON response
    --------------------------------------------------------------
    procedure commit_and_send_response(
        p_json json_element_t default null);
    --------------------------------------------------------------
    -- Handle error by sending HTTP error with $error payload
    --------------------------------------------------------------
    procedure handle_error(
        p_error_message   in varchar2,
        p_http_error_code in number default 400);
end app_common_rest;

The package body follows for you to study. Notice it uses:

• owa_util.mime_header – to set standard Content-Type MIME type header

• owa_util.status_line – to set a non-default HTTP response status code

• htp.p – to write out the response body as serialized JSON text.

If the g_debug_mode flag is enable by a call to set_debug, then notice also that the handle_error adds an additional debug$error property whose value is a JSON array containing the lines of the PL/SQL call stack info from format_error_stack and format_error_backtrace in the dbms_utility package.

create or replace package body app_common_rest as
    g_debug_mode boolean := false;
    ------------------------------------
    procedure set_debug(
        p_value in varchar2)
    is
    begin
        if upper(p_value) = 'TRUE' then
            g_debug_mode := true;
        end if;
    end set_debug;
    ------------------------------------
    procedure commit_and_send_response(
        p_json in json_element_t default null)
    is
        l_has_json boolean := p_json is not null;
    begin
        commit;
        owa_util.mime_header('application/json', l_has_json);
        if p_json is not null then
            htp.p(p_json.stringify);
        else
            owa_util.status_line(204, 'No content', true);
        end if;
    end;
    --------------------------------------------------------------
    -- Handle error by sending HTTP 400 with $error payload
    --------------------------------------------------------------
    procedure handle_error(
        p_error_message   in varchar2,
        p_http_error_code in number default 400)
    is
        l_json_err json_object_t := json_object_t();
        l_json_arr json_array_t;
    begin
        owa_util.mime_header('application/json', p_error_message is null);
        owa_util.status_line(p_http_error_code, 'Error', true);
        if p_error_message is not null then
            l_json_err.put('$error',
                           regexp_replace(p_error_message,'^ORA-[0-9]+:\s'));
            if g_debug_mode then
                l_json_arr := json_array_t();
                for j in (select rownum, column_value
                            from table(apex_string.split(
                                dbms_utility.format_error_stack()
                                || chr(10)
                                || dbms_utility.format_error_backtrace())))
                loop
                    if j.column_value is not null then
                        l_json_arr.append(j.column_value);
                    end if;
                end loop;
                l_json_err.put('debug$error', l_json_arr);
            end if;
            htp.p(l_json_err.stringify);
        end if;
        rollback;
    end;
end app_common_rest;