Raise a user-defined exception with RAISE_APPLICATION_ERROR - PolarDB

RAISE_APPLICATION_ERROR is a stored procedure that raises a user-defined exception from within an SPL program. When called, it immediately aborts the current program and returns a custom error code and message, making it straightforward to distinguish different failure conditions in your exception handlers.

Exceptions raised this way follow the same handling rules described in Exception handling.

Syntax

RAISE_APPLICATION_ERROR(error_number, message);

Parameters

Parameter	Type	Description
`error_number`	Integer or expression	The error code returned in `SQLCODE` when the procedure runs. Must be between `-20000` and `-20999`.
`message`	String literal or expression	The error message returned in `SQLERRM`.

Example

The following procedure validates an employee record and raises a distinct error for each missing field. The EXCEPTION block captures the code and message from SQLCODE and SQLERRM.

CREATE OR REPLACE PROCEDURE verify_emp (
    p_empno         NUMBER
)
IS
    v_ename         emp.ename%TYPE;
    v_job           emp.job%TYPE;
    v_mgr           emp.mgr%TYPE;
    v_hiredate      emp.hiredate%TYPE;
BEGIN
    SELECT ename, job, mgr, hiredate
        INTO v_ename, v_job, v_mgr, v_hiredate FROM emp
        WHERE empno = p_empno;
    IF v_ename IS NULL THEN
        RAISE_APPLICATION_ERROR(-20010, 'No name for ' || p_empno);
    END IF;
    IF v_job IS NULL THEN
        RAISE_APPLICATION_ERROR(-20020, 'No job for' || p_empno);
    END IF;
    IF v_mgr IS NULL THEN
        RAISE_APPLICATION_ERROR(-20030, 'No manager for ' || p_empno);
    END IF;
    IF v_hiredate IS NULL THEN
        RAISE_APPLICATION_ERROR(-20040, 'No hire date for ' || p_empno);
    END IF;
    DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno ||
        ' validated without errors');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
END;

Running the procedure for an employee whose manager number is missing produces the following output:

EXEC verify_emp(7839);
-- Output:
SQLCODE: -20030
SQLERRM: polar-20030: No manager for 7839

Each error code in the range -20000 to -20999 maps to a distinct condition, so the EXCEPTION block — or the calling code — can identify exactly which field is missing without parsing the message string.