 | |
help calling procedure in package from different users
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
help calling procedure in package from different users -
01-29-2008
, 08:42 AM
#2
| |||
| |||
|
|
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 |
#3
| |||
| |||
|
|
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 |
#4
| |||
| |||
|
|
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 |
#5
| |||
| |||
|
|
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 |
#6
| |||
| |||
|
|
Martin <martin.j.evans (AT) gmail (DOT) com> wrote in news:8d8ce4c8-fe39-41e7-b6e5-dee08441b714 (AT) b2g2000hsg (DOT) 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. |
#7
| |||
| |||
|
|
Martin <martin.j.evans (AT) gmail (DOT) com> wrote in news:8d8ce4c8-fe39-41e7-b6e5-dee08441b714 (AT) b2g2000hsg (DOT) 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. |
#8
| |||
| |||
|
|
Martin <martin.j.evans (AT) gmail (DOT) com> wrote in news:8d8ce4c8-fe39-41e7-b6e5-dee08441b714 (AT) b2g2000hsg (DOT) 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. |
#9
| |||
| |||
|
|
Martin <martin.j.evans (AT) gmail (DOT) com> wrote in news:8d8ce4c8-fe39-41e7-b6e5-dee08441b714 (AT) b2g2000hsg (DOT) 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. |
#10
| |||
| |||
|
|
Martin <martin.j.ev... (AT) gmail (DOT) com> wrote innews:8d8ce4c8-fe39-41e7-b6e5-dee08441b714 (AT) b2g2000hsg (DOT) 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/B...w.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. |
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
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.