Is there a @@trancount equivalent in Oracle?-CodePudding

I'm looking for some way to replicate the behavior of @@trancount from T-SQL in Oracle, I can't figure out a clever way to do it.

Any help would be appreciated:)

CodePudding user response：

This is a bit foreign in Oracle, since you generally only have a single transaction open in a session. See documentation for more on the concept of transactions in Oracle

But you can query v$transaction to see open transactions in your current session.

select count(*) from v$transaction t
join v$session s on t.ses_addr = s.saddr
  and s.AUDSID = Sys_Context('USERENV', 'SESSIONID');

CodePudding user response：

If you really want to simulate @@trancount, you can follow this approach

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection. On TSQL

PRINT @@TRANCOUNT  
--  The BEGIN TRAN statement will increment the  
--  transaction count by 1.  
BEGIN TRAN  
    PRINT @@TRANCOUNT  
    BEGIN TRAN  
        PRINT @@TRANCOUNT  
--  The COMMIT statement will decrement the transaction count by 1.  
    COMMIT  
    PRINT @@TRANCOUNT  
COMMIT  
PRINT @@TRANCOUNT  
--Results  
--0  
--1  
--2  
--1  
--0

In Oracle there is no such functionality, but can be emulated by a variable.

set serveroutput on 
declare
vtrancount pls_integer := 0;
--  The BEGIN statement will increment the transaction count by 1.  
begin
 vtrancount := vtrancount   1;
 dbms_output.put_line(vtrancount);
 -- ... dml 
    begin   
        vtrancount := vtrancount   1;
        dbms_output.put_line(vtrancount);
        ... dml 
        commit;  
        vtrancount := vtrancount - 1;
        dbms_output.put_line(vtrancount);
    end;
commit; 
vtrancount := vtrancount - 1;
dbms_output.put_line(vtrancount);
exception when others then 
    vtrancount := 0;
    rollback;
    raise;
end;
/