Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Newbie question on Packages

RE: Newbie question on Packages

From: Jesse, Rich <>
Date: Thu, 01 Mar 2001 13:52:10 -0800
Message-ID: <>

Let the beatings begin! <grin>

The package will compile, but the error is basically a security issue. For some stupid reason, Oracle requires that access to objects referenced within packages and procedures (and views, too, methinks) be done explicitly to the owner of the package, and not to a role that the grantee is in. Try "GRANT SELECT ON V_$SESSION TO MYUSER;" from SYS, then recompiling the package.

NOTE THE UNDERSCORE IN THE "V_$SESSION"! If you don't have it there in the GRANT, you'll get an error (ORA-2030?) about only being able to do this on a fixed view or something.

HAND! :)

Rich Jesse                          System/Database Administrator             Quad/Tech International, Sussex, WI USA

Disclaimer: As President of the Beer Ale Lager Lovers Society, I must leave work soon to drink beer to celebrate Beer Day. And since Beer Day only occurs every other Payday, I must have more than one. So don't blame me for my opinions stated in this email. That is all.

-----Original Message-----
Sent: Thursday, March 01, 2001 14:41
To: Multiple recipients of list ORACLE-L

Beat me with an overextended RBS if
you need be, but other than any
permission issue, don't you need
to SELECT....INTO... in PL/SQL?
Ready for my beating,
- Ross
-----Original Message-----
Sent: Thursday, March 01, 2001 3:27 PM
To: Multiple recipients of list ORACLE-L

Hi, I a newbie in Oracle and am trying to create a package body in PL/SQL, so please bear with me.
The statements are:
SQL> create or replace package body RA_GET_TX_INFO_   2 as
  3 begin
  4 select SID from V$SESSION;
  5 end RA_GET_TX_INFO_;

Warning: Package Body created with compilation errors. SQL> show errors package body RA_GET_TX_INFO_; Errors for PACKAGE BODY RA_GET_TX_INFO_:   


-------- ----------------------------------------------------------------- 
3/10     PL/SQL: SQL Statement ignored 
3/26     PLS-00201: identifier 'SYS.V_$SESSION' must be declared 

How can I select from V_$SESSION inside the body? The user account has dba role granted and I can select from V_$SESSION from a normal PL/SQL session. Any help greatly appreciated.

This message has been scanned for viruses with Trend Micro's Interscan VirusWall.

Please see the official ORACLE-L FAQ:

Author: Jesse, Rich

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message to: (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Mar 01 2001 - 15:52:10 CST

Original text of this message