Re: Calling procedures in another schema?

From: Stephan Born <stephan.born_at_beusen.de>
Date: Wed, 10 Jan 2001 15:17:38 +0100
Message-ID: <3A5C6F02.4069D2FA@beusen.de>

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.

The only way I see is to use dynamic SQL. You can build your SQL-statements
as you need it.

for example:

the following procedure exists in schema A:

create or replace procedure do_it (schema_name in varchar2) as

   c_id number;
   stmt varchar2(2000);
   result number;
begin

c_id := dbms_sql.open_cursor;

   stmt := 'select col_name ' ||
           'from ' || schema_name || '.tab_name ' ||
           'where second_col_name = 1';

dbms_sql.parse (c_id, stmt, dbms_sql.native);

dbms_sql.define_column (c_id, 1, result); processed_rows := dbms_sql.execute_and_fetch (c_id, exact => true); dbms_sql.column_value (c_id, 1, result);

dbms_sql.close_cursor(c_id);
exception

when others then

      if dbms_sql.is_open (c_id) the
         dbms_sql.close_cursor(c_id);
      end if;
      raise

end do_it;

grant execute on this procedure to the schemas B and C. Now you can execute the procedure from these schemas by

begin

A.do_it ('B');
end;

respectively

begin

A.do_it ('C');
end;

I didn't compile und run this, but it should work.

Since Oracle8 (8i ?) you cane use

exec immediate stmt

instead of

dbms_sql.execute (c_id, exact => true);