Begin
   Commit;
   Parse the DDL – verify privileges and syntax
   Begin
      Do_the_ddl;
      Commit;
   Exception
      When others then
         Rollback;
   End;
End;

Note the rollback - not a commit in there!


The DDL works in its own transaction - it will commit the outstanding work and then do its stuff - and either commits all of its stuff or it rolls back all of uts stuff.


The commit happens before the DDL even executes, so in answer to:

I think if the VERY reason of executing of DDL is defeated , then why still autocommit should happen?

is that the commit already happened before we know if the DDL will succeed or fail. *it already took place*



f the application is trying to create to a new table ,

which does not require any locks to be released OR which has no impact on the

previous processing prior to DDL execution, why still autocommit is required?

because if the create table fails, we need to UNDO it. We need to rollback. And we don't want to rollback your work - so the design decision was made that DDL commits.


The savepoint in theory would/could wok - technically speaking - HOWEVER - DDL is a very serial operation, most dictionary operations are. If we used a savepoint - we would not be able to "publish" that ddl - we'd be stuck in limbo. The table would be "created" but the current session would not be able to see it really (none of our recursive sql would be able to see it - they run in a separate transaction - it would not be committed). No other session would be able to see it. Furthermore - the dictionary would be inaccessible for other sessions attempting to do dictionary things (add a new partition, whatever...)


A savepoint might technically work - but it would introduce locking issues you don't really want and give you DDL that you cannot use until you commit anyway.

If you don't want the auto-commit, you can use an autonomous transaction...