Sending mail by PL Sql in oracle

A stored procedure, to send mail can be as:

We are using a new type in the SP for containing the CC mail list. For this purpose, we can do as:


CREATE OR REPLACE TYPE mail_ccs AS TABLE OF VARCHAR2;

SP Definition:

CREATE OR REPLACE PROCEDURE sendMail(
    username       IN VARCHAR2,
    password       IN VARCHAR2,
    smtpHost       IN VARCHAR2,
    smtpPort       IN PLS_INTEGER DEFAULT 25,
    mailFrom       IN VARCHAR2,
    rcptTo         IN VARCHAR2,
    ccs            IN mail_ccs,
    messageSubject IN VARCHAR2,
    messageBody    IN VARCHAR2)
IS
  l_conn UTL_SMTP.connection;
  l_ccs              VARCHAR2(2000);
  l_encoded_username VARCHAR2(200);
  l_encoded_password VARCHAR2(200);
BEGIN
  --open connection
  /*
  l_conn := UTL_SMTP.open_connection(smtpHost, smtpPort);
  UTL_SMTP.helo(l_conn, smtpHost);
  */
  --In case of authentication needed
  l_encoded_username := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(username)));
  l_encoded_password := UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw(password)));
  l_conn             := UTL_SMTP.open_connection(smtpHost, smtpPort);
  UTL_SMTP.ehlo(l_conn, smtpHost);--DO NOT USE HELO
  UTL_SMTP.command(l_conn, 'AUTH', 'LOGIN');
  UTL_SMTP.command(l_conn, l_encoded_username);
  UTL_SMTP.command(l_conn, l_encoded_password);
  --prepare headers
  UTL_SMTP.mail(l_conn, mailFrom);
  UTL_SMTP.rcpt(l_conn, rcptTo);
  /*if we have multiple recipients or CCs, we must call UTL_SMTP.rcpt once for each one
  however, we shall specify that there are CCs in the mail header in order for them to appear as such*/
  IF ccs IS NOT NULL THEN
    FOR i IN ccs.FIRST..ccs.LAST
    LOOP
      UTL_SMTP.rcpt(l_conn, ccs(i));--add recipient
      l_ccs:=l_ccs||ccs(i)||',';    --mark as CC
    END LOOP;
    --now remove the trailing comma at the end of l_ccs
    l_ccs:=SUBSTR(l_ccs,0,LENGTH(l_ccs)-1 );
  END IF;
  --start multi line message
  UTL_SMTP.open_data(l_conn);
  --prepare mail header
  /*DO NOT USE MON instead of MM in the date pattern if you run the script on machines with different locales as it will be misunderstood
  and the mail date will appear as 01/01/1970*/
  UTL_SMTP.write_data(l_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'To: ' || rcptTo || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'Cc: ' || l_ccs || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'From: ' || mailFrom || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_conn, 'Subject: ' || messageSubject || UTL_TCP.crlf || UTL_TCP.crlf);
  --include the message body
  UTL_SMTP.write_data(l_conn, messageBody || UTL_TCP.crlf || UTL_TCP.crlf);
  --send the email
  UTL_SMTP.close_data(l_conn);
  UTL_SMTP.quit(l_conn);
END; 



To use the above SP, we can execute that by calling below code:

DECLARE
  USERNAME VARCHAR2(200);
  PASSWORD VARCHAR2(200);
  SMTPHOST VARCHAR2(200);
  SMTPPORT BINARY_INTEGER;
  MAILFROM VARCHAR2(200);
  RCPTTO VARCHAR2(200);
  CCS PAUL.MAIL_CCS;
  MESSAGESUBJECT VARCHAR2(200);
  MESSAGEBODY VARCHAR2(200);
BEGIN
  USERNAME := 'paul.pronabananda';
  PASSWORD := 'paulpassword';
  SMTPHOST := '172.20.1.1';
  SMTPPORT := 50;
  MAILFROM := 'paul.pronabananda';
  RCPTTO := 'paul.pronabananda@gmail.com';
  -- Modify the code to initialize the variable
  -- CCS := NULL;
  MESSAGESUBJECT := 'Test';
  MESSAGEBODY := 'Test Body';

  SENDMAIL(
    USERNAME => USERNAME,
    PASSWORD => PASSWORD,
    SMTPHOST => SMTPHOST,
    SMTPPORT => SMTPPORT,
    MAILFROM => MAILFROM,
    RCPTTO => RCPTTO,
    CCS => CCS,
    MESSAGESUBJECT => MESSAGESUBJECT,
    MESSAGEBODY => MESSAGEBODY
  );
--rollback; 

END;

If mail server address is not already added in the acl list. in that case we will have to add that info in the acl:

If ACL not exists, at first we need to create ACL by below code:

BEGIN 

DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( 

acl => 'utlpkg.xml', 

description => 'created by paul', 

principal => 'PAUL', 

is_grant => TRUE, 

privilege => 'connect', 

start_date => null, 

end_date => null); 

END;

After creation, we are in need to add the host IP and port in that ACL using below:

BEGIN

  DBMS_NETWORK_ACL_ADMIN.assign_acl (

    acl         => 'utlpkg.xml',

    host        => '172.20.1.1', 

    lower_port  => 1,

    upper_port  => 100);   

  COMMIT;

END;

--