10 Replies Latest reply on May 21, 2018 9:02 PM by epipko

    Sending mail with UTL_SMTP (help needed ...)

    epipko

      Oracle 9.2.0.8 on Windows

      Need to be able to send an email with attachment(s) (if passed in)

       

      I found the following code that does it:

      PROCEDURE MAIL_OUT_P IS
      
      
      DECLARE
         v_From       VARCHAR2(80) := 'oracle@co.com';
         v_To         VARCHAR2(80) := 'me@co.com';
         v_Subject    VARCHAR2(80) := 'test subject';
         v_Mail_Host  VARCHAR2(100) := 'smtp.co.com';
         v_Mail_Conn  utl_smtp.Connection;
         crlf         VARCHAR2(2)  := chr(13)||chr(10);
         
         
      BEGIN
        v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
        UTL_SMTP.HELO(v_Mail_Conn, v_Mail_Host);
        UTL_SMTP.MAIL(v_Mail_Conn, v_From);
        UTL_SMTP.RCPT(v_Mail_Conn, v_Recipient);
      
      
        UTL_SMTP.DATA(v_Mail_Conn,
                      'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
                      'From: '   || v_From                    || crlf ||
                      'Subject: '|| v_Subject                 || crlf ||
                      'To: '     || v_Recipient               || crlf ||
      
      
                      -- Use MIME mail standard
                      'MIME-Version: 1.0'                     || crlf ||    
                      'Content-Type: multipart/mixed;'        || crlf ||
                      ' boundary="-----SECBOUND"'             || crlf || crlf ||
      
      
                      -- Message body
                      '-------SECBOUND'                       || crlf ||
                      'Content-Type: text/plain;'             || crlf ||
                      'Content-Transfer_Encoding: 7bit'       || crlf || crlf ||
                      'some message text'                     || crlf ||   
                      'more message text'                     || crlf || crlf ||
      
      
                      -- Content of attachment
                      '-------SECBOUND'                       || crlf ||
                      'Content-Type: text/plain;'             || crlf ||
                      ' name="excel.csv"'                     || crlf ||
                      'Content-Transfer_Encoding: 8bit'       || crlf ||
                      'Content-Disposition: attachment;'      || crlf ||
                      ' filename="excel.csv"'                 || crlf || crlf ||
                      'CSV,file,attachement'                  || crlf || crlf ||  
                      
      
      
                      '-------SECBOUND--'            -- End MIME mail
                  );
      
      
        UTL_SMTP.QUIT(v_mail_conn);
        
        
      EXCEPTION
        WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
          raise_application_error(-20000, 'Unable to send mail', TRUE);
      END;
      /
      

       

      I am not sure how to modify it to be able to pass parameters into it as:

      mail_out_p (
         v_From, 
         v_To,
         v_Subject,
          v_Msg,
         v_Attachment
      );
      
        • 2. Re: Sending mail with UTL_SMTP (help needed ...)
          John Thorton

          user8599970 wrote:

           

          Oracle 9.2.0.8 on Windows

          Need to be able to send an email with attachment(s) (if passed in)

           

          I found the following code that does it:

          1. PROCEDUREMAIL_OUT_PIS
          2. DECLARE
          3. v_FromVARCHAR2(80):='oracle@co.com';
          4. v_ToVARCHAR2(80):='me@co.com';
          5. v_SubjectVARCHAR2(80):='testsubject';
          6. v_Mail_HostVARCHAR2(100):='smtp.co.com';
          7. v_Mail_Connutl_smtp.Connection;
          8. crlfVARCHAR2(2):=chr(13)||chr(10);
          9. BEGIN
          10. v_Mail_Conn:=utl_smtp.Open_Connection(v_Mail_Host,25);
          11. UTL_SMTP.HELO(v_Mail_Conn,v_Mail_Host);
          12. UTL_SMTP.MAIL(v_Mail_Conn,v_From);
          13. UTL_SMTP.RCPT(v_Mail_Conn,v_Recipient);
          14. UTL_SMTP.DATA(v_Mail_Conn,
          15. 'Date:'||to_char(sysdate,'Dy,DDMonYYYYhh24:mi:ss')||crlf||
          16. 'From:'||v_From||crlf||
          17. 'Subject:'||v_Subject||crlf||
          18. 'To:'||v_Recipient||crlf||
          19. --UseMIMEmailstandard
          20. 'MIME-Version:1.0'||crlf||
          21. 'Content-Type:multipart/mixed;'||crlf||
          22. 'boundary="-----SECBOUND"'||crlf||crlf||
          23. --Messagebody
          24. '-------SECBOUND'||crlf||
          25. 'Content-Type:text/plain;'||crlf||
          26. 'Content-Transfer_Encoding:7bit'||crlf||crlf||
          27. 'somemessagetext'||crlf||
          28. 'moremessagetext'||crlf||crlf||
          29. --Contentofattachment
          30. '-------SECBOUND'||crlf||
          31. 'Content-Type:text/plain;'||crlf||
          32. 'name="excel.csv"'||crlf||
          33. 'Content-Transfer_Encoding:8bit'||crlf||
          34. 'Content-Disposition:attachment;'||crlf||
          35. 'filename="excel.csv"'||crlf||crlf||
          36. 'CSV,file,attachement'||crlf||crlf||
          37. '-------SECBOUND--'--EndMIMEmail
          38. );
          39. UTL_SMTP.QUIT(v_mail_conn);
          40. EXCEPTION
          41. WHENutl_smtp.Transient_ErrorORutl_smtp.Permanent_Errorthen
          42. raise_application_error(-20000,'Unabletosendmail',TRUE);
          43. END;
          44. /

           

          I am not sure how to modify it to be able to pass parameters into it as:

          1. mail_out_p(
          2. v_From,
          3. v_To,
          4. v_Subject,
          5. v_Msg,
          6. v_Attachment
          7. );

          is GOOGLE broken for you?

           

          LMGTFY

           

          Consider to upgrade this instance while you still can.

          • 3. Re: Sending mail with UTL_SMTP (help needed ...)
            EdStevens

            user8599970 wrote:

             

            Oracle 9.2.0.8 on Windows

            Woefully out of support.  Upgrading to a supported release should be your number one priority.

            • 4. Re: Sending mail with UTL_SMTP (help needed ...)
              cormaco

              I am not sure how to modify it to be able to pass parameters into it as:

              You mean you don't know how to write a procedure with parameters?

              https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg10pck.htm

              • 5. Re: Sending mail with UTL_SMTP (help needed ...)
                jaramill

                Are you familiar with the PL/SQL language?

                • 6. Re: Sending mail with UTL_SMTP (help needed ...)
                  epipko

                  Wow, I feel welcomed by all of you. Really not sure how you all get to the levels you you're in. Certainly not by providing same answers to everyone else.

                   

                  I know PL/SQL and how to write procedure with parameters.

                  I never asked if I have to upgrade or not.

                   

                  If you have nothing to add to the answer, don't bother.

                  • 7. Re: Sending mail with UTL_SMTP (help needed ...)
                    EdStevens

                    user8599970 wrote:

                     

                    Wow, I feel welcomed by all of you. Really not sure how you all get to the levels you you're in. Certainly not by providing same answers to everyone else.

                     

                    I know PL/SQL and how to write procedure with parameters.

                    Your earlier statement, "I am not sure how to modify it to be able to pass parameters into it as:" suggested otherwise.

                     

                     

                     

                    I never asked if I have to upgrade or not.

                    No, you didn't ask, but you are so far behind, so far out of support, it is worth noting, worth reminding.  As you should be reminding the decision maker. 

                    If you pull into a gas station and ask directions, and the attendant says "oh, by the way, your rear bumper is detached and dragging behind your car", is your response "I didn't come here to ask about my bumper"?

                     

                     

                     

                    If you have nothing to add to the answer, don't bother.

                    Often more questions have to be asked in order to elicit the information needed to offer good advice.  Often the OP makes contradictory statements that appear contradictory ("I know how to write procedures with parameters" vs. "I am not sure how to modify it to be able to pass parameters into it") and so need clarification.

                    • 8. Re: Sending mail with UTL_SMTP (help needed ...)
                      Marwim

                      There are some free packages that can already do what you need, e.g. Oracle -> Send Mails from PL/SQL (self advertise)

                      1 person found this helpful
                      • 9. Re: Sending mail with UTL_SMTP (help needed ...)
                        BluShadow

                        user8599970 wrote:

                        I know PL/SQL and how to write procedure with parameters.

                         

                         

                         

                        So what is the problem you are actually having then?

                         

                        You said you have code that works to send emails.

                        You said you didn't know how to change that to take parameters.

                        That suggests you don't know how to write procedures with parameters, and people have given you links to guide you in that respect.

                        Then you berate people for that, telling them you know how to write procedures with parameters.

                        In which case, you don't have an issue.  Add the parameters to the procedure that sends mail using your existing knowledge of writing procedures with parameters.

                         

                        If that's not the case, then you need to explain clearly what issue you are actually having, because at the minute you are making contradictory statements.

                         

                        People are more than willing to help, if you're willing to reciprocate.  Telling people not to bother responding can often result in just that... people not bothering to respond to you... which is no worries to them, and leaves you without any help.  Is that what you really want?