Emailing With Large Text

 While using conventional PLSQL procedure, the Oracle UTL_SMTP.Write_Data have input parameter as varchar2 -- which has a limit of size, so while emailing large content through single Write_data, is not possible. so the solution is to pass the data to email through chunks.

Below is the old Procedure. (Having trouble with size)


CREATE OR REPLACE PROCEDURE SUPPORT_PORTAL.send_mail_html_new (

   p_to            IN   VARCHAR2,

   p_from          IN   VARCHAR2,

   p_cc            IN   VARCHAR2,

   p_bcc           IN   VARCHAR2,

   p_subject       IN   VARCHAR2,

   p_text_msg      IN   VARCHAR2 DEFAULT NULL,

   p_attach_name   IN   VARCHAR2 DEFAULT NULL,

   p_attach_mime   IN   VARCHAR2 DEFAULT NULL,

   p_attach_blob   IN   BLOB DEFAULT NULL,

   p_smtp_host     IN   VARCHAR2,

   p_smtp_port     IN   NUMBER DEFAULT 25

)

AS

   l_mail_conn     UTL_SMTP.connection;

   l_boundary      VARCHAR2 (50)       := '----=*#abc1234321cba#*=';

   l_step          PLS_INTEGER         := 12000;

   vrfy_msg        UTL_SMTP.reply;

   success_value   NUMBER (4)          := 252;

   email_addr      VARCHAR2 (50)       := NULL;

   from_addr       VARCHAR2 (100)      := p_from;

   to_addr         VARCHAR2 (1000)     := p_to;

   cc_addr         VARCHAR2 (1000)     := p_cc;

   bcc_addr         VARCHAR2 (1000)     := p_bcc;

   to_mail         VARCHAR2 (1000)     := NULL;

   cc_mail         VARCHAR2 (1000)     := NULL;

   bcc_mail         VARCHAR2 (1000)     := NULL;

   no_addresses    NUMBER (4)          := 0;

   temp            VARCHAR2 (4000)     := NULL;

   temp2           VARCHAR2 (4000)     := NULL;

-- make sure you set a multiple of 3 not higher than 24573

BEGIN

   l_mail_conn := UTL_SMTP.open_connection (p_smtp_host, p_smtp_port);

   UTL_SMTP.helo (l_mail_conn, p_smtp_host);

   UTL_SMTP.mail (l_mail_conn, p_from);

   to_addr := to_addr || ';';

   cc_addr := cc_addr || ';';

   bcc_addr := bcc_addr || ';';

   to_mail := to_addr;

   cc_mail := cc_addr;

   bcc_mail := bcc_addr;


/*** Send mail to To: addresses ****/

   WHILE (INSTR (to_addr, ';') != 0)

   LOOP

      email_addr := SUBSTR (to_addr, 1, INSTR (to_addr, ';') - 1);

      vrfy_msg := UTL_SMTP.vrfy (l_mail_conn, email_addr);

      UTL_SMTP.rcpt (l_mail_conn, email_addr);  

--      IF vrfy_msg.code = success_value

--      THEN

--         UTL_SMTP.rcpt (l_mail_conn, email_addr);

--      END IF;


      to_addr := SUBSTR (to_addr, INSTR (to_addr, ';') + 1);

      email_addr := NULL;

   END LOOP;


/*** Send mail to CC: addresses ****/

   WHILE (INSTR (cc_addr, ';') != 0)

   LOOP

      email_addr := SUBSTR (cc_addr, 1, INSTR (cc_addr, ';') - 1);

      vrfy_msg := UTL_SMTP.vrfy (l_mail_conn, email_addr);

      UTL_SMTP.rcpt (l_mail_conn, email_addr);  

--      IF vrfy_msg.code = success_value

--      THEN

--         UTL_SMTP.rcpt (l_mail_conn, email_addr);

--      END IF;


      cc_addr := SUBSTR (cc_addr, INSTR (cc_addr, ';') + 1);

      email_addr := NULL;

   END LOOP;


/*** Send mail to BCC: addresses ****/

   WHILE (INSTR (bcc_addr, ';') != 0)

   LOOP

      email_addr := SUBSTR (bcc_addr, 1, INSTR (bcc_addr, ';') - 1);

      vrfy_msg := UTL_SMTP.vrfy (l_mail_conn, email_addr);

      UTL_SMTP.rcpt (l_mail_conn, email_addr);

--      IF vrfy_msg.code = success_value

--      THEN

--         UTL_SMTP.rcpt (l_mail_conn, email_addr);

--      END IF;


      bcc_addr := SUBSTR (bcc_addr, INSTR (bcc_addr, ';') + 1);

      email_addr := NULL;

   END LOOP;


   --UTL_SMTP.rcpt (l_mail_conn, p_to);

   --UTL_SMTP.rcpt (l_mail_conn, p_cc);

   --UTL_SMTP.rcpt (l_mail_conn, p_bcc);


