I have below code which i am using for sending emails to SMTP.
create or replace PROCEDURE "SEND_MAIL_SMTP"
( sender IN VARCHAR2,
recipient IN VARCHAR2,
--CC IN VARCHAR2,
subject IN VARCHAR2,
message IN LONG
)
IS
err_code VARCHAR(100);
err_msg VARCHAR(200);
mailhost VARCHAR2(30) := 'w010.k'; -- -- host mail address
mail_conn utl_smtp.connection ;
crlf VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
mesg long;
l_encoded_username VARCHAR2(300):= UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('s@l.com')));
l_encoded_password VARCHAR2(300):= UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(UTL_RAW.cast_to_raw('s01')));
BEGIN
err_code :=SUBSTR(SQLCODE, 1, 100);
err_msg := SUBSTR(SQLERRM, 1, 200);
mail_conn := utl_smtp.open_connection(mailhost, 25);
mesg:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
'From: <'||sender||'>' || crlf ||
'Subject: '||subject || crlf ||
'To: '||recipient || crlf ||
--'CC: '|| CC || crlf ||
'' || crlf || message;
--utl_smtp.helo(mail_conn, '85.13.150.55');
UTL_SMTP.ehlo(mail_conn, 'w010.k');
UTL_SMTP.command(mail_conn, 'AUTH', 'LOGIN');
UTL_SMTP.command(mail_conn, l_encoded_username);
UTL_SMTP.command(mail_conn, l_encoded_password);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
--utl_smtp.rcpt(mail_conn, CC);
utl_smtp.data(mail_conn, mesg);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
UTL_SMTP.QUIT(mail_conn);
EXCEPTION
WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR THEN
NULL; -- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The QUIT call raises an
-- exception that we can ignore.
WHEN OTHERS THEN
EXECUTE IMMEDIATE 'INSERT INTO SEND_MAIL_SMTP_EXCEPTION values(''MAIL FAILED ERROR'',error_code|| '' '' || err_msg,SYSDATE)';
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;
And below is the code which takes the data from Track_Records table and send mails to s@l.com. I have created DB links for Verify and Production database and i am also taking the data from Track_Records table from this databases and also sending emails ata the same time with Test_Server database. I have create scheduler jobs every 1 hour to trigger Execute_Send_Mail_Smtp procedure. I am getting emails from PreProd database regularly but for Verifiy and Prodcution database its skiping the emails in between some time. I have checked the DB links its working fine. I dont know whether its because of transaction issue from Execute_Send_Mail_Smtp procedure as i am sending the 3 emails at the same time from Test_Server,Verify and Production database or is there any other reason. I am also tracking exception if any in SEND_MAIL_SMTP_EXCEPTION table. but this table is also empty which means no error with procedure.
create or replace PROCEDURE "EXECUTE_SEND_MAIL_SMTP" as
PRAGMA AUTONOMOUS_TRANSACTION;
v_kpi_preprod varchar2(2000) :=' ';
v_kpi_verification varchar2(2000) :=' ';
v_kpi_production varchar2(2000) :=' ';
--CURSOR KPI_TRACK_RUNNING IS
--Select KPI_NAME FROM RATOR_MONITORING.Track_Records;
begin
-----------------------------------------Send Mail from PreProd-----------------------------------------------------------------------------
FOR Track_Records_Row IN (Select KPI_NAME,KPI_DEF_ID FROM Track_Records)
LOOP
IF Track_Records_Row.KPI_NAME IS NULL THEN
v_kpi_preprod :='NO RECORDS EXIST IN Track_Records';
ELSE
v_kpi_preprod :=v_kpi_preprod||Track_Records_Row.KPI_NAME|| ' ' || Track_Records_Row.KPI_DEF_ID || chr(10) || chr(10);
END IF;
END LOOP;
SEND_MAIL_SMTP(
's@l.com', --Sender
's@l.com', --Recipient
'TEST_SERVER - KPIs NOT RUNNING', --Subject
'The Quartz job did not complete for below KPIs:' || chr(10) || v_kpi_preprod --Message
);
-------------------------------------------Send Mail from Verification--------------------------------------------------------------------------------------------
FOR Track_Records_Row IN (Select KPI_NAME,KPI_DEF_ID FROM RATOR_MONITORING.Track_Records@VERIFY)
LOOP
IF Track_Records_Row.KPI_NAME IS NULL THEN
v_kpi_verification :='NO RECORDS EXIST IN Track_Records';
ELSE
v_kpi_verification :=v_kpi_verification||Track_Records_Row.KPI_NAME|| ' ' || Track_Records_Row.KPI_DEF_ID || chr(10) || chr(10);
END IF;
END LOOP;
SEND_MAIL_SMTP(
's@l.com', --Sender
's@l.com', --Recipient
'VERIFICATION - KPIs NOT RUNNING', --Subject
'The Quartz job did not complete for below KPIs:' || chr(10) || v_kpi_verification --Message
);
---------------------------------------------Send Mail from Production-------------------------------------------------------------------------------------
FOR Track_Records_Row IN (Select KPI_NAME,KPI_DEF_ID FROM RATOR_MONITORING.Track_Records@PRODUCTION_RATOR_MONITORING)
LOOP
IF Track_Records_Row.KPI_NAME IS NULL THEN
v_kpi_production :='NO RECORDS EXIST IN Track_Records';
ELSE
v_kpi_production :=v_kpi_production||Track_Records_Row.KPI_NAME|| ' ' || Track_Records_Row.KPI_DEF_ID || chr(10) || chr(10);
END IF;
END LOOP;
SEND_MAIL_SMTP(
's@l.com', --Sender
's@l.com', --Recipient
'PRODUCTION - KPIs NOT RUNNING', --Subject
'The Quartz job did not complete for below KPIs:' || chr(10) || v_kpi_production --Message
);
---------------------------------------------------------------------------------------------------------------------------------------------------------------
EXCEPTION WHEN OTHERS THEN
EXECUTE IMMEDIATE 'INSERT INTO SEND_MAIL_SMTP_EXCEPTION values(''MAIL FAILED ERROR'',error_code|| '' '' || err_msg,SYSDATE)';
RAISE;
end execute_send_mail_smtp;