| 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
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
> 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
>
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam
> protection around
> http://mail.yahoo.com
> --
> http://www.freelists.org/webpage/oracle-l
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 15 2006 - 13:33:08 CDT
 |
 |