View Single Post
  #1 (permalink)  
Old 07-02-09, 12:30
jp7234 jp7234 is offline
Registered User
 
Join Date: Aug 2006
Posts: 58
How to access the tables from others user?

Hello , everyone.

Here I have written a Package in Oracle 10g as follow:
①HEAD:
CREAT OR REPLACE PACKAGE cm.com
IS
PROCEDURE IsOK(name OUT VARCHAR2);
END cm.com;

①BODY:
CREAT OR REPLACE PACKAGE BODY cm.com
IS
PROCEDURE IsOK(name OUT VARCHAR2)
IS
w_person_name VARCHAR2(50);
w_sys_name VARCHAR2(50);

BEGIN
 SELECT person_name INTO w_person_name from person_t;
 SELECT sys_name INTO w_sys_name from system_t;
 name := w_person_name||w_sys_name;
END IsOK;

END cm.com;

Here:
①person_t is belonged to the user [cm]
②system_t  is belonged to the user [dm]. However the user [cm] have the only previlage [select] to the dm.system_t.

The problem is:
When I compiled the package, there was a error : the table or view system_t not exist.... But, in PL/SQL DEVELOPER, I logged in with the user cm, I could get the data form system_t with the statement:
①select * from system_t.

I can use the following way to get data in the package:
①SELECT sys_name INTO w_sys_name from dm.system_t;
BUt I do not want to use the shema in front of the table [system_t] or synonyms. Are there other ways for this?

Thank very much!

Reply With Quote