Re: PL/SQL procedure managing multiple schema???

fred

Yes, it is possible, but the schema where the procedure is running must be granted access rights by the schema which owns the items you want to operate on.

It is also a good idea to make the location of the items you are operating on transparent to the application (in your case, the procedure) by setting up synonyms.

Mark

On Thu, 1 Jul 1999 18:07:17 +0200, "fred" <frederic.servais_at_advalvas.be> wrote:

>Is it possible to manage more than one schema in the same procedure...:
>
>SQLWKS> create procedure setToOperStatus
> 2> is begin
> 3> update schema1.component pc
> 4> set com_status_id = (select com_status_id from schema2.component oc
>where pc.com_id = oc.com_id)
> 5> end;
> 6>
>MGR-00072: Warning: PROCEDURE SETTOOPERSTATUS created with compilation
>errors.
>SQLWKS> show errors procedure settooperstatus
>Errors for PROCEDURE SETTOOPERSTATUS:
>LINE/COL ERROR
>----------------------------------------------------------------------------
>----
>4/48 PLS-00201: identifier 'schema2.COMPONENT' must be declared
>
>???
>
>
Received on Tue Jul 06 1999 - 06:28:20 CDT