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

Discussion in 'SQL PL/SQL' started by rajavu, Dec 5, 2008.

  1. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    PL/SQL allows developers to define their own exceptions.User can define the error/exception programically based on the business rule. This is usually done on validating some values or parameters.Even exception can be given meaningful names.

    There are 3 steps to handle the User exception

    1. Define Exception

    We need to define the exception before we raise and handle. User Exceptions are defined using keyword EXCEPTION in declaration section of the block.

    The syntax is as follows

    Code (Text):

     <exception_name> EXCEPTION ;
     

    2. Raise the Exception

    Once the exceptions are defined , they need to be raised anywhere in the body depending upon predifined logic. User exceptions are raised using the keyword RAISE.

    Syntax is as shown below

    Code (Text):

    RAISE <exception_name>
     
    3. Handle the Exception.

    User exception are handled in the same way predefined exceptions are handled. They are handlded in exception block using WHEN .. THEN keyword

    Syntax is as shown below

    Code (Text):

    WHEN <exception_name> THEN
    .....
     
    Alternately we can give user defined error code and error message for the hadled excpetion. RAISE_APPLICATION_ERROR is used for this purpose.

    Its syntax is as follows.

    Code (Text):

    RAISE_APPLICATION_ERROR(<error_code>, <error_message>)
     
    Here "error_code" should be in the range of (-20000,-20999) and "error_message" is the user defined error message for the user defined exception.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    User Defined Exception Handling

    Here are the examples of user defined exceptions.

    Lets start with Demo tables and values usual .

    Code (Text):

    CREATE TABLE EMP_EXC_DEMO
    (
      EMPNO   NUMBER    NOT NULL,
      DEPTNO  NUMBER    NOI NULL,
      SAL     NUMBER(10),
      PRIMARY KEY
     (EMPNO)
    );
     
    Code (Text):

    SQL> SELECT * FROM EMP_EXC_DEMO ;

         EMPNO     DEPTNO        SAL
    ---------- ---------- ----------
          1000         10      10000
          2000         10      12000
          3000         20      11500

    SQL>
     
    Normal User Defined Exception Handling

    Here it tries td define the user exception based on rule that salary inserted should not be less than the minimum theshold set.

    Code (Text):

    SQL> DECLARE
      2   low_sal EXCEPTION;
      3   min_sal NUMBER:= 10000;
      4   new_sal NUMBER:= 8000;
      5  BEGIN
      6   INSERT INTO EMP_EXC_DEMO(EMPNO, DEPTNO, SAL)
      7                    VALUES (4000,20,new_sal);
      8   IF new_sal < min_sal THEN
      9      RAISE low_sal;
     10   END IF;
     11   commit;
     12  EXCEPTION
     13  WHEN low_sal THEN
     14     Rollback;
     15     DBMS_OUTPUT.PUT_LINE ('Salary is less than '||min_sal);
     16  WHEN OTHERS THEN
     17     DBMS_OUTPUT.PUT_LINE (SQLERRM);
     18  END;
     19  /
    Salary is less than 10000

    PL/SQL procedure successfully completed.

    SQL>
     

    Exception Handling using RAISE_APPLICATION_ERROR

    Here also it tries to bind the same rule using the RAISE_APPLICATION_ERROR.

    Code (Text):

    SQL> DECLARE
      2   low_sal EXCEPTION;
      3   min_sal NUMBER:= 10000;
      4   new_sal NUMBER:= 8000;
      5  BEGIN
      6   INSERT INTO EMP_EXC_DEMO(EMPNO, DEPTNO, SAL)
      7                    VALUES (4000,20,new_sal);
      8   IF new_sal < min_sal THEN
      9      RAISE low_sal;
     10   END IF;
     11   commit;
     12  EXCEPTION
     13  WHEN low_sal THEN
     14     Rollback;
     15     RAISE_APPLICATION_ERROR(-20102, 'Salary is Less than '||min_sal);
     16  WHEN OTHERS THEN
     17     DBMS_OUTPUT.PUT_LINE (SQLERRM);
     18  END;
     19  /
    DECLARE
    *
    ERROR at line 1:
    ORA-20102: Salary is Less than 10000
    ORA-06512: at line 15


    SQL>
     
    Note here that error code genereted is "ORA-20102" and error message is "Salary is Less than 10000" as specified in the RAISE_APPLICATION_ERROR.

    Table values remain the same as expected.

    Code (Text):

    SQL> SELECT * FROM EMP_EXC_DEMO ;

         EMPNO     DEPTNO        SAL
    ---------- ---------- ----------
          1000         10      10000
          2000         10      12000
          3000         20      11500

    SQL>
     
     
  3. tshugars

    tshugars Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Albany, NY
    I have a question about the scope of User-defined exceptions. I have an application that uses multiple schemas. In schema A, I raise an application exception -20036. In schema B, I also raise an application exception -20036. Schema A calls one or more functions in schema B which, in turn, calls other procedures or functions in schema C.

    An exception gets generated in schema C that does not get handled until it gets back to Schema B where the ORA-20036 exception gets raised. Even though Schema B handles ORA-20036, another unhandled exception causes control to revert to Schema A which, in turn, raises ORA-20036.

    My question is "What is the scope of user-defined exceptions?"

    In the above scenario, some sort of collision occurs resulting in havoc and mayhem in the executing functions/procedures. I resolved the problem by changing the number in Schema A to -20057. But why did I have to do this? And now, do I need to examine all of my Schema A, B, and C exception numbers to insure that there is no duplication of use?
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I am little bit confused about the scenarios mentioned. Actually the description is vague.

    May be you can check the scope here and it may clarify your doubt.
     
  5. tshugars

    tshugars Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Albany, NY
    Dear rajavu,

    Unfortunately, due to confidentiality agreements, it will be rather difficult for me to NOT be vague but I'll try to get a little more specific.

    Let's say that you have a .NET application that uses an Oracle database as its primary data source.

    This batch processing .NET application uses stored procedure call(s) to get the data that it needs.

    The stored procedure in the first package (A) that the application calls needs to call another stored procedure in different package (A2) in the same schema to get some data from Oracle but, while the A2-stored procedure is processing the data, an error is encountered and an exception (-20036) gets raised. The A-package stored procedure handles the exception it receives from the A2-package but now needs to notify the IT programming (or tech support) team that this error has occurred.

    To do this, the A-package calls another package (B) in a different Oracle schema to compile and send an e-mail message to the IT team. The B-package is designed to create an e-mail using the message and error number sent to it from the A-package. It also adds some additional parameters of its own before calling a third package (C) in another Oracle schema to actually send the e-mail message. Within the C-package, something goes wrong and an exception gets raised which the C-package, being a generic e-mail function, does not handle but, instead passes control back to the B-package. The B-package detects the exception from the C-package and control passes to the exception handling section of the B-packages stored procedure. After adding some
    additional text to the exception message, the B-package's stored procedure raises its own exception transferring control back to the
    A-package. The A-package receives this unexpected exception but handles it by rolling back all of the Oracle transactions that have happened up to that point. It then returns control to the .NET application which exits.

    The problem has to do with the "user defined exceptions" that were created (by three different teams of programmers) in packages A, B, and C. The A-package 'batch processing' team created a user-defined exception and assigned it the number -20036. The B-package 'e-mail compilation' team's stored procedure created a user-defined exception and coincidentally also assigned it the number -20036. And the C-package 'E-mail-transmission' team provided the initial exception that it handed back to the B-package 'e-mail compilation' procedure.

    The batch processing package (A) accesses data from an Oracle schema which I'll call BATCH. The e-mail compilation package is stored in a separate Oracle schema which I'll call COMPILE. And the e-mail transmission package is contained within a third Oracle schema which I'll call SEND. What this means is that if I wanted to look at tables, packages, procedures, etc. in the SEND schema, I would have to login as a SEND user. If I wanted to look at something in the COMPILE schema, I would have to login as a COMPILE user. And if I wanted to look at something in the BATCH schema, I would have to login as a BATCH user.

    The packages have the correct access grants set up so they can work with one another.

    My question concerns the scope of user-defined exceptions in Oracle.

    Shouldn't ORA-20036 in the BATCH schema be unrelated to ORA-20036 in the COMPILE schema? This does not appear to be the case because when I changed the Oracle exception number that gets raised in the BATCH schema stored procedure from -20036 to -20057, the processing works as designed.

    (A SIDE NOTE: The unrelated initiating exception in the C-package SEND schema does not even get raised once this change has been made.)

    I really do hope this helps to clarify this rather complex scenario.
     
  6. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi Raj, my question is
    1.how many types of user defined exceptions?
    2.raise_application_error is procedure or function?