Search notes:

Oracle SQL - DML statement

call, delete, explain plan, insert, lock table, merge, select and update.

Some consider select to be a DRL (Data Retrieval Language) statement.

A DML statement cannot be executed in a PL/SQL function which is selected in a query, see the error message ORA-14551: cannot perform a DML operation inside a query.

Undoing changes of DML statements

As long as a transaction is not committed, DML statements can be undone with a rollback. This is possible because the necessary information is stored in undo data.

After committing a transaction, it might be possible with Oracle Flashback Technology.

Locks

There are two types of locks that come into play with DML statements: row locks and table locks.

These lock types must be distinguished from system locks (such as latches, mutexes and internal locks.

Row locks (TX)

Records that were changed by a DML statement are proteced from being changed by another transaction until the modification is committed by a row lock (aka TX lock, TX apparently standing for «transaction»).

Table locks (TM)

A DML statement on a table, as well as the for update clause and the lock table statement, lock the entire table with a table lock (aka TM lock).

A table lock can be held in different modes: RS, RX, S, SRX and X

Error handling

See dbms_errlog and the log errors clause in insert statements.