PL/SQL User's Guide and Reference

COMMIT Statement

Description

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 "Processing Transactions" .

Syntax

commit_statement ::=

COMMIT [WORK] [COMMENT 'text'];

Keyword and Parameter Description

WORK

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

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.

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 cursor that was declared using FOR UPDATE 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 Oracle7 Server Concepts.

PL/SQL does not support the FORCE clause, which, in SQL, manually commits an in-doubt distributed transaction. For example, the following COMMIT statement is illegal:

COMMIT FORCE '23.51.54';  -- illegal

With embedded SQL, the optional RELEASE parameter is allowed after COMMIT WORK. The keyword RELEASE acts like a "disconnect" statement, which logs you off the database once your transaction is committed. PL/SQL does not support data control statements such as CONNECT, GRANT, or REVOKE. Therefore, it does not support the RELEASE parameter.