If you are using the 11g database, you can use the DBMS_XA package DBMS_XA that one session can join the transaction started by the first session. As Tim Hall demonstrates, you can start a transaction in one session, join this transaction from another session and read the uncommitted changes made to the transaction. Unfortunately, however, this does not help with session variables (it is assumed that “session variable” means a packet variable that has a session scope).
Create a package and a table:
CREATE TABLE foo( col1 NUMBER ); create or replace package pkg_foo as g_var number; procedure set_var( p_in number ); end; create or replace package body pkg_foo as procedure set_var( p_in number ) as begin g_var := p_in; end; end;
In session 1, we start a global transaction, set the package variable and insert a row into the table before pausing the global transaction (which allows it to resume another session).
SQL> ed Wrote file afiedt.buf 1 declare 2 l_xid dbms_xa_xid := dbms_xa_xid( 1 ); 3 l_ret integer; 4 begin 5 l_ret := dbms_xa.xa_start( l_xid, dbms_xa.tmnoflags ); 6 pkg_foo.set_var(42); 7 dbms_output.put_line( 'Set pkg_foo.g_var to ' || pkg_foo.g_var ); 8 insert into foo values( 42 ); 9 l_ret := dbms_xa.xa_end( l_xid, dbms_xa.tmsuspend ); 10* end; SQL> / Set pkg_foo.g_var to 42 PL/SQL procedure successfully completed.
In session 2, we resume the global transaction, read from the table, read the session variable, and complete the global transaction. Note that the query for the table sees the row we inserted, but the change in the package variable is not visible.
SQL> ed Wrote file afiedt.buf 1 declare 2 l_xid dbms_xa_xid := dbms_xa_xid( 1 ); 3 l_ret integer; 4 l_col1 integer; 5 begin 6 l_ret := dbms_xa.xa_start( l_xid, dbms_xa.tmresume ); 7 dbms_output.put_line( 'Read pkg_foo.g_var as ' || pkg_foo.g_var ); 8 select col1 into l_col1 from foo; 9 dbms_output.put_line( 'Read COL1 from FOO as ' || l_col1 ); 10 l_ret := dbms_xa.xa_end( l_xid, dbms_xa.tmsuccess ); 11* end; SQL> / Read pkg_foo.g_var as Read COL1 from FOO as 42 PL/SQL procedure successfully completed.
To share session state between sessions, is it possible to use a global context instead of using package variables? You can combine this with DBMS_XA packages if you want to read both the database tables and the session state.
Create context and package using getter and setter
CREATE CONTEXT my_context USING pkg_foo ACCESSED GLOBALLY; create or replace package pkg_foo as procedure set_var( p_session_id in number, p_in in number ); function get_var( p_session_id in number ) return number; end; create or replace package body pkg_foo as procedure set_var( p_session_id in number, p_in in number ) as begin dbms_session.set_identifier( p_session_id ); dbms_session.set_context( 'MY_CONTEXT', 'G_VAR', p_in, null, p_session_id ); end; function get_var( p_session_id in number ) return number is begin dbms_session.set_identifier( p_session_id ); return sys_context('MY_CONTEXT', 'G_VAR'); end; end;
In session 1, set the value of the G_VAR context G_VAR to 47 for session 12345
begin pkg_foo.set_var( 12345, 47 ); end;
Session 2 can now read the value from context
1* select pkg_foo.get_var( 12345 ) from dual SQL> / PKG_FOO.GET_VAR(12345) ---------------------- 47