| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: 1 Stored Procedure for all Tablespaces
A copy of this was sent to Barry Ludwig <barry.ludwig_at_informore.com>
(if that email address didn't require changing)
On 19 Jan 1999 09:30:52 GMT, you wrote:
>Hi all,
>
>
>I have been writing some stored procedures.
>Oracle puts the UserId before the table name.
>
>Is there a way to create a procedure, that will run in the active
>tablespace for the active user.
>
>Eg
>
>user1.emp using program to average the salary, but if user2 runs this
>program he gets the result of user1.emp, as user1 has created
>the program.
>
>How can I work arround this problem
short of using dynamic sql and building every query yourself, you currently cannot do that. the pl/sql is compiled and bound to objects AT compile time, not at run time. So, "select * from emp" put into a procedure written by scott is "select * from scott.emp" since scott.emp is what scott sees when he compiles it.
This changes in 8i... In that release, you can specify at compile time whether to resolve the referenced tables and such with the definers schema or to resolve them at runtime within the current_users schema...
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jan 19 1999 - 13:19:58 CST
 |
 |