Re: Calling procedures in another schema?

Hi Carsten,

If the user will always be within the same schema as the tables and procedure, then you should not need the third set of procedures. If both users have a procedure of the same name, each of which refers to the tables in the same schema, then they should work as desired.

Using your example, if user miller's schema contains procedure p_orders and tables customer and order, then (as long as the schema name is not specified) user miller will automatically invoke his/her procedure which will in turn use his/her tables. Likewise, if user smith has a procedure and tables of the same name in the smith schema, then when this user invokes the procedure, Oracle will use procedure smith.p_orders. It is only if you have users other than miller and smith that need to call miller's and/or smith's procedures that you will run into problems.

I hope this helps.

Mark

"Carsten Jacobs" <carsten.jacobs_at_tool42.com> wrote in message news:3A5B0A04.8A3EF720_at_tool42.com...
> Hi,
>
> I have 2 schemas and both have the same set of tables. And for both I
> need the same set of procedures and packages.
> Is it possible to have the procedures and packages in a third schema and
> call them from the other schemas.
>
> schema: miller
> Tables: customer, order
>
> schema: smith
> Tables: customer, order
>
> schema proc
> Procedure p_orders
>
> The problem is that in dependence of the schema sql statements must
> qualify the tablenames with the owner of course.
> But when the procedure p_orders is called from one of the schemas miller
> or meyer is there a way to avoid the ownername qualifier?
>
> e.g. owner miller is logged on and wants to use the procedure p_orders
> with his tables and just types execute proc.p_orders
>
> I hope it is clear what I mean.
>
> Regards
> Carsten
>
>
Received on Tue Jan 09 2001 - 07:59:23 CST