Obsessed with Oracle PL/SQL

If you've got a read-only database, you don't have to worry about transactions. But for almost every application you're ever going to build, that is not the case. Therefore, the concept and managing of transactions is central to the success of your application. A transaction is a sequence of one or more SQL statements that Oracle Database treats as a unit: either all of the statements are performed, or none of them are. A transaction implicitly begins with any operation that obtains a TX lock: When a statement that modifies data is issued (e.g., insert, update, delete, merge) When a SELECT ... FOR UPDATE statement is issued When a transaction is explicitly started with a SET TRANSACTION statement or the DBMS_TRANSACTION package Issuing either a COMMIT or ROLLBACK statement explicitly ends the current transaction. This post reviews how to define, manage and control the transactions in your application with the following statements and features: Commit and Roll...

This question rolled into my In Box today: If I have a procedure that is AUTONOMOUS_TRANSACTION that does an insert and then it calls a procedure with an insert, does the second procedure need a commit, or will the procedure with the AUTONOMOUS_TRANSACTION handle the commit? If you don't know off the top of your head, don't worry, I can build a test. First of all, if you ever find yourself writing something like "If you don't know off the top of your head, don't worry, I can build a test." then please by all means go right ahead and build yourself a test script. By doing so, you will better understand the feature in question and remember what you learned. Plus you end up with a script you can share with the community on LiveSQL . But I don't mind answering such questions. That way I get to better understand the feature in question, remember what I learned, share a script on LiveSQL (link at bottom of post), and also add to my blog. :-) So here goe...