Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> SYS_CONTEXT('userenv','sessionid') always returns zero

SYS_CONTEXT('userenv','sessionid') always returns zero

From: Jong-Wook Hwang <goodday_at_webmail.hmd.co.kr>
Date: 22 Feb 2002 17:03:07 -0800
Message-ID: <f6cef816.0202221703.71a821e0@posting.google.com>


Hi!
I made 'AFTER ON LOGON DATABASE' trigger extract user's logon program in v$session like below,

//------------------

create or replace TRIGGER after_logon_audit after logon on database
WHEN ( user in ('SCOTT', 'SCOTT2'))
declare
	err_num number;
	err_msg varchar2(100);

begin         
	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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US