Re: help calling procedure in package from different users

On Jan 29, 3:28 pm, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> Martin <martin.j.ev..._at_gmail.com> wrote innews: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.

thanks. This seems to work for everyone:

create public synonym testsynonymn for user1.package1 begin testsynonym.procedure1(args); end;

Anyone have any other suggestions as this would require the creating of a massive number of synonyms?

Martin Received on Tue Jan 29 2008 - 10:20:52 CST