Oracle8i Supplied Packages Reference
Release 8.1.5
A68001-01

Library
Product
Contents
Index

Prev Next

51
DBMS_TRANSACTION

This package provides access to SQL transaction statements from stored procedures.

See Also:

Oracle8i SQL Reference  

Requirements

This package runs with the privileges of calling user, rather than the package owner SYS.

Summary of Subprograms

Table 51-1 DBMS_TRANSACTION Package Subprograms
Subprogram  
READ_ONLY procedure
 		 
READ_WRITE procedure
 		 
ADVISE_ROLLBACK procedure
 		 
ADVISE_NOTHING procedure
 		 
ADVISE_COMMIT procedure
 		 
USE_ROLLBACK_SEGMENT procedure
 		 
COMMIT_COMMENT procedure
 		 
COMMIT_FORCE procedure
 		 
COMMIT procedure
 		 
SAVEPOINT procedure
 		 
ROLLBACK procedure
 		 
ROLLBACK_SAVEPOINT procedure
 		 
ROLLBACK_FORCE procedure
 		 
BEGIN_DISCRETE_TRANSACTION procedure
 		 
PURGE_MIXED procedure
 		 
PURGE_LOST_DB_ENTRY procedure
 		 
LOCAL_TRANSACTION_ID function
 		 
STEP_ID function
 

READ_ONLY procedure

This procedure is equivalent to following SQL statement: 

SET TRANSACTION READ ONLY

Syntax

DBMS_TRANSACTION.READ_ONLY;

Parameters

None.

READ_WRITE procedure

This procedure is equivalent to following SQL statement: 

SET TRANSACTION READ WRITE

Syntax

DBMS_TRANSACTION.READ_WRITE;

Parameters

None.

ADVISE_ROLLBACK procedure

This procedure is equivalent to following SQL statement: 

ALTER SESSION ADVISE ROLLBACK

Syntax

DBMS_TRANSACTION.ADVISE_ROLLBACK;

Parameters

None.

ADVISE_NOTHING procedure

This procedure is equivalent to following SQL statement: 

ALTER SESSION ADVISE NOTHING

Syntax

DBMS_TRANSACTION.ADVISE_NOTHING;

Parameters

None.

ADVISE_COMMIT procedure

This procedure is equivalent to following SQL statement: 

ALTER SESSION ADVISE COMMIT

Syntax

DBMS_TRANSACTION.ADVISE_COMMIT;

Parameters

None.

USE_ROLLBACK_SEGMENT procedure

This procedure is equivalent to following SQL statement: 

SET TRANSACTION USE ROLLBACK SEGMENT <rb_seg_name>

Syntax

DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT (
   rb_name VARCHAR2);

Parameters

Table 51-2 USE_ROLLBACK_SEGMENT Procedure Parameters
Parameter  Description  
rb_name
 

Name of rollback segment to use.  

COMMIT_COMMENT procedure

This procedure is equivalent to following SQL statement: 

COMMIT COMMENT <text>

Syntax

DBMS_TRANSACTION.COMMIT_COMMENT (
   cmnt VARCHAR2);

Parameters

Table 51-3 COMMIT_COMMENT Procedure Parameters
Parameter  Description  
cmnt
 

Comment to associate with this commit.  

COMMIT_FORCE procedure

This procedure is equivalent to following SQL statement: 

COMMIT FORCE <text>, <number>"

Syntax

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

Parameters

Table 51-4 COMMIT_FORCE Procedure Parameters
Parameter  Description  
xid
 

Local or global transaction ID.  

 
scn
 

System change number.  

COMMIT procedure

This procedure is equivalent to following SQL statement: 

COMMIT

Here for completeness. This is already implemented as part of PL/SQL.

Syntax

DBMS_TRANSACTION.COMMIT;

Parameters

None.

SAVEPOINT procedure

This procedure is equivalent to following SQL statement: 

SAVEPOINT <savepoint_name>

Here for completeness. This is already implemented as part of PL/SQL.

Syntax

DBMS_TRANSACTION.SAVEPOINT (
   savept VARCHAR2);

Parameters

Table 51-5 SAVEPOINT Procedure Parameters
Parameter  Description  
savept
 

Savepoint identifier.  

ROLLBACK procedure

This procedure is equivalent to following SQL statement: 

ROLLBACK

Here for completeness. This is already implemented as part of PL/SQL.

Syntax

DBMS_TRANSACTION.ROLLBACK;

Parameters

None.

ROLLBACK_SAVEPOINT procedure

This procedure is equivalent to following SQL statement: 

ROLLBACK TO SAVEPOINT <savepoint_name>

Here for completeness. This is already implemented as part of PL/SQL.

Syntax

DBMS_TRANSACTION.ROLLBACK_SAVEPOINT (
   savept VARCHAR2);

Parameters

Table 51-6 ROLLBACK_SAVEPOINT Procedure Parameters
Parameter  Description  
savept
 

Savepoint identifier.  

ROLLBACK_FORCE procedure

This procedure is equivalent to following SQL statement: 

ROLLBACK FORCE <text>

Syntax

