Exception Handler

[campbere]

I am working on an exception handler and I want it to do two things, but I have only be able to get to it to do one of the two tasks.

I want the exception handler to call a stored procedure to update a Product ERROR table. Then I would like to use the raise_application_error procedure.

If I list both items, the raise_application_error procedure works but the stored procedure to populate the PRODUCT ERROR table doesn't.

I have tested only putting the stored procedure call to populate the PRODUCT ERROR table and it works. However there is a glitch there as well. The stored procedure continues after the exception handler and goes to the next BEGIN statement. So it doesn't exit the stored procedure. How can I do this?

Below is my Exception Handler:
EXCEPTION

WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;

UPDATE_PRODUCT_ERROR(Prod_VAR,TIMESTAMP_VAR,INS_DATE_VAR);

raise_application_error(SQLCODE, SQLERRM,true);

So my questions are:

Can I do both the UPDATE_PRODUCT_ERROR stored procedure and raise_application_error stored procudure?
If so how?

If I only want to use the UPDATE_PRODUCT_ERROR stored procedure how do I get it to exit the stored procedure after executing that stored procedure in the handler?

Thank you for your help.

 

[Chalco]

I think what is happening, is that the update is being rolled back - you could try autonomous (excuse spelling !!) transaction

 

[carp]

I think Chalco is exactly right - if you don't commit your exception's UPDATE statement, it will be rolled back.

If you are doing DML in your procedure and don't want to commit it in the event of an exception, you might try this:

WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
ROLLBACK;
UPDATE_PRODUCT_ERROR(Prod_VAR,TIMESTAMP_VAR,INS_DATE_VAR);
COMMIT;
raise_application_error(SQLCODE, SQLERRM,true);