Re: PL/SQL: how to SELECT from other user?

In article <92tp5o$qp9$1_at_nnrp1.deja.com>,   susana73_at_hotmail.com wrote:
> Hi,
>
> I have a stored procedure on user A. What it does is it inserts data
 by
> selecting from user B. I granted the SELECT permission on user A to
> select that particular table(ARCH) from user B.
>
> CREATE OR REPLACE PROCEDURE move_arch(
> var_name IN VARCHAR2,
> var_kind IN VARCHAR2
> )
> IS
> BEGIN
>
> INSERT INTO archive (name, kind)
> SELECT name, kind FROM USERB.arch
> WHERE name = var_name AND kind = var_kind;
> ......
>
> ##Error##
> 10/5 PL/SQL: SQL Statement ignored
> 11/44 PLS-00201: identifier 'USERB.ARCH' must be declared
>
> It didn't work after all because of the above error. Then I tried
> creating a synonym 'ARCH' on USERA so that I could select directly
 from
> ARCH table without specifying the USERB user. However, I still got
 the
> SAME error mesage.
>
> Please help!!
>
> Susan
>
> Sent via Deja.com
> http://www.deja.com/
>

as userb you need to grant select on table arch to usera

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/