home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


4.2 DBMS_TRANSACTION: Interfacing to SQL Transaction Statements

The DBMS_TRANSACTION package provides a programmatic interface to a number of the SQL transaction statements. The majority of the DBMS_TRANSACTION programs have SQL equivalents that you can utilize directly from within SQL. For this reason, developers and DBAs may choose to use the direct SQL equivalents rather than these procedures. A number of other procedures and functions have no equivalents, however, and nicely abstract the PL/SQL programmer or database administrator from the internal details managed by the database.

4.2.1 Getting Started with DBMS_TRANSACTION

The DBMS_TRANSACTION package is created when the Oracle database is installed. The dbmsutil.sql script (found in the built-in packages source code directory, as described in Chapter 1 ) contain the source code for this package's specification. This script is called by catproc.sql , which is normally run immediately after database creation. The script creates the public synonym DBMS_TRANSACTION for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

4.2.1.1 DBMS_TRANSACTION programs

Table 4.4 lists the procedures and functions available through DBMS_TRANSACTION, along with their SQL equivalents (if applicable).


Table 4.4: DBMS_TRANSACTION Programs

Name

Description

Use in

SQL?

ADVISE_COMMIT

Executes the equivalent of the ALTER SESSION ADVISE COMMIT command.

Yes

ADVISE_NOTHING

Executes the equivalent of the ALTER SESSION ADVISE NOTHING command.

Yes

ADVISE_ROLLBACK

Executes the equivalent of the ALTER SESSION ADVISE ROLLBACK command.

Yes

BEGIN_DISCRETE_TRANSACTION

Sets the discrete transaction mode.

No

COMMIT

Executes the equivalent of the COMMIT command.

Yes

COMMIT_COMMENT

Executes the equivalent of the COMMIT COMMENT command.

Yes

COMMIT_FORCE

Executes the equivalent of the COMMIT FORCE command.

Yes

LOCAL_TRANSACTION_ID

Returns a local (to instance) unique identfier for the current transaction.

No

PURGE_MIXED

Deletes information on a mixed outcome transaction (a possible scenario with two-phase commit).

No

PURGE_LOST_DB_ENTRY

Removes "lost database entries" otherwise used to control recovery in pending two-phase commit operations.

No

READ_ONLY

Executes the equivalent of the SET TRANSACTION READ ONLY command.

Yes

READ_WRITE

Executes the equivalent of the SET TRANSACTION READ WRITE command.

Yes

ROLLBACK

Executes the equivalent of the ROLLBACK command.

Yes

ROLLBACK_FORCE

Executes the equivalent of the ROLLBACK FORCE command.

Yes

ROLLBACK_SAVEPOINT

Executes the equivalent of the ROLLBACK TO command.

Yes

SAVEPOINT

Executes the equivalent of the SAVEPOINT command.

Yes

STEP_ID

Returns a local (to local transaction) unique positive integer that orders the DML operations of a transaction.

No

SEGMENT

Executes the equivalent of the SET TRANSACTION USE ROLLBACK SEGMENT command.

Yes

4.2.1.2 DBMS_TRANSACTION exceptions

The DBMS_TRANSACTION package gives names (using the EXCEPTION_INIT pragma) to Oracle exceptions -8175 and -8176 as follows:

Name

Number

Description.

DISCRETE_TRANSACTION_FAILED

-8175

Discrete transaction restriction violated. An attempt was made to perform an action that is not currently supported in a discrete transaction. Roll back the transaction and retry it as a normal transaction.

CONSISTENT_READ_FAILURE

-8176

Cannot continue consistent read for the table/index -- no undo records. Oracle encountered an operation that does not generate undo records. Retry the operation with a different snapshot time. If an index is involved, retry the operation without using the index.

These exceptions may be raised in calls to the BEGIN_DISCRETE_TRANSACTION procedure.

4.2.2 Advising Oracle About In-Doubt Transactions

