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 - 5 of 5 Answers


  • 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
SELECT empname INTO v_empname FROM Employee WHERE job = 'MANAGER';
End if;


  • 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

  Was this answer useful?  Yes

Ananda Kumar Behera

  • Nov 12th, 2013

To avoid use:

  1. DECLARE a variable

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

  3. IF num==1 THEN

  4. EXECUTE your SELECT INTO statement

  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