Transactions - Oracle Database Transactions and Locking Revealed - page 87

That PL/SQL code reads a record at a time from ALL_OBJECTS , inserts the record into table T and commits each
record as it is inserted. Logically, that code is the same as this:
EODA@ORA12CR1> create or replace procedure p
2 as
3 begin
4 for x in ( select * from all_objects )
5 loop
6 insert into t values X;
7 commit write NOWAIT;
8 end loop;
9
10 -- make internal call here to ensure
11 -- redo was written by LGWR
12 end;
13 /
Procedure created.
So, the commits performed in the routine are done with WRITE NOWAIT and before the PL/SQL block of code
returns to the client application, PL/SQL makes sure that the last bit of redo it generated was safely recorded to
disk—making the PL/SQL block of code and its changes durable.
Integrity Constraints and Transactions
It is interesting to note exactly when integrity constraints are checked. By default, integrity constraints are checked
after the entire SQL statement has been processed. There are also deferrable constraints that permit the validation
of integrity constraints to be postponed until either the application requests they be validated by issuing a SET
CONSTRAINTS ALL IMMEDIATE command or upon issuing a COMMIT .
IMMEDIATE Constraints
For the first part of this discussion, we'll assume that constraints are in IMMEDIATE mode, which is the norm. In this
case, the integrity constraints are checked immediately after the entire SQL statement has been processed. Note that
I used the term “SQL statement,” not just “statement.” If I have many SQL statements in a PL/SQL stored procedure,
each SQL statement will have its integrity constraints validated immediately after its individual execution, not after
the stored procedure completes.
So, why are constraints validated after the SQL statement executes? Why not during ? This is because it is very
natural for a single statement to make individual rows in a table momentarily inconsistent. Taking a look at the partial
work by a statement would result in Oracle rejecting the results, even if the end result would be OK. For example,
suppose we have a table like this:
EODA@ORA12CR1> create table t ( x int unique );
Table created.
EODA@ORA12CR1> insert into t values ( 1 );
1 row created.