How to create a Stored Procedure with Try Catch and Transaction

CREATE PROCEDURE dbo.spname

AS

BEGIN

BEGIN TRANSACTION;

BEGIN TRY

/*

YOUR COMMAND

/*

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

       ROLLBACK TRANSACTION;

END CATCH;

IF @@TRANCOUNT > 0

   COMMIT TRANSACTION;

END;

Cheers!
Uma

How to rollback Query on Oracle SQL Developer

You don’t need to use AUTOCOMMITED OFF; START TRANSACTION; in SQL Developer. All you need is after running the script just run ROLLBACK / COMMIT command, this will support insert/delete/update. Because by default in SQL Developer will not do auto commit.

If you give 

rollback;

Then the whole transactions is roll backed.

If you give 

commit;

Then the whole transaction is committed and all savepoints are removed.

If you give,

rollback to a; 

Then rollback up to the specified savepoints.

  

Example

insert into emp (empno,ename,sal) values (109,’Sami’,3000);

savepoint a;

insert into dept values (10,’Sales’,’Hyd’);

savepoint b;

insert into salgrade values (‘III’,9000,12000);

Cheers!

Uma