Oracle7 Server SQL Reference

ROLLBACK (Embedded SQL)

Purpose

To end the current transaction, discard all changes in the current transaction, and release all locks and optionally release resources and disconnect from the database.

Prerequisites

To roll back your current transaction, no privileges are necessary.

To manually roll back an in-doubt distributed transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually roll back an in-doubt distributed transaction originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.

Syntax

Keywords and Parameters

identifies the database to which the ROLLBACK statement is issued. The database can be identified by either:

db_name is a database identifier declared in a previous DECLARE DATABASE statement.

:host_variable is a host variable whose value is a previously declared db_name.

If you omit this clause, the statement is issued to your default database.

WORK

is optional and has no effect on ROLLBACK.

rolls back the transaction to a previously declared savepoint.

RELEASE

releases all resources and disconnects you from the database.

FORCE

manually rolls back an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING.

Usage Notes

Always explicitly commit or rollback the last transaction in a program using the RELEASE option to disconnect from Oracle.

Oracle7 automatically rolls back your current transaction if the program terminates abnormally.

The ROLLBACK command has no effect on the contents of the host variables or on the control flow of the program.

Example

This example illustrates the use of the embedded SQL ROLLBACK command:

EXEC SQL ROLLBACK TO SAVEPOINT point4