V$ select in stored procedure not working

From: Christopher Latta <clatta_at_ozemail.com.au>
Date: Fri, 6 Apr 2001 12:06:37 +1000
Message-ID: <5y9z6.140$pO2.3934@ozemail.com.au>

I'm trying to move my auditing into database triggers rather than done in my app. I have worked out the SQL that I need to get the audit information I want, and it works when logged in as my app user dba:

  INSERT INTO AUDITINFO
(AUDSID, ORACLEUSER, SCHEMANAME, OSUSER, WORKSTATION, LOGONTIME,
SOURCETABLE, OPERATION)
    select MAX(s.AUDSID), MAX(s.USERNAME), MAX(s.SCHEMANAME), MAX(sci.OSUSER), MAX(s.TERMINAL), MAX(s.LOGON_TIME), 'MATTER', 'INSERT'

      from V$SESSION s, V$SESSION_CONNECT_INFO sci
      where s.SID = sci.SID
        and s.AUDSID = userenv('sessionid')
      group by s.SID;

This works fine. However, when I put that SQL into a stored procedure or trigger, it doesn't compile:

create or replace procedure AUDITTHIS (
  pTableName IN varchar2,
  pOperation IN varchar2
  ) IS
Begin
  INSERT INTO AUDITINFO
(AUDSID, ORACLEUSER, SCHEMANAME, OSUSER, WORKSTATION, LOGONTIME,
SOURCETABLE, OPERATION)
    select MAX(s.AUDSID), MAX(s.USERNAME), MAX(s.SCHEMANAME), MAX(sci.OSUSER), MAX(s.TERMINAL), MAX(s.LOGON_TIME), pTableName, pOperation

      from V$SESSION s, V$SESSION_CONNECT_INFO sci
      where s.SID = sci.SID
        and s.AUDSID = userenv('sessionid')
      group by s.SID;

End;

It gives an error saying it can't find v$session. Replacing it with sys.v_$session doesn't help either.

Why is the visibility for select on that table/view different when you do it from a stored procedure or trigger? What can I do to get this to work?

TIA,
Christopher Latta Received on Thu Apr 05 2001 - 21:06:37 CDT