The DBMS_TRANSACTION advise procedures (ADVISE_COMMIT, ADVISE_NOTHING, and ADVISE_ROLLBACK) specify what in-doubt transaction advice is sent to remote databases during distributed transactions. This advice appears on the remote database in the ADVICE column of the DBA_2PC_PENDING data dictionary view if the distributed transaction becomes in doubt (i.e., a network or machine failure occurs during the commit). The remote database administrator can then review the DBA_2PC_PENDING information and manually commit or roll back in-doubt transactions using the FORCE clause of the COMMIT or ROLLBACK commands.

Each call to an ADVISE procedure remains in effect for the duration of that connection or until a different ADVISE procedure call is made. This allows you to send different advice to various remote databases.

4.2.2.1 The DBMS_TRANSACTION. ADVISE_ROLLBACK, and ADVISE_COMMIT procedures

Here are the headers for the three advise procedures:

PROCEDURE DBMS_TRANSACTION.ADVISE_ROLLBACK;

PROCEDURE DBMS_TRANSACTION.ADVISE_NOTHING;

PROCEDURE DBMS_TRANSACTION.ADVISE_COMMIT;

4.2.2.1.1 Example

In the following example, we address a common data-warehousing scenario. We want to promote daily extract data from our legacy systems to each of our data marts and our corporate data warehouse. First, the extract data is summarized and loaded into a staging database copy of the fact table. Then, this fact table's data is promoted to each of the data marts and the data warehouse. The marketing department wants its data mart loaded very aggressively (i.e., ADVISE_COMMIT). The accounting department, being more conservative, wants its data mart loaded with caution (i.e., ADVISE_ROLLBACK). Finally, management does not have a preference for loading the data warehouse. We could run the following PL/SQL locally from our staging database:

BEGIN
   FOR fact_rec IN (SELECT * FROM fact_load_table)
   LOOP
      DBMS_TRANSACTION.ADVISE_COMMIT;
      INSERT INTO fact_table@marketing_data_mart
         VALUES (fact_rec.product_id, fact_rec.location_id,
         fact_record.period_id, fact_rec.numeric_value1);

      DBMS_TRANSACTION.ADVISE_ROLLBACK;
      INSERT INTO fact_table@accounting_data_mart
         VALUES (fact_rec.product_id, fact_rec.location_id, 
         fact_record.period_id, fact_rec.numeric_value1);

       DBMS_TRANSACTION.ADVISE_NOTHING;
       INSERT INTO fact_table@corp_data_warehouse
         VALUES (fact_rec.product_id, fact_rec.location_id, 
         fact_record.period_id, fact_rec.numeric_value1);

      COMMIT;
   END LOOP;
END;
/

4.2.3 Committing Data

The DBMS_TRANSACTION package offers a number of programs you can use to issue COMMITs in your application.

4.2.3.1 The DBMS_TRANSACTION.COMMIT procedure

The COMMIT procedure is provided primarily for completeness. It is equivalent to the COMMIT command of PL/SQL. Here's the header for this procedure:

PROCEDURE DBMS_TRANSACTION.COMMIT;

There is no advantage to using this program instead of the COMMIT command.

4.2.3.2 The DBMS_TRANSACTION.COMMIT_COMMENT procedure

The COMMIT_COMMENT procedure specifies what in-doubt transaction comment is sent to remote databases during distributed transactions. The specification for the procedure follows:

PROCEDURE DBMS_TRANSACTION.COMMIT_COMMENT (cmnt IN VARCHAR2);

This comment (cmnt parameter) appears on the remote database in the TRAN_COMMENT column of the DBA_2PC_PENDING data dictionary view if the distributed transaction becomes in doubt (i.e., a network or machine failure occurs during the commit). The remote database administrator can then review the DBA_2PC_PENDING information and manually commit or roll back in-doubt transactions using the FORCE clause of the COMMIT or ROLLBACK commands.

In the following example, we update our previous data mart and data warehouse promotion PL/SQL code to utilize the COMMIT_COMMENT procedure:

