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@nnrp1.deja.com>

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/ Received on Tue Jan 02 2001 - 17:44:59 CST