Re: DBMS_ALERT from trigger does not work, why?
Date: 1996/09/10
Message-ID: <51486n$5s3_at_inet-nntp-gw-1.us.oracle.com>#1/1
In article <32343135.1122_at_sectra.se>, Almut Herzog <al-her_at_sectra.se> writes:
|> Hi,
|>
|> I try to use the DBMS_ALERT package from a trigger and it will not work
|> though I can user DBMS_ALERT from the command line (see examples below)!
|> So it's not as easy as "run catproc" or "grant execute on SYS.DBMS_ALERT
|> to ME".
|> I never had trouble with this before, but I am not installing on my own
|> server but on someone else's (i.e. I have not much rights to go roaming
|> in the system and would anyway not know where to look).
|> Any suggestions?
|> Please reply by e-mail, too (it's faster!).
You are probably granted EXECUTE on DBMS_ALERT via a role. Try granting the privilege directly.
|> TIA,
|>
|> Almut
|> -----------------------
|>
|> SQL> create table t1 (t1 number(3));
|>
|> Table created.
|>
|> SQL> create or replace trigger tt1
|> 2 after insert or update on t1
|> 3 begin
|> 4 dbms_alert.signal('hello', 'msg');
|> 5 end;
|> 6 /
|> create or replace trigger tt1
|> *
|> ERROR at line 1:
|> ORA-06550: line 4, column 3:
|> PLS-00201: identifier 'SYS.DBMS_ALERT' must be declared
|> ORA-06550: line 4, column 3:
|> PL/SQL: Statement ignored
|> ------------------------
|>
|> SQL> begin
|> 2 dbms_alert.signal('hello', 'msg');
|> 3 end;
|> 4 /
|>
|> PL/SQL procedure successfully completed.
|>
|> -------------------------
|> SQL> select * from V$VERSION;
|>
|> Oracle7 Server Release 7.2.3.0.0 - Production Release
|> PL/SQL Release 2.2.3.0.0 - Production
|> CORE Version 3.4.3.0.0 - Production
|> TNS for HPUX: Version 2.2.3.0.0 - Production
|> NLSRTL Version 3.1.4.6.0 - Production
|>
|>
|> --
|> Almut Herzog
|> SECTRA-Imtec AB e-mail: al-her_at_sectra.se
|> Teknikringen 2 voice: ++46 13 235221
|> 583 30 Linkoping fax: ++46 13 212185
|> SWEDEN
Scott Urman Oracle Corporation surman_at_us.oracle.com
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2 Published by Oracle Press - http://www.osborne.com/oracle/index.htm
"The opinions expressed here are my own, and are not necessarily that of Oracle Corporation"
Received on Tue Sep 10 1996 - 00:00:00 CEST