BEGIN
   FOR fact_rec IN (SELECT * FROM fact_load_table)
   LOOP
      DBMS_TRANSACTION.ADVISE_COMMIT;
      INSERT INTO fact_table@marketing_data_mart
         VALUES (fact_rec.product_id, 
             fact_rec.location_id, fact_record.period_id, 
             fact_record.numeric value1);

      DBMS_TRANSACTION.ADVISE_ROLLBACK;
      INSERT INTO fact_table@accounting_data_mart
         VALUES (fact_rec.product_id, fact_rec.location_id, 
             fact_record.period_id, 
             fact_rec.numeric_value1);

      DBMS_TRANSACTION.ADVISE_NOTHING;
       INSERT INTO fact_table@corp_data_warehouse
         VALUES (fact_rec.product_id, 
             fact_rec.location_id, fact_record.period_id, 
             fact_rec.numeric_value1);

      DBMS_TRANSACTION.COMMIT_COMMENT
         ('Fact Load for date: '||TO_CHAR(sysdate,'MON-DD-YYYY'));
   END LOOP;
END;
/

4.2.3.3 The DBMS_TRANSACTION.COMMIT_FORCE procedure

The COMMIT_FORCE procedure manually commits local in doubt, distributed transactions. Here's the specification for the procedure:

PROCEDURE DBMS_TRANSACTION.COMMIT_FORCE
    (xid IN VARCHAR2
    ,scn IN VARCHAR2 DEFAULT NULL);

Parameters are summarized in the following table.

Parameter

Description

xid

The transaction's local or global transaction ID. To find these transaction IDs, query the data dictionary view DBA_2PC_PENDING.

scn

System change number (SCN) under which to commit the transaction.

Specifying a system change number (scn parameter) allows you to commit an in-doubt transaction with the same SCN assigned by other nodes, thus maintaining the synchronized commit time of the distributed transaction. If the scn parameter is omitted, the transaction is committed using the current SCN.

Any decisions to force in-doubt transactions should be made after consulting with the database administrator(s) at the remote database location(s). If the decision is made to locally force any transactions, the database administrator should either commit or roll back such transactions (as was done by nodes that successfully resolved the transactions). Otherwise, the administrator should query the DBA_2PC_PENDING view's ADVICE and TRAN_COMMENT columns for further insight.

For more information on this topic, see "Manually Overriding In-Doubt Transactions" in the Oracle Corporation document Oracle8 Server Distributed Systems.

4.2.4 Rolling Back Changes

The DBMS_TRANSACTION package offers a number of programs you can use to issue rollbacks in your application.

4.2.4.1 The DBMS_TRANSACTION.ROLLBACK procedure

The ROLLBACK procedure is provided primarily for completelness. It is equivalent to the ROLLBACK command of PL/SQL. The header for this procedure follows:

PROCEDURE DBMS_TRANSACTION.ROLLBACK;

There is no advantage to using this program instead of the ROLLBACK command.

4.2.4.2 The DBMS_TRANSACTION.ROLLBACK_FORCE procedure

The ROLLBACK_FORCE procedure manually rolls back local in-doubt, distributed transactions. The specification for the procedure is,

PROCEDURE DBMS_TRANSACTION.ROLLBACK_FORCE (xid IN VARCHAR2);

where xid identifies the transaction's local or global transaction ID. To find these transaction IDs, query the data dictionary view DBA_2PC_PENDING.

Any decisions to force in-doubt transactions should be made after consulting with the database administrator(s) at the remote database location(s). If the decision is made to locally force any transactions, the database administrator should either commit or roll back such transactions (as was done by nodes that successfully resolved the transactions). Otherwise, the administrator should query the DBA_2PC_PENDING view's ADVICE and TRAN_COMMENT columns for further insight.

For more information on this topic, see "Manually Overriding In-Doubt Transactions" in the Oracle Corporation document Oracle8 Server Distributed Systems .

4.2.4.3 The DBMS_TRANSACTION.SAVEPOINT procedure

The SAVEPOINT procedure is equivalent to the SAVEPOINT command, which is already implemented as part of PL/SQL. The header for this procedure is,

PROCEDURE DBMS_TRANSACTION.SAVEPOINT (savept IN VARCHAR2);

where savept specifies the savepoint.

