How Can I Send Emails in PL/SQL (Oracle)?

Question

I have a procedure which count the number of rows in SMS_OUTBOX table and send emails if its row cont is over 1000. My procedure is given below:

CREATE OR REPLACE PROCEDURE SEND_EMAIL_ABOUT_PENDING_SMS IS
  CHECK_SMS_COUNT NUMBER := 1000;
  CURRENT_SMS_COUNT NUMBER;
BEGIN
  SELECT COUNT(1) INTO CURRENT_SMS_COUNT FROM SMS_SCHEMA.SMS_OUTBOX;

  IF CURRENT_SMS_COUNT >= CHECK_SMS_COUNT THEN
    UTL_MAIL.SEND(
        sender=>'<SENDER_EMAIL>',
        recipients=>'<RECIPIENT_EMAIL>',
        subject=>'Pending SMS',
        Message=>'Pending SMS count exceeded.'
    );
  END IF;
END SEND_EMAIL_ABOUT_PENDING_SMS;
/

When I compile the above I got this error.

Then I tried this line of code to execute without the procedure:

EXEC UTL_MAIL.SEND(
    sender=>'<SENDER_EMAIL>',
    recipients=>'<RECIPIENT_EMAIL>',
    subject=>'Pending SMS',
    Message=>'Pending SMS count exceeded.'
);

Then I got this error:

PLS-00302: component 'SEND' must be declared

I am very new to Oracle (and PL/SQL). Can anyone please help me on sending emails in Oracle and what are the configurations needed?

Answer 1

Please check whether UTL_MAIL has been installed properly.

Follow the link UTL_MAIL for information and configuration required

Setup
The package is loaded by running the following scripts.

CONN sys/password AS SYSDBA
@$ORACLE_HOME/rdbms/admin/utlmail.sql
@$ORACLE_HOME/rdbms/admin/prvtmail.plb

In addition the SMTP_OUT_SERVER parameter must be set to identify the SMTP server.

CONN sys/password AS SYSDBA
ALTER SYSTEM SET smtp_out_server='smtp.domain.com' SCOPE=SPFILE;

-- Instance restart only necessary in 10gR1.
SHUTDOWN IMMEDIATE
STARTUP