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

"Marco" <marcoat_at_NOyahooSPAM.com> wrote in message news:lMU2a.7884$SB2.5427_at_nwrddc03.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
>
>

This is a FAQ. PLEASE ALWAYS search the Google archives b4 posting.

You should be able to find 1000s of identical answers when you search for the error code.

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address