• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

can't compare NUMBER with NULL ?

 
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
In a store procedure

...
IS
salary_data NUMBER
BEGIN
select salary into salary_data from employee where name = "XYZ"
if salary_data is null
DBMS_OUTPUT.PUT_LINE('No such employee');
else
..
end if
....

I found that, when I test "XYZ" , since it doesn't exisit in my employee table, the store procedure never prints the line of "No such employee" because it complains at the above line saying "no data found".

so how to check a NUMBER type to see if there is something returned or not ?
 
Ranch Hand
Posts: 338
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Raj Ohadi:
In a store procedure

...
IS
salary_data NUMBER
BEGIN
select salary into salary_data from employee where name = "XYZ"
if salary_data is null
DBMS_OUTPUT.PUT_LINE('No such employee');
else
..
end if
....

I found that, when I test "XYZ" , since it doesn't exisit in my employee table, the store procedure never prints the line of "No such employee" because it complains at the above line saying "no data found".

so how to check a NUMBER type to see if there is something returned or not ?



Testing for no data is different than than testing for NULL in a column. You have to use an exception block to perform a test for the exception no_data_found.

...
IS
salary_data NUMBER
BEGIN
select salary into salary_data from employee where name = "XYZ"
if salary_data is null -- tests for null data in salary
DBMS_OUTPUT.PUT_LINE('No Salary Data for Employee: '||name);
else
..
end if
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors
DBMS_OUTPUT.PUT_LINE('No such employee');
END;

[ October 19, 2007: Message edited by: Paul Campbell ]
[ October 19, 2007: Message edited by: Paul Campbell ]
 
Raj Ohadi
Ranch Hand
Posts: 316
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
thank you paul. this one works nnow.
 
reply
    Bookmark Topic Watch Topic
  • New Topic