Create Trigger dynamically (dbms_sql) on a table of another schema

Hello Guru,

I'm using Oracle8.0.5.
I'm logged as a user dbadata having dba role. In this account, i want to create a trigger dynamically with dbms_sql package.

I have already assign explicitly rights "create trigger to dbadata" so that dbadata account can create trigger in a pl/sql stored function. Here is the content of this trigger:

CREATE OR REPLACE TRIGGER OP411PTE_T2_AIUD    AFTER INSERT OR UPDATE OR DELETE ON OP411PTE.T2    FOR EACH ROW
DECLARE

   Dt      DATE := SYSDATE;                      -- date courante du serveur

As described above the owner of Table T2 is the account OP411PTE. When connected as op411pte, i have also granted the following grant to dbadata:

grant all on t2 to dbadata
/

The problem is that the dynamic compilation of this trigger didn't work:
table of view does not exist ????
What kind of additionnal grant must i give to dbadata so that this dynamic compilation can be performed in dbadata account ?

Thank you in advance.
P. Gineste Received on Mon Oct 07 2002 - 10:57:04 CEST