Forum Stats

  • 3,875,410 Users
  • 2,266,914 Discussions
  • 7,912,197 Comments

Discussions

What are the DB settings/configurations we need to look for if the mails are to be triggered?

What are the DB settings/configurations we need to look for if the mails are to be triggered from the DB end to the email servers and eventually into my inbox. My development team developed a procedure to send an email from the DB end. The procedure (PMNT_API_MAILER) is mentioned below.

create or replace PROCEDURE PMNT_API_MAILER(PMNT_DATE IN DATE)

AS

BEGIN

  DECLARE

    strMailHost   VARCHAR2(50);

    strMailPort   VARCHAR2(4);

    strHeader    VARCHAR2(4000);

    strMessage   VARCHAR2(4000);

    nPos      NUMBER;

    strFrom     VARCHAR2(100);

    strSendTo    VARCHAR2(1000);

    strCCTo     VARCHAR2(1000);

    strSubject   VARCHAR2(500);

    strSendTo_BAF  VARCHAR2(1000);

    ---------------------------------------------------------------------------------------------


    P_TEXT     clob;--VARCHAR2(4000);

    P_HTML     clob;

    V_SUM_LOAN_PRIN_ADV  NUMBER := 0;

    V_Count     Number;

    V_BusinessDate VARCHAR(20);

    v_error varchar2(1000);

    P_DATE     DATE := PMNT_DATE;

    v_cnt_cu    number := 0;

    attachments PKG_SEND_MAIL.ARRAY_ATTACHMENTS := PKG_SEND_MAIL.ARRAY_ATTACHMENTS();

    V_REJECT_DATA CLOB;

    V_SUCCESS_DATA CLOB;


    CURSOR REJ IS

      SELECT a.agreementid,a.downloadid,b.chequeid,b.BANK_REJ_REASON

      FROM lea_h2hpayment_hdr a, lea_h2hpayment_dtl b

      where a.downloadid = b.downloadid

      and a.download_date = P_DATE

      and a.pmnt_method = 'API'

      and a.status = 'A'

      and b.BANK_REJ_REASON is not null;


    CURSOR SUCC IS

      SELECT a.agreementid,a.downloadid,b.chequeid,b.BANK_TXN_REF_NO

      FROM lea_h2hpayment_hdr a, lea_h2hpayment_dtl b

      where a.downloadid = b.downloadid

      and a.download_date = P_DATE

      and a.pmnt_method = 'API'

      and a.status = 'A'

      and b.BANK_TXN_REF_NO is not null;



  BEGIN

     v_error :=null;

      

    BEGIN


     select CONF_VALUE

     into strMailHost

     from WS_PMNT_CONFIG

     WHERE CONF_KEY = 'MAIL_HOST'

     and status = 'A';


      select CONF_VALUE

     into strMailPort

     from WS_PMNT_CONFIG

     WHERE CONF_KEY = 'MAIL_PORT'

     and status = 'A';


    EXCEPTION WHEN OTHERS THEN

      v_error := 'Error while fetching data from WS_PMNT_CONFIG' || sqlerrm;

    END;


    BEGIN

     SELECT EMAIL

     INTO strFrom

     FROM APIPMT_MAIL_RECEPIENTS

     WHERE FROM_TO ='F'

     AND ROWNUM = 1;


   EXCEPTION WHEN OTHERS THEN

      v_error := 'Error while fetching sender mail from APIPMT_MAIL_RECEPIENTS' || sqlerrm;

      return;

    END;


    strHeader  := 'Date: ' || TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss') ;

    strSubject := 'Payment records processed via API on ' || to_char(P_DATE,'dd-MON-yyyy');


    P_HTML := '<HTML><BODY bgcolor="white">

          <TABLE BORDER=0>

          <font color="black" face="Tahoma" SIZE="2" point-size=100 weight=600>

          <TR><TD colspan="2">Dear Team,

          <BR>

          <BR>

          </TD></TR>

          <TR><TD colspan="2">Please find below information related to Payment Disbursement :<BR></TD></TR>

           <TR></TR>

           <BR>

          <TR><TD colspan="2">1. Success file<BR></TD></TR>

          <BR>

          <TR><TD colspan="2">2. Reject file<BR></TD></TR>

          </font></TABLE>';


    P_TEXT := '';


    P_TEXT := '<BR>';


    P_HTML := P_HTML || P_TEXT;


    P_TEXT := '';


    P_TEXT := '<TABLE BORDER=0>

          <font color="black" face="Tahoma" SIZE="2" point-size=100 weight=600>

          <TR><TD colspan="2">Thanks & Regards</TD></TR>

          <TR><TD colspan="2">IT Team</TD></TR></font></TABLE>';


    P_HTML := P_HTML || P_TEXT ;


    P_HTML := P_HTML || '</BODY></HTML>';



    SELECT LISTAgg(EMAIL,',') WITHIN GROUP (ORDER BY EMAIL)

    into strSendTo

    FROM APIPMT_MAIL_RECEPIENTS

    where status = 'A'

    and from_to = 'T';


    V_SUCCESS_DATA := 'LAN ID,CHQ ID,BATCH ID,UTR'||chr(13)||chr(10);

    FOR S IN SUCC LOOP

      V_SUCCESS_DATA := V_SUCCESS_DATA ||

               S.AGREEMENTID||','||S.CHEQUEID||','||S.DOWNLOADID||','||S.BANK_TXN_REF_NO||chr(13)||chr(10);

    END LOOP;

    V_REJECT_DATA := 'LAN ID,CHQ ID,BATCH ID,Reject Reason'||chr(13)||chr(10);

    FOR R IN REJ LOOP

      V_REJECT_DATA := V_REJECT_DATA ||

               R.AGREEMENTID||','||R.CHEQUEID||','||R.DOWNLOADID||','||R.BANK_REJ_REASON||chr(13)||chr(10);

    END LOOP;


    attachments.extend(2);


    attachments(1).attach_name := 'Reject_'||PMNT_DATE||'.csv';

    attachments(1).data_type := 'text/plain';

    attachments(1).attach_content := V_REJECT_DATA;


    attachments(2).attach_name := 'Success_'||PMNT_DATE||'.csv';

    attachments(2).data_type := 'text/plain';

    attachments(2).attach_content := V_SUCCESS_DATA;


    PKG_SEND_MAIL.SEND_MAIL ( strFrom,strSendTo,strSubject,P_HTML,strCCTo,attachments ,'text/html', strMailHost, strMailPort);


    trap('End SEND_MAIL');


  EXCEPTION


    WHEN UTL_SMTP.INVALID_OPERATION THEN

      v_error:='Invalid Operation in SMTP transaction.'||SQLERRM;


    WHEN UTL_SMTP.TRANSIENT_ERROR THEN

      v_error:='Temporary problems in sending email - try again later.'||SQLERRM;


    WHEN UTL_SMTP.PERMANENT_ERROR THEN

      v_error:='Errors in code for SMTP transaction.'||SQLERRM;


    WHEN OTHERS THEN

      v_error:=' Error Occured while sending Mail.'||SQLERRM;

       

       DBMS_OUTPUT.put_line(v_error);

  END;

  

