You Asked

Hi Tom,

I am working on a database that is used by a manufacturing equipment control system. I have created a series of packages and stored procedures as the interface to/from the database. Some of these packages have a fair amount of logic in them and use variables hold data between calls. My problem is that now the control system has gotten more complex, and there will be session pooling. All the connections will log in as the same USER, but the package variables will have different values for each session.

I have been looking into DBMS_SESSIONS and SET CONTEXT, but I can not figure out how to make it work, for what I am trying to do (share package variables across sessions), or even if it is the best solution. I suppose I could log every value to the database on every call, and then fetch it on every subsequent call. However, these calls are continuously coming in from the control system at sub-second rates, and that seems like a lot of data round trips (CPU usage, hard drive reads/writes, etc.).

This must be a fairly common problem, but so far, I have not found an example I could follow. Can you:

1) advise the best solution?

2) provide a simple example that could pass data between 2 sessions?

Thanks.

and Tom said...

well, in APEX (application express), they maintain an application state for you - and that persists in.....

database tables.

It will not result in significant "read/write" increases - because of the buffer cache (just because you insert a row into a table does not mean you will write that block to disk right away). If you "use it", it'll stay in the cache.

There are also global application contexts
http://asktom.oracle.com/pls/ask/search?p_string=%22global+application+context%22

as well.

I would suggest that if you are heading to a connection pool you immediately, right now, first - OUTLAW any and all global variables. No variable may be defined outside of a procedure or function. If you do not do that, you will have many things that appear to work by accident in your test environment (without much load, you probably get the same connection over and over and don't accidentally lose your package state).

So, either an index organized table:

create table state ( sessionid number, name varchar2(30), value varchar2(4000),
primary key (sessionid,name) ) organization index;

that you persist your values in until no longer needed or global application contexts.

I sort of like the table approach personally. You would permit one global (stateful) variable here and ensure that everyone calls "set_state" first (upon grabbing a connection)

eg (warning, this code is just out of the oven, might not be fully baked, demonstrating the concept here - I strongly encourage you to keep the dbms_application_info and dbms_session calls - app info populates v$session columns, dbms_session sets the client id and that will be in the audit trail if you use it, and DBMS_MONITOR can be used to set sql trace on/off for a given session !!!!! very important):

ops$tkyte%ORA10GR2> create table state ( sessionid number, name varchar2(30), value varchar2(2000), primary key (sessionid,name) ) organization index;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package my_state
  2  as
  3      procedure set_state( p_sessionid in state.sessionid%type,
  4                           p_real_user in varchar2,
  5                           p_application_name in varchar2,
  6                           p_part_of_application in varchar2 );
  7
  8      function get_state_value( p_sessionid in state.sessionid%type, p_name in state.name%type ) return state.value%type;
  9      procedure set_state_value( p_sessionid in state.sessionid%type, p_name in state.name%type, p_value in state.value%type );
 10      procedure save_state( p_sessionid in state.sessionid%type );
 11  end;
 12  /

Package created.

ops$tkyte%ORA10GR2> show err
No errors.
ops$tkyte%ORA10GR2> pause

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package body my_state
  2  as
  3      type array is table of state.value%type index by state.name%type;
  4      g_data array;
  5      g_sessionid state.sessionid%type default -1;
  6
  7      procedure set_state( p_sessionid in state.sessionid%type,
  8                           p_real_user in varchar2,
  9                           p_application_name in varchar2,
 10                           p_part_of_application in varchar2 )
 11      is
 12      begin
 13          dbms_application_info.set_client_info( p_real_user );
 14          dbms_application_info.set_module( p_application_name, p_part_of_application );
 15          dbms_session.set_identifier( p_real_user );
 16
 17          g_data.delete;
 18
 19          -- in 10g, this loop array fetches, in 9i, you'd want to bulk collect!
 20          for x in (select name, value from state where sessionid = p_sessionid)
 21          loop
 22              g_data( x.name ) := x.value;
 23          end loop;
 24          g_sessionid := p_sessionid;
 25      end;
 26
 27      function get_state_value( p_sessionid in state.sessionid%type, p_name in state.name%type ) return state.value%type
 28      is
 29      begin
 30          if (p_sessionid <> g_sessionid)
 31          then
 32              raise_application_error( -20001, 'you did not set your state!' );
 33          end if;
 34          return g_data(p_name);
 35      end;
 36      procedure set_state_value( p_sessionid in state.sessionid%type, p_name in state.name%type, p_value in state.value%type )
 37      is
 38      begin
 39          if (p_sessionid <> g_sessionid)
 40          then
 41              raise_application_error( -20001, 'you did not set your state!' );
 42          end if;
 43          g_data(p_name) := p_value;
 44      end;
 45      procedure save_state( p_sessionid in state.sessionid%type )
 46      is
 47          type vcArray is table of state.value%type index by binary_integer;
 48          l_name vcArray;
 49          l_val  vcArray;
 50          l_idx  state.name%type;
 51      begin
 52          if (p_sessionid <> g_sessionid)
 53          then
 54              raise_application_error( -20001, 'you did not set your state!' );
 55          end if;
 56          l_idx := g_data.first;
 57          while (l_idx is not null )
 58          loop
 59              l_name(l_name.count+1) := l_idx;
 60              l_val(l_val.count+1) := g_data(l_idx);
 61              l_idx := g_data.next(l_idx);
 62          end loop;
 63          delete from state state where sessionid = p_sessionid;
 64          forall i in 1 .. l_name.count
 65              insert into state ( sessionid, name, value ) values ( p_sessionid, l_name(i), l_val(i) );
 66      end;
 67  end;
 68  /

Package body created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec my_state.set_state( 123, 'me', 'my application', 'page1' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec my_state.set_state_value( 123, 'x', 'hello world' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec my_state.set_state_value( 123, 'y', 'goodbye world' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_output.put_line( my_state.get_state_value( 123, 'x' ) );
hello world

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_output.put_line( my_state.get_state_value( 123, 'y' ) );
goodbye world

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec my_state.save_state( 123 )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> exec dbms_output.put_line( my_state.get_state_value( 123, 'x' ) );
BEGIN dbms_output.put_line( my_state.get_state_value( 123, 'x' ) ); END;

*
ERROR at line 1:
ORA-20001: you did not set your state!
ORA-06512: at "OPS$TKYTE.MY_STATE", line 32
ORA-06512: at line 1


ops$tkyte%ORA10GR2> exec my_state.set_state( 123, 'me', 'my application', 'page2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_output.put_line( my_state.get_state_value( 123, 'x' ) );
hello world

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_output.put_line( my_state.get_state_value( 123, 'y' ) );
goodbye world

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec my_state.set_state_value( 123, 'x', 'HELLO WORLD' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec my_state.set_state_value( 123, 'y', 'GOODBYE WORLD' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec my_state.save_state( 123 )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> exec my_state.set_state( 123, 'me', 'my application', 'page3' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_output.put_line( my_state.get_state_value( 123, 'x' ) );
HELLO WORLD

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec dbms_output.put_line( my_state.get_state_value( 123, 'y' ) );
GOODBYE WORLD

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec my_state.save_state( 123 )

PL/SQL procedure successfully completed.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Tom said...

More to Explore

PL/SQL demos

PL/SQL docs

package variables persisting accross session pools

Breadcrumb

Question and Answer

You Asked

and Tom said...

More to Explore

PL/SQL demos

PL/SQL docs