1.8.1 Transaction Management

The Oracle RDBMS provides a transaction model based on a unit of work. The PL/SQL language supports most, but not all, of the database model for transactions (you cannot, for example, ROLLBACK FORCE). Transactions begin with the first change to data and end with either a COMMIT or ROLLBACK. Transactions are independent of PL/SQL blocks. Transactions can span multiple PL/SQL blocks, or there can be multiple transactions in a single PL/SQL block. The PL/SQL supported transaction statements are: COMMIT, ROLLBACK, SAVEPOINT, SET TRANSACTION, and LOCK TABLE. Each is detailed here:

1.8.2 Native Dynamic SQL (Oracle8i)

Native dynamic SQL introduces a new PL/SQL statement, EXECUTE IMMEDIATE, and new semantics for the OPEN FOR, FETCH, and CLOSE statement family. The former applies to single-row queries and DDL, while the latter supports dynamic multi-row queries. The syntax for these statements is:

EXECUTE IMMEDIATE SQL_statement_string 
[INTO { define_variable_list | record | 
   object_variable }]
[USING [IN | OUT | IN OUT] bind_argument_list];

OPEN cursor_variable FOR 
   SELECT_statement_string;

FETCH cursor_variable INTO {define_variable_list 
    | record | object_variable};
CLOSE cursor_variable;

The EXECUTE IMMEDIATE statement parses and executes the SQL statement in a single step. It can be used for any SQL statement except a multi-row query. define_variable_list is a comma-delimited list of variable names; the bind_argument_list is a comma-delimited list of bind arguments. The parameter mode is optional and defaults to IN. Do not place a trailing semicolon in the SQL_statement_string.

This is the statement that can be used to execute DDL without the DBMS_SQL package. For example:

EXECUTE IMMEDIATE 'TRUNCATE TABLE foo';
EXECUTE IMMEDIATE 'GRANT SELECT ON '|| tabname_v ||
   ' TO ' || grantee_list;

The OPEN FOR statement assigns a multi-row query to a weakly typed cursor variable. The rows are then FETCHed and the cursor CLOSEd.

DECLARE 
   TYPE cv_typ IS REF CURSOR;
   cv cv_typ;
   laccount_no NUMBER;
   lbalance NUMBER;
BEGIN
   OPEN cv FOR
      'SELECT account_no, balance
         FROM accounts
        WHERE balance < 500';
   LOOP
      FETCH cv INTO laccount_no, lbalance;
      EXIT WHEN cv%NOTFOUND;
      -- Process the row.
   END LOOP;
   CLOSE cv;
END;

1.8.3 Autonomous Transactions (Oracle8i)

Autonomous transactions execute within a block of code as separate transactions from the outer (main) transaction. Changes can be committed or rolled back in an autonomous transaction without committing or rolling back the main transaction. Changes committed in an autonomous transaction are visible to the main transaction, even though they occur after the start of the main transaction. Changes committed in an autonomous transaction are visible to other transactions as well. The RDBMS suspends the main transaction while the autonomous transaction executes:

PROCEDURE main IS
BEGIN
   UPDATE ...-- Main transaction begins here.
   DELETE ...
   at_proc;  -- Call the autonomous transaction.
   SELECT ...
   INSERT ...
   COMMIT;   -- Main transaction ends here.
END;

PROCEDURE at_proc IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN        -- Main transaction suspends here.
   SELECT ...
   INSERT ...-- Autonomous transaction begins here.
   UPDATE ...
   DELETE ...
   COMMIT;   -- Autonomous transaction ends here.
END;         -- Main transaction resumes here.

So, changes made in the main transaction are not visible to the autonomous transaction and if the main transaction holds any locks that the autonomous transaction waits for, a deadlock occurs. Using the NOWAIT option on UPDATE statements in autonomous transactions can help to minimize this kind of deadlock. Functions and procedures (local program, standalone, or packaged), database triggers, top-level anonymous PL/SQL blocks, and object methods can be declared autonomous via the compiler directive PRAGMA AUTONOMOUS_TRANSACTION.

In the example below, the COMMIT does not make permanent pending changes in the calling program. Any rollback in the calling program would also have no effect on the changes committed in this autonomous procedure:

CREATE OR REPLACE PROCEDURE add_company (
   name_in   company.name%TYPE
   )
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   determine_credit(name);
   create_account(name);
   ...
   COMMIT;  -- Only commit this procedure's changes.
END add_company;