Home arrow Oracle arrow Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks

Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks

This is part 7 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, I gave an introduction to exception handling and went through some predefined exceptions. In this article, we will focus on user defined exceptions in PL/SQL and some tips for working with exceptions efficiently.

  1. Database Interaction with PL/SQL: User defined Exceptions, Nested Blocks
  2. A frequently faced problem in exception handling
  3. The solution
  4. The most efficient solution
  5. Nested blocks with local variable declarations
By: Jagadish Chatarji
Rating: starstarstarstarstar / 36
July 05, 2005

print this article



Please note that all the examples in this series have been tested only with Oracle 10g. I didn't really test them with all the previous versions of Oracle. I suggest you refer to the documentation for the version you are using, if any of the programs fail to execute.

User defined exceptions

The sixth part of my series (the previous article) completely focused on predefined exceptions in Oracle PL/SQL. Now, how do we create our own exception, raise it and handle it?

The declaration of a user defined exception is similar to that of a variable declaration. Instead of providing a data type to that variable, we provide a keyword EXCEPTION. PL/SQL runtime doesn't know when to raise (or fire) our exception. So, it is our responsibility to inform the PL/SQL runtime when it has to raise (or fire). Once the exception is raised, it is handled in the same way as we handle predefined exceptions. Let us consider the following example.

  v_empno emp.empno%type:=&empno;
  v_inc emp.sal%type:=&increment;
  v_sal emp.sal%type;
  e_High_Sal exception;
  Select sal into v_sal
  From emp Where empno=v_empno;

  If v_sal>2000 then
    Raise e_High_Sal;
  End if;

  Update emp set sal=sal+ v_inc
  Where empno=v_empno;

  dbms_output.put_line('Successfully Updated');

  When e_High_Sal then
    dbms_output.put_line('Salary is Already High');

From the above program, we can observe that 'e_High_Sal' is declared as an EXCEPTION. The exception gets raised when the salary is above two thousand. The 'raise' statement makes the flow of execution jump immediately to the exception section (skipping all the statements in between). If the salary is above two thousand, the exception 'e_High_Sal' gets handled through a 'when' statement within the exception section, and displays the message 'Salary is Already High'.

You may be wondering why this much of a mechanism is necessary to display a simple message based on a condition. The same thing can be achieved very easily and comfortably by using an IF..ELSE construct. In the case of above example, I should admit it.

Another question you might as is, can't we write any program in PL/SQL without using a user defined exception? To which the answer is almost YES. The user defined exceptions are not necessary (basically for simple programs). But when your code within the PL/SQL block grows more and more, or blocks get nested more and more, or for efficiently coding sub-programs or packages, you would definitely appreciate the use of user-defined exceptions.

>>> More Oracle Articles          >>> More By Jagadish Chatarji

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: