Oracle8i SQLJ Developer's Guide and Reference
Release 3 (8.1.7)

Part Number A83723-01


Solution Area



Go to previous page Go to beginning of chapter Go to next page

Basic Transaction Control

This section discusses how to manage your changes to the database.

For information about SQLJ support for more advanced transaction control functions--access mode and isolation level--see "Advanced Transaction Control".

Overview of Transactions

A transaction is a sequence of SQL operations that Oracle treats as a single unit. A transaction begins with the first executable SQL statement after any of the following:

A transaction ends with a COMMIT or ROLLBACK operation.


In the Oracle database, all DDL commands (such as CREATE and ALTER) include an implicit COMMIT. This will commit not only the DDL command, but any preceding DML commands (INSERT, DELETE, UPDATE) that had not yet been committed or rolled back.  

Automatic Commits versus Manual Commits

In using SQLJ or JDBC, you can either have your changes automatically committed to the database or commit them manually. In either case, each COMMIT operation starts a new transaction. You can specify that changes be committed automatically by enabling the auto-commit flag, either when you define a SQLJ connection, or by using the setAutoCommit() method of the underlying JDBC connection object of an existing connection. You can use manual control by disabling the auto-commit flag and using SQLJ COMMIT and ROLLBACK statements.

Enabling auto-commit may be more convenient, but gives you less control. You have no option to roll back changes, for example. In addition, some SQLJ or JDBC features are incompatible with auto-commit mode. For example, you must disable the auto-commit flag for update batching or SELECT FOR UPDATE syntax to work properly.

Specifying Auto-Commit as You Define a Connection

When you use the Oracle.connect() or Oracle.getConnection() method to create a DefaultContext instance and define a connection, the auto-commit flag is set to false by default. There are signatures of these methods, however, that allow you to set this flag explicitly. The auto-commit flag is always the last parameter.

The following is an example of instantiating DefaultContext and using the default false setting for auto-commit mode:

Oracle.getConnection (
   "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger");

Or you can specify a true setting:

Oracle.getConnection (
   "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", true);

For the complete list of signatures for Oracle.connect() and Oracle.getConnection(), see "More About the Oracle Class".

If you use a constructor to create a connection context instance, either of DefaultContext or of a declared connection context class, you must specify the auto-commit setting. Again, it is the last parameter, as in the following example:

DefaultContext ctx = new DefaultContext (
   "jdbc:oracle:thin:@localhost:1521:orcl", "scott", "tiger", false);

For the complete list of signatures for DefaultContext constructors, see "More About the DefaultContext Class".

If you have reason to create a JDBC Connection instance directly, then the auto-commit flag is set to true by default if your program runs on a client, or false by default if it runs in the server. (You cannot specify an auto-commit setting when you create a JDBC Connection instance directly, but you can use the setAutoCommit() method to alter the setting, as described in "Modifying Auto-Commit in an Existing Connection".)


Auto-commit functionality is not supported by the JDBC server-side internal driver.  

Modifying Auto-Commit in an Existing Connection

There is typically no reason to change the auto-commit flag setting for an existing connection, but you can if desired. You can do this by using the setAutoCommit() method of the underlying JDBC connection object.

You can retrieve the underlying JDBC connection object by using the getConnection() method of any SQLJ connection context instance (whether it is an instance of the DefaultContext class or of a connection context class you have declared).

You can accomplish these two steps at once, as follows. In these examples, ctx is a SQLJ connection context instance:





Do not alter the auto-commit setting in the middle of a transaction.  

Using Manual COMMIT and ROLLBACK

If you disable the auto-commit flag, then you must manually commit any changes to the database.

To commit any changes (such as updates, inserts, or deletes) that have been executed since the last COMMIT operation, use the SQLJ COMMIT statement, as follows:

#sql { COMMIT };

To roll back (cancel) any changes that have been executed since the last COMMIT operation, use the SQLJ ROLLBACK statement, as follows:

#sql { ROLLBACK };

Do not use the COMMIT or ROLLBACK commands when auto-commit is enabled. This will result in unspecified behavior (or perhaps SQL exceptions).


  • All DDL statements in Oracle SQL include an implicit COMMIT operation. There is no special SQLJ functionality in this regard; such statements follow standard Oracle SQL rules.

  • If auto-commit mode is off and you close a connection context instance from a client application, then any changes since your last COMMIT will be rolled back (unless you close the connection context instance with KEEP_CONNECTION, which is explained in "Closing Shared Connections").


Effect of Commits and Rollbacks on Iterators and Result Sets

COMMIT operations (either automatic or manual) and ROLLBACK operations do not affect open result sets and iterators. The result sets and iterators will still be open, and all that is relevant to their content is the state of the database at the time of execution of the SELECT statements that populated them.

This also applies to UPDATE, INSERT, and DELETE statements that are executed after the SELECT statements--execution of these statements does not affect the contents of open result sets and iterators.

Consider a situation where you SELECT, then UPDATE, then COMMIT. A result set or iterator populated by the SELECT statement will be unaffected by the UPDATE and COMMIT.

As a further example, consider a situation where you UPDATE, then SELECT, then ROLLBACK. A result set or iterator populated by the SELECT will still contain the updated data, regardless of the subsequent ROLLBACK.

Go to previous page
Go to beginning of chapter
Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.


Solution Area