By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,504 Members | 1,884 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,504 IT Pros & Developers. It's quick & easy.

ORA-00911 in Dynamic SQL

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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;
/

Jul 19 '05 #2

P: n/a
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
Jul 19 '05 #3

P: n/a
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

Jul 19 '05 #4

P: n/a
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
Jul 19 '05 #5

P: n/a
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

Jul 19 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.