Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004
    Posts
    76

    Lightbulb Unanswered: viewing other users table in a stored procedure

    Hi,

    I need to select values from other users table within a stored procedure.
    For eg the below procedure is executed in user USER1



    create or replace procedure sp1
    as
    var1 number(9);
    begin
    select empno into var1 from USER2.employee;
    DBMS_OUTPUT.PUT_LINE('The Emp No = ' || var1);
    end;

    I am able select the values from the table in SQL Plus but not able to do it within a Stored Procedure. Any pointers will help

    Thanks,
    Cheeku

  2. #2
    Join Date
    Oct 2004
    Location
    Karachi
    Posts
    9
    Try using Synonym

  3. #3
    Join Date
    Aug 2004
    Posts
    76
    you got my intension right. I had created a synonym and used the synonym in the stored procedure which resulted in error. Hence expanded the query with the user name directly.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Have you done 'grant select on user2.employee to xxx' where xxx is the owner of the procedure. Also dont forget procedures dont 'see' roles so you have to explicitly grant priviliges to the owner of the procedure for the procedure to use them.

    Alan

  5. #5
    Join Date
    Aug 2004
    Posts
    76
    Hi,

    The Grant statment did the trick for me. Thanks a lot Alan & maliksarmad.

    -Cheeku

Posting Permissions

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