Why would you use this procedure and not simply rely on the SAVEPOINT command? When you use SAVEPOINT, you must use an "undeclared identifier" for the savepoint:

BEGIN
   SAVEPOINT right_here;
   do_my_stuff;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK TO right_here;
END;

The "right_here" identifier is not declared anywhere; it is simply hard-coded into your application. With the DBMS_TRANSACTION programs, you can soft code these savepoint names, as you can see from the following block:

DECLARE
   v_sp VARCHAR2(30) := 'right_here';
BEGIN
   DBMS_TRANSACTION.SAVEPOINT (v_sp);
   do_my_stuff;

EXCEPTION
   WHEN OTHERS
   THEN
      /* Soft-coded rollback to, as well! */
      DBMS_TRANSACTION.ROLLBACK_SAVEPOINT (v_sp);
END;
/

4.2.4.4 The DBMS_TRANSACTION.ROLLBACK_SAVEPOINT procedure

The ROLLBACK_SAVEPOINT procedure is equivalent to the ROLLBACK TO command in PL/SQL. The header for this procedure is,

PROCEDURE DBMS_TRANSACTION.ROLLBACK_SAVEPOINT (savept IN VARCHAR2);

where savept specifies the savepoint.

You should use this program in coordination with the SAVEPOINT procedure, as illustrated in the example in the previous section. With this program, you can roll back to a savepoint that is not hard-coded into your application.

4.2.4.5 The DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT procedure

The USE_ROLLBACK_SEGMENT procedure assigns the current transaction to the specified rollback segment. This option also establishes the transaction as a read-write transaction.

Here's the specification for the procedure:

PROCEDURE DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT (rb_name IN VARCHAR2); 

The specified rollback segment (rb_name) must be online. Often, the rollback specified is a large one that is kept offline during the day and is specifically enabled at night for large batch jobs. You cannot use both the DBMS_TRANSACTION.READ_ONLY (see next section) and the USE_ROLLBACK_SEGMENT procedures within the same transaction. Read-only transactions do not generate rollback information and thus cannot be assigned rollback segments.

In the following example, we have modified our data warehousing extract promotion program to reference the staging fact table remotely in order to load our local fact table -- and to do so utilizing the big rollback segment. This version of the code has the advantage that it could be run without change from each remote database (assuming that each remote database had a big rollback segment named BIG_RBS).

BEGIN
   DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT('BIG_RBS');
   INSERT INTO fact_table
      SELECT * FROM fact_table@staging;
   COMMIT;
 





END;
/

4.2.5 Setting Transaction Characteristics

DBMS_TRANSACTION offers several programs that set various characteristics of the transaction for your session.

4.2.5.1 The DBMS_TRANSACTION.READ_ONLY procedure

The READ_ONLY procedure establishes transaction-level read consistency (i.e., repeatable reads). Here's the header for this program:

PROCEDURE DBMS_TRANSACTION.READ_ONLY;

Once a transaction is designated as read-only, all queries within that transaction can see only changes committed prior to that transaction's start. Hence, read-only transactions permit you to issue two or more queries against tables that may be undergoing concurrent inserts or updates, and yet return results consistent as of the transaction's start. The READ_ONLY procedure is quite useful for reports. Long-running read-only transactions can receive a "snapshot too old" error (ORA-01555). If this occurs, increase rollback segment sizes.

In the following example, we can separately query the order and item tables successfully, regardless of other transactions. Even if someone deletes the item we are interested in while we query the order, a read-consistent image will be maintained in the rollback segments. So we will always be able to see the items for any order we look at.

DECLARE
   lv_order_count INTEGER := 0;
  lv_item_count INTEGER := 0;
BEGIN
   DBMS_TRANSACTION.READ_ONLY;
   SELECT COUNT(*) INTO lv_order_count FROM order WHERE order_number = 12345;
   SELECT COUNT(*) INTO lv_item_count FROM item WHERE order_number = 12345;
END;
/

4.2.5.2 The DBMS_TRANSACTION.READ_WRITE procedure

