[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

Centralised error management in PL/SQL

I have a number of packages that control EXCEPTIONS the same the way.
The instructions in the EXCEPTION section are
EXCEPTION
    WHEN NO_DATA_FOUND THEN
….
    WHEN DUP_VAL_ON_INDEX THEN
---
    WHEN OTHERS THEN
        ---
There is a global package which contain the definition of constants use in the various packages. I would like to define a procedure or function in this package that handles all the exception. Instead of writing the same code I would just need to call it.
How can it be done.
 Thanks in advance
0
diteps06
Asked:
diteps06
  • 3
1 Solution
 
sdstuberCommented:
PROCEDURE handle_exception(p_sqlcode IN PLS_INTEGER default SQLCODE)
IS
BEGIN
    CASE p_sqlcode
        WHEN c_some_constant
        THEN
            NULL;                                                               -- fill in code here
        WHEN c_other_constant
        THEN
            NULL;                                                               -- fill in code here
        WHEN c_yet_another_constant
        THEN
            NULL;                                                               -- fill in code here
        ELSE
            NULL;                                              -- this will act as the "WHEN OTHERS"
    END CASE;
END;

then in  in your other procedures


EXCEPTION WHEN OTHERS THEN handle_exception;

0
 
sdstuberCommented:
it will, as in the example above default to the most recent error (whatever the last SQLCODE raised)

but you can test the handling by passing in any error code you want


begin handle_exception(-100); end;

begin handle_exception(100); end;
0
 
sdstuberCommented:
you might want to look into dbms_utility package for error stack and back trace functions  to pull additional information about the error
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now