Re: ORA-00911 in Dynamic SQL

traceable1_at_hotmail.com (traceable1) wrote in message news:<8551d8c9.0404121025.6c539428_at_posting.google.com>...
> 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.
>

Sounds like you want to create a "utility" userid or schema and compile the procedure there. Then grant execute on that procedure to whoever needs it. You can grant the priv to see v$session to that userid so the proc will compile ..

Note - nobody actually logs in using that userid, they still use their own login, they just run a procedure that belongs to somebody else ... no big deal .. (btw - you'll probably want to use Public Synonym for this as well) ..

The Elementalist Received on Tue Apr 13 2004 - 22:15:34 CEST