The READ_WRITE procedure establishes the current transaction as a read-write transaction. As this is the default transaction mode, you will not often need to use this procedure. The header for this program follows:

PROCEDURE DBMS_TRANSACTION.READ_WRITE;

The following example demonstrates using READ_WRITE in a transaction where we want to delete an order and its associated items. However, this example would execute in exactly the same way, even if the READ_WRITE procedure call were commented out.

BEGIN
   DBMS_TRANSACTION.READ_WRITE;
   DELETE FROM item WHERE order_number = 12345;
   DELETE FROM order WHERE order_number = 12345;
END;
/

4.2.5.3 The DBMS_TRANSACTION.BEGIN_DISCRETE_TRANSACTION procedure

The BEGIN_DISCRETE_TRANSACTION procedure streamlines transaction processing so that short transactions can execute more rapidly. The header for this program follows:

PROCEDURE DBMS_TRANSACTION.BEGIN_DISCRETE_TRANSACTION;

During discrete transactions, normal redo information is generated, although it is stored in a separate location in memory. When the discrete transaction commits, the redo information is written to the redo log file and data block changes are applied directly. As a result, there is no need for undo information in rollback segments. The block is then written to the database file in the usual manner. The call to this procedure is effective only until the transaction is committed or rolled back; the next transaction is processed as a standard transaction.

4.2.5.3.1 Restrictions

Although discrete transactions offer improved performance, there are numerous restrictions:

  • The database initialization parameter DISCRETE_TRANSACTIONS_ENABLED must be set to TRUE; otherwise, calls to this procedure are ignored and transactions function normally.

  • Discrete transactions cannot be distributed transactions.

  • Discrete transactions can change each database block only once.

  • Discrete transactions cannot see their own changes (since there are no rollback segments).

  • Discrete transactions cannot perform inserts or updates on both tables involved in a referential integrity constraint.

  • Discrete transactions cannot modify tables containing any LONG values.

4.2.5.3.2 Exceptions

This procedure may raise either of the following exceptions:

DBMS_TRANSACTION.DISCRETE_TRANSACTION_FAILED
DBMS_TRANSACTION.CONSISTENT_READ_FAILURE

4.2.5.3.3 Example

In the following example, we have modified the last version of our data warehousing extract promotion program to utilize discrete transactions. The code is written in such a way that it ensures that the transaction is attempted again in the event of a discrete transaction failure. This coding practice should be followed strictly.

BEGIN
   FOR fact_rec in (SELECT * FROM fact table@staging)
   LOOP
      DBMS_TRANSACTION. BEGIN_DISCRETE_TRANSACTION;
      FOR I in 1 .. 2
      LOOP
         BEGIN
            INSERT INTO fact_table
               VALUES (fact_rec.product_id, 
                  fact_rec.location_id,
                  fact_record.period_id, 
                  fact_rec.numeric_value1, 
                  fact_rec.numeric_value2);
            COMMIT;
            EXIT;
         EXCEPTION
            WHEN DBMS_TRANSACTION.DISCRETE_TRANSACTION_FAILED 
            THEN 
               ROLLBACK; 
         END;
      END LOOP;
   END LOOP;
END;
/

For more information on this topic, see "Using Discrete Transactions" in the Oracle Corporation document Oracle8 Server Tuning .

4.2.6 Cleaning Up Transaction Details

When performing two-phase commits, it is possible to "strand" information about recovery or resolution steps. The two procedures PURGE_MIXED and PURGE_LOST_DB_ENTRY are provided by Oracle to clean up that information.

4.2.6.1 The DBMS_TRANSACTION.PURGE_MIXED procedure

The PURGE_MIXED procedure deletes information about a given in-doubt, distributed transaction that has had mixed outcomes as the consequence of transaction resolution mismatch. This occurs when an in-doubt, distributed transaction is forced to commit or roll back on one node, and other nodes do the opposite. For example, we may force commit on node 1 a distributed transaction that rolled back on node 2. Oracle cannot automatically resolve such inconsistencies, but it does flag entries in the DBA_2PC_PENDING view by setting the MIXED column to "yes." When the database administrator is sure that any inconsistencies for a transaction have been resolved, he or she can call PURGE_MIXED procedure. The specification for the procedure is,

