PL/SQL Language Elements, 10 of 52

COMMIT Statement

The COMMIT statement explicitly makes permanent any changes made to the database during the current transaction. Changes made to the database are not considered permanent until they are committed. A commit also makes the changes visible to other users. For more information, see "Overview of Transaction Processing in PL/SQL".

Syntax

Text description of commit_statement.gif follows

Text description of the illustration commit_statement.gif

Keyword and Parameter Description

COMMENT

This keyword specifies a comment to be associated with the current transaction and is typically used with distributed transactions. The text must be a quoted literal no more than 50 characters long.

WORK

This keyword is optional and has no effect except to improve readability.

Usage Notes

The COMMIT statement releases all row and table locks. It also erases any savepoints you marked since the last commit or rollback. Until your changes are committed, the following conditions hold:

You can see the changes when you query the tables you modified, but other users cannot see the changes.
If you change your mind or need to correct a mistake, you can use the ROLLBACK statement to roll back (undo) the changes.

If you commit while a FOR UPDATE cursor is open, a subsequent fetch on that cursor raises an exception. The cursor remains open, however, so you should close it. For more information, see "Using FOR UPDATE".

When a distributed transaction fails, the text specified by COMMENT helps you diagnose the problem. If a distributed transaction is ever in doubt, Oracle stores the text in the data dictionary along with the transaction ID. For more information about distributed transactions, see Oracle9i Database Concepts.

In SQL, the FORCE clause manually commits an in-doubt distributed transaction. However, PL/SQL does not support this clause. For example, the following statement is not allowed:

COMMIT WORK FORCE '23.51.54';  -- not allowed

In embedded SQL, the RELEASE option frees all Oracle resources (locks and cursors) held by a program and disconnects from the database. However, PL/SQL does not support this option. For example, the following statement is not allowed:

COMMIT WORK RELEASE;  -- not allowed