Error checking during procedure execution in oracle

Question

create or replace procedure proc_advertisement(CustomerID in Number, NewspaperID in number, StaffID in Number, OrderDate in date, PublishDate in date, Type in varchar, Status in varchar, Units in number) is begin insert into PMS.Advertisement(CustomerID, NewspaperID, StaffID, OrderDate, PublishDate, Type, Status, Units) values(CustomerID,NewspaperID, StaffID, OrderDate, PublishDate, Type, Status, Units); dbms_output.put_line('Advertisement Order Placed Successfully'); end; 

How to check if any error occurred during the execution of the procedure, and if any error occurred, I want to show an error message.

Answer 1

First of all, Oracle itself will raise an error message if any error occurs during the execution of the procedure - for example:

 ORA-02291: integrity constraint (EMP.MGR_FK) violated - parent key not Found 

You can handle errors explicitly by writing an exception handler, but if you do not, it is likely that you will simply confuse the problem. For example, you can simply add this (shortly before the end of your procedure:

 EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'An error occured'); 

But now your user will not know what kind of error, whereas before they could conclude that the specified dispatcher does not exist. You can also show the original error:

 EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,'An error occured: '||SQLERRM); 

if it adds any value. Or you can just show a general error and then write the SQLERRM value to the log table.

You can also handle certain exceptions: for example

 PROCEDURE ... IS e_invalid_fk EXCEPTION; PRAGMA EXCEPTION_INIT(e_invalid_fk,-2291); BEGIN ... EXCEPTION WHEN e_invalid_fk THEN IF SQLERRM LIKE '%(EMP.MGR_FK)%' THEN raise_application_error(-20001,'Invalid manager specified'); ELSE RAISE; END IF; END; 

Pay attention to RAISE: if any part of your exception handler does not throw either RAISE or RAISE_APPLICATION_ERROR, then you will effectively throw an exception under the carpet - the user will consider this procedure to work.

By the way, DBMS_OUTPUT.PUT_LINE is great for testing and debugging in SQL Plus or IDE, but it has no place in real code, since users and applications that invoke the procedure will never see the output it produces.