Re: Calling procedures in another schema?

Carsten,

Which db version are you using?

If you are pre-8i:
  The procedure assumes that all tables specified belong to the owner of the procedure unless otherwise stated. If you want another table in another schema, then you'll have to use dynamic SQL (with the DBMS_SQL package) to dynamically determine your SQL statements based on the user who executes the procedure.

If you are using Oracle 8i:
  The procedure can act as above (using "definer rights"), or you can use "invoker rights". Please refer to the Oracle documentation. http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76962/ch2.htm#104095

HTH,
Brian

Carsten Jacobs wrote:
>
> 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
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================