Nikhil_4_Oracle

• 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

 i.e
-------------
------------
----------
 Exception
  when NO_DATA_FOUND then
  Dbms_output.put_line('No Record');
---------
-----------
-------------

Regards,

Nikhil.

Nikhil_4_Oracle

• 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.

i.e

select empno, ename into eid, enam from emp

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

Thanks,

Nikhil.

mala

• 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.

Regards.
Mala

g_sidhu

 Profile 

• 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.

4e671d1f5972f2fe1d8a3215db5167fd

 Profile Answers by 4e671d1f5972f2fe1d8a3215db5167fd 

• May 8th, 2008

 

You may avoid too_many_rows exception using CURSORS.

In cursors if NO_DATA_FOUND then it will not raise any exception. In cursors you need to handle it using %FOUND or %NOTFOUND cursor attributes.

binurajnair

 Profile Answers by binurajnair 

• 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;
else
  --Select statement not executed because it will throw exception
  v_column=dummy_value;
end if;

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

vnraos

 Profile Answers by vnraos 

• 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.