--   IF p_bcc IS NOT NULL

--   THEN

--      UTL_SMTP.rcpt (l_mail_conn, bcc_addr);

--   END IF;


   UTL_SMTP.open_data (l_mail_conn);

   UTL_SMTP.write_data (l_mail_conn,

                           'Date: '

                        || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')

                        || UTL_TCP.crlf

                       );

   UTL_SMTP.write_data (l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);

   UTL_SMTP.write_data (l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);

   UTL_SMTP.write_data (l_mail_conn, 'Cc: ' || p_cc || UTL_TCP.crlf);

   --UTL_SMTP.write_data (l_mail_conn, 'Bcc: ' || p_bcc || UTL_TCP.crlf);

   UTL_SMTP.write_data (l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);

   UTL_SMTP.write_data (l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf);

   UTL_SMTP.write_data (l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);

   UTL_SMTP.write_data (l_mail_conn,

                           'Content-Type: multipart/mixed; boundary="'

                        || l_boundary

                        || '"'

                        || UTL_TCP.crlf

                        || UTL_TCP.crlf

                       );


   IF p_text_msg IS NOT NULL

   THEN

      UTL_SMTP.write_data (l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

      UTL_SMTP.write_data

                         (l_mail_conn,

                             'Content-Type: text/html; charset="iso-8859-1"'

                          || UTL_TCP.crlf

                          || UTL_TCP.crlf

                         );

      UTL_SMTP.write_data (l_mail_conn, p_text_msg);

      UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

   END IF;


   IF p_attach_name IS NOT NULL

   THEN

      UTL_SMTP.write_data (l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);

      UTL_SMTP.write_data (l_mail_conn,

                              'Content-Type: '

                           || p_attach_mime

                           || '; name="'

                           || p_attach_name

                           || '"'

                           || UTL_TCP.crlf

                          );

      UTL_SMTP.write_data (l_mail_conn,

                           'Content-Transfer-Encoding: base64' || UTL_TCP.crlf

                          );

      UTL_SMTP.write_data (l_mail_conn,

                              'Content-Disposition: attachment; filename="'

                           || p_attach_name

                           || '"'

                           || UTL_TCP.crlf

                           || UTL_TCP.crlf

                          );


      FOR i IN 0 .. TRUNC ((DBMS_LOB.getlength (p_attach_blob) - 1) / l_step)

      LOOP

         UTL_SMTP.write_data

            (l_mail_conn,

             UTL_RAW.cast_to_varchar2

                    (UTL_ENCODE.base64_encode (DBMS_LOB.SUBSTR (p_attach_blob,

                                                                l_step,

                                                                i * l_step + 1

                                                               )

                                              )

                    )

            );

      END LOOP;


      UTL_SMTP.write_data (l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

   END IF;


   UTL_SMTP.write_data (l_mail_conn,

                        '--' || l_boundary || '--' || UTL_TCP.crlf

                       );

   UTL_SMTP.close_data (l_mail_conn);

   UTL_SMTP.quit (l_mail_conn);

END;

/

------------x-x--x-------------------x--------------------x----------------------x-----------------------x

--Below is the New Procedure

CREATE OR REPLACE PROCEDURE SUPPORT_PORTAL.html_email (
    p_to              IN VARCHAR2,
    p_from            IN VARCHAR2,
    p_cc              IN VARCHAR2,
    p_subject         IN VARCHAR2,
    p_text            IN VARCHAR2 DEFAULT NULL,
    p_html            IN CLOB DEFAULT NULL,
    p_smtp_hostname   IN VARCHAR2,
    p_smtp_portnum    IN VARCHAR2)
IS
    l_boundary     VARCHAR2 (255) DEFAULT 'a1b2c3d4e3f2g1';
    l_connection   UTL_SMTP.connection;
    l_body_html    CLOB := EMPTY_CLOB;    --This LOB will be the email message
    l_offset       NUMBER;
    l_ammount      NUMBER;
    l_temp         VARCHAR2 (32767) DEFAULT NULL;
BEGIN
    l_connection :=
        UTL_SMTP.open_connection (p_smtp_hostname, p_smtp_portnum);
    UTL_SMTP.helo (l_connection, p_smtp_hostname);
    UTL_SMTP.mail (l_connection, p_from);
    UTL_SMTP.rcpt (l_connection, p_to);
    UTL_SMTP.rcpt (l_connection, p_cc);


    l_temp := l_temp || 'MIME-Version: 1.0' || CHR (13) || CHR (10);
    l_temp := l_temp || 'To: ' || p_to || CHR (13) || CHR (10);
    l_temp := l_temp || 'CC: ' || p_cc || CHR (13) || CHR (10);
    l_temp := l_temp || 'From: ' || p_from || CHR (13) || CHR (10);
    l_temp := l_temp || 'Subject: ' || p_subject || CHR (13) || CHR (10);
    l_temp := l_temp || 'Reply-To: ' || p_from || CHR (13) || CHR (10);
    l_temp :=
           l_temp
        || 'Content-Type: multipart/alternative; boundary='
        || CHR (34)
        || l_boundary
        || CHR (34)
        || CHR (13)
        || CHR (10);

    ----------------------------------------------------
    -- Write the headers
    DBMS_LOB.createtemporary (l_body_html, FALSE, 10);
    DBMS_LOB.write (l_body_html,
                    LENGTH (l_temp),
                    1,
                    l_temp);

    /* open if you want to send plain text email
        ----------------------------------------------------
        -- Write the text boundary
        l_offset := dbms_lob.getlength(l_body_html) + 1;
        l_temp   := '--' || l_boundary || chr(13)||chr(10);
        l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' ||
                      chr(13) || chr(10) || chr(13) || chr(10);
        dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

        ----------------------------------------------------
        -- Write the plain text portion of the email
        l_offset := dbms_lob.getlength(l_body_html) + 1;
        dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

      */
    ----------------------------------------------------
    -- Write the HTML boundary
    l_temp :=
           CHR (13)
        || CHR (10)
        || CHR (13)
        || CHR (10)
        || '--'
        || l_boundary
        || CHR (13)
        || CHR (10);
    l_temp :=
           l_temp
        || 'content-type: text/html; charset="iso-8859-1"'
        || CHR (13)
        || CHR (10)
        || CHR (13)
        || CHR (10);
    l_offset := DBMS_LOB.getlength (l_body_html) + 1;
    DBMS_LOB.write (l_body_html,
                    LENGTH (l_temp),
                    l_offset,
                    l_temp);

    ----------------------------------------------------
    -- Write the HTML portion of the message
    l_offset := DBMS_LOB.getlength (l_body_html) + 1;
    DBMS_LOB.APPEND (l_body_html, p_html);

    ----------------------------------------------------
    -- Write the final html boundary
    l_temp := CHR (13) || CHR (10) || '--' || l_boundary || '--' || CHR (13);
    l_offset := DBMS_LOB.getlength (l_body_html) + 1;
    DBMS_LOB.write (l_body_html,
                    LENGTH (l_temp),
                    l_offset,
                    l_temp);


    ----------------------------------------------------
    -- Send the email in 1900 byte chunks to UTL_SMTP
    l_offset := 1;
    l_ammount := 1900;
    UTL_SMTP.open_data (l_connection);

    WHILE l_offset < DBMS_LOB.getlength (l_body_html)
    LOOP
        UTL_SMTP.write_data (
            l_connection,
            DBMS_LOB.SUBSTR (l_body_html, l_ammount, l_offset));
        l_offset := l_offset + l_ammount;
        l_ammount :=
            LEAST (1900, DBMS_LOB.getlength (l_body_html) - l_ammount);
    END LOOP;

    UTL_SMTP.close_data (l_connection);
    UTL_SMTP.quit (l_connection);
    DBMS_LOB.freetemporary (l_body_html);
END;
/



Comments

Post a Comment

Popular posts from this blog

Oracle Database Listener Configuration

Image
Since Listener is s Program to establish connection with the Database from other applications. Hence to establish a connection it must have a Database Service to Bind If no Service is binded then it listener will be working but showing no service in the LOG There are two type of  Service Binding in TCP/IP 1. Dynamic Service Registration Through Database . 2. Static Service Registration Through Listenr.ora .   1.  Dynamic Service Registration In this type of Registration,  service name is mapped in database in a way that, we either map listener description in the LOCAL_LISTENER parameter of the database or just add alisas in this parameter. Here I have just added the alias LOCAL_ORCLP and its detail string in mentioned in TNSNAME.ORA . You could also directly mention the string into the LOCAL_LISTENER PARAMETER Anyway, once this all is setup then we have two options to initiate the service,     1. execute the command Alter System Register     2....
Read more

Oracle Weblogic Repository DB Users Password Reset

Image
When Repository DB Schema Password  expires, then WEblogic Admin Server Could not be started, hence it will prompt following error in the Weblogic Admin  Console. ------------------------------------------------------------------------------------------------------------------------ Caused by: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.6.4.v20160829-44060b6): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLException: ORA-28000: The account is locked. Error Code: 28000         at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:331)         at org.eclipse.persistence.exceptions.DatabaseException.sqlException(DatabaseException.java:326)         at org.eclipse.persistence.sessions.DefaultConnector.connect(DefaultConnector.java:138)         at org.eclipse.persistence.sessions.DatasourceLogin.connectToDataso...
Read more