| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can't see index from package
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Knight
Sent: Thursday, September 14, 2006 5:41 PM
To: oracle-l_at_freelists.org
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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 14 2006 - 16:58:22 CDT
 |
 |