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

That sounds like an interesting solution. We have some "shared database" (many applications in 1 database) environments where way too many grants to public were done . So, I have seen this problem you are talking about with Access.

And, you can't revoke grants to public.

So, MS Access will (when linking an Oracle table) do exactly as you say - take forever to provide the complete list and then it takes forever in Access to find what you are looking for in the limited window provided by Access.

Anyway, my comment on grants were intended for the PlSql to see everything granted via a role. I was talking about doing the grants on the tables and views that you wanted the PlSql to see - directly to the account that owns the PlSql. This may not be feasible in your situation.

You could always write the code in something other than PlSql outside the database. Then, it would not encounter the problem you are hitting with stored PlSql and role privs.

-- 
"Ivan Petrovski" <ivan_pet_at_hotmail.com> wrote in message
news:bng2cr$6v7$1_at_ls219.htnet.hr...


> 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?

> > >

> > >

> >

> >

>

>