DBMS_TRANSACTION.ROLLBACK_FORCE (
   xid VARCHAR2);

Parameters

Table 51-7 ROLLBACK_FORCE Procedure Parameters
Parameter  Description  
xid
 

Local or global transaction ID.  

BEGIN_DISCRETE_TRANSACTION procedure

This procedure sets "discrete transaction mode" for this transaction.

Syntax

DBMS_TRANSACTION.BEGIN_DISCRETE_TRANSACTION;

Parameters

None.

Exceptions

Table 51-8 BEGIN_DISCRETE_TRANSACTION Procedure Exceptions
Exception  Description  
ORA-08175
 

A transaction attempted an operation which cannot be performed as a discrete transaction.

If this exception is encountered, then rollback and retry the transaction  

 
ORA-08176
 

A transaction encountered data changed by an operation that does not generate rollback data: create index, direct load or discrete transaction.

If this exception is encountered, then retry the operation that received the exception.  

Example

DISCRETE_TRANSACTION_FAILED exception;
    pragma exception_init(DISCRETE_TRANSACTION_FAILED, -8175);
CONSISTENT_READ_FAILURE exception;
    pragma exception_init(CONSISTENT_READ_FAILURE, -8176);

PURGE_MIXED procedure

When in-doubt transactions are forced to commit or rollback (instead of letting automatic recovery resolve their outcomes), there is a possibility that a transaction can have a mixed outcome: Some sites commit, and others rollback. Such inconsistency cannot be resolved automatically by Oracle; however, Oracle flags entries in DBA_2PC_PENDING by setting the MIXED column to a value of 'yes'.

Oracle never automatically deletes information about a mixed outcome transaction. When the application or DBA is certain that all inconsistencies that might have arisen as a result of the mixed transaction have been resolved, this procedure can be used to delete the information about a given mixed outcome transaction.

Syntax

DBMS_TRANSACTION.PURGE_MIXED (
   xid VARCHAR2);

Parameters

Table 51-9 PURGE_MIXED Procedure Parameters
Parameter  Description  
xid
 

Must be set to the value of the LOCAL_TRAN_ID column in the DBA_2PC_PENDING table.  

PURGE_LOST_DB_ENTRY procedure

When a failure occurs during commit processing, automatic recovery consistently resolves the results at all sites involved in the transaction. However, if the remote database is destroyed or recreated before recovery completes, then the entries used to control recovery in DBA_2PC_PENDING and associated tables are never removed, and recovery will periodically retry. Procedure PURGE_LOST_DB_ENTRY enables removal of such transactions from the local site.

Syntax

DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY (
   xid VARCHAR2);

WARNING:

PURGE_LOST_DB_ENTRY should only be used when the other database is lost or has been recreated. Any other use may leave the other database in an unrecoverable or inconsistent state.  

Before automatic recovery runs, the transaction may show up in DBA_2PC_PENDING as state "collecting", "committed", or "prepared". If the DBA has forced an in-doubt transaction to have a particular result by using "commit force" or "rollback force", then states "forced commit" or "forced rollback" may also appear. Automatic recovery normally deletes entries in any of these states. The only exception is when recovery finds a forced transaction which is in a state inconsistent with other sites in the transaction; in this case, the entry is left in the table and the MIXED column has the value 'yes'.

However, under certain conditions, it may not be possible for automatic recovery to run. For example, a remote database may have been permanently lost. Even if it is recreated, it gets a new database ID, so that recovery cannot identify it (a possible symptom is ORA-02062). In this case, the DBA may use the procedure PURGE_LOST_DB_ENTRY to clean up the entries in any state other than "prepared". The DBA does not need to be in any particular hurry to resolve these entries, because they are not holding any database resources.

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

Table 51-10 PURGE_LOST_DB_ENTRY Procedure States
State of 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 (1)  

 
Prepared
 

Unknown  

Prepared  

None  

FORCE COMMIT or ROLLBACK  

 
Forced commit
 

Unknown  

Committed  

None  

PURGE_LOST_DB_ENTRY (1)  

 
Forced 
rollback
 

Unknown  

Rolled back  

None  

PURGE_LOST_DB_ENTRY (1)  

 
Forced commit  
(mixed)
 

Mixed  

Committed  

(2)  

 

 
Forced 
rollback 
(mixed)
 

Mixed  

Rolled back  

(2)  

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

Note 2:

Examine and take any manual action to remove inconsistencies, then use the procedure PURGE_MIXED.

Parameters

Table 51-11 PURGE_LOST_DB_ENTRY Procedure Parameters
Parameter  Description  
xid
 

Must be set to the value of the LOCAL_TRAN_ID column in the DBA_2PC_PENDING table.  

LOCAL_TRANSACTION_ID function

This function returns the local (to instance) unique identifier for current transaction. It returns null if there is no current transaction.

Syntax

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

Parameters

Table 51-12 LOCAL_TRANSACTION_ID Function Parameters
Parameter  Description  
create_transaction
 

If true, then start a transaction if one is not currently active.  

STEP_ID function

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

Syntax

DBMS_TRANSACTION.STEP_ID 
   RETURN NUMBER;

Parameters

None.


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index