How to use savepoints in oracle procedure

Question

I have multiple updates and insert statements in a procedure.

Please refer below example:

Procedure Example

--code

Update 1

insert 1

Update 2

Update 3 --Suppose exception occurs

Now i want to rollback to before 1st update statement means no update or insert affects.

Kneel

Answer 1

BEGIN

  Savepoint do_update_1;

  Update 1;

  insert 1;

  Update 2;

  Update 3; --Suppose exception occurs

EXCEPTION
  WHEN some_exception THEN Rollback To do_update_1;
END;

====== edit ==========

Working example: http://sqlfiddle.com/#!4/b94a93/1

create table tttt(
  id int,
  val int
)
/

declare 
  x int := 0;
begin
  insert into tttt values( 1,1);
  insert into tttt values( 2,2);
  Savepoint do_update_1;

  insert into tttt values( 3,3);
  update tttt set val = 0 where id = 2;
  update tttt set val = 10 / val where id = 2;

exception
  when zero_divide then rollback to do_update_1;
end;
/

Rowney

i had tried this but error coming is no savepoint found do_update_1 – Kneel

Do you make a commit (or rollback) after savepoint is created? Maybe you call a DDL statement which makes an implicit commit. – Pietro

no, i didn't use a commit or rollback after same point created. I also think it is making an implicit commit. – Kneel

I've tested this code and appended a link to a working example to the answer, please take a look at it. – Rowney

Answer 2

You can catch exception in exception when clause and execute rollback statement, e.g.

procedure test
is
begin
    Insert into t values (1);
    Update t set x = 1;
exception when <your exception> then
    Rollback;
end;

Flofloat

Answer 3

try

BEGIN

  BEGIN
  Savepoint do_update_1;


  Update 1;

  insert 1;

  Update 2;

  Update 3; --Suppose exception occurs

  EXCEPTION
    WHEN some_exception THEN Rollback To do_update_1;

  END;
END;

** note the BEGIN END block

Corenda