CODEWITHSUNDEEP
oracle-databaseexceptionplsqldatabase-trigger
Darko Kulfa
Darko Kulfa

Reputation: 11

PL/SQL Continue after exception raised

I've got simple trigger with exception handling. My problem is how continue with application when user get error message.

  CREATE OR REPLACE TRIGGER "AR_CHECK"
BEFORE INSERT  ON TABLE
REFERENCING OLD AS OLD NEW AS NEW 
FOR EACH ROW 

DECLARE

v_check number;

cursor c_ar_check is
select ar_no 
from name_view 
where name_id = :new.bill_to_name_id 
and name_type in ('TRAVEL_AGENT','COMPANY');

begin

open c_ar_check;
fetch c_ar_check into v_check;
close c_ar_check;

if v_check is null then
   raise_application_error(-20101, 'ERROR - NUMBER MISSING');
end if;


end;

With code above I've got error message but user cannot continue with next step. Is it possible to get user warning about NULL value but with possibility to go on?

Upvotes: 0

Views: 1220

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

With raise_application_error, no. It will terminate execution.

But, if you substitute it with something else - a trivial dbms_output.put_line would do in testing phase, while some logging process - such as calling a(n autonomous transaction) procedure - might be a better option in production.

If you choose dbms_output.put_line, note that your won't see any message until the procedure is finished.

Upvotes: 1

Related Questions