Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_ALERT Unknown Within User-Defined PL/SQL Package

Avi AbramiJul 12 2004 — edited Jul 12 2004
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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 9 2004
Added on Jul 12 2004
1 comment
587 views