NO_DATA_FOUND or TOO_MANY_ROW

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

Questions by jkverma29

Showing Answers 1 - 15 of 15 Answers

Tiger81

  • Sep 23rd, 2008
 

can take count of the actual query and based upon the count you can proceed like

SELECT empname INTO v_empname FROM Employee WHERE job = 'MANAGER';
Instead of this you can first check the count

SELECT COUNT(1) INTO v_cntmanager FROM Employee WHERE job = 'MANAGER';
IF v_cntmanager = 0 then
......
Else if v_cntmanager > 1 then
........
Else
SELECT empname INTO v_empname FROM Employee WHERE job = 'MANAGER';
End if;

OracleBrain

  • Aug 5th, 2012
 

using Exception block to catch error

  Was this answer useful?  Yes

Ashok Kumar Lenka

  • Dec 6th, 2012
 

By Using the Exception Block ,
when_no_data_found then
//Put your code here
when_too_many_rows then
//Put your code here
when others then
//Put your code here
end;

  Was this answer useful?  Yes

Ananda Kumar Behera

  • Nov 12th, 2013
 

To avoid use:

Code
  1. DECLARE a variable

    2. 

  2. SELECT COUNT(*) INTO num WHERE <Condition OF your data retreival FOR SELECT INTO statement>;

    3. 

  3. IF num==1 THEN

    4. 

  4. EXECUTE your SELECT INTO statement

    5. 

  5. END IF

  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