Greetings,
I am using Oracle 9i (9.2.0.4.0) database on SUN [sparc] Solaris 9.
From an anonymous PL/SQL block, I can invoke procedures in the DBMS_ALERT package, for example:
begin
DBMS_ALERT.SIGNAL('Avi','was here');
end;
But if I put the above call to DBMS_ALERT.SIGNAL() in a stored procedure, or in a PL/SQL package, I get the following error:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
PL/SQL procedure successfully completed.
SQL> create procedure P_AVI
2 is
3 begin
4 DBMS_ALERT.SIGNAL('Avi','was here, again');
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE P_AVI:
Hit <ENTER> to continue...
LINE/COL ERROR
-------- -------------------------------------------------------
4/3 PLS-00201: identifier 'DBMS_ALERT' must be declared
4/3 PL/SQL: Statement ignored
I have searched the OTN forum archives, and the
MetaLink Web site, and the Oracle documentation, but I couldn't find anything of help.
I would like someone to explain what I need to do in order to be able to invoke DBMS_ALERT.SIGNAL() from within a user-defined (PL/SQL) package, or an alternative to DBMS_ALERT.SIGNAL().
Basically, I have a procedure that receives a list of parameters, and updates several database tables, based on the supplied parameters. If one (or more) of the parameters is invalid, the procedure does not raise an error, it merely sends an "alert" that notifies any interested parties, that a database table was updated with possibly invalid data.
Thanks (in advance),
Avi.