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...
For the last twenty years, I have managed to transform an obsession with PL/SQL into a paying job. How cool is that?