Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## 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:

- connection to the database

- `COMMIT` (committing changes to the database, either automatically or manually)

- `ROLLBACK` (canceling changes to the database)

A transaction ends with a `COMMIT` or `ROLLBACK` operation.

Note: 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".)

Note: 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:

    ctx.getConnection().setAutoCommit(false);

or:

    ctx.getConnection().setAutoCommit(true);

Note: 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).

Notes: 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`.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index