Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Transactions and COMMIT

505977May 22 2006 — edited May 22 2006
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 19 2006
Added on May 22 2006
16 comments
1,074 views