Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to store EXCEPTION code in separate code block for ease of maintenance?

temJun 6 2012 — edited Jun 8 2012
I'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;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 6 2012
Added on Jun 6 2012
14 comments
442 views