END;


The above proc is dependent on another proc named PKG_SEND_MAIL

create or replace PACKAGE BODY PKG_SEND_MAIL AS


 PROCEDURE SEND_MAIL(

    v_from_name  VARCHAR2,

    v_to_name   VARCHAR2,

    v_subject   VARCHAR2,

    v_message_body VARCHAR2,

    v_cc_name   VARCHAR2 DEFAULT '',

    attachments  array_attachments DEFAULT NULL,

    v_message_type VARCHAR2 DEFAULT 'text/plain',

    v_smtp_ser  varchar2 ,

    v_smtp_port   varchar2

  ) AS

  v_smtp_server    VARCHAR2(20) := v_smtp_ser;

  n_smtp_server_port NUMBER    := v_smtp_port;

  conn        utl_smtp.connection;

  v_boundry      VARCHAR2(20) := 'SECBOUND';

  n_offset      NUMBER    := 0;

  n_amount      NUMBER    := 1900;

  v_final_to_name   CLOB     := '';

  v_final_cc_name   CLOB     := '';

  v_mail_address   VARCHAR2(100);

  v_error_msg     varchar2(4000 byte);


 BEGIN

  conn := utl_smtp.open_connection(v_smtp_server,n_smtp_server_port);

  utl_smtp.helo(conn, v_smtp_server);

  utl_smtp.mail(conn, v_from_name);


  -- Add all recipient

  v_final_to_name := v_to_name;

  v_final_to_name := replace(v_final_to_name, ' ');

  v_final_to_name := replace(v_final_to_name, ',', ';');

  LOOP

   n_offset := n_offset + 1;

   v_mail_address := regexp_substr(v_final_to_name, '[^;]+', 1, n_offset);

   EXIT WHEN v_mail_address IS NULL;

   utl_smtp.rcpt(conn, v_mail_address);

  END LOOP;


  -- Add all recipient

  v_final_cc_name := v_cc_name;

  v_final_cc_name := replace(v_final_cc_name, ' ');

  v_final_cc_name := replace(v_final_cc_name, ',', ';');

  n_offset := 0;

  LOOP

   n_offset := n_offset + 1;

   v_mail_address := regexp_substr(v_final_cc_name, '[^;]+', 1, n_offset);

   EXIT WHEN v_mail_address IS NULL;

   utl_smtp.rcpt(conn, v_mail_address);

  END LOOP;


 -- Open data

  utl_smtp.open_data(conn);


 -- Message info

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('To: ' || v_final_to_name || UTL_TCP.crlf));

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Cc: ' || v_final_cc_name || UTL_TCP.crlf));

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || UTL_TCP.crlf));

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('From: ' || v_from_name || UTL_TCP.crlf));

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Subject: ' || v_subject || UTL_TCP.crlf));

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('MIME-Version: 1.0' || UTL_TCP.crlf));

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: multipart/mixed; boundary="' || v_boundry || '"' || UTL_TCP.crlf || UTL_TCP.crlf));


 -- Message body

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--' || v_boundry || UTL_TCP.crlf));

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || v_message_type || UTL_TCP.crlf || UTL_TCP.crlf));

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(v_message_body || UTL_TCP.crlf));


 -- Attachment Part

  IF attachments IS NOT NULL

  THEN

    FOR i IN attachments.FIRST .. attachments.LAST

    LOOP

    -- Attach info

      utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--' || v_boundry || UTL_TCP.crlf));

      utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Type: ' || attachments(i).data_type

                || ' name="'|| attachments(i).attach_name || '"' || UTL_TCP.crlf));

      utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('Content-Disposition: attachment; filename="'

                || attachments(i).attach_name || '"' || UTL_TCP.crlf || UTL_TCP.crlf));


    -- Attach body

      n_offset := 1;

      WHILE n_offset < dbms_lob.getlength(attachments(i).attach_content)

      LOOP

        utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw(dbms_lob.substr(attachments(i).attach_content, n_amount, n_offset)));

        n_offset := n_offset + n_amount;

      END LOOP;

      utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('' || UTL_TCP.crlf));

    END LOOP;

  END IF;

 -- Last boundry

  utl_smtp.write_raw_data(conn, utl_raw.cast_to_raw('--' || v_boundry || '--' || UTL_TCP.crlf));


 -- Close data

  utl_smtp.close_data(conn);

  utl_smtp.quit(conn);

   

  EXCEPTION

  WHEN OTHERS THEN

  v_error_msg := 'Error in PKG_SEND_MAIL. '||sqlerrm;

  DBMS_OUTPUT.PUT_LINE(v_error_msg);


  END;


END;


They tested this on a localhost DB server. It worked fine. When it was handed over to me for a testing in a DB environment which is not a localhost. It does not work as expected. Getting the error "ORA-29278: SMTP transient error: 421 Service not available". What could have gone wrong? Can someone help me out here.

Answers