Mark, Your suggestion actually fixed our issue querying an "all_tab..." view. Now, we're facing "all_indexes". We've granted select, update, delete, & alter on the table (which has the index) to the package owner. We've also granted ... INDEX ALTER ANY INDEXTYPE EXECUTE ANY INDEXTYPE I know we're just shooting from the hip at the moment, but hoping to find the right one ... Thanks, Jon Knight --- "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx> wrote: > Jonathan, > > If the owner of the PL/SQL stored object has grants > on > cp_owner.cp_table_name only via a role, the query > will return no rows > from within PL/SQL because roles are disabled in > PL/SQL. > > Try granting the owner of the PL/SQL object a direct > grant (select is > good enough) on cp_owner.cp_table_name. > > Hope that helps, > > -Mark > > > -- > Mark J. Bobak > Senior Oracle Architect > ProQuest Information & Learning > > "A human being should be able to change a diaper, > plan an invasion, > butcher a hog, conn a ship, design a building, write > a sonnet, balance > accounts, build a wall, set a bone, comfort the > dying, take orders, give > orders, cooperate, act alone, solve equations, > analyze a new problem, > pitch manure, program a computer, cook a tasty meal, > fight efficiently, > die gallantly. Specialization is for insects." > --Robert A. Heinlein > > > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of > Jonathan Knight > Sent: Thursday, September 14, 2006 5:41 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: Can't see index from package > > This one's got me scratching my head. So, before I > scratch a hole in it > ... > > When I execute the below query from > SQL*Plus/TOAD/etc, I have no problem > getting the rows back. But, when placed in a > procedure within a > package, it returns no_data_found. > > select * > from all_indexes > where 1 = 1 > and owner = upper ( cp_owner ) > and table_name = upper ( cp_table_name ) > order by index_name > > We had a similar issue querying > all_part_key_columns, but resolved it by > connecting to the schema owner and granting "alter > table" to oracle (the > package owner). > But doing the same for the index has not resolved > it. > > Any thoughts? > > Thanks in advance, > Jon Knight > > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam > protection around > http://mail.yahoo.com > -- > //www.freelists.org/webpage/oracle-l > > > -- > //www.freelists.org/webpage/oracle-l > > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- //www.freelists.org/webpage/oracle-l