Re: V$ select in stored procedure not working

Hi Christopher,
I'd look at how privileges are granted...stored procedures will often require direct granting of privileges rather than privileges granted through a role.. Perhaps this will point you in the right direction.

Regards,
Steve

Christopher Latta wrote:

> 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 Fri Apr 06 2001 - 06:58:12 CDT