ORDS Custom Error Responses and Exception Handling

Professional Error Responses

By default, when a PL/SQL handler in ORDS raises an exception, the API consumer receives a generic error message with Oracle-specific error codes that are meaningless to external developers. Custom error handling transforms these into structured, informative JSON error responses that follow REST API best practices.

Handling Errors in PL/SQL Handlers

Wrap your handler logic in an exception block that returns a proper HTTP status code and structured error body:

-- ORDS handler source (POST method)
DECLARE
    l_id NUMBER;
BEGIN
    -- Validate input
    IF :product_name IS NULL THEN
        OWA_UTIL.STATUS_LINE(400, 'Bad Request');
        HTP.P('{"error":"validation_error","message":"product_name is required","field":"product_name"}');
        RETURN;
    END IF;

    -- Attempt the operation
    INSERT INTO products (product_name, category, unit_price)
    VALUES (:product_name, :category, :unit_price)
    RETURNING product_id INTO l_id;

    OWA_UTIL.STATUS_LINE(201, 'Created');
    HTP.P('{"product_id":' || l_id || ',"status":"created"}');

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        OWA_UTIL.STATUS_LINE(409, 'Conflict');
        HTP.P('{"error":"duplicate","message":"A product with this name already exists"}');
    WHEN OTHERS THEN
        OWA_UTIL.STATUS_LINE(500, 'Internal Server Error');
        HTP.P('{"error":"internal_error","message":"An unexpected error occurred"}');
        -- Log the real error server-side
        log_api_error(SQLCODE, SQLERRM, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

Consistent Error Format

Define a standard error JSON structure and use it everywhere. A good format includes an error code for programmatic handling, a human-readable message, and optionally a detail field for additional context:

{
    "error": "not_found",
    "message": "Product with ID 999 does not exist",
    "detail": "Query returned no rows for products.product_id = 999",
    "timestamp": "2025-03-02T14:30:00Z"
}

Centralized Error Handler Package

Create a package that all ORDS handlers call for consistent error formatting:

CREATE OR REPLACE PACKAGE api_error AS
    PROCEDURE bad_request(p_message VARCHAR2, p_field VARCHAR2 DEFAULT NULL);
    PROCEDURE not_found(p_message VARCHAR2);
    PROCEDURE conflict(p_message VARCHAR2);
    PROCEDURE internal_error(p_context VARCHAR2 DEFAULT NULL);
END;

CREATE OR REPLACE PACKAGE BODY api_error AS
    PROCEDURE send_error(p_status NUMBER, p_status_text VARCHAR2,
                         p_code VARCHAR2, p_message VARCHAR2) IS
    BEGIN
        OWA_UTIL.STATUS_LINE(p_status, p_status_text);
        OWA_UTIL.MIME_HEADER('application/json', TRUE);
        HTP.P('{"error":"' || p_code || '","message":"' ||
               REPLACE(p_message, '"', '"') || '"}');
    END;

    PROCEDURE bad_request(p_message VARCHAR2, p_field VARCHAR2 DEFAULT NULL) IS
    BEGIN send_error(400, 'Bad Request', 'validation_error', p_message); END;

    PROCEDURE not_found(p_message VARCHAR2) IS
    BEGIN send_error(404, 'Not Found', 'not_found', p_message); END;

    PROCEDURE conflict(p_message VARCHAR2) IS
    BEGIN send_error(409, 'Conflict', 'duplicate', p_message); END;

    PROCEDURE internal_error(p_context VARCHAR2 DEFAULT NULL) IS
    BEGIN
        log_api_error(SQLCODE, SQLERRM, p_context);
        send_error(500, 'Internal Server Error', 'internal_error',
                   'An unexpected error occurred');
    END;
END;