help calling procedure in package from different users

From: Martin <>
Date: Tue, 29 Jan 2008 06:42:31 -0800 (PST)
Message-ID: <>

I have a large perl program which connects to oracle and calls procedures in a package using:

call user1.package1.procedure1(args);

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 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

Original text of this message