RE: Can't see index from package
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
- To: <knightjck_work@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 14 Sep 2006 17:58:22 -0400
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
Other related posts:
