Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

emails not sending in regular interval from oracle

user12251389Feb 28 2017 — edited Feb 28 2017

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;

This post has been answered by BluShadow on Feb 28 2017
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 28 2017
Added on Feb 28 2017
23 comments
1,185 views