Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Unanswered: 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!


  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >BUt I do not want to use the shema in front of the table [system_t] or synonyms.
    >Are there other ways for this?
    No other way
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by jp7234
    BUt I do not want to use the shema in front of the table [system_t] or synonyms. Are there other ways for this?
    Why don't you want to use synonyms? A user synonym is the perfect solution for your problem. You can even automate the creation for that.

    The only other workaround would be to use views but that is more or less the same as user synonyms.

    On a side note: tables in Oracle are never written with this strange bracket notation

  4. #4
    Join Date
    Aug 2006
    Posts
    58
    Thank you very much. So, i have to use synonyms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •