Oracle offers a number of ways to capture and raise EXCEPTIONS and I was wondering what the gurus here believe is best practice.
Let's say I want to implement some business rules, reflected in the R.I. of the database, is it common practice to use PRAGMA?
For example, you are not allowed to delete a department with employees in it or a stock item with existing inventory because it would violate R.I.
Here are some of the codes that I was (literally) playing with the whole day:
/******* HANDLING NON-PREDEFINED EXCEPTIONS using PRAGMA *********/
set serveroutput on;
set verify off;
ACCEPT p_deptno PROMPT 'Delete this department: '
DECLARE
x_emps_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT (
x_emps_remaining, -2292 ); -- an integrity constraint violation has occurred!
v_deptno dept.deptno%TYPE := &p_deptno;
BEGIN
DELETE FROM dept
WHERE deptno = v_deptno;
COMMIT;
EXCEPTION
WHEN x_emps_remaining THEN
dbms_output.put_line('Cannot remove dept '|| to_char(v_deptno)||'. Employees exist.');
END;
/
/********** HANDLING USER-DEFINED EXCEPTIONS *********/
set serveroutput on;
set verify off;
ACCEPT p_deptno PROMPT 'Dept Number: '
ACCEPT p_new_loc PROMPT 'New location: '
DECLARE
x_invalid_deptno EXCEPTION;
BEGIN
UPDATE dept
SET loc = '&p_new_loc'
WHERE deptno = &p_deptno;
IF SQL%NOTFOUND THEN
RAISE x_invalid_deptno;
END IF;
COMMIT;
EXCEPTION
WHEN x_invalid_deptno THEN
dbms_output.put_line(to_char(&p_deptno) ||' is an invalid department number.');
END;
/
/********** HANDLING PROPAGATION OF EXCEPTIONS from SUB-BLOCKS *********/
-- This is interesting inasmuch that an exception (which is not necessarily an Oracle Server error) must be reported but it should not stop the outer block!
-- QUESTION: handle the exception in the sub-block or propagate it to the outer-most block?
/
/********** HANDLING PRE-DEFINED EXCEPTIONS using RAISE_APPLICATION_ERROR PROCEDURE *********/
-- This seems to be the way to capture application related error, which is not an Oracle Server error.
-- I can customize SQLCode between 20000 and 30999 for my applicatin
/
QUESTION: where can I find a list of Oracle Server number for constraint violations ?