Explicit Raise of System Exception
CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
BEGIN
IF salary_in < 0
THEN
RAISE VALUE_ERROR;
END IF;
END;Procedure created.
BEGIN
use_salary (salary_in => -1);
END;ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SQL_LIHEQGPXOJYFNIVBDXCAOEHRP.USE_SALARY", line 6 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SQL", line 1707More Details: https://docs.oracle.com/error-help/db/ora-06502
Raise a User-Defined Exception
CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
negative_salary EXCEPTION;
PRAGMA EXCEPTION_INIT (negative_salary, -6502);
BEGIN
IF salary_in < 0
THEN
RAISE negative_salary;
END IF;
END;Procedure created.
BEGIN
use_salary (salary_in => -1);
END;ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SQL_LIHEQGPXOJYFNIVBDXCAOEHRP.USE_SALARY", line 8 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SQL", line 1707More Details: https://docs.oracle.com/error-help/db/ora-06502
Different Code for Different Error Conditions
CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
PROCEDURE notify_support (string_in IN VARCHAR2)
IS
BEGIN
-- Just a placeholder to make a point!
DBMS_OUTPUT.PUT_LINE ('Hey support, deal with THIS: ' || string_in);
END;
PROCEDURE notify_hr (string_in IN VARCHAR2)
IS
BEGIN
-- Just a placeholder to make a point!
DBMS_OUTPUT.PUT_LINE ('Hey HR, deal with THIS: ' || string_in);
END;
BEGIN
CASE
WHEN salary_in < 0
THEN
notify_support (
'Negative salary submitted ' || salary_in);
RAISE VALUE_ERROR;
WHEN salary_in > 10000
THEN
notify_support (
'Too large salary submitted ' || salary_in);
RAISE VALUE_ERROR;
WHEN salary_in < 100
THEN
notify_hr (
'No one should be treated so shabbily! ' || salary_in);
RAISE VALUE_ERROR;
ELSE
/* No problems, proceed with normal execution*/
NULL;
END CASE;
/* Rest of procedure */
END;Procedure created.
BEGIN
use_salary (salary_in => -1);
END;ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SQL_LIHEQGPXOJYFNIVBDXCAOEHRP.USE_SALARY", line 22 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SQL", line 1707More Details: https://docs.oracle.com/error-help/db/ora-06502
Move Exception Handling Logic to Exception Section
CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
negative_salary EXCEPTION;
too_large_salary EXCEPTION;
too_small_salary EXCEPTION;
PROCEDURE notify_support (string_in IN VARCHAR2)
IS
BEGIN
-- Just a placeholder to make a point!
DBMS_OUTPUT.PUT_LINE ('Hey support, deal with THIS: ' || string_in);
END;
PROCEDURE notify_hr (string_in IN VARCHAR2)
IS
BEGIN
-- Just a placeholder to make a point!
DBMS_OUTPUT.PUT_LINE ('Hey HR, deal with THIS: ' || string_in);
END;
BEGIN
CASE
WHEN salary_in < 0 THEN RAISE negative_salary;
WHEN salary_in > 10000 THEN RAISE too_large_salary;
WHEN salary_in < 100 THEN RAISE too_small_salary;
ELSE NULL;
END CASE;
/* Rest of procedure */
EXCEPTION
WHEN negative_salary
THEN
notify_support (
'Negative salary submitted ' || salary_in);
RAISE VALUE_ERROR;
WHEN too_large_salary
THEN
notify_support (
'Too large salary submitted ' || salary_in);
RAISE VALUE_ERROR;
WHEN too_small_salary
THEN
notify_hr (
'No one should be treated so shabbily! ' || salary_in);
RAISE VALUE_ERROR;
END;Hey support, deal with THIS: Negative salary submitted -1
BEGIN
use_salary (salary_in => -1);
END;ORA-06502: PL/SQL: numeric or value error ORA-06512: at "SQL_LIHEQGPXOJYFNIVBDXCAOEHRP.USE_SALARY", line 35 ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SQL_LIHEQGPXOJYFNIVBDXCAOEHRP.USE_SALARY", line 35 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SQL", line 1707More Details: https://docs.oracle.com/error-help/db/ora-06502
Use RAISE_APPLICATION_ERROR for App-Specific Error Message
BEGIN
RAISE_APPLICATION_ERROR (-20000, 'Say whatever you want');
END;ORA-20000: Say whatever you want ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SQL", line 1707More Details: https://docs.oracle.com/error-help/db/ora-20000
CREATE OR REPLACE PROCEDURE use_salary (salary_in IN NUMBER)
IS
negative_salary EXCEPTION;
too_large_salary EXCEPTION;
too_small_salary EXCEPTION;
PROCEDURE notify_support (string_in IN VARCHAR2)
IS
BEGIN
-- Just a placeholder to make a point!
DBMS_OUTPUT.PUT_LINE ('Hey support, deal with THIS: ' || string_in);
END;
PROCEDURE notify_hr (string_in IN VARCHAR2)
IS
BEGIN
-- Just a placeholder to make a point!
DBMS_OUTPUT.PUT_LINE ('Hey HR, deal with THIS: ' || string_in);
END;
BEGIN
CASE
WHEN salary_in < 0 THEN RAISE negative_salary;
WHEN salary_in > 10000 THEN RAISE too_large_salary;
WHEN salary_in < 100 THEN RAISE too_small_salary;
ELSE NULL;
END CASE;
/* Rest of procedure */
EXCEPTION
WHEN negative_salary
THEN
notify_support (
'Negative salary submitted ' || salary_in);
RAISE_APPLICATION_ERROR (-20001,
'Negative salaries are not allowed. Please re-enter.');
WHEN too_large_salary
THEN
notify_support (
'Too large salary submitted ' || salary_in);
RAISE_APPLICATION_ERROR (-20001,
'We are not nearly that generous. Please re-enter.');
WHEN too_small_salary
THEN
notify_hr (
'No one should be treated so shabbily! ' || salary_in);
RAISE_APPLICATION_ERROR (-20001,
'C''mon, a person''s gotta eat! Please re-enter.');
END;Hey support, deal with THIS: Negative salary submitted -1
BEGIN
use_salary (salary_in => -1);
END;ORA-20001: Negative salaries are not allowed. Please re-enter. ORA-06512: at "SQL_LIHEQGPXOJYFNIVBDXCAOEHRP.USE_SALARY", line 35 ORA-06512: at line 2 ORA-06512: at "SYS.DBMS_SQL", line 1707More Details: https://docs.oracle.com/error-help/db/ora-20001