In PL/SQL if we write select statement with INTO clause it may return two exceptions NO_DATA_FOUND or TOO_MANY_ROW . To do you avoid these execeptions. How do you write SQL statement in alternative way?

Questions by ddkdhar   answers by ddkdhar

Showing Answers 1 - 7 of 7 Answers


  • Mar 5th, 2007

hi all,
 there is no other way for NO_DATA_FOUND and TOO_MANY_ROWS
 Exception. Just handle them by using Oracle Exception Handler

  when NO_DATA_FOUND then
  Dbms_output.put_line('No Record');



  Was this answer useful?  Yes


  • Mar 6th, 2007

HI All,

There is no solution for NO_DATA_FOUND else you handle it using Exception block as i

do above...

well TOO_MANY_ROWS is programming fault, when
your select query

uses "=" operator then may be there is Too_many_rows exception, to avoid this

always use "IN" operator in u r select Query.


select empno, ename into eid, enam from emp

where deptno IN (select deptno from emp where sal>3000);



  Was this answer useful?  Yes


  • Mar 17th, 2007

Hi All,

Yes you can handle Too_Many_Rows exception when you are writing into a PL/SQL block SELECT...INTO statement.

Just write the SELECT...INTO  statement in a CURSOR.


  Was this answer useful?  Yes


  • Feb 5th, 2008

PL/SQL manages these errors by raising standard exceptions, which you can trap in the exception section of the block with the NO_DATA_FOUND and TOO_MANY_ROWS exceptions. Code SELECT statements to return a single row.

  Was this answer useful?  Yes


  • Jul 30th, 2008

If you don't want an excpetion to be raised on a SELECT statement, you can avoid executing the statement by having a count just before the SELECT as shown below

SELECT count(rowid)
into v_count
from tables
where conditions.

if(v_count = 1) then
  SELECT column
  into v_column
  from tables
  where conditions;
  --Select statement not executed because it will throw exception
end if;

However it is a much better practice to handle these errors using exceptions

  Was this answer useful?  Yes


  • Sep 5th, 2008

First, let us understand why these errors cropup.

1) NO_DATA_FOUND - is raised only for "select into" statements when  the where clause of the query does not match any rows.

2)TOO_MANY_rows - is raised if a "select into" statement matches more than one row.

Solution for 1 - In the exception section use a handler like this "when no_data_found then insert into log_error ('no matching data')".

for 2) - This is a result of poor design or programming. One need to use a cursor.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions