12 Replies Latest reply on Sep 20, 2013 5:15 PM by thomaso

    Sending PDF attachment thru PL-SQL (sends garbage)

    Panamanian

      Hi guys.

       

      I copy a procedure I found in the web (to attach external files), but when I send it and the user opens the email all I see is things like:

       

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

      This is email body

      MIME-Version: 1.0

      Content-Type: multipart/mixed; boundary="-----7D81B75CCC90D2974F7A1CBD"

       

      -------7D81B75CCC90D2974F7A1CBD

      Content-Type: application/pdf

      Content-Disposition: attachment; filename="adm022.pdf"

      Content-Transfer-Encoding: base64

       

      JVBERi0xLjQKJcfsj6IKNSAwIG9iago8PC9MZW5ndGggNiAwIFIvRmlsdGVyIC9G

      bGF0ZURlY29k

      ZT4+CnN0cmVhbQp4nM2de3Mcx3XFCwAJ4lUgIYMwJcXikpQsrGKs+j09/k+2bEcu

      ppLYdFIVw5VS

      SX4WRVuyXfm8+Sbpvud29xA7O4td3tihitSpnTu7v73dM91zT8/s1zO10Gam8n9F

      fPHV0dez4Om1

      mXEm/e0WfR9mwSja+vHPutmnfzz6tyM1+0n6+9ujr4/iwuY/tMtQf/HV7Acv0g5x

      FhbpHV785kil

      t4rK46P0zPVx0ZlZZ/Uivfbiq6NfXv7r/Cqk10wXLv9nbhe6D7G//G1Vv59fmYXv

      THf5qr72eVWz

      uV4EZUK4/P78yuuwCC5e6vmV7qNdOHv5ZY389dx7n2F+9eKnR2bhlAv9TKuFTRhf

      Jowfz8PCdqE3

      KbLs88X8KvF3MYVe/u72n142N3U1d4uuc8ZfqvpaP7LV1NfUyLvY4Ve48gunlXcz

      oxZdwHf42fzK

      LrzXuht8iT9V9ceqvqmf+Jf6FX49/bV+2N77J3O1CMal4Mv/qjsNPrux+5HvE4bf

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

      and so on...


      Is it something wrong I'm doing?

      by the way the code is:


      Blue color is the code I'm using.

      ====================

      declare

          p_recipient VARCHAR2(500) := 'sender@domain.com';

          p_subject VARCHAR2(500) := 'Test attachement';

          p_message VARCHAR2(500) := 'This is email body';

          p_filename VARCHAR2(500) := 'adm022.pdf';

          v_msg VARCHAR2(32000);

          src_file bfile;

          i integer := 1;

          v_raw raw(57);

          v_length integer := 0;

          v_buffer_size integer := 57;

          v_mailconn utl_smtp.connection;

          gc_crlf VARCHAR2(4) := chr(13) || chr(10);

          gc_lf VARCHAR2(4) := chr(10);

          gc_mailhost varchar2(30) := '10.20.1.139';

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

          FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;

          LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||

          utl_tcp.CRLF;

          -- A MIME type that denotes multi-part email (MIME) messages.

          MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||

          BOUNDARY || '"';

      BEGIN

          v_mailconn := utl_smtp.open_connection(gc_mailhost,25);

          utl_smtp.helo(v_mailconn,gc_mailhost);

          utl_smtp.mail(v_mailconn,'sender@domain.com');

          utl_smtp.rcpt(v_mailconn,p_recipient);

          utl_smtp.open_data (v_mailconn);

          utl_smtp.write_data (v_mailconn, 'From: ' || p_recipient || utl_tcp.crlf );

          utl_smtp.write_data (v_mailconn, 'To: ' || p_recipient || utl_tcp.crlf );

          utl_smtp.write_data (v_mailconn, 'Subject: ' || p_subject || utl_tcp.crlf );

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

          utl_smtp.write_data (v_mailconn, 'MIME-Version: 1.0' || utl_tcp.CRLF);

          utl_smtp.write_data (v_mailconn, 'Content-Type: multipart/mixed; boundary="'|| BOUNDARY || '"' || utl_tcp.CRLF);

          utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

          utl_smtp.write_data (v_mailconn, FIRST_BOUNDARY);

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

          utl_smtp.write_data (v_mailconn, 'Content-Disposition: attachment; filename="' || p_filename || '"' || utl_tcp.crlf);

          --utl_smtp.write_data (v_mailconn, 'Content-Disposition: attachment; filename="ADM022.pdf"' || utl_tcp.crlf);

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

          utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

          src_file := bfilename('EXAMPLEDIR', p_filename);

          dbms_lob.fileopen(src_file, dbms_lob.file_readonly);

          v_length := dbms_lob.getlength(src_file);

          while i < v_length loop

             dbms_lob.read( src_file, v_buffer_size, i, v_raw );

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

             utl_smtp.write_data( v_mailconn, utl_tcp.crlf );

             i := i + v_buffer_size;

          end loop while_loop;

          utl_smtp.write_data (v_mailconn, LAST_BOUNDARY);

          utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

          dbms_lob.fileclose(src_file);

          utl_smtp.close_data (v_mailconn);

          utl_smtp.quit(v_mailconn);

      EXCEPTION

         WHEN OTHERS THEN

            utl_smtp.quit(v_mailconn);

            dbms_output.put_line(sqlcode||sqlerrm);

      END;

      =================

       

      The file to attach "adm022.pdf" is in the server and I created an alias directory named: EXAMPLEDIR

       

      I really appreciate your help.

       

      Gracias.



        • 1. Re: Sending PDF attachment thru PL-SQL (sends garbage)
          thomaso

          Try this small change:

               ...

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

                 -- remove crlf here: utl_smtp.write_data( v_mailconn, utl_tcp.crlf );

                 i := i + v_buffer_size;

              end loop while_loop;

              -- add crlf here..

              utl_smtp.write_data( v_mailconn, utl_tcp.crlf );

              utl_smtp.write_data (v_mailconn, LAST_BOUNDARY);

          ...

          Thomas

          • 2. Re: Sending PDF attachment thru PL-SQL (sends garbage)
            Panamanian

            I replace it, as you mentioned, with this "last part of the procedure":

             

                while i < v_length loop

                   dbms_lob.read( src_file, v_buffer_size, i, v_raw );

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

                   --utl_smtp.write_data( v_mailconn, utl_tcp.crlf);

                   i := i + v_buffer_size;

                end loop while_loop;

                utl_smtp.write_data( v_mailconn, utl_tcp.crlf);

                utl_smtp.write_data (v_mailconn, LAST_BOUNDARY);

                utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

                dbms_lob.fileclose(src_file);

                utl_smtp.close_data (v_mailconn);

                utl_smtp.quit(v_mailconn);

             

             

             

            Is that what you meant?

             

            Thanks

            • 4. Re: Sending PDF attachment thru PL-SQL (sends garbage)
              Panamanian

              No, I'm afraid I got the same "garbage".

               

              Why is it happening? and what else could I try?

               

              Also, thanks a lot for your previous answer.


              Gracias

              • 5. Re: Sending PDF attachment thru PL-SQL (sends garbage)
                thomaso

                I'm comparing your code with similar working procedure.

                I can see one more difference:

                 

                Try adding this:

                ...

                utl_smtp.write_data (v_mailconn, LAST_BOUNDARY);

                utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

                utl_smtp.write_data (v_mailconn, utl_tcp.crlf ||'.'|| utl_tcp.crlf||utl_tcp.crlf);

                dbms_lob.fileclose(src_file);

                utl_smtp.close_data (v_mailconn);

                utl_smtp.quit(v_mailconn);

                ...

                 

                Thomas

                • 6. Re: Sending PDF attachment thru PL-SQL (sends garbage)
                  Panamanian

                  the same garbage.   

                  it failed again.

                   

                  why is it happening?

                  • 7. Re: Sending PDF attachment thru PL-SQL (sends garbage)
                    thomaso

                    Ok, I've go it working.

                    You have to separate email body from attachment.

                    Change your code:

                     

                    ...
                        v_mailconn := utl_smtp.open_connection(gc_mailhost,25);
                        utl_smtp.helo(v_mailconn,gc_mailhost);
                        utl_smtp.mail(v_mailconn,'sender@domain.com');
                        utl_smtp.rcpt(v_mailconn,p_recipient);
                        utl_smtp.open_data (v_mailconn);
                        utl_smtp.write_data (v_mailconn, 'From: ' || p_recipient || utl_tcp.crlf );
                        utl_smtp.write_data (v_mailconn, 'To: ' || p_recipient || utl_tcp.crlf );
                        utl_smtp.write_data (v_mailconn, 'Subject: ' || p_subject || utl_tcp.crlf );


                        utl_smtp.write_data (v_mailconn, 'MIME-Version: 1.0' || utl_tcp.CRLF);
                        utl_smtp.write_data (v_mailconn, 'Content-Type: multipart/mixed; boundary="'|| BOUNDARY || '"' || utl_tcp.CRLF);

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

                        --

                        -- New boundary block for text of the email

                        --
                        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

                        utl_smtp.write_data (v_mailconn, FIRST_BOUNDARY);
                        utl_smtp.write_data (v_mailconn, 'Content-Type: text/plain;'|| utl_tcp.CRLF);
                        utl_smtp.write_data (v_mailconn, ' charset=US-ASCII'|| utl_tcp.CRLF);
                        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );   
                        utl_smtp.write_data (v_mailconn, 'This is email body' || utl_tcp.CRLF);   

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

                        --

                        -- and now boundary block for attachment

                        --

                        utl_smtp.write_data (v_mailconn, FIRST_BOUNDARY);
                        utl_smtp.write_data (v_mailconn, 'Content-Type' || ': ' || 'application/pdf' || utl_tcp.CRLF);
                        utl_smtp.write_data (v_mailconn, 'Content-Disposition: attachment; ' || utl_tcp.crlf);
                        utl_smtp.write_data (v_mailconn, ' filename="' || p_filename || '"' || utl_tcp.crlf);
                        utl_smtp.write_data (v_mailconn, 'Content-Transfer-Encoding: base64' || utl_tcp.crlf );
                        utl_smtp.write_data (v_mailconn, utl_tcp.crlf );
                    ...

                     

                    It worked on my test.

                    HTH

                    Thomas

                    • 8. Re: Sending PDF attachment thru PL-SQL (sends garbage)
                      Panamanian

                      Same "garbage".

                       

                      With some tweaking the most i did get was as follow:

                       

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

                      This is email body

                       

                      ADM022.pdfADM022.pdf
                      0 K   View   Download 

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

                       

                      It seemed to worked but size is zero, therefore maybe something is incomplete, I'm like blind working.

                       

                      What did you do so it worked? Also, did your attachment had size?

                      Please post your complete code (from the beginning).

                       

                      Gracias.

                      • 9. Re: Sending PDF attachment thru PL-SQL (sends garbage)
                        thomaso

                        I created this procedure as a part of my email testing package.

                         

                        PROCEDURE TestEmail AS

                            p_recipient VARCHAR2(500) := '<add your email address>';
                            p_subject VARCHAR2(500) := 'Test attachement';
                            p_message VARCHAR2(500) := 'This is email body';
                            p_filename VARCHAR2(500) := 'test01.pdf';
                            --
                            -- Make sure directory object is valid and your PDF file is in it.
                            --
                            p_directory VARCHAR2(100) := '<add your existing oracle directory name';
                           
                            v_msg VARCHAR2(32000);
                            src_file bfile;
                            i integer := 1;
                            v_raw raw(57);
                            v_length integer := 0;
                            v_buffer_size integer := 57;
                            v_mailconn utl_smtp.connection;
                            gc_crlf VARCHAR2(4) := chr(13) || chr(10);
                            gc_lf VARCHAR2(4) := chr(10);
                            gc_mailhost varchar2(30) := '192.168.200.1' ;
                            BOUNDARY CONSTANT VARCHAR2(256) := '7D81B75CCC90D2974F7A1CBD';
                            FIRST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || utl_tcp.CRLF;
                            LAST_BOUNDARY CONSTANT VARCHAR2(256) := '--' || BOUNDARY || '--' ||
                            utl_tcp.CRLF;
                            MULTIPART_MIME_TYPE CONSTANT VARCHAR2(256) := 'multipart/mixed; boundary="'||BOUNDARY ||'"';

                        BEGIN

                            v_mailconn := utl_smtp.open_connection(gc_mailhost,25);
                            utl_smtp.helo(v_mailconn,gc_mailhost);
                            utl_smtp.mail(v_mailconn,'sender@domain.com');
                            utl_smtp.rcpt(v_mailconn,p_recipient);
                            utl_smtp.open_data (v_mailconn);
                            utl_smtp.write_data (v_mailconn, 'From: ' || p_recipient || utl_tcp.crlf );
                            utl_smtp.write_data (v_mailconn, 'To: ' || p_recipient || utl_tcp.crlf );
                            utl_smtp.write_data (v_mailconn, 'Subject: ' || p_subject || utl_tcp.crlf );


                            utl_smtp.write_data (v_mailconn, 'MIME-Version: 1.0' || utl_tcp.CRLF);
                            utl_smtp.write_data (v_mailconn, 'Content-Type: multipart/mixed; boundary="'|| BOUNDARY || '"' || utl_tcp.CRLF);
                            utl_smtp.write_data (v_mailconn, utl_tcp.crlf );

                            utl_smtp.write_data (v_mailconn, FIRST_BOUNDARY);
                            utl_smtp.write_data (v_mailconn, 'Content-Type: text/plain;'|| utl_tcp.CRLF);
                            utl_smtp.write_data (v_mailconn, ' charset=US-ASCII'|| utl_tcp.CRLF);
                            utl_smtp.write_data (v_mailconn, utl_tcp.crlf );   
                            utl_smtp.write_data (v_mailconn, 'This is email body' || utl_tcp.CRLF);   


                            utl_smtp.write_data (v_mailconn, FIRST_BOUNDARY);
                            utl_smtp.write_data (v_mailconn, 'Content-Type' || ': ' || 'application/pdf' || utl_tcp.crlf);
                            utl_smtp.write_data (v_mailconn, 'Content-Disposition: attachment; ' || utl_tcp.crlf);
                            utl_smtp.write_data (v_mailconn, ' filename="' || p_filename || '"' || utl_tcp.crlf);


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

                            src_file := bfilename(p_directory, p_filename);
                            dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
                            v_length := dbms_lob.getlength(src_file);

                            while i < v_length loop

                               dbms_lob.read( src_file, v_buffer_size, i, v_raw );
                               utl_smtp.write_raw_data( v_mailconn, utl_encode.base64_encode(v_raw) );
                               i := i + v_buffer_size;

                            end loop;

                            utl_smtp.write_data( v_mailconn, utl_tcp.crlf );
                            utl_smtp.write_data (v_mailconn, LAST_BOUNDARY);
                            utl_smtp.write_data (v_mailconn, utl_tcp.crlf );
                            dbms_lob.fileclose(src_file);
                            utl_smtp.close_data (v_mailconn);
                            utl_smtp.quit(v_mailconn);

                        EXCEPTION

                           WHEN OTHERS THEN
                              utl_smtp.quit(v_mailconn);
                          RAISE;

                        END;

                         

                         

                        HTH

                        T.

                        • 10. Re: Sending PDF attachment thru PL-SQL (sends garbage)
                          Panamanian

                          Wow, Thomaso, you are a "monster" (a person with plenty of knowledge).

                           

                          Finaly it worked as expected. Thanks a lot my friend.

                           

                          Cheers.

                          • 11. Re: Sending PDF attachment thru PL-SQL (sends garbage)
                            Panamanian

                            I have another question. (dont worry, it's working good now). However...

                            If I run it from an implicit store procedure:

                             

                            declare

                            ...

                             

                            ...

                            end;

                             

                            It works perfect, but if I create a store procedure "create or replace procedure ......"

                            it sends me an "

                            ORA-24247: network access denied by access control list (ACL)"

                             

                            why or what's the difference? in both cases I'm running them with the same oracle user.

                             

                            Are store procedures ran by another user? "SYS" or "SYSTEM" or something internally?

                             

                            Please clarify me.

                             

                            And a lot of thanks again.

                            • 12. Re: Sending PDF attachment thru PL-SQL (sends garbage)
                              thomaso

                              In Oracle 11g and later you have to set up ACLs to let pl/sql programs access external urls.

                              That includes SMPT servers.

                               

                              ACLs are "granting" access

                              to a specific ip address or url or a group of addresses or a group of urls

                              for a specific user (or procedure owner).

                               

                              So between Your run of the anonymous block

                              BEGIN

                              ...

                              END;

                              and Your stored procedure something has changed,

                              either the user or the address of the SMTP server.

                               

                              DBMS_NETWORK_ACL_ADMIN

                               

                              HTH

                              T.