ORACLE PL/SQL check whether string is NULL

Question

Query 1:

create or replace procedure toUp(code in number)
is sname staff_master.staff_name%type;
recnotfound exception;
begin
select staff_name into sname from staff_master where staff_code=code;
if sname is NULL then
raise recnotfound;
else
update staff_master set staff_name=upper(staff_name) where staff_code=code;
end if;
exception
when recnotfound then dbms_output.put_line('Record not found');
end;

Query 2:

declare
commsn emp.comm%type;
no_comm exception;
begin
select comm into commsn from emp where empno=7369;
if commsn is NULL then
raise no_comm;
else
dbms_output.put_line('Comm is '||commsn);
end if;
exception
when no_comm then dbms_output.put_line('Commsn for emp doesnt exist');
end;

Here in Query 1 I'm checking whether sname is null.. However, when I pass an invalid code as a parameter to the procedure.. sname should be NULL and hence the exception 'recnotfound' must get raised.. but it is showing the following error:

SQL> exec toUp(7369);
BEGIN toUp(7369); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "LAB06TRG15.TOUP", line 6
ORA-06512: at line 1

But when I do the same with Query 2 it is working as expected.. I guess it has something to do with how varchar2 is checked for null.. Am I doing it correctly?

I modified the code as follows :

create or replace procedure toUp(code in number)
is
sname staff_master.staff_name%type;
recnotfound exception;
begin
select staff_name into sname from staff_master where staff_code=code;
if sname is NULL then
dbms_output.put_line('a');
raise recnotfound;
else
dbms_output.put_line('b');
--update staff_master set staff_name=upper(staff_name) where staff_code=code;
end if;
exception
when recnotfound then dbms_output.put_line('Record not found');
when no_data_found then raise recnotfound;
end;

I get :

BEGIN toUp(7369); END;

*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "LAB06TRG15.TOUP", line 16
ORA-01403: no data found
ORA-06512: at line 1

How do I solve this?

P.S. I want to do this using Exception only.. Its part of an assignment ..

Answer 1

If a query returns no rows then an "ORA-01403: no data found" error is raised. Your expectation, I think, is that execution will continue but no value will have been assigned to the variable -- that's not the case.

If what you want to do is check for the existence of a record then use:

select count(*)
into   row_found
from   ...
where  ...
   and rownum = 1;

this is guaranteed to return a single row with a value of 0 or 1 into the row_found variable.

With regard to your edit, you are not handling the raising of the user defined exception in the exception handling block. Wrap the SELECT with a BEGIN-END-EXCEPTION.

begin
  begin
    select ..
  exception when NO_DATA_FOUND then raise recnotfound;
  end;
  if sname is NULL then
    dbms_output.put_line('a');
    raise recnotfound;
  end if;
exception
  when recnotfound then dbms_output.put_line('Record not found');
end;

I'm not clear what you're trying to do here though. Is the sname ever going to be returned as null from the query?