Sql – Do DDL statements always give you an implicit commit, or can you get an implicit rollback

this is an interesting question !

When Oracle encounters an error, it will rollback the current statement, not the transaction. A statement is any top-level instruction, it can be a SQL statement (INSERT, UPDATE...) or a PL/SQL block.

This means that when a statement (for example a pl/sql procedure called from java) returns an error, Oracle will put the transaction in the same logical state as before the call. This is immensely helpful, you don't have to worry about half-executed procedures (**).

This thread on AskTom covers the same topic:

[the statement] either ENTIRELY happens or it ENTIRELY DOES NOT happen and the way that works is the database does the logical equivalent of:

begin
   savepoint foo;
   <<your statement>>
exception
   when others then rollback to foo; 
                    RAISE;
end;

This feature, in my opinion, is why it is a lot easier to write database code (*) in pl/sql than in any other language.

(*) code that interacts with an Oracle DB of course, I suppose the native procedural languages of the other DBMS have similar features.

(**) This only concerns DML since DDL are not transactional in Oracle. Be also careful with some DBMS packages that update the data dictionary (such as DBMS_STATS), they often do DDL-like changes and issue commits. Refer to the documentation in case of doubts.

Update: this behaviour is one of the most important concept in PL/SQL, I will provide a small example to demonstrate the atomicity of the pl/sql statements:

SQL> CREATE TABLE T (a NUMBER);

Table created

SQL> CREATE OR REPLACE PROCEDURE p1 AS
  2  BEGIN
  3     -- this statement is successful
  4     INSERT INTO t VALUES (2);
  5     -- this statement will raise an error
  6     raise_application_error(-20001, 'foo');
  7  END p1;
  8  /

Procedure created

SQL> INSERT INTO t VALUES (1);

1 row inserted

SQL> EXEC p1;

begin p1; end;

ORA-20001: foo
ORA-06512: at "VNZ.P1", line 5
ORA-06512: at line 2

SQL> SELECT * FROM t;

         A
----------
         1

Oracle has rolled back the transaction to the point just before calling p1. There is no half-work done. It is as if the procedure p1 had never been called.

In your example transaction Propagation.REQUIRED is used as the default value, and all the logic transactions are mapped to the single physical transaction

When the propagation setting is PROPAGATION_REQUIRED, a logical transaction scope is created for each method upon which the setting is applied. Each such logical transaction scope can determine rollback-only status individually, with an outer transaction scope being logically independent from the inner transaction scope. Of course, in case of standard PROPAGATION_REQUIRED behavior, all these scopes will be mapped to the same physical transaction. So a rollback-only marker set in the inner transaction scope does affect the outer transaction's chance to actually commit (as you would expect it to).

So in your example two logical transactions are mapped to one physical transaction.

See the documentation