Transactions and COMMIT
505977May 22 2006 — edited May 22 2006I am converting some procedures from SQL Server to Oracle. I'm a little confused on "transactions" in Oracle.
Generally, if I have a procedure in SS, I need to declare the beginning of the transaction with a BEGIN TRANS and then COMMIT when I am done. Obviously a rollback if an error is detected.
Question 1: Should I code COMMIT into all of my procedures that just perform a single DML statement in the procedure? (i.e. one INSERT statement) Or is this implied when the procedure finishes running?
Question 2: To code the same transaction procedure in Oracle (multiple inserts, updates, etc), do I need to use Savepoints and then Rollback to that Savepoint if an error occurs? Basically how would I do this:
begin trans
Insert into .....;
if error rollback
Insert into ....;
if error rollback
Update ....;
if error rollback
Commit;
given that all DML statements must complete or none must complete.
Thanks,
Greg