PL/SQL: how to SELECT from other user?

From: <susana73_at_hotmail.com>
Date: Tue, 02 Jan 2001 23:44:59 GMT
Message-ID: <92tp5o$qp9$1_at_nnrp1.deja.com>

Hi,

[Quoted] I have a stored procedure on user A. What it does is it inserts data by [Quoted] selecting from user B. I granted the SELECT permission on user A to [Quoted] 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 [Quoted] [Quoted] ARCH table without specifying the USERB user. However, I still got the [Quoted] SAME error mesage.

[Quoted] Please help!!

Susan

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 03 2001 - 00:44:59 CET