I have written a trigger whose PL/SQL block contains a simple select statment among many other statements.
Now I find that, if the select statement returns no rows the trigger does not continue its operation further and aborts there itself. And if the select statement returns some rows, then it works fine.

I tried to execute a simplified PL/SQL block of the trigger in SQL*Plus and following were the results:

************************
declare
tempdate date;
begin
select trdt into tempdate from inv_trans;
if sql%notfound then
null;
end if;
end;
/
************************

When no data is present in inv_trans table, the result was:
************************
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
************************

And when the table inv_trans had data, the result was:
************************
PL/SQL procedure successfully completed.
************************

Why is the piece of code flashing an error when I have already given a treatment if no data is found.
Why is it taking "No Data in table" as an abnormal condition and not normal?

-------------------------

Your problem is in the fact that you haven't given a treatment if no data was found, as you said you've been doing'.

sql%notfound was never reached in case where no record was returned.
The exception was raised in the select into statement.
That's the default Oracle behaviour.

select into statement always raise ORA-01403 error
if no record was returned and no value set into variable.

You'll have to handle the exception.

Here is the code that will work the same logic, without any problem:

[color=red]declare
tempdate date;
begin
select trdt into tempdate from inv_trans;
exception
when no_data_found then
null;
end;
/ [/color]

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips