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 :
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.
As HEMANT, I then execute :
As SYSTEM, the DBA can monitor HEMANT
Then, HEMANT intends to run a query on the EMPLOYEES Table.
SYSTEM can see what he is doing with
Returning, to the HEMANT login, I can see :
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)
.
.
.
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)
.
.
.