Re: procedures and table scope... PLS-00201

When are you refering a table in Stored procedure from other schema you need to grant the select on that table directly without involving any role.

Faheem

"

VC" <boston103_at_hotmail.com> wrote in message news:<m8oCb.375468$ao4.1254277_at_attbi_s51>...
> Hello Andrew,
>
> A privilege ('select/update/delete') has to be granted directly to the
> procedure executor, not via a role.
>
> sqlplus can read the table because the access is granted via a role.
>
> Rgds.
>
> "Andrew Metcalfe" <chicagoandy_at_hotmail.com> wrote in message
> news:a2588ec3.0312120944.ad3188f_at_posting.google.com...
> > I'm having trouble writing a simple procedure.
> >
> > I'm trying to simply select against a table in a different schema.
> >
> > I first wrote a simple procedure that selected against a table in my
> > schema, which worked.
> >
> > CREATE OR REPLACE PROCEDURE test
> > IS
> > CURSOR cur_test
> > IS
> > select *
> > from tablename;
> > BEGIN
> > FOR itemrec IN cur_test
> > LOOP
> > DBMS_OUTPUT.PUT_LINE('record!');
> >
> > END LOOP;
> > END test;
> >
> > A simple change to this is to change my select line to:
> >
> > select *
> > from otherTablespace.tablename;
> >
> > This gives me an error saying "otherTablespace.tablename must be
> > declared."
> >
> > My first thought was to create a public synonym, so I did.
> >
> > CREATE PUBLIC SYNONYM mySyn FOR otherTablespace.tablename
> >
> > I also change my select line to read:
> >
> > select *
> > from mySyn
> >
> > PLS-00201: identifier mySyn' must be declared.
> >
> > What am I missing? Why can't I "see" the other schemas table in a
> > procedure even when explicitly referenced using the
> > tablespace.tablename?
> >
> > I am able to access the table through SQLPlus...?
> >
> > Thanks.
> >
> > _Am
Received on Sat Dec 13 2003 - 03:51:37 CET