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

The solution - 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



The following program solves the problem explained in the previous section:

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

  v_SearchingEmpno := v_empno2;
  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 with '||v_SearchingEmpno);

The magic is in using a temporary variable (v_SearchingEmpno) to hold the employee number, which is being fetched through the SELECT statement.  I stored ‘v_empno1’ into ‘v_SearchingEmpno’ before it is  searched for (or fetched).  If the first SELECT fails, the exception handler displays the value available in ‘v_SearchingEmpno’ (which is nothing but ‘v_empno1’).  The same thing happens with ‘v_empno2’ as well.

But still there is a minor problem. Even though the above program gives the details of the invalid employee number to the user, it doesn't give you both! Which means, if I provide wrong employee numbers for both 'v_empno1' and 'v_empno2', only one gets reported to the user.

And another problem is that, if I provide an invalid value to the first employee number and a valid value to the second employee number, we would never get the details of the second employee (even though the second employee number is a valid one). The next section addresses these issues using nested blocks in PL/SQL (a new concept!).

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