Home arrow Oracle arrow Page 2 - Database Interaction with PL/SQL: Pre-defined Exceptions

No Data Found, Too Many Rows - Oracle

This is part 6 of a series focusing on database interactions with Oracle PL/SQL. The previous articles discussed several types of collections in PL/SQL. Now Jagadish will look into exception handling using the predefined exceptions available in Oracle PL/SQL.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: Pre-defined Exceptions
  2. No Data Found, Too Many Rows
  3. Dup Val on Index, Zero Divide, Invalid Number, Value Error
  4. A Word About OTHERS Exception
By: Jagadish Chatarji
Rating: starstarstarstarstar / 22
June 28, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

NO_DATA_FOUND

NO_DATA_FOUND exception is raised when SELECT…INTO statement could not find any row (or value) to fetch into variables.

Let us consider the following example:

Declare
  v_empno emp.empno%Type := &empno;
  v_ename emp.ename%Type;
Begin
  Select ename into v_ename From emp 
  where empno=v_empno;
  dbms_output.put_line('Name: '||v_ename);
Exception
  when no_data_found then
    dbms_output.put_line('Employee not found');
End;

From the above program, we declare two variables 'v_empno' and 'v_ename'. We accept input from the user just by placing '&empno'. All this happens within declaration section. Within the body (between 'begin' and 'end'), we fetch the 'ename' and place it in 'v_ename' from the table 'emp', based on the 'where' condition framed from the user input. To display the value available in 'v_ename', we use DBMS_OUTPUT.PUT_LINE statement. If employee number could not be found, it will raise an exception where we need to handle it within the EXCEPTION section before 'END' statement.

The exception section is used to handle any type of exception. So, we need to specify the exception (which is to be handled) using WHEN statement within the exception section. Using DBMS_OUTPUT.PUT_LINE statement we can display the error 'Employee not found' on to the screen.

The flow of execution of program will be something like the following (in brief):

  • Declares the variables and accepts input from the user
  • Try to execute the SELECT statement and fetch 'ename' into 'v_ename'
  • If SELECT statement gets successfully executed, it displays the employee name (next line of SELECT) and directly jumps to END (skipping the exception section).
  • If SELECT statement could not retrieve any row (in this case it is 'ename'), it immediately jumps to exception section (skipping the DBMS_OUTPUT statement next to SELECT statement) and checks whether the exception (or error) is NO_DATA_FOUND or not. If so, displays the message 'Employee not found'.

TOO_MANY_ROWS

Now that we understood about NO_DATA_FOUND, we shall look into TOO_MANY_ROWS. We all know very well that SELECT..INTO statement will not be able to retrieve more than one row. So, when a SELECT ….INTO statement in a PL/SQL blocks tries to return more than one row, TOO_MANY_ROWS exception gets raised. Let us consider the following program.

Declare
  v_emp emp%rowtype;
  v_Sal emp.sal%Type := &sal;
Begin
  Select * into v_emp From emp
  where sal=v_sal;
  dbms_output.put_line('Name:'||v_emp.ename);
Exception
  When Too_Many_rows then
    dbms_output.put_line('More than one employee having same salary');
End ;

The above program is very similar to the first program, and the flow of execution of program will be something like the following (in brief):

  • Declares the variables and accepts input from the user
  • Try to execute the SELECT statement and fetch entire row from 'emp' into 'v_emp'
  • If SELECT statement gets successfully executed with only single row, it displays the employee name (next line of SELECT) and directly jumps to END (skipping the exception section).
  • If SELECT statement is trying to get more than one row (as there exists a possibility of having more than one employee with the same salary given), it immediately jumps to exception section (skipping the DBMS_OUTPUT statement next to SELECT statement) and checks whether the exception (or error) is TOO_MANY_ROWS or not. If so, displays the message 'More than one employee having same salary'.



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

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