পৃষ্ঠাসমূহ

এই ব্লগটি সন্ধান করুন

রবিবার, ২৪ সেপ্টেম্বর, ২০১৭

Send Mail Oracle Procedure

create or replace PROCEDURE "SEND_MAIL" (p_from       IN VARCHAR2,
                       p_to IN VARCHAR2,
                       p_cc IN VARCHAR2,
                       p_subject IN VARCHAR2,
                       p_message   IN VARCHAR2,
                       argUserId IN VARCHAR2 DEFAULT NULL,
                       p_returnReceipt IN CHAR DEFAULT 'N',
                       p_dispositionNotification IN CHAR DEFAULT 'N'
)
  as

  -- 6.1.0: Aspire #29648 - Added UserID, returnReceipt and dispositionNotification arguments to support acknowledgement mail in Messaging Service

     l_mailhost VarChar2(255);
     l_mail_conn utl_smtp.connection;
     crlf char(2) := chr(13)||chr(10);
     msg_data VarChar2(4000);
     p_recipient varChar2(4000);
     p_recipients varChar2(4000);
     l_EaiURL VarChar2(255);
     l_EaiUserid VarChar2(128);
     l_EaiPassword VarChar2(128);
     l_EaiEmailSaveFlag VarChar2(3);
     xml VarChar2(4000);
     result VarChar2(4000);
     l_returnRecipient VarChar2(255);  -- Aspire #29648 - Added l_returnRecipient variable to store the email notification type
  BEGIN
     SELECT  EmailHost,   EaiURL
       INTO l_mailhost, l_EaiURL
       FROM ValidSite;

     IF (l_mailhost > ' ') THEN
/* Setting the Email Header for Email Notification in msgdata */
        IF (p_dispositionNotification = 'Y') THEN
IF (p_returnReceipt = 'Y') THEN
l_returnRecipient := 'Return_Receipt_To: ' || p_from || crlf ||
                           'Disposition_Notification_To: ' || p_from || crlf;
ELSE
l_returnRecipient := 'Disposition_Notification_To: ' || p_from || crlf;
END IF;
ELSIF(p_returnReceipt = 'Y') THEN
l_returnRecipient := 'Return_Receipt_To: ' || p_from || crlf;
END IF;


        msg_data:= 'Date: ' || TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss' ) || crlf ||
                   'From:  <'|| p_from ||'>' || crlf ||
                   'Subject: '|| nvl( p_subject, '(no subject)' ) || crlf ||
                   'To: '|| p_to || crlf ||
                   'Cc: '|| p_cc || crlf ||
                   l_returnRecipient ||
                   '' || crlf || p_message;
        l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
        utl_smtp.helo(l_mail_conn, l_mailhost);
        utl_smtp.mail(l_mail_conn, p_from);
        p_recipients := p_to;
        WHILE ( p_recipients > ' ' ) LOOP
           IF (instr(p_recipients , ';') > 0) THEN
              p_recipient := substr(p_recipients , 1, instr(p_recipients , ';') - 1);
              p_recipients  := substr(p_recipients , instr(p_recipients , ';') + 1);
           ELSE
              p_recipient := p_recipients ;
              p_recipients  := ' ';
           END IF;
           utl_smtp.rcpt(l_mail_conn, p_recipient );
        END LOOP;
        p_recipients := p_cc;
        WHILE ( p_recipients > ' ' ) LOOP
           IF (instr(p_recipients , ';') > 0) THEN
              p_recipient := substr(p_recipients , 1, instr(p_recipients , ';') - 1);
              p_recipients  := substr(p_recipients , instr(p_recipients , ';') + 1);
           ELSE
              p_recipient := p_recipients ;
              p_recipients  := ' ';
           END IF;
           utl_smtp.rcpt(l_mail_conn, p_recipient );
        END LOOP;
        utl_smtp.data(l_mail_conn, msg_data);
        utl_smtp.quit(l_mail_conn);
     ELSE
/* changed the mail Sending throght EAI Since we implemented Sending mail thorugh Messaging Service */
           xml :=  '<Command>'
                || ' <Email>'
                || '  <To>' || p_to || '</To>'
                || '  <From>' || p_from || '</From>'
                || '  <CC>' || p_cc || '</CC>'
                || '  <Subject>' || p_subject || '</Subject>'
                || '  <Body>' || p_message || '</Body>'
                || '  <ReturnReceipt>' || p_returnReceipt || '</ReturnReceipt>'
                || '  <DispositionNotification>' || p_dispositionNotification || '</DispositionNotification>'
                || ' </Email>'
                || '</Command>';

        /* Insert the xml into Message table */
        INSERT INTO Message (MessageRSN, CommandXml, StatusCode, StampDate, StampUser)
        VALUES(MessageSeq.NextVal, xml, 0, SYSDATE, argUserId);
     END IF;
 end;

এর দ্বারা পোস্ট করা এই সময়ে

কোন মন্তব্য নেই:

একটি মন্তব্য পোস্ট করুন

এতে সদস্যতা: মন্তব্যগুলি পোস্ট করুন (Atom)