help calling procedure in package from different users
Date: Tue, 29 Jan 2008 06:42:31 -0800 (PST)
I have a large perl program which connects to oracle and calls procedures in a package using:
user1 is the owner/creator of "package1".
This works when the Perl is connected to the database as user1 and when logged in as user2 who did not create the package. However, the call syntax has a big problem in that NO_DATA_FOUND exceptions are hidden (see http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/overview.htm#g1461293 which says "using the CALL statement can suppress an ORA-01403: no data found error".
If I replace "call" with:
begin user1.package1.procedure1(args); end;
then the NO_DATA_FOUND exceptions are not suppressed which is what I want. The problem is the above syntax only works when user2 wants to call the procedure and fails as below for user1 (the package owner):
PLS-00302: component 'PACKAGE1' must be declared
Is there any way for user1 and user2 to use exactly the same syntax to call procedure1 in package1 owned by user1?
Thanks Received on Tue Jan 29 2008 - 08:42:31 CST