Null value return problem

Question

I wanted to check my monthly payroll in which there are employee salary along with other detail.

I have created a PL/SQL block but when I place my condition for checking of existing employee id with another table some return null value and hence my table does not go further.

set serveroutput on 
declare
  emp_id NUMBER :=&emp;
  temp NUMBER;
begin
  select nvl(employee_id,10) into temp FROM bhavya_temp where bhavya_temp.employee_id=emp_id;

  dbms_output.put_line(temp);

  if temp is NULL
  then 
    dbms_output.put_line('employee ID does not exist');
  else 
    dbms_output.put_line('bye');
  end if;
end;

When I enter employee id 1 or 2 which exist in table the result is

anonymous block completed
1
bye

When I enter 3 or more which is not there

Error report:
ORA-01403: no data found
ORA-06512: at line 6
01403. 00000 -  "no data found"
*Cause:    
*Action:

Thanks for help in advance.

Answer 1

It would be helpful to have the create table statements with some example data, so that the problem can be reproduced.

To the question : If the row with the ID does not exist vou get the ORA-1403 . There is no data, there is no NULL to convert. Oracle does not dream a row up for you.

Answer 2

I've never written any PL/SQL, but a very similar error is reported here.

It looks like you can handle the problem by placing the SELECT ... INTO portion inside of a BEGIN ... EXCEPTION .. END block.

Hope this helps.

Answer 3

Wrap your select stament in an exception block and catch the 'no data found' exception, and act accordingly

Answer 4

thx visage and all i got the solution from you guys thx a lot !!!!!