Howto use Rollback/Commit in Oracle SQL

Question

I am trying to utilize transaction functionality in Oracle SQL for the first time and can't seem to find a good explanation. I understand that starting a new session will begin a new transaction. I also understand that commit/rollback is used to end it. What I am trying to do is execute two statements and if I either of them fail, undo any changes they might have made and continue with execution. How can I check for this condition and issue a commit or rollback accordingly?

Answer 1

Use a PL/SQL block and write something like this:

begin

  statement_zero;

  savepoint my_savepoint;

  begin
    -- if either of these fail, then exception section will be executed
    statement_one;
    statement_two;
  exception
     when others then
       rollback to my_savepoint;
  end;

  statement_three;

  commit;

end;

See also http://www.adp-gmbh.ch/ora/concepts/transaction.html

Answer 2

Along with a nice exaplample ObiWanKenobi provded a detailed explanation of Oracle transactions can be found at Chapter 4 of Oracle Concepts guide (the link I've provided goes for 10.2, you can find the doc suitable for your version at Oracle website as well). I suggest you read this chapter to understand how Oracle handles transaction management, and the doc at whole is very good piece of information for undestanding how Oracle DB work.