Oracle DBMS_TRANSACTION
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Provides access to SQL transaction statements from stored procedures
AUTHID CURRENT_USER
Dependencies
DBMS_AQADM_SYS DBMS_ISCHED MGMT_USER
DBMS_ASSERT DBMS_LOGMNR_INTERNAL PENDING_SESSIONS$
DBMS_DEFER DBMS_PRVTAQIM PENDING_SUB_SESSIONS$
DBMS_DEFER_INTERNAL_SYS DBMS_PRVTAQIP PENDING_TRANS$
DBMS_DEFER_SYS DBMS_SNAPSHOT SYSLSBY_EDS_DDL_TRIG
DBMS_IJOB MGMT_DELTA  
Documented Yes
Exceptions
Error Code Reason
ORA-08175 DISCRETE_TRANSACTION_FAILED: Cannot be performed as a discrete transaction
ORA-08176 CONSISTENT_READ_FAILURE: Data changed by an operation that does not generate rollback data
First Available 7.3.4
Security Model Owned by SYS with  EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmstrns.sql
Subprograms
 
ADVISE_COMMIT
Equivalent to SQL "ALTER SESSION ADVISE COMMIT" dbms_transaction.advise_commit;
exec dbms_transaction.advise_commit;
 
ADVISE_NOTHING
Equivalent to SQL "ALTER SESSION ADVISE NOTHING" dbms_transaction.advise_nothing;
exec dbms_transaction.advise_nothing;
 
ADVISE_ROLLBACK
Equivalent to SQL "ALTER SESSION ADVISE ROLLBACK" dbms_transaction.advise_rollback;
exec dbms_transaction.advise_rollback;
 
BEGIN_DISCRETE_TRANSACTION
Set "discrete transaction mode" for this transaction dbms_transaction.begin_discrete_transaction;
exec dbms_transaction.begin_discrete_transaction;
 
COMMIT
Equivalent to SQL "COMMIT" dbms_transaction.commit;
exec dbms_transaction.commit;
 
COMMIT_COMMENT
Comment a commit statement dbms_transaction.commit_comment(cmnt IN VARCHAR2);
CREATE TABLE t (
testcol NUMBER(2));

INSERT INTO t (testcol) VALUES (1);

exec dbms_transaction.commit_comment('Commiting a test record');

set linesize 121

SELECT local_tran_id, global_tran_id, state, mixed, advice,
tran_comment
FROM dba_2pc_pending;
 
COMMIT_FORCE
Equivalent to SQL "COMMIT FORCE dbms_transaction.commit_force(
xid IN VARCHAR2,               -- local or global transaction id
scn IN VARCHAR2 DEFAULT NULL); -- system change number
exec dbms_transaction.commit_force(xid, scn);
 
LOCAL_TRANSACTION_ID
Returns local (to instance) unique identifier for current transaction. Returns null if there is no current transaction. dbms_transaction.local_transaction_id(
create_transaction BOOLEAN := FALSE) RETURN VARCHAR2;
conn uwclass/uwclass@pdbdev

SELECT dbms_transaction.local_transaction_id
FROM dual;

CREATE TABLE t (
testcol NUMBER(3));

INSERT INTO t
VALUES (1);

SELECT dbms_transaction.local_transaction_id
FROM dual;

CREATE OR REPLACE FUNCTION atf RETURN VARCHAR2 IS
 PRAGMA AUTONOMOUS_TRANSACTION;
 x VARCHAR2(20);
BEGIN
  INSERT INTO t
  (testcol)
  VALUES
  (2);

  x := dbms_transaction.local_transaction_id;
  COMMIT;

  RETURN x;
END atf;
/

set serveroutput on

DECLARE
 x VARCHAR2(20);
BEGIN
  x := atf;
  dbms_output.put_line(x);

  INSERT INTO t VALUES (3);

  x := dbms_transaction.local_transaction_id;
  dbms_output.put_line(x);
  COMMIT;
END;
/
 
PURGE_LOST_DB_ENTRY
Purge in-doubt transaction if remote database is destroyed

Follow linkto the TRANSACTION page of the library for more information on use of this proc.
When a failure occurs during commit processing, automatic recovery will consistently resolve 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 will never be removed, and recovery will periodically retry. Procedure purge_lost_db_entry allows removal of such transactions from the local site.
dbms_transaction.purge_lost_db_entry(xid IN VARCHAR2); -- tx id
exec dbms_transaction.purge_lost_db_entry(xid);
 
PURGE_MIXED
Purge in-doubt transaction 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 will flag entries in DBA_2PC_PENDING by setting the MIXED column to a value of 'yes'. Oracle will never automatically delete information about a mixed outcome transaction. When the application or DBA is sure 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.
dbms_transaction.purge_mixed(xid IN VARCHAR2); -- transaction id
exec dbms_transaction.purge_mixed(xid);
 
READ_ONLY
Equivalent to SQL "SET TRANSACTION READ ONLY" dbms_transaction.read_only;
exec dbms_transaction.read_only;
 
READ_WRITE
Equivalent to SQL "SET TRANSACTION READ WRITE" dbms_transaction.read_write;
exec dbms_transaction.read_write;
 
ROLLBACK
Equivalent to SQL "ROLLBACK". dbms_transaction.rollback;
exec dbms_transaction.rollback;
 
ROLLBACK_FORCE
Equivalent to SQL "ROLLBACK FORCE <text>" dbms_transaction.rollback_force(xid IN VARCHAR2); -- tx id
exec dbms_transaction.rollback_force(xid);
 
ROLLBACK_SAVEPOINT
Equivalent to SQL "ROLLBACK TO SAVEPOINT <savepoint_name>" dbms_transaction.rollback_savepoint(savept IN VARCHAR2);
exec dbms_transaction.rollback_savepoint('SP2');
 
SAVEPOINT
Equivalent to SQL "SAVEPOINT <savepoint_name>" dbms_transaction.savepoint(savept IN VARCHAR2);
exec dbms_transaction.savepoint('SP2');
 
STEP_ID
Return local (to local transaction) unique positive integer that orders the DML operations of  transaction dbms_transaction.step_id RETURN NUMBER;
exec dbms_transaction.step_id
FROM dual;
 
USE_ROLLBACK_SEGMENT
Equivalent to SQL "SET TRANSACTION USE ROLLBACK SEGMENT

Deprecated
Used to select a specific named rollback segment for a transaction. As rollback segments will not be part of future Oracle releases, and should not exist in a well designed 9i database, this should not be used.
dbms_transaction.use_rollback_segment(rb_name IN VARCHAR2);
exec dbms_transaction.use_rollback_segment(rbs01);

Related Topics
Packages
Transaction

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved