You Asked

Hi, Tom.

1- At the beginning of all packages/procedures I use dbms_application_info.set_module in order the DBA can monitor which module/action is currently running.

2- In my error handling routines I always send "when others" errors to the screen and also call a procedure (load_log) where useful information (sqlcode, sqlerrm, date, host, ip_adress, user, module, action) is stored in an error log table in order I can check it in case the user missed to give me detailed information about what happened.

I fill the host, user and ip_adress columns with sys_context('usernev', 'xxxx') but to fill module and action columns (that I set with dbms_application_info in 1) I have two choices (since sys_context('usernev', 'module_name') and sys_context('userenv', 'module_action') don't exist):

a- hardcode into variables every module and action in the routine and then pass these variables as parameters for the load_log procedure.

b- use dbms_application_info.set_client_info with the same info I use in set_module/set_action and then, in load_log, use sys_context('usernev', 'client_info') to store the info in the module/action columns.

I'm using option 2 but
is there any way i can get the module name/action without having to define it with set_client_info?
I guess dbms_application_info.read_module can be an option but it needs 2 output parameters so I have to write more code (or i can even use create context). Seems there's no way I can define it at the beginning and the just simple call something like sys_context to get the info.

As always, really grateful for any answer
Mariano.-

and Tom said...

there are at least two options here:

select client_info, action, module from v$session where sid = ( select sid from v$mystat where rownum=1 );

dbms_application_info.read_module( p_mod_name, p_act_name );

either will get you the module and action. Since you need a single row table to retrieve sys_context from, you can use v$sesion:

insert into your_log_table ( .... )
select sys_context( 'whatever' ), ....., action, module
from v$session
where sid = (select sid from v$mystat where rownum=1);

Rating

(1 rating)

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

Comments

Diff between user and sys_context

Abhijit Mallick, July 11, 2005 - 8:59 am UTC

Hi Tom,
   Thanks for the information. I have a question when I am connected through SQL is there any difference among the followings

SQL> SHOW USER
USER IS SCOTT

SQL>SELECT USER FROM DUAL;

USER
------------------------------
ECC

SQL>ECC@COMPONE> SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
 FROM DUAL;
    
    SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
    --------------------------------------------------------------------------------
ECC 

If there is no difference, is there any situation where the queries may give different result??

Thanks
--Abhijit Mallick

July 11, 2005 - 11:33 am UTC

current schema is the effective user id under which you are currently running, the privilege domain, yes it varies from USER:

ops$tkyte@ORA9IR2> create or replace procedure show_user
  2  as
  3  begin
  4          for x in ( select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') u1, user u2
  5                       from dual )
  6          loop
  7                  dbms_output.put_line( x.u1 || ', ' || x.u2 );
  8          end loop;
  9  end;
 10  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> grant execute on show_user to scott;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> @connect scott/tiger
ops$tkyte@ORA9IR2> set termout off
scott@ORA9IR2> set termout on
scott@ORA9IR2> exec ops$tkyte.show_user
OPS$TKYTE, SCOTT
 
PL/SQL procedure successfully completed.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Diff between user and sys_context

More to Explore

PL/SQL demos

PL/SQL docs

sys_context('userenv', 'module_name')?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Diff between user and sys_context

More to Explore

PL/SQL demos

PL/SQL docs