CODEWITHSUNDEEP
oracle-databaseplsql
F0cus
F0cus

Reputation: 625

Send Mail using PL/SQL without SMTP

Is there any other way to send email from Oracle except using SMTP :

Kindly let me know if there is any way with some example if possible.

Upvotes: 0

Views: 1750

Answers (1)

are
are

Reputation: 2615

Starting from Oracle 8i release 8.1.6, one can send E-mail messages directly from PL/SQL using either the UTL_TCP or UTL_SMTP packages.

Send mail with UTL_TCP

CREATE OR REPLACE PROCEDURE SEND_MAIL (
  msg_from    varchar2 := 'oracle',
  msg_to      varchar2,
  msg_subject varchar2 := 'E-Mail message from your database',
  msg_text    varchar2 :=  )
IS
  c  utl_tcp.connection;
  rc integer;
BEGIN
  c := utl_tcp.open_connection('127.0.0.1', 25);       -- open the SMTP port 25 on local machine
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'HELO localhost');
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'MAIL FROM: '||msg_from);
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'RCPT TO: '||msg_to);
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'DATA');                 -- Start message body
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'Subject: '||msg_subject);
  rc := utl_tcp.write_line(c, );
  rc := utl_tcp.write_line(c, msg_text);
  rc := utl_tcp.write_line(c, '.');                    -- End of message body
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  rc := utl_tcp.write_line(c, 'QUIT');
  dbms_output.put_line(utl_tcp.get_line(c, TRUE));
  utl_tcp.close_connection(c);                         -- Close the connection
END;
/

-- Test it:
set serveroutput on

exec send_mail(msg_to  =>'[email protected]');

exec send_mail(msg_to  =>'[email protected]',  -
           msg_text=>'Look Ma, I can send mail from plsql' -
              );

Upvotes: 1

Related Questions