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

David Aldridge · Accepted Answer · 2013-12-10 12:05:35Z

4

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?

edited Dec 10, 2013 at 12:05

answered Dec 10, 2013 at 11:53

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3012820 Over a year ago

OK.. but in query 2 why is it working as expected.. as in the execution continues after select is executed..

David Aldridge Over a year ago

If NO_DATA_FOUND is being raised then it's because the query did not return any rows. If it was not raised then it did return rows.

user3012820 Over a year ago

I checked by entering an invalid empno in query 2.. and the user defined exception is raised as expected.. The problem is I exectued query 2 first.. got the result as expected.. now when I'm trying to the same in query 1 it doesnt work.. so I'm confused where the problem lies?

user3012820 Over a year ago

My bad.. in query 2 I'm providing a valid empno but here mgr=NULL.. in query 1 there are no rows returned hence NO_DATA_FOUND exception.. hence no further execution..

Kaspars Ozols · Accepted Answer · 2013-12-10 11:51:52Z

0

Actually, exceptions happens even before your IF statement. If SELECT INTO statement doesn't return a row, ORA-01403 is thrown. You might expect that in this situation NULL value is assigned to variable, but it is not so and exception is thrown instead.

You must add exception handling in your stored procedure to get over it. Documentation on how to do that can be found here

Sorry, don't have ORACLE now, so I can't check it, but it should be something like this:

...
select staff_name into sname from staff_master where staff_code=code;
exception
when NO_DATA_FOUND then ...handle no data...;
when TOO_MANY_ROWS then ...handle too many data rows...;
...

answered Dec 10, 2013 at 11:51

Kaspars Ozols

7,0371 gold badge22 silver badges33 bronze badges

Comments

Aditya · Accepted Answer · 2013-12-10 12:07:47Z

0

Exception will be raised as soon as no record is returned by your SELECT query. The code will go into the exception there only and will not continue is that was your expectation.

Try this instead :

    create or replace procedure toUp(code in number)
    is sname staff_master.staff_name%type;err_count number;
    recnotfound exception;
    begin
    select count(*) into err_count from staff_master where staff_code=code;
    if count > 0 then
    select staff_name into sname from staff_master where staff_code=code;
    else 
    raise recnotfound;

Not sure whether the syntax is exactly correct, but I hope you get the drift

answered Dec 10, 2013 at 12:07

Aditya

412 bronze badges

2 Comments

David Aldridge Over a year ago

Your two select queries have different consistency points, so there's no guarantee that a record existing for the first query will still be there for the second. It's also more efficient to just handle the NO_DATA_FOUND error.

Aditya Over a year ago

Agreed, this was a very basic way to handle this issue.Purpose was to only show the OP what was wrong in his query.I agree that a check for NO_DATA_FOUND is the best way to handle this issue in a consistent manner and is the correct way to do things

Avrajit · Accepted Answer · 2013-12-11 08:50:16Z

0

This is a sample procedure created regarding your query. Please modify accordingly and try it should work. Thanks

create or replace procedure av_stack_test(sr_no_var in number)
as
nme avrajit.name%type;
no_rec exception;
num_count number;
begin
select count(*) into num_count from avrajit
where sr_no=sr_no_var;
if num_count>0 then
select name into nme from avrajit
where sr_no=sr_no_var;
update avrajit
set name=nme
where sr_no=sr_no_var;
else
raise no_rec;
end if;
dbms_output.put_line(sr_no_var);
exception
when no_rec then
dbms_output.put_line('No rec found');
when others then
dbms_output.put_line('Some other exception');
end;

answered Dec 11, 2013 at 8:50

Avrajit

2301 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

ORACLE PL/SQL check whether string is NULL

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related