help calling procedure in package from different users
in [Oracle Help]
|
From: Martin on 29 Jan 2008 09:42 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
From: Ana C. Dent on 29 Jan 2008 10:28 Martin <martin.j.evans(a)gmail.com> wrote in news:8d8ce4c8-fe39-41e7-b6e5-dee08441b714(a)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.
From: Gabriel on 29 Jan 2008 11:04 "Ana C. Dent" <anacedent(a)hotmail.com> a �crit dans le message de news: XCHnj.8107$Mg7.3623(a)newsfe12.phx... > Martin <martin.j.evans(a)gmail.com> wrote in > news:8d8ce4c8-fe39-41e7-b6e5-dee08441b714(a)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. And grant execute on this object to user2 while being connected as user1.
From: Martin on 29 Jan 2008 11:20 On Jan 29, 3:28 pm, "Ana C. Dent" <anaced...(a)hotmail.com> wrote: > Martin <martin.j.ev...(a)gmail.com> wrote innews:8d8ce4c8-fe39-41e7-b6e5-dee08441b714(a)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
From: Frank van Bortel on 29 Jan 2008 14:04 Martin wrote: > 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 One of: - automate the synonym creation (write a script, that delivers the correct statement) - rethink you strategy of having so many users, that all own a couple of procedures. It is far more customary to have one or two users that "own" the application: data, procedures, etc. -- Regards, Frank van Bortel Top-posting in UseNet newsgroups is one way to shut me up
|
Pages: 1 Prev: Require Oracle Professional for Kuwait Next: Selct DISTINCT |