PROCEDURE DBMS_TRANSACTION.PURGE_MIXED (xid IN VARCHAR2);

where xid identifies the mixed transaction's local transaction ID, which can be found in the LOCAL_TRAN_ID column of the data dictionary view DBA_2PC_PENDING.

For more information on this topic, see "Manually Overriding In-Doubt Transactions" in the Oracle Corporation document Oracle8 Server Distributed Systems .

4.2.6.2 The DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY procedure

The PURGE_LOST_DB_ENTRY procedure deletes information about a given in-doubt, distributed transaction that has had mixed outcomes as the consequence of a lost database. This occurs when an in-doubt, distributed transaction is able to commit or roll back on one node and other nodes have either destroyed or recreated their databases. For example, we may successfully commit on node 1 a distributed transaction that is no longer represented in the recreation of the database now on node 2. Oracle cannot automatically resolve such inconsistencies. The information in DBA_2PC_PENDING will never be deleted, and Oracle will try periodically to recover (even though it can't).

When the database administrator is sure that any inconsistencies for a transaction have been resolved, he or she can call the PURGE_MIXED procedure.

The specification for the procedure is,

PROCEDURE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (xid IN VARCHAR2);

where xid identifies the mixed transaction's local transaction ID, which can be found in the LOCAL_TRAN_ID column of the data dictionary view DBA_2PC_PENDING.

Oracle's dbmsutil.sql file, which contains the definition of the DBMS_TRANSACTION package, offers some insights into when and how you would use this program. According to that file, the DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY procedure should only be used when the other database is lost or has been recreated. Any other use may leave that other database in an unrecoverable or inconsistent state.

Before you run automatic recovery, the transaction appears in the DBA_2PC_PENDING view in the state "collecting," "committed," or "prepared." If the DBA has forced an in-doubt transaction to have a particular result by using the COMMIT FORCE or ROLLBACK FORCE options, then the additional states FORCED COMMIT or FORCED ROLLBACK may also appear.

Automatic recovery will normally delete entries that are in any of these states. The only exception occurs when the recovery process finds a forced transaction that is in a state inconsistent with other sites in the transaction. In this case, the entry will be left in the table and the MIXED column will be given a value of "yes."

Under certain conditions, it may not be possible for an automatic recovery to execute without errors. For example, a remote database may have been permanently lost. In this case, even if it is recreated, it will be assigned a new database ID. As a result, recovery cannot identify it. (A possible symptom for this situation is when the ORA-02062 error is raised.)

In this case, the DBA may use the DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY procedure to clean up any entries whose state is not "prepared."

The following table indicates what the various states indicate about the transaction and what the DBA actions should be.

State Column

State of Global Transaction

State of Local Transaction

Normal DBA Action

Alternative DBA Action

Collecting

Rolled back

Rolled back

None

PURGE_LOST_DB_ENTRY[ 1 ]

Committed

Committed

Committed

None

PURGE_LOST_DB_ENTRY

Prepared

Unknown

Unknown

None

COMMIT_FORCE or

ROLLBACK_FORCE

Forced commit

Unknown

Committed

None

PURGE_LOST_DB_ENTRY

Forced rollback

Unknown

Rolled back

None

PURGE_LOST_DB_ENTRY

Forced commit (mixed)

Mixed

Committed

[ 2 ]

 

 

Forced rollback (mixed)

Mixed

Rolled back

 

[1] Use only if significant reconfiguration has occurred so that automatic recovery cannot resolve the transaction. Examples are total loss of the remote database, reconfiguration in software resulting in loss of two-phase commit capability, or loss of information from an external transaction coordinator such as a TP monitor.

[2] Examine manual action to remove inconsistencies, then use the PROCEDURE_PURGE_MIXED. The xid argument must be set to the value of the LOCAL_TRAN_ID column in the DBA_2PC_PENDING table.

For more information on this topic, see "Manually Overriding In-Doubt Transactions" in the Oracle Corporation document Oracle8 Server Distributed Systems, and the dbmsutil file under comments for PURGE_LOST_DB_ENTRY.

4.2.7 Returning Transaction Identifiers

The functions described in this section return indentifiers used by DBMS_TRANSACTION.

4.2.7.1 The DBMS_TRANSACTION.LOCAL_TRANSACTION_ID function

The LOCAL_TRANSACTION_ID function returns the unique identifier for the current transaction. The specification for the function follows:

FUNCTION DBMS_TRANSACTION.LOCAL_TRANSACTION_ID
   (create_transaction IN BOOLEAN := FALSE)
RETURN VARCHAR2;

The create_transaction parameter specifies whether to create a new transaction or use the current one. The function returns NULL if there is no current transaction.

So in your session, the transaction ID begins as NULL and is assigned a value upon first DML operation (transaction initiation). Transaction closure (COMMIT or ROLLBACK) nullifies the transaction ID. Explicit transaction initiation (passing TRUE for second argument to procedure call) assigns a new transaction ID value, regardless of whether you've actually started a transaction.

See an example of using this function in the next section (STEP_ID).

4.2.7.2 The DBMS_TRANSACTION.STEP_ID function

The STEP_ID function returns the unique positive integer that orders the DML operations of the current transaction. Here's the specification for the function:

FUNCTION DBMS_TRANSACTION.STEP_ID RETURN NUMBER;

If a transaction has not been initiated, then this function returns NULL. Values of step ID across transactions do not appear to have any guaranteed relationship to each other. Within a transaction, however, they will always be increasing.

The following script (written by John Beresniewicz) demonstrates the usage and output of the LOCAL_TRANSACTION_ID and STEP_ID functions:

/* Filename on companion disk:

 transid.sql */*
set serveroutput on size 1000000
CREATE TABLE foo (col1 NUMBER);
DECLARE
   trx   VARCHAR2(200);
   step  NUMBER;

   PROCEDURE printem(message_IN IN VARCHAR2)
   IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE(message_IN);
      DBMS_OUTPUT.PUT_LINE
         ('Trx id: '||RPAD(NVL(trx,'NULL'),30)||
          'Step: '||NVL(TO_CHAR(step),'NULL') );
   END printem;
   
   PROCEDURE getem
   IS 
   BEGIN
      trx := DBMS_TRANSACTION.local_transaction_id;
      step := DBMS_TRANSACTION.step_id;
   END getem;
        
BEGIN
   /* close any open transaction */
   COMMIT; 
   /* how does it look at start of trx? */
   getem; printem('BEGIN');
   /*
   || do some DML and see  how step changes
   */
   INSERT INTO foo SELECT ROWNUM FROM dictionary;
   getem; printem('INSERT');
   UPDATE foo SET col1 = col1 + 1;
   getem; printem('UPDATE');
   DELETE FROM foo WHERE MOD(col1,2) = 0;
   getem; printem('DELETE');
   /* 
   || now commit, they should be null
   */
   COMMIT;
   getem; printem('COMMIT');
   /*
   || now explicitly open a new transaction
   */ 
   trx := DBMS_TRANSACTION.local_transaction_id(TRUE);
   getem; printem('NEW TRX FORCED');
END;
/

The results of this script follow:

SQL> @transid.sql
Table created.
BEGIN
Trx id: NULL                          Step: NULL
INSERT
Trx id: 1.6.28680                     Step: 42802896748631309
UPDATE
Trx id: 1.6.28680                     Step: 42802896748632625
DELETE
Trx id: 1.6.28680                     Step: 42802896748633616
COMMIT
Trx id: NULL                          Step: NULL
NEW TRX FORCED


Trx id: 2.7.28508                     

Step: 42045333236522790


Previous: 4.1 DBMS_LOCK: Creating and Managing Resource Locks Oracle Built-in Packages Next: 5. Oracle Advanced Queuing
4.1 DBMS_LOCK: Creating and Managing Resource Locks Book Index 5. Oracle Advanced Queuing

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference