RE: Can't see index from package
- From: Jonathan Knight <knightjck_work@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx, Mark.Bobak@xxxxxxxxxxxxxxx
- Date: Fri, 15 Sep 2006 11:33:08 -0700 (PDT)
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
Other related posts:
