Calling Stored Procedure in Exception Handler 1

[campbere]

I want to call a stored procedure in my exception handler. However when I have an error that triggers the exception handler it doesn't seem to trigger the stored procedure.

Is it possible to do this?

Is there something I am doing wrong? I do however get a message from the raise_application_error that lets me know something went wrong.

Here is my exception handler:

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

--Call stored procedure
UPDATE_Product_Problem(INSERT_PRODUCT.PROD_ID, 'INVALID);

raise_application_error(SQLCODE, SQLERRM,true);

 

[Sudhi78]

UPDATE_Product_Problem(INSERT_PRODUCT.PROD_ID, 'INVALID');


close the single quotes after INVALID. it'll work if the procedure is valid.



sudhi

 

[sem]

I suppose that raise_application_error causes rollback, so you don't see changes made by your procedure.

Regards, Dima

 

[campbere]

Dima,

Is there a way to use the raise_application_error and get the stored procedure to also run? You were right about it rolling things back.

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

--Call stored procedure
UPDATE_Product_Problem(INSERT_PRODUCT.PROD_ID, 'INVALID');

raise_application_error(SQLCODE, SQLERRM,true);

 

[sem]

On Oracle 7 we used something like

EXCEPTION
WHEN OTHERS THEN
StoO_error := SQLCODE;
StoO_errmsg := SQLERRM;
rollback;
--Call stored procedure
call_loging_procedure(StoO_error, StoO_errmsg );
commit;

Then we used autonomous transactions, but found some drawbacks (e.g. distributed transactions).

Now we use asynchronous logging via pipes and feel sorry for not having used them with 7




Regards, Dima