Re: help calling procedure in package from different users

From: Ana C. Dent <>
Date: Tue, 29 Jan 2008 15:28:55 GMT
Message-ID: <XCHnj.8107$Mg7.3623@newsfe12.phx>

Martin <> wrote in

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

It might help to create a synonym for USER2. Received on Tue Jan 29 2008 - 09:28:55 CST

Original text of this message