5 Replies Latest reply: Apr 24, 2012 12:24 AM by Billy~Verreynne RSS

    Email from PL/SQL

    Postie
      Hi

      I am using PL/SQL to send email with an attachment.. around 100k in size

      But I am having problems using UTL_TCP.. getting TCP Socket (KGAS) timeout errors?

      If I use UTL_MAIL.. I will be limited to file size and need to send more emails/files..

      Is there another better way to send emails in PL/SQL

      Dean
        • 1. Re: Email from PL/SQL
          JustinCave
          You can use the older UTL_SMTP package.

          You should be able to use the UTL_TCP package. Without seeing your code and the actual error stack, it's obviously a bit difficult to figure out where the bugs in your code are.

          Justin
          • 2. Re: Email from PL/SQL
            Postie
            This is my code
            Declare
               msg_from      VARCHAR2 (300)     := 'dca@asdasd.com';
               msg_to        VARCHAR2 (300)     := 'dca@asdasd.com';
               msg_subject   VARCHAR2 (300)     := 'PPm Upload file';
               msg_text      VARCHAR2 (300) := 'Attached is PPM Upload';
               v_output1     VARCHAR2 (300);
               c             UTL_TCP.connection;
               rc            INTEGER;
               crlf          VARCHAR2 (2)       := UTL_TCP.crlf;
               mesg          VARCHAR2 (32767);
            
               CURSOR dataqry
               IS
                  SELECT   * from
                           fdp.fdp_basehours h;
            BEGIN
               c := UTL_TCP.open_connection ('mailgate', 25);
               rc := UTL_TCP.write_line (c, 'HELO mailgate');
               rc := utl_tcp.write_line (c, 'EHLO mailgate');
               rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || msg_from);
               rc := UTL_TCP.write_line (c, 'RCPT TO: ' || msg_to);
               rc := UTL_TCP.write_line (c, 'DATA'); 
               rc :=
                  UTL_TCP.write_line (c,
                                      'Date: '
                                      || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
                                     );
               rc :=
                   UTL_TCP.write_line (c, 'From: ' || msg_from || ' <' || msg_from || '>');
               rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');
               rc := UTL_TCP.write_line (c, 'To: ' || msg_to || ' <' || msg_to || '>');
               rc := UTL_TCP.write_line (c, 'Subject: ' || msg_subject);
               rc := UTL_TCP.write_line (c, 'Content-Type: multipart/mixed;');
               rc := UTL_TCP.write_line (c, ' boundary="-----SECBOUND"');
               rc := UTL_TCP.write_line (c,null);
               rc := UTL_TCP.write_line (c, '-------SECBOUND');
               rc := UTL_TCP.write_line (c, 'Content-Type: text/plain');
               rc := UTL_TCP.write_line (c, 'Content-Transfer-Encoding: 7bit');
               rc := UTL_TCP.write_line (c,null);
               rc := UTL_TCP.write_line (c, msg_text);         ----- TEXT OF EMAIL MESSAGE
               rc := UTL_TCP.write_line (c,null);
               rc := UTL_TCP.write_line (c, '-------SECBOUND');
               rc := UTL_TCP.write_line (c, 'Content-Type: text/plain;');
               rc := UTL_TCP.write_line (c, ' name="Test.txt"');
               rc := UTL_TCP.write_line (c, 'Content-Transfer_Encoding: 8bit');
               rc := UTL_TCP.write_line (c, 'Content-Disposition: attachment;');
               rc := UTL_TCP.write_line (c, ' filename="Test.txt"');
               rc := UTL_TCP.write_line (c,null);
               v_output1 :=
                     '"Batch# Api","Employee#","Job#","Workdate","Duty Type","Paycode","Hour Minute","Unit","One Off Flag","Ts Reason","Award","Classification","Step","Rate","Confirm Date","Gl Override","Userid","Amt","Manual Pay Flag","Payrun"'
                  || crlf;
               rc := UTL_TCP.write_line (c, v_output1);
            
               FOR z IN dataqry
               LOOP
                  v_output1 :=
                        ','
                     || z.employee_id
                     || ','
                     || z.jobnum
                     || ','
                     || z.workdate
                     || ',,'
                     || z.paycode
                     || ',,'
                     || z.unit
                     || ',,,,,,,,,,,,';
                  rc := UTL_TCP.write_line (c, v_output1);
               END LOOP;
            
               rc := UTL_TCP.write_line (c, '-------SECBOUND--');
               rc := UTL_TCP.write_line (c,null);
               rc := UTL_TCP.write_line (c, '.');             ----- EMAIL MESSAGE BODY END
               rc := UTL_TCP.write_line (c, 'QUIT');          ----- ENDS EMAIL TRANSACTION
               UTL_TCP.close_connection (c);              ----- CLOSE SMTP PORT CONNECTION
            END;
            • 3. Re: Email from PL/SQL
              JustinCave
              And your error stack?

              Justin
              • 4. Re: Email from PL/SQL
                Postie
                I have isolated where problem is this will work
                  rc := utl_tcp.write_line(c, '-------SECBOUND');
                  rc := utl_tcp.write_line(c, 'Content-Type: application/octet-stream;');       ----- 2ND BODY PART.
                  rc := utl_tcp.write_line(c, ' name="Test.txt"');
                  rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
                  rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
                  rc := utl_tcp.write_line(c, ' filename="Test.txt"');             ----- SUGGESTED FILE NAME FOR ATTACHMENT
                  rc := utl_tcp.write_line(c);
                
                  rc := utl_tcp.write_line(c, 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT WILL BE IN A TEXT FILE ATTACHED TO THE EMAIL.');
                
                   v_output1 :=
                         '"Batch# Api","Employee#","Job#","Workdate","Duty Type","Paycode","Hour Minute","Unit","One Off Flag","Ts Reason","Award","Classification","Step","Rate","Confirm Date","Gl Override","Userid","Amt","Manual Pay Flag","Payrun"';
                   rc := UTL_TCP.write_line (c, v_output1);
                
                   FOR z IN dataqry
                   LOOP
                      v_output1 :=
                            ','
                         || z.employee_id
                         || ','
                         || z.jobnum
                         || ','
                         || z.workdate
                         || ',,'
                         || z.paycode
                         || ',,'
                         || z.unit
                         || ',,,,,,,,,,,,';
                      rc := UTL_TCP.write_line (c, v_output1);
                   END LOOP;
                this won't..
                  rc := utl_tcp.write_line(c, '-------SECBOUND');
                  rc := utl_tcp.write_line(c, 'Content-Type: application/octet-stream;');       ----- 2ND BODY PART.
                  rc := utl_tcp.write_line(c, ' name="Test.txt"');
                  rc := utl_tcp.write_line(c, 'Content-Transfer_Encoding: 8bit');
                  rc := utl_tcp.write_line(c, 'Content-Disposition: attachment;'); ----- INDICATES THAT THIS IS AN ATTACHMENT
                  rc := utl_tcp.write_line(c, ' filename="Test.txt"');             ----- SUGGESTED FILE NAME FOR ATTACHMENT
                  rc := utl_tcp.write_line(c);
                   v_output1 :=
                         '"Batch# Api","Employee#","Job#","Workdate","Duty Type","Paycode","Hour Minute","Unit","One Off Flag","Ts Reason","Award","Classification","Step","Rate","Confirm Date","Gl Override","Userid","Amt","Manual Pay Flag","Payrun"';
                   rc := UTL_TCP.write_line (c, v_output1);
                
                   FOR z IN dataqry
                   LOOP
                      v_output1 :=
                            ','
                         || z.employee_id
                         || ','
                         || z.jobnum
                         || ','
                         || z.workdate
                         || ',,'
                         || z.paycode
                         || ',,'
                         || z.unit
                         || ',,,,,,,,,,,,';
                      rc := UTL_TCP.write_line (c, v_output1);
                   END LOOP;
                the only ommision/change
                  rc := utl_tcp.write_line(c, 'THIS IS THE TEXT OF THE ATTACHMENT FILE. THIS TEXT WILL BE IN A TEXT FILE ATTACHED TO THE EMAIL.');
                • 5. Re: Email from PL/SQL
                  Billy~Verreynne
                  Postie wrote:

                  I am using PL/SQL to send email with an attachment.. around 100k in size
                  But I am having problems using UTL_TCP.. getting TCP Socket (KGAS) timeout errors?
                  That points to a mail server or network problem - assuming your client side correctly participates in the conversation. And the latter I suspect is the cause of the errors you are getting.
                  If I use UTL_MAIL.. I will be limited to file size and need to send more emails/files..
                  Understandable. UTL_MAIL is a shoddy implementation and shoddily written.
                  Is there another better way to send emails in PL/SQL
                  UTL_MAIL uses UTL_SMTP. UTL_SMTP uses UTL_TCP. So the deeper down the s/w stack you move, the more complex it becomes.

                  Your code fails to read SMTP server responses - and that is a critical step in a proper SMTP client-server conversation.

                  I suggest that you use UTL_SMTP and not UTL_TCP.

                  Earlier this year I also had to write a custom mail interface for our developers. They had to send pretty large attachments (20+MB in size). I used UTL_SMTP as it makes the SMTP protocol conversation with the mail server a lot easier.

                  Why manually read SMTP server responses and parse the return code to determine whether the command just send were successful or not? UTL_SMTP does that for you.