SEND EMAIL USING PL/SQL
Below is the code used :
We are creating a procedure to achieve this,
CREATE OR REPLACE PROCEDURE xx_sendmail
AS
AS
crlf VARCHAR2(2) := CHR(13)||CHR(10);
v_Mail_Conn utl_smtp.Connection;
V_MAIL_HOST VARCHAR2(1000):= NULL;
V_MAIL_HOST1 VARCHAR2(1000):= NULL;
V_RECIPIENT VARCHAR2(1000):= NULL;
V_SUBJECT VARCHAR2(1800):= NULL;
v_sender varchar2(400);
v_tot_msg varchar2(300);
BEGIN
SELECT UTL_INADDR.GET_HOST_ADDRESS into v_mail_host from dual;
dbms_output.put_line('host_addre :'||v_mail_host);
v_mail_host1:='abc@gmail.com';
v_sender:='abc@gmail.com';
V_RECIPIENT:='xyz@gmail.com';
v_subject:='Hi....';
v_tot_msg:='mail send through pl sql code';
V_MAIL_CONN := UTL_SMTP.OPEN_CONNECTION(V_MAIL_HOST, 25);
UTL_SMTP.HELO(V_MAIL_CONN, V_MAIL_HOST);
UTL_SMTP.MAIL(V_MAIL_CONN, v_sender);
UTL_SMTP.RCPT(V_MAIL_CONN, V_RECIPIENT);
UTL_SMTP.DATA(V_MAIL_CONN,
'DATE: ' || TO_CHAR(SYSDATE, 'DY, DD MON YYYY HH24:MI:SS') || CRLF ||
'FROM: ' || V_MAIL_HOST1 || CRLF ||
'SUBJECT: '|| V_SUBJECT || CRLF ||
'TO: ' || V_RECIPIENT || CRLF ||
CRLF ||
CRLF ||
CRLF ||
V_TOT_MSG|| CRLF -- MESSAGE BODY
);
UTL_SMTP.QUIT(V_MAIL_CONN);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
RAISE_APPLICATION_ERROR(-20000, 'UNABLE TO SEND MAIL: '||SQLERRM);
When others then
dbms_output.put_line(sqlerrm);
END;
/
/
execute the procedure xx_sendmail
Thanks!!
