Checking for Error during execution of a procedure 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 for if any error has occurred during the execution of the procedure and if any error has occurred then I wish to display an error message.

Answer 1

First of all, Oracle itself will raise an error message if any error occurs while running 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 unless you do this well you are quite likely to just obfuscate the problem. For example you could simply add this (just before the END of your procedure:

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

But now your user won't know what kind of error, whereas before they could infer that it was that the specified Manager did not exist. You could show the original error also like this:

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

if that adds any value. Or you could just show a generic error and then write the value of SQLERRM to a log table.

You can also handle particular 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;

Note the RAISE: if any part of your exception handler doesn't issue either a RAISE or a RAISE_APPLICATION_ERROR then you are effectively sweeping the exception under the carpet - the user will think the procedure worked.

By the way, DBMS_OUTPUT.PUT_LINE is great for trying things out and debugging, in SQL Plus or an IDE, but it has no place in real code as users and applications that call the procedure will never see the output it produces.