how to store EXCEPTION code in separate code block for ease of maintenance?
temJun 6 2012 — edited Jun 8 2012I'm new to pl/sql and oracle, but I've created a lot of procedures that use the same business logic for security checks. I've tried to outline an example stored procedure below.
If my security checks that I use to raise exceptions are always the same for all of the stored procedures, what's the best way to structure the procedure/environment so that if I ever want to change (such as add or delete) the exceptions, that I don't need to manually edit each individual procedure? Could I call another procedure to do this, and the exceptions will be raised up through it to the calling procedure? Any example would be much appreciated.
create or replace
PROCEDURE MY_SPROC(
AS
begin
...
/***** security check ****/
SELECT col1, col2 INTO v_col1, v_col2
FROM my_table1 WHERE user_email = in_user_id;
-- check 1
IF v_col1 != in_var1 THEN
RAISE e_bad_input;
-- check 2
ELSIF v_col2 = in_var2 THEN
RAISE e_bad_form;
ELSIF ...
...
END IF;
... /* body of code */
EXCEPTION
WHEN e_bad_input THEN out_msg := 'Error 1 …';
WHEN e_bad_form THEN out_msg := 'Error 2 ...';
WHEN e_bad_output THEN out_msg := 'Error 3 ...';
WHEN NO_DATA_FOUND THEN out_msg := 'Error 4 …';
WHEN OTHERS THEN out_msf := 'Error 5 ...';
RAISE;
end MY_SPROC;