Re: ORA-00911 in Dynamic SQL

This is for an application in which a user can be running multiple processes. Some of our clients do not want their users to have access to v$session, so the procedure needs to be written in such a manner that it uses v$session for clients who will grant the access, and thus have the added feature of running multiple processes, or, it they choose not to grant the access, the procedure goes another route. If they choose not to grant access, the procedure will not compile unless it is run via dynamic SQL.

sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0404090534.53998fc9_at_posting.google.com>...
> traceable1_at_hotmail.com (traceable1) wrote in message news:<8551d8c9.0404081235.4d9251c_at_posting.google.com>...
> > I am trying to create a stored procedure with dynamic sql referencing
> > the V$SESSION table (view). I need to use this dynamically, because
> > the procedure will not compile if the user does not have access to
> > this table.
>
> Untrue. Create the procedure with authid current_user or grant direct
> privilege to the user, and dump the dynamic sql.
> Not sure why you would need any user to do this though.
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Mon Apr 12 2004 - 20:25:41 CEST