Home » SQL & PL/SQL » SQL & PL/SQL » Why I can't read sys.v_$session from procedures or functions? (Oracle 9 and 10)
Why I can't read sys.v_$session from procedures or functions? [message #289626] Mon, 24 December 2007 08:23 Go to next message
stas
Messages: 3
Registered: December 2007
Location: RUSSIA
Junior Member
I have grant for select table sys.v_$session for my login. I can do:
. . .
SELECT AUDSID FROM sys.v_$session WHERE AUDSID <> 18543 AND ROWNUM
. . .
inside SQL Navigator, bat I can't read sys.v_$session from any my procedures and functions:
. . .
PROCEDURE TESTSTAS1
IS
VV number;
BEGIN
SELECT AUDSID into VV FROM sys.v_$session WHERE AUDSID <> 18543 AND ROWNUM <=1;
END;

. . .
Always I receive the message: PL/SQL: ORA-00942: table or view dos not exist

WHY?
Re: Why I can't read sys.v_$session from procedures or functions? [message #289631 is a reply to message #289626] Mon, 24 December 2007 09:06 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is one of the most common errors in PL/SQL programming.
Google for the keywords oracle function "table or view does not exist" to see what to do.
Remember that, both for beginning programmers and for experienced programmers, googling is often the way to find an answer quickly.
Re: Why I can't read sys.v_$session from procedures or functions? [message #289633 is a reply to message #289631] Mon, 24 December 2007 09:40 Go to previous messageGo to next message
stas
Messages: 3
Registered: December 2007
Location: RUSSIA
Junior Member
But this message come ONLY from PL/SQL functions and procedures. And only for view sys.v_$session. I do NOT receive this message from SQL SELECT... and SQL DECLARE SELECT... This view is reading from them without problems, because of "Grant select on sys.v_$session to public". And why it dos not apply for reading from FUNCTIONS and PROCEDURES?
Re: Why I can't read sys.v_$session from procedures or functions? [message #289634 is a reply to message #289626] Mon, 24 December 2007 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Privileges acquired via ROLE do NOT apply within PL/SQL procedures.

SQL> SET ROLE NONE
& then do the SELECT
Re: Why I can't read sys.v_$session from procedures or functions? [message #289637 is a reply to message #289626] Mon, 24 December 2007 10:06 Go to previous messageGo to next message
stas
Messages: 3
Registered: December 2007
Location: RUSSIA
Junior Member
Thank you.
Re: Why I can't read sys.v_$session from procedures or functions? [message #289646 is a reply to message #289633] Mon, 24 December 2007 13:57 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
stas wrote on Mon, 24 December 2007 16:40

But this message come ONLY from PL/SQL functions and procedures. And only for view sys.v_$session. I do NOT receive this message from SQL SELECT... and SQL DECLARE SELECT... This view is reading from them without problems, because of "Grant select on sys.v_$session to public". And why it dos not apply for reading from FUNCTIONS and PROCEDURES?

Did you bother to google as I advised you?
Previous Topic: HOW to use DBMS_SHARED_POOL package
Next Topic: Using sql object types as an out parameter to a procedure
Goto Forum:
  


Current Time: Fri Dec 09 15:32:09 CST 2016

Total time taken to generate the page: 0.10411 seconds