Transactions handling

Informix^® and ORACLE handle transactions differently. The differences in the transactional models can affect the program logic.

Informix native mode (non ANSI):

DDL statements can be executed (and canceled) in transactions.
Transactions must be started with BEGIN WORK. Statements executed outside of a transaction are automatically committed.

ORACLE:

Beginnings of transactions are implicit; two transactions are delimited by COMMIT or ROLLBACK.
The current transaction is automatically committed when a DDL statement is executed.

Transactions in stored procedures: avoid using transactions in stored procedures to allow the client applications to handle transactions, in accordance with the transaction model.

Informix version 11.50 introduces savepoints with the following instructions:

    SAVEPOINT name [UNIQUE]
    ROLLBACK [WORK] TO SAVEPOINT [name] ]
    RELEASE SAVEPOINT name

ORACLE supports savepoints too. However, there are differences:

Savepoints cannot be declared as UNIQUE
Rollback must always specify the savepoint name
You cannot release savepoints (RELEASE SAVEPOINT)

Solution

Regarding transaction control instructions, BDL applications do not have to be modified in order to work with ORACLE. The Informix behavior is simulated with an autocommit mode in the ORACLE interface. A switch to the explicit commit mode is done when a BEGIN WORK is performed by the BDL program.

When executing a DDL statement inside a transaction, ORACLE automatically commits the transaction. Therefore, you must extract the DDL statements from transaction blocks.

If you want to use savepoints, do not use the UNIQUE keyword in the savepoint declaration, always specify the savepoint name in ROLLBACK TO SAVEPOINT, and do not drop savepoints with RELEASE SAVEPOINT.