can I maintain one Oracle session from two oci cli

Question

Is there a possibility to connect to Oracle (via OCI) from one process, then connect on the same database session from another process?

In my current app, there are two ways to access the database: a synchronous one and an asynchronous one (by using a separate process, communicating via sockets). The problem is the two methods implement distinct sessions.

If I attempt e.g. an update on one session, then try to update the same table from the other session without committing, I get a hang on the OCI call.

Worse, if a session variable is set from one session - the other session does not see it (which is exactly what the name says...).

Answer 1

If you are using an 11g database, you could use the DBMS_XA package to allow one session to to join a transaction started by the first session. As Tim Hall deomonstrates, you can start a transaction in one session, join that transaction from another session, and read the uncommitted changes made in the transaction. Unfortunately, however, that is not going to help with session variables (assuming that "session variable" means package variable that have session scope).

Create the package and the 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 suspending the global transaction (which allows another session to resume it)

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 end the global transaction. Note that the query against the table sees the row we inserted but the package variable change 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 the sessions, would it be possible to use a global application context rather than using package variables? You could combine that with the DBMS_XA packages if you want to read both database tables and session state.

Create the context and the package with the 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 context variable G_VAR to 47 for session 12345

begin
  pkg_foo.set_var( 12345, 47 );
end;

Now, session 2 can read the value from the context

  1* select pkg_foo.get_var( 12345 ) from dual
SQL> /

PKG_FOO.GET_VAR(12345)
----------------------
                    47