How to catch exception for ORA-01427 in Oracle PL/SQL

 

Suppose you want to catch ORA-01427 in your exception in the PL/SQL, the following is the process

PROCEDURE my_proc(p_one VARCHAR2, p_err OUT VARCHAR2) IS

MORE_VALUES EXCEPTION;    -- here MORE_VALUES is the user defined exception
PRAGMA EXCEPTION_INIT(MORE_VALUES , -1427);


BEGIN


--- write your code here for the business logic.


EXCEPTION
    WHEN MORE_VALUES THEN
        p_err := 'Sub query returns more than one valuse';
        RAISE; -- re-raise the exception for the caller

END;