TRY CATCH AND ROLLBACK in Oracle Syntax

I have a sql server stored procedure that uses a try catch block and then does a rollback if an error occurs. Can someone show me how to do the same thing in Oracle Syntax?

CREATE OR REPLACE PROCEDURE MYPROC

AS

BEGIN TRY -- Close to top of my stored procedure



END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION


END CATCH
LVL 2
brgdotnetcontractorAsked:
Who is Participating?
 
sdstuberCommented:
try/catch corresponds to BEGIN/EXCEPTION/END.

CREATE OR REPLACE PROCEDURE myproc
AS
BEGIN
    NULL;
--- do something more interesting than NULL here

EXCEPTION
    WHEN OTHERS  -- OTHERS is the catchall for exceptions, you can list individual exceptions by name each with their own WHEN...THEN clause
    THEN
        NULL;
-- handle the exception with something other than NULL here

END;

Open in new window


unfortunately there is no direct correlation to try/catch/finally
you can mimic it by nesting begin/exception/end blocks inside other blocks but it's not as clean of an api

You can find a list of predefined named-exceptions here

https://docs.oracle.com/database/121/LNPLS/errors.htm#GUID-8C327B4A-71FA-4CFB-8BC9-4550A23734D6

you can define your own named exceptions with the EXCEPTION keyword, as well as give names to specific error codes with PRAGMA

https://docs.oracle.com/database/121/LNPLS/errors.htm#LNPLS99871
0
 
brgdotnetcontractorAuthor Commented:
Thank you. Apologies for the delay.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.