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...

You'd think that after working with Oracle Database and PL/SQL since 1990, I'd know everything . Ha. Not so. :-) Of course, there are always the new features, such as those coming in 12.2 (I will be blogging about those extensively in the coming months). But even for features that have been in the language for decades, I still encounter small gaps in my knowledge. For example, I had long known that when you execute a DDL (data definition language) statement in PL/SQL (which must be done as dynamic SQL via EXECUTE IMMEDIATE or DBMS_SQL.PARSE /EXECUTE) a commit is executed implicitly after the statement. What I'd somehow missed was that a commit is also performed before the DDL statement is executed. So that is the point of this post: Oracle Database issues a commit before a DDL statement is executed, and then afterwards as well. You can see this behavior in action in the script below, which is generated from a recent PL/SQL Challenge quiz  and can be run direct...