var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: viewing other users table in a stored procedure
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
select empno into var1 from USER2.employee;
DBMS_OUTPUT.PUT_LINE('The Emp No = ' || var1);
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
Try using Synonym
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.
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.
The Grant statment did the trick for me. Thanks a lot Alan & maliksarmad.