ORA-00911 in Dynamic SQL

[traceable1]

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. The $ is what's causing the trouble:

declare v_sql varchar2(4000);
begin
v_sql := 'select * from v$session;';
execute immediate (v_sql);
end;
/
I've tried \ and double quotes, but they don't work. Any clues?

THANK YOU!!!!

trace

[AnaCDent]

traceable1 wrote:

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. The $ is what's causing the trouble:

declare v_sql varchar2(4000);
begin
v_sql := 'select * from v$session;';
execute immediate (v_sql);
end;
/
I've tried \ and double quotes, but they don't work. Any clues?

THANK YOU!!!!

trace

declare v_sql varchar2(4000);
begin
v_sql := 'select * from v$session';
execute immediate v_sql;
end;
/

[sybrandb]

tr********@hotmail.com (traceable1) wrote in message news:<85*************************@posting.google.c om>...

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

[traceable1]

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.
sy******@yahoo.com wrote in message news:<a1**************************@posting.google. com>...

tr********@hotmail.com (traceable1) wrote in message news:<85*************************@posting.google.c om>...

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

[The Elemenatlist]

tr********@hotmail.com (traceable1) wrote in message news:<85**************************@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

[traceable1]

Thank you for you response. Unfortunately, the installation of this
database schema is run completely by the user. We cannot have the
clients log in w/dba rights and create another user for this
procedure.
Is the $ added in to the name of these views just to make it
impossible to use in dynamic SQL?

Thanks,
trace
th**************@hotmail.com (The Elemenatlist) wrote in message news:<d0**************************@posting.google. com>...

tr********@hotmail.com (traceable1) wrote in message news:<85**************************@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