Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> SYS_CONTEXT('userenv','sessionid') always returns zero
Hi!
I made 'AFTER ON LOGON DATABASE' trigger extract user's logon program
in v$session like below,
//------------------
err_num number; err_msg varchar2(100);
insert into logon_audit select sysdate, sys_context('userenv', 'ip_address'),sys_context('userenv', 'session_user'), a.osuser, a.machine, a.terminal, a.program,
sys_context('userenv', 'sessionid'), sys_context('userenv', 'session_userid')
from v$session a, dual where sys_context('userenv', 'sessionid') = a.audsid and sys_context('userenv', 'session_userid') = a.user#; commit; Exception when Others then err_num := SQLCODE; err_msg := substr(SQLERRM, 1, 100); insert into logon_audit_err values( sysdate, err_num, err_msg );end;
//----------------------------------
Of course, trigger owner is scott and scott has a select privilege on
v_$session and adminster database trigger privilege.
Above trigger was compiled without error and is valid now.
But, nothing was inserted to logon_audit table after logon by user
scott or scott2.
After all, the reason was that sys_context('userenv', 'sessionid')
always returns zero at trigger execution time. But,
sys_context('userenv', 'sessionid') returns correct value(same value
as audsid of v$session) in sql*plus after logon.
So, can't extract user logon program from v$session.
How can i solve this problem? Please give me your hand...
Received on Fri Feb 22 2002 - 19:03:07 CST