Re: Trigger call to a package in another schema generates an error

From: Shmyg <shmyg_at_umc.com.ua>
Date: 20 Feb 2003 08:58:17 -0800
Message-ID: <32122f56.0302200858.2a6b7fca_at_posting.google.com>


"Marco" <marcoat_at_NOyahooSPAM.com> wrote in message news:<wdU2a.12940$rE3.7918_at_nwrddc01.gnilink.net>...
> Hey guys . . .
>
> Has anyone ever called a package from a trigger that is owned by another
> schema in Oracle 8.1.7?
>
> NOTE: I can execute this procedure in SQL*Plus as Schema1 with no problem.
> The problem only exists when I try
> to call this procedure inside Schema1's trigger!
>
> When I attempt to compile the trigger, I get the following error:
>
> TRIG1
> PLS-00201: identifier 'SCHEMA2.PKG1.PROC1' must be declared
>
> I have two schemas in an instance and they are laid out in the following
> way:
>
> Schema1: resource permission
> execute any procedure
> grants for Table2 in Schema2 (insert, update, delete, select)
>
> TABLE1
> which has a trigger:
>
> CREATE OR REPLACE TRIGGER TRIG1
> AFTER INSERT ON TABLE1
> FOR EACH ROW
> BEGIN
> SCHEMA2.PKG1.PROC1(:new.col1);
> END;
>
>
> Schema2: dba permission
> Table2
>
> PKG1 which has PROC1 procedure
> PROC1 inserts into TABLE2
>
> CREATE OR REPLACE PACKAGE PKG1 IS
> PROCEDURE PROC1(parm1 in varchar2);
> END:
>
> CREATE OR REPLACE PACKAGE BODY PKG1 IS
> PROCEDURE PROC1(parm1 in varchar2) IS
> BEGIN
> INSERT INTO TABLE2 VALUES (PARM1,'C',SYSDATE);
> END;
> END;
>
> - Public synonym for PKG1
>
> NOTE: This is oversimplified code used only to post this message, but the
> underlying logic is the same.
> Any ideas would be appreciated.
>
> Thanks,
>
> Ann

I'm not sure for 100% but it looks for me that in this situation you need to grant direct rights on this package to schema1 - not by 'EXECUTE ANY PROCEDURE'. Try it - may be it will work. Received on Thu Feb 20 2003 - 17:58:17 CET

Original text of this message