1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

User defined exception in oracle

Discussion in 'General' started by TechQueryPond, Mar 21, 2016.

  1. TechQueryPond

    TechQueryPond Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    INDIA


    User Defined Exception in Oracle

    1)We can defined exception to make your own exception.
    2)This exception must be declare yourself and RAISE statement to raise explicitly.

    Declare exception : user_define_exception_name EXCEPTION;

    RAISE exception : RAISE user_define_exception_name;

    How to use :

    WHEN user_define_exception_name THEN
    //User Action


    DECLARE
    exception1 EXCEPTION;
    g NUMBER;
    BEGIN
    FOR g IN (SELECT * FROM EMPLOYEE) LOOP
    IF g.ID = 7 THEN
    RAISE exception1;
    END IF;
    END LOOP;
    EXCEPTION
    WHEN exception1 THEN
    dbms_output.put_line('WE HAVE FOUND EMPLOYEE NUMBER 7');
    END;
    /
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why are you raising an exception when no error has been thrown? That isn't a valid use of an exception, it's a case for reporting the correct output:

    DECLARE
    g NUMBER;
    BEGIN
    FOR g IN (SELECT * FROM EMPLOYEE) LOOP
    IF g.ID = 7 THEN
    dbms_output.put_line('WE HAVE FOUND EMPLOYEE NUMBER 7');
    END IF;
    END LOOP;
    END;
    /

    Exceptions should be used for error handling, and should provide a USABLE error message that makes sense to the end user; I've blogged on that here:

    https://dfitzjarrell.wordpress.com/2008/05/02/ive-never-seen-that-error-before/

    If you're going to provide examples please have them make sense.
     
  3. TechQueryPond

    TechQueryPond Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    INDIA
    Raising the message instead of exception is a special case. yes we definitely raise an exceptions , but some times we can raise proper messages to the user according to our application needs.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    They are called exceptions for a reason; you create user-defined exceptions to post non-standard error messages, not to inform a user that his selection is correct or that some particular value has been 'found'. What you have done in your example is, in my mind, a misuse of exceptions. Exception blocks are just that, blocks of code designed to handle ERRORS that are encountered during execution.

    There is no 'special case' here, there is only poor programming practice you're trying to pass off as valid.
     
    sneha singh likes this.