Home » SQL & PL/SQL » SQL & PL/SQL » Why package can't see v$session?
Why package can't see v$session? [message #258260] Fri, 10 August 2007 13:31 Go to next message
ashtonkm
Messages: 10
Registered: August 2007
Junior Member
I am trying to create a small package that can access the v$session view.
SQL> CREATE OR REPLACE PACKAGE k
  2  AS
  3    PROCEDURE audit_tes ;
  4  END k;
  5  /

Package created.

SQL> 
SQL> CREATE OR REPLACE PACKAGE BODY k
  2  IS
  3   PROCEDURE audit_tes AS
  4   CURSOR c1 IS
  5   SELECT count(*) from v$session;
  6   BEGIN
  7    null;
  8   END audit_tes;
  9  END k;
 10  /

Warning: Package Body created with compilation errors.

SQL> show errors
Errors for PACKAGE BODY K:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/2      PL/SQL: SQL Statement ignored
5/23     PL/SQL: ORA-00942: table or view does not exist

Does anyone know why I would be getting this error? I can execute the code just fine if I take it out of the package. Why is the view visible to my schema yet my package cannot see it?

Thanks
Re: Why package can't see v$session? [message #258265 is a reply to message #258260] Fri, 10 August 2007 13:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By default, roles are not enabled in stored PL/SQL object.

Regards
Michel
Re: Why package can't see v$session? [message #258266 is a reply to message #258260] Fri, 10 August 2007 13:45 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Try to grant SELECT on v$session directly to your user (not via a role).
Re: Why package can't see v$session? [message #258267 is a reply to message #258265] Fri, 10 August 2007 13:48 Go to previous messageGo to next message
ashtonkm
Messages: 10
Registered: August 2007
Junior Member
I connected as sysdba and ran the following. This is just local instance that I am running. Is that what you meant by granting SELECT to the user?

 
grant select on v$session to system;
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

[Updated on: Fri, 10 August 2007 13:53]

Report message to a moderator

Re: Why package can't see v$session? [message #258269 is a reply to message #258267] Fri, 10 August 2007 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you have to.

Regards
Michel
Re: Why package can't see v$session? [message #258270 is a reply to message #258267] Fri, 10 August 2007 13:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
grant select on v_$session to system;

Regards
Michel
Re: Why package can't see v$session? [message #258272 is a reply to message #258260] Fri, 10 August 2007 14:03 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Do not do development in the SYS or SYSTEM schema's. They are used by oracle and you can damage your database if you use them!!!.
Re: Why package can't see v$session? [message #258287 is a reply to message #258269] Fri, 10 August 2007 16:24 Go to previous message
ashtonkm
Messages: 10
Registered: August 2007
Junior Member
Thats a good point I shouldn't do much in System or Sys even if it is a throw away database. Better not to even get in the habit. Thanks for the replies and help guys!
Previous Topic: single quote substitution
Next Topic: Sql Hints
Goto Forum:
  


Current Time: Sat Dec 10 05:12:26 CST 2016

Total time taken to generate the page: 0.11415 seconds