Re: help calling procedure in package from different users

Martin <martin.j.evans_at_gmail.com> wrote in news:8d8ce4c8-fe39-41e7-b6e5-dee08441b714_at_b2g2000hsg.googlegroups.com:

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

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