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

A frequently faced problem in exception handling - Oracle

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



This is a very common situation, generally faced by any beginner, who tries to program using exception handling. Instead of explaining the problem, let us go through an example of the appropriate type of problem.


  v_empno1 emp.empno%Type := &empno1;
  v_ename emp.ename%Type;
  v_empno2 emp.empno%Type := &empno2;
  Select ename into v_ename
  From emp Where empno=v_empno1;
  dbms_output.put_line('First Employee Name: '||v_ename);


  Select ename into v_ename
  From emp Where empno=v_empno2;
  dbms_output.put_line('Second Employee Name:'||v_ename);

  When no_data_found then
    dbms_output.put_line('Employee not found');


The above program is very simple. It just accepts two employee numbers from the user and displays the names of those two. If you properly give the two employee numbers (which exist in database table), the program works fine.

Say I gave a wrong employee number for 'v_empno1'. The first SELECT statement fails and immediately jumps to the exception section (without even worrying about the second employee number). Even the message is generic (not showing which employee number it could not find). Say that now I gave a wrong employee number for 'v_empno2'. The second SELECT gets failed and jumps to the exception section.

From the above explanation, we can deduce that we are unable to provide proper information (in the form of message) to the user about the invalid employee number. Instead of providing a generic message, I would also like to add the invalid employee number (along with the message), to make the user happy by letting them see the invalid employee number. The next section gives you the solution for the above example.

>>> 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: