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

From: VC <boston103_at_hotmail.com>
Date: Fri, 12 Dec 2003 18:53:06 GMT
Message-ID: <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 Fri Dec 12 2003 - 19:53:06 CET

Original text of this message