1

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.

enter image description here

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?

1 Answers1

2

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
psaraj12
  • 4,772
  • 2
  • 21
  • 30
  • When I configure SMTP in Oracle, I could not find any article about SMTP password configuration in Oracle. That is one confusion I have at the moment. All they configure is SMTP server and port. Since I am new to oracle, am I missing anything? –  Mar 03 '19 at 14:18
  • You need to configure ACL for the SMTP server.This is what Oracle documentation says "This UTL_MAIL package is now an invoker's rights package and the invoking user will need the connect privilege granted in the ACL(access control list) assigned to the remote network host to which he wants to connect. – psaraj12 Mar 03 '19 at 14:29
  • refer this link for ACL https://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG98075 – psaraj12 Mar 03 '19 at 14:36
  • 2
    Note that Oracle does not include an actual SMTP server. So you need to install an email server if you don't already have one. – APC Mar 03 '19 at 17:20