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 ]