4 Replies Latest reply on Mar 13, 2019 7:31 AM by 3782197

    how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is  > 32000 bytes?

    3782197

      The base procedure mail_attach_binary(created below) to e-mail binary files

      from a directory location on the database (/home/alert)

       

       

       

       

      My Question is from here how can at

      how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is  > 32000 bytes?

       

       

       

       

      --SPEC

      PROCEDURE mail_attach_binary

          (recipients VARCHAR2,

           cc VARCHAR2 DEFAULT NULL,

           subject VARCHAR2,

           message VARCHAR2 DEFAULT NULL,

           att_filename VARCHAR2 DEFAULT NULL,

           att_file_loc  VARCHAR2);    

      END SPP_EMAIL;

       

       

      --BODY

      PROCEDURE mail_attach_binary

          (recipients     VARCHAR2,

           cc             VARCHAR2,

           subject        VARCHAR2,

           message        VARCHAR2,

           att_filename   VARCHAR2,

           att_file_loc   VARCHAR2) AS

      --file attachment paramaters    

          v_bfile         BFILE;

          v_clob          CLOB;

          destOffset      INTEGER:=1;

          srcOffset       INTEGER := 1;

          lang_context INTEGER := DBMS_LOB.default_lang_ctx;

          warning INTEGER;

         

      --    v_mime_type    VARCHAR2(30) := 'application/pdf';

       

       

        BEGIN

       

          setup_smtp_server;

      --Get the file to attach to the e-mail

          v_bfile := BFILENAME (att_file_loc, att_filename);

          DBMS_LOB.OPEN (v_bfile);

          DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);  

          DBMS_LOB.LOADCLOBFROMFILE(

              dest_lob => v_clob,

              src_bfile => v_bfile,

              amount => DBMS_LOB.GETLENGTH(v_bfile),

              dest_offset => destOffset,

              src_offset => srcOffset,

              bfile_csid => DBMS_LOB.default_csid,

              lang_context => lang_context,

              warning => warning);

          DBMS_LOB.CLOSE(v_bfile);    

         

          EXCEPTION WHEN

            INVALID_ARGUMENT THEN

            alert('EMAIL',1000,'Invalid argument passed to e-mail attachment from utl_mail.send_attach_varchar2');

        END mail_attach_binary;

        • 1. Re: how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is  > 32000 bytes?
          Billy~Verreynne

          Attach binary? That codes reads the contents of a file into a CLOB (text) - not a BLOB (binary).

           

          A binary mail attachment needs to be Base64 encoded. See Re: embeded image in email body in pl/sql for an example of how the e-mail needs to look like, when sending it to the server via UTL_SMTP.

          • 2. Re: how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is  > 32000 bytes?
            3782197

            The below code is attaching PDF but with 0KB (when I open pdf it has error) please advice

             

             

            PROCEDURE mail_attach_binary

                (recipients     VARCHAR2,

                 cc             VARCHAR2,

                 subject        VARCHAR2,

                 message        VARCHAR2,

                 att_filename   VARCHAR2,

                 att_file_loc   VARCHAR2) AS

            --file attachment paramaters    

                v_bfile         BFILE;

                --v_blob          BLOB;

                v_blob          BLOB;

                destOffset      INTEGER:=1;

                srcOffset       INTEGER := 1;

                lang_context    INTEGER := DBMS_LOB.default_lang_ctx;

                v_mail_conn     utl_smtp.connection; --T37250

                v_buffer_size   integer := 1200; --T37250

                v_smtp_server_port NUMBER := 25; --T37250

                v_raw           raw(57); --T37250

                v_length        NUMBER;

             

             

                warning INTEGER;

             

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

             

            BEGIN

              setup_smtp_server;

              v_mail_conn := UTL_SMTP.open_connection(v_smtp_server, v_smtp_server_port);

              UTL_SMTP.helo(v_mail_conn, v_smtp_server_port);

              UTL_SMTP.mail(v_mail_conn, v_sender_email);

              UTL_SMTP.rcpt(v_mail_conn, recipients);

             

             

              UTL_SMTP.open_data(v_mail_conn);

             

             

              UTL_SMTP.write_data(v_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);

              UTL_SMTP.write_data(v_mail_conn, 'To: ' || recipients || UTL_TCP.crlf);

              UTL_SMTP.write_data(v_mail_conn, 'From: ' || v_sender_email || UTL_TCP.crlf);

              UTL_SMTP.write_data(v_mail_conn, 'Subject: ' || subject || UTL_TCP.crlf);

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

              UTL_SMTP.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

             

              IF message IS NOT NULL THEN

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

                UTL_SMTP.write_data(v_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

             

             

                UTL_SMTP.write_data(v_mail_conn, message);

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

              END IF;

             

             

              IF att_filename IS NOT NULL THEN

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

                UTL_SMTP.write_data(v_mail_conn, 'Content-Type: ' || att_file_loc || '; name="' || att_filename || '"' || UTL_TCP.crlf);

                UTL_SMTP.write_data(v_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);

                UTL_SMTP.write_data(v_mail_conn, 'Content-Disposition: attachment; filename="' || att_filename || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

             

                -- Write attachment contents

             

              --Get the file to attach to the e-mail

             

                v_length := dbms_lob.getlength(v_blob);    

               

                <<while_loop>>

                while destOffset < v_length loop

                  dbms_lob.read( v_blob, v_buffer_size, destOffset, v_raw );

                  utl_smtp.write_raw_data( v_mail_conn, utl_encode.base64_encode(v_raw) );

                  utl_smtp.write_data( v_mail_conn, utl_tcp.crlf );

                  destOffset := destOffset + v_buffer_size;

                end loop while_loop;

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

              END IF;

             

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

              UTL_SMTP.close_data(v_mail_conn);

              UTL_SMTP.quit(v_mail_conn);

             

             

             

             

                EXCEPTION WHEN

                  INVALID_ARGUMENT THEN

                  jfpm_spp_alert('EMAIL',1000,'Invalid argument passed to e-mail attachment from utl_mail.send_attach_varchar2');

              END mail_attach_binary;

            • 4. Re: how can I allow this procedure to attach to email(pdf file in the directory), pdf file that is  > 32000 bytes?
              3782197

              I did manage to get the perfect output Thanks

               

              PROCEDURE mail_attach_binary (

                      recipients     VARCHAR2,

                      subject        VARCHAR2,

                      message        VARCHAR2,

                      att_filename   VARCHAR2,

                      att_file_loc   VARCHAR2

                  ) AS

              --file attachment paramaters    

               

               

                      v_bfile               BFILE;

                      --v_clob                CLOB; --T37250

                      destoffset            INTEGER := 1;

                      --srcoffset             INTEGER := 1; --T37250

                      --lang_context          INTEGER := dbms_lob.default_lang_ctx;--T37250

                      warning               INTEGER;

                      v_mail_conn           utl_smtp.connection; --T37250

                      v_smtp_server_port    NUMBER := 25; --T37250

                      v_length              INTEGER :=0; --T37250

                      v_raw                 RAW(57); --T37250

                      v_buffer_size         INTEGER := 57; --T37250

                      l_boundary            CONSTANT VARCHAR2(256) := '7D81B75CCC90D2974F7A1CBD'; --T37250

                      first_boundary        CONSTANT VARCHAR2(256) := '--'|| l_boundary|| utl_tcp.crlf; --T37250

                      last_boundary         CONSTANT VARCHAR2(256) := '--'|| l_boundary|| '--'|| utl_tcp.crlf; --T37250

                      multipart_mime_type   CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'|| l_boundary|| '"'; --T37250

               

               

                  BEGIN

                      setup_smtp_server;

                      v_mail_conn := utl_smtp.open_connection(v_smtp_server, v_smtp_server_port);

                      utl_smtp.helo(v_mail_conn, v_smtp_server_port);

                      utl_smtp.mail(v_mail_conn, v_sender_email);

                      utl_smtp.rcpt(v_mail_conn, recipients);

                     

                      utl_smtp.open_data(v_mail_conn);

                     

                      utl_smtp.write_data(v_mail_conn, 'Date: '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')|| utl_tcp.crlf);

                      utl_smtp.write_data(v_mail_conn, 'To: '|| recipients|| utl_tcp.crlf);

                      utl_smtp.write_data(v_mail_conn, 'From: '|| v_sender_email|| utl_tcp.crlf);

                      utl_smtp.write_data(v_mail_conn, 'Subject: '|| subject|| utl_tcp.crlf);

                      utl_smtp.write_data(v_mail_conn, 'This is email body' ||utl_tcp.CRLF);

                     

                      --Use MIME mail standard

                      utl_smtp.write_data(v_mail_conn, 'MIME-Version: 1.0' || utl_tcp.crlf);

                      utl_smtp.write_data(v_mail_conn, 'Content-Type: multipart/mixed; boundary="'|| l_boundary|| '"'|| utl_tcp.crlf);

                      utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);

                     

                      -- Write the plain text portion of the email in Message body

                      IF message IS NOT NULL THEN

                          utl_smtp.write_data(v_mail_conn, first_boundary);

                          utl_smtp.write_data(v_mail_conn, 'Content-Type: text/plain;'|| utl_tcp.crlf);

                          utl_smtp.write_data(v_mail_conn, ' charset=US-ASCII' || utl_tcp.crlf);

                          utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);

                           utl_smtp.write_data(v_mail_conn, message ||utl_tcp.crlf);

                      END IF;

                     

                      -- Content of attachment

                      utl_smtp.write_data(v_mail_conn, first_boundary);

                      utl_smtp.write_data(v_mail_conn, 'Content-Type'||':'||'application/pdf'|| utl_tcp.crlf);

                      utl_smtp.write_data (v_mail_conn, 'Content-Disposition: attachment; ' || utl_tcp.crlf);

                      utl_smtp.write_data (v_mail_conn, ' filename="' || att_filename || '"' || utl_tcp.crlf);

                      utl_smtp.write_data(v_mail_conn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf);

                      utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);

               

                     

                      --Get the file to attach to the e-mail

                      v_bfile := bfilename(att_file_loc, att_filename);

                      dbms_lob.fileopen(v_bfile, dbms_lob.file_readonly);

                     

                  -- Send the email byte chunks to UTL_SMTP

                     

                       -- Get the size of the file to be attached

                      v_length := dbms_lob.getlength(v_bfile);

                      << while_loop >>

                      WHILE destoffset < v_length LOOP

                          dbms_lob.read(v_bfile, v_buffer_size, destoffset, v_raw);   

                          utl_smtp.write_raw_data(v_mail_conn, utl_encode.base64_encode(v_raw));

                          destoffset := destoffset + v_buffer_size;

                      END LOOP;

               

               

                      utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);

                      utl_smtp.write_data(v_mail_conn,last_boundary);

                      utl_smtp.write_data(v_mail_conn, utl_tcp.crlf);

                     

                      --close SMTP connection and LOB file

                      DBMS_LOB.filecloseall;

                      dbms_lob.fileclose(v_bfile);

                      utl_smtp.close_data(v_mail_conn);

                      utl_smtp.quit(v_mail_conn);

                  EXCEPTION

                      WHEN invalid_argument THEN

                          jfpm_spp_alert('EMAIL', 1000, 'Invalid argument passed to e-mail attachment from utl_mail.send_attach_varchar2');

                  END mail_attach_binary;