SEO

I am logged in my schema. Plain SQL Plus:

-SELECT COUNT(*) FROM ALL_TABLES
461
-I have my own table LNK_TABLES:
TRUNCATE TABLE LNK_TABLES;
table truncated

INSERT INTO LNK_TABLES
SELECT fields...
FROM ALL_TABLES;
COMMIT;

SELECT COUNT(*) FROM LNK_TABLES;
461 <= this is OK

- BUT, the same thing in a procedure:

BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE LNK_TABLES';
INSERT INTO LNK_TABLES
SELECT fields...
FROM ALL_TABLES;
COMMIT;
END:

After execution fills LNK_TABLES with only 64 records!!??
What am I doing wrong?
Do I need some special grants, privileges or something for doing in
procedure something I can obviously do in a simple SQL statement?
It's a mistery to me!

"Ivan Petrovski" <ivan_pet@hotmail.com> a écrit dans le message de
news:bneklc$pou$1@ls219.htnet.hr...
> I am logged in my schema. Plain SQL Plus:
>
> -SELECT COUNT(*) FROM ALL_TABLES
> 461
> -I have my own table LNK_TABLES:
> TRUNCATE TABLE LNK_TABLES;
> table truncated
>
> INSERT INTO LNK_TABLES
> SELECT fields...
> FROM ALL_TABLES;
> COMMIT;
>
> SELECT COUNT(*) FROM LNK_TABLES;
> 461 <= this is OK
>
> - BUT, the same thing in a procedure:
>
> BEGIN
> EXECUTE IMMEDIATE 'TRUNCATE TABLE LNK_TABLES';
> INSERT INTO LNK_TABLES
> SELECT fields...
> FROM ALL_TABLES;
> COMMIT;
> END:
>
> After execution fills LNK_TABLES with only 64 records!!??
> What am I doing wrong?
> Do I need some special grants, privileges or something for doing in
> procedure something I can obviously do in a simple SQL statement?
> It's a mistery to me!
>
>
>

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

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

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@hotmail.com> wrote in message
news:bnepns$vtj$1@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?
>
>

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@bellsouth.net> wrote in message
news:P3Fmb.45086$h47.40458@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@hotmail.com> wrote in message
> news:bnepns$vtj$1@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?
> >
> >
>
>

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@hotmail.com> wrote in message
news:bng2cr$6v7$1@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@bellsouth.net> wrote in message
> news:P3Fmb.45086$h47.40458@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@hotmail.com> wrote in message
> > news:bnepns$vtj$1@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?
> > >
> > >
> >
> >
>
>

Ivan Petrovski wrote:

>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.
>
>
>
All synonyms contains, please correct me if I am wrong, 3.7 million records?

<NOT TO BE TAKEN SERIOUSLY>
Take 1 kilo of C4 or RDX and ....
</NOT TO BE TAKEN SERIOUSLY>

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

> All synonyms contains, please correct me if I am wrong, 3.7 million
records?

As someone who is and has worked on many, many legacy systems, this practice
is more common than you think.

And yes, 1 kilo of C4 would really help the situation.

Jeff
"Daniel Morgan" <damorgan@x.washington.edu> wrote in message
news:1067194257.984037@yasure...
> Ivan Petrovski wrote:
>
> >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.
> >
> >
> >
> All synonyms contains, please correct me if I am wrong, 3.7 million
records?
>
> <NOT TO BE TAKEN SERIOUSLY>
> Take 1 kilo of C4 or RDX and ....
> </NOT TO BE TAKEN SERIOUSLY>
>
> --
> Daniel Morgan
> http://www.outreach.washington.edu/e...ad/oad_crs.asp
> http://www.outreach.washington.edu/e...oa/aoa_crs.asp
> damorgan@x.washington.edu
> (replace 'x' with a 'u' to reply)
>

Daniel Morgan <damorgan@x.washington.edu> wrote in message news:<1067194257.984037@yasure>...
> >
> All synonyms contains, please correct me if I am wrong, 3.7 million records?
>
> <NOT TO BE TAKEN SERIOUSLY>
> Take 1 kilo of C4 or RDX and ....
> </NOT TO BE TAKEN SERIOUSLY>


Aha! 3.7 million. It's sick!