suppose a very simple setup like this:
create table words_temp(
word_type varchar2(10),
word_desc varchar2(10),
word_lang char(1));
insert into words_temp values('gr1', 'thanks', 'E');
insert into words_temp values('gr1', 'gracias', 'S');
insert into words_temp values('gr1', 'merci', 'F');
insert into words_temp values('gr1', 'danke', 'G');
insert into words_temp values('fd1', 'drink', 'E');
insert into words_temp values('fd1', 'bebida', 'S');
insert into words_temp values('fd1', 'boisson', 'F');
now, these two queries against this table would both return "no rows selected" in SQL*PLUS:
select * from words_temp where word_type = 'fd1' and word_lang = 'G';
select * from words_temp where word_type = 'nt1' and word_lang = 'G';
no rows selected
HOWEVER...
in a PRO*C program I have the following code:
.........
EXEC SQL WHENEVER NOT FOUND CONTINUE;
select * from words_temp where word_type = 'fd1' and word_lang = 'G';
.........
and the program "continues" to execute after this query,
but in the same program when I run this:
.........
EXEC SQL WHENEVER NOT FOUND CONTINUE;
select * from words_temp where word_type = 'nt1' and word_lang = 'G';
.........
the program stops, like after an error...
but NOT FOUND is not an error, AND, MOST IMPORTANTLY, IT LOOKS LIKE THESE NOT FOUND EXCEPTIONS ARE NOT THE SAME!
In other words, when there are 0 rows of a certain word_type in the table (nt1), it raises NOT FOUND, but when there are rows of this type (fd1), just no rows with this word_lang (G), it DOES NOT raise NOT FOUND...
It doesn't make any sense to me...
Please help.