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

Marco wrote:

> 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

Privileges granted through roles are not valid for functions, procedures, or packages. The grants must be explicit. And as Sybrand has said ... we've covered this thousands of times in the last few years ... search the google.com archives.

Daniel Morgan Received on Thu Feb 13 2003 - 22:28:01 CST