Nice, easy one today, thanks Steve
Not easy for those with an Oracle background who may well get this wrong.
I can barely spell Oracle. I have worked with it but never for more than a few weeks at a time. What is different about Oracle in this situation?
Hi - there is an autocommit on DDL in Oracle.
Does that override an explicit transaction? That is what we have here.
Hi Lynn, yes that will override the explicit transaction:
A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.
Well that seems really silly. The auto commit is only for ddl? Meaning that ddl ignores explicit transactions in oracle? Seems like that is an interesting choice from the designers.
Use Autonomous Transactions to execute the DDL.
However, as Tom Kyte said when talking about misused features in Oracle:
"The third issue is the autonomous transaction which allows people to do things such as commit in a trigger because they can do that in SQL Server. - SQL Server triggers work differently than Oracle triggers and, committing in a trigger is a horribly bad idea. I believe it’s horribly bad in SQL Server, but it’s doubly horribly bad in Oracle.
Many people inappropriately use an autonomous transaction all over the place to get around what they think are limitations, but when I look at that code and I explain to them what’s going on they don’t even realise the transactional integrity issues that they’ve introduced into their application.
You know if you commit in a trigger that means you commit that work. But they don’t realize it only commits the work that was done in the trigger, not the statement that caused the trigger to fire. Think about what happens then when the user rolls back? The stuff you did in your trigger doesn’t roll back with you, it’s non-transactional, you’ve already committed it and now the database is sort of left in this inconsistent state. "
Gosh that is bad and breaks ACID. At least in sql server the calling code would get an exception because there is no transaction if it was committed in a trigger. Not saying it handled gracefully but at least it doesn't commit part of the work.