Re: Can't select from ALL_TABLES in a stored procedure

Reason:
Users need to be able to link Oracle tables to MS Access. But the database is so screwed up with zillions of objects and synonyms (and we are not allowed to do anything about it, for some legal reasons, contracts signed with the company that sold us this crappy application, blah blah etc.). It takes ages to link just one table. But I have come up with a solution; an ODBC driver that (sorry, I, must not give any information about it) doesn't look into those SYS.ALL_SOMETHING views, but rather the indexed tables created form them. It works fantastic! But I want to refresh the data in my tables daily (during night). That's why I need a procedure like this. There are actualy 11 views to gather data fom, the worst one being ALL_SYNONYMS with approx. 3.700.000 records.

So, when you say "do the grants" it means grants to "ALL_SOMETHING" (preffered, but will it work), or grants to each table and view in database (this doesn't solve anything)?
Can I set role from within procedure?

"Burt Peltier" <burttemp1ReMoVeThIs_at_bellsouth.net> wrote in message news:P3Fmb.45086$h47.40458_at_bignews4.bellsouth.net...
> Seems like the only/best solution is to not use roles for the 1 account
that
> needs this Plsql . For this 1 account, do the grants directly to the
> account. You could still use the role for other accounts.
>
> But, why are you writing this Plsql? Given the reason, someone could
maybe
> come up with a better solution.
>
> "Ivan Petrovski" <ivan_pet_at_hotmail.com> wrote in message
> news:bnepns$vtj$1_at_ls219.htnet.hr...
> > > Roles are not enabled in a procedure.
> > > Execute "set role none" and then your statements in SQL*Plus and I bet
> > you'll get only 64 records.
> > >
> > > Hope this helps
> > > Michel Cadot
> > >
> > >
> > Thanks, this helps me produce the undesired behaviour ;-)
> > But what should I do? Will it work if DBA grants me select on
all_tables?
> >
> >
>
>
Received on Sun Oct 26 2003 - 03:07:34 CST