| General |
| Purpose |
Provides access to SQL transaction statements from stored procedures |
| Source |
{ORACLE_HOME}/rdbms/admin/dbmstrns.sql |
| First Available |
7.3.4 |
| Dependencies |
| DBMS_AQADM_SYS |
DBMS_PRVTAQIM |
| DBMS_ASSERT |
DBMS_PRVTAQIP |
| DBMS_DEFER |
DBMS_SNAPSHOT |
| DBMS_DEFER_INTERNAL_SYS |
MGMT_DELTA |
| DBMS_DEFER_SYS |
MGMT_USER |
| DBMS_IJOB |
PENDING_SESSIONS$ |
| DBMS_ISCHED |
PENDING_SUB_SESSIONS$ |
| DBMS_LOGMNR_INTERNAL |
PENDING_TRANS$ |
|
| Exceptions |
| Exception |
Name |
Description |
| 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 |
|
| Security Model |
EXECUTE is granted to PUBLIC |
| |
| 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". Already
part of PL/SQL |
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
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". Already part of PL/SQL |
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>". Already part of PL/SQL |
dbms_transaction.rollback_savepoint(savept IN VARCHAR2); |
|
exec dbms_transaction.rollback_savepoint('SP2'); |
| |
| SAVEPOINT |
|
Equivalent to SQL "SAVEPOINT <savepoint_name>". Already part of PL/SQL |
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); |
