RE: What stupid mistake am I making with this onlogin trigger?

Many thanks to Paul Drake for suggesting I set role none; in the sql*plus session to duplicate what was happening in the trigger. That let me identify the problem (although I had granted v_$session directly I had missed v$mystat).  

This was my first trigger written in several years so I don't feel too guilty calling in help.  

Jay Miller
Sr. Oracle DBA
201.369.8355  

From: Miller, Jay
Sent: Monday, February 25, 2008 3:34 PM
To: oracle-l
Subject: What stupid mistake am I making with this onlogin trigger?

After going back an forth on this for a while today I figured a few more sets of eyes couldn't hurt.

I need to track connections from a specific module (not SQL*Plus but that was easiest for testing purposes). This trigger compiles but does not create any rows. The insert statement works if run from a sql*plus prompt. I'm sure I'm missing something really obvious here...

Does the onlogin trigger possibly execute as the user logging in as opposed to as sys?

Trigger created:
SQL> create or replace trigger sys.trace_php_onlogin after logon on database
  2 begin
  3
  4 insert into wsidba.php_logins
  5 select sid,username,module
  6 from sys.v_$session
  7 where sid = (select unique(sid) from v$mystat)   8 and upper(module) like '%SQL%';
  9 commit;
 10
 11 end;
 12 /

Trigger created.

Doesn't enter any data to table:
usnjc01urp001:/services/home/oracle<lass> $ sqlplus wsidba

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 25 15:23:43 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production

SQL> select * from wsidba.php_logins;

no rows selected

Works from SQL*Plus:
$ sqlplus wsidba

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 25 15:25:58 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production

SQL> insert into wsidba.php_logins
  select sid,username,module
  from sys.v_$session
  where sid = (select unique(sid) from v$mystat)   and upper(module) like '%SQL%'; 2 3 4 5

1 row created.

TIA,
Jay Miller

--
http://www.freelists.org/webpage/oracle-l