Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> PL/SQL procedure: How to access tables not owned by you

PL/SQL procedure: How to access tables not owned by you

From: Grace Tsai <gtsai_at_ribm00.rhic.bnl.gov>
Date: 1997/11/05
Message-ID: <3460B6B5.41C6@ribm00.rhic.bnl.gov>#1/1

In a PL/SQL procedure, how do you access the table which is not owned by you but is granted to you? The following test.sql is a very short .sql code which I use as an example to test:

test.sql


create procedure test is
  out_empno integer;
begin
  select empno into out_empno from emp where ename=upper('james'); end;
/

I logged on as "scott/tiger" in sqlplus, and entered "start test" to store this procedure. ***BUT*** in this procedure, I also like to access a table "address" which is owned by the user "grace", and "grace" granted the privilege of using this table to the user "scott/tiger". What's the syntax to use in this procedure? I tried

 select zip into out_zip from grace.address where aname=upper('james');

but I got the error message

   "PLS-00201: "IDENTIFIER GRACE.ADDRESS must be declared".

Does anybody know the syntax of accessing the tables not owned by you? Thanks in advance. Sorry to ask you about this stupid question, I am just taking over the Oracle DBA assignment recentcly.

Regards,
  Grace Received on Wed Nov 05 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US