CODE : Persistent Variables via PL/SQL Package and DBMS_APPLICATION_INFO

I am now introducing some code samples in my blog.  I won't restrict myself to SQL but will also include PL/SQL  (C ? Bourne/Korn Shell ? what else ?)

This is the first of such samples.


Here I demonstrate using a PL/SQL Package to define persistent variables and then using them with DBMS_APPLICATION_INFO.  This demo consists of only 2 variables being used by 1 session.  But we could have a number of variables in this Package and invoked by multiple client sessions in the real workd.

I first :

SQL> grant create procedure to hr;

Grant succeeded.

SQL> 

Then, in the HR schema, I setup a Package to define variables that can persist throughout a session.  Public Variables defined in a Package, once invoked, persist throughout the session that invoked them.

create or replace package
define_my_variables
authid definer
is
  my_application varchar2(25) := 'Human Resources';
  my_base_schema varchar2(25) := 'HR';
end;
/

grant execute on define_my_variables to hemant;
grant select on employees to hemant;

As HEMANT, I then execute :

SQL> connect hemant/hemant  
Connected.
SQL> execute dbms_application_info.set_module(-
> module_name=>HR.define_my_variables.my_application,-
> action_name=>NULL);

PL/SQL procedure successfully completed.

SQL> 

As SYSTEM, the DBA can monitor HEMANT

QL> show user
USER is "SYSTEM"
SQL> select sid, serial#, to_char(logon_time,'DD-MON HH24:MI:SS') Logon_At, module, action
  2  from v$session
  3  where username = 'HEMANT'
  4  order by 1
  5  /

       SID    SERIAL# LOGON_AT                 MODULE
---------- ---------- ------------------------ ----------------------------------------------------------------
ACTION
----------------------------------------------------------------
         1     63450 23-JUL 23:24:03           Human Resources



SQL> 

Then, HEMANT intends to run a query on the EMPLOYEES Table.

SQL> execute dbms_application_info.set_action(-
> action_name=>'Query EMP');

PL/SQL procedure successfully completed.

SQL> select count(*) from hr.employees where job_id like '%PROG%'
  2  /

  COUNT(*)
----------
         5

SQL> 

SYSTEM can see what he is doing with

SQL> l
  1  select sid, serial#, to_char(logon_time,'DD-MON HH24:MI:SS') Logon_At, module, action
  2  from v$session
  3  where username = 'HEMANT'
  4* order by 1
SQL> /

       SID    SERIAL# LOGON_AT                 MODULE
---------- ---------- ------------------------ ----------------------------------------------------------------
ACTION
----------------------------------------------------------------
         1      63450 23-JUL 23:24:03          Human Resources
Query EMP


SQL> 

Returning, to the HEMANT login, I can see :

SQL> show user
USER is "HEMANT"
SQL> execute dbms_output.put_line(-
> 'I am running ' || hr.define_my_variables.my_application || '  against ' || hr.define_my_variables.my_base_schema);
I am running Human Resources  against HR

PL/SQL procedure successfully completed.

SQL> 

So, I have demonstrated :
1.  Using a PLSQL Package Specification (without the need for a Package Body) to define variables that are visible to another session.

2.  The possibility of using this across schemas.  HR could be my "master schema" that setups all variables and HEMANT is one of many "client" schemas (or users) that use these variables..

3. The variables defined will persist throughout the client session once they are invoked.

4.  Using DBMS_APPLICATION_INFO to call these variables and setup client information.


Note :  SYSTEM can also trace HEMANT's session using DBMS_MONITOR as demonstrated in Trace Files -- 2 : Generating SQL Traces (another session)

.
.
.