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


--- "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
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 
> 


__________________________________________________
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


Other related posts: