Forum Stats

  • 3,782,151 Users
  • 2,254,605 Discussions
  • 7,879,944 Comments

Discussions

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

3782197
3782197 Member Posts: 6
edited Mar 13, 2019 3:31AM in SQL & PL/SQL

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;

3782197Ahmed Haroon

Answers

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,653 Red Diamond
    edited Feb 20, 2019 12:16AM

    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 for an example of how the e-mail needs to look like, when sending it to the server via UTL_SMTP.

  • 3782197
    3782197 Member Posts: 6
    edited Mar 12, 2019 3:28AM

    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;

  • Marwim
    Marwim Member Posts: 3,649 Gold Trophy
    edited Mar 12, 2019 3:56AM

    Don't try to reinvent the wheel. there are a lot of working examples out there.

    Like https://oracle-base.com/articles/misc/email-from-oracle-plsql

    Or to advertise mine: Oracle -> Send Mails from PL/SQL

    Marcus

    3782197Ahmed Haroon
  • 3782197
    3782197 Member Posts: 6
    edited Mar 13, 2019 3:31AM

    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;