1 Reply Latest reply on Jan 20, 2011 12:07 PM by Saubhik

    UTL_SMTP mail with attachment( Problem in adding multiple recipient)

    San
      Hi All,

      I am using the below code for sending the attachment.

      When i try to add the mail group name in my recipient details i am getting the following error
      ORA-29279: SMTP permanent error: 501 Syntax error, parameters in command "RCPT TO:Application Support - IT" unrecognized or missing
      How to add the group in the recipient or cc ????



      My mail code is:
      create or replace PROCEDURE "SSSL_SEND_MAIL" ( p_sender varchar2,
            p_recipient varchar2,
            p_cc varchar2,
            p_subject varchar2,
            p_filename varchar2,
            text varchar2) is  
      
          /*LOB operation related varriables */
         v_src_loc  BFILE; 
         l_buffer   RAW(54);
         l_amount   BINARY_INTEGER := 54;
         l_pos      INTEGER := 1;
         l_blob     BLOB := EMPTY_BLOB;
         l_blob_len INTEGER;
         v_amount   INTEGER;
        
          /*UTL_SMTP related varriavles. */
          v_connection_handle  UTL_SMTP.CONNECTION;
          v_from_email_address VARCHAR2(200); 
          v_to_email_address   VARCHAR2(200) ;
          v_cc                 VARCHAR2(200);
          v_smtp_host          VARCHAR2(10) ;
          v_subject            VARCHAR2(500) ;
          l_message            VARCHAR2(3000);
          l_filename           VARCHAR2(4000);
        
          /* This send_header procedure is written in the documentation */
          PROCEDURE send_header(pi_name IN VARCHAR2, pi_header IN VARCHAR2) AS
          BEGIN
          --dbms_output.put_line('entering into procedure');
          --dbms_output.put_line(pi_name || ': ' || pi_header);
            UTL_SMTP.WRITE_DATA(v_connection_handle,
                                pi_name || ': ' || pi_header || UTL_TCP.CRLF);
          END;
       
        BEGIN
         v_src_loc             := BFILENAME('BROKERREPORTS',p_filename);
         v_from_email_address  := p_sender;
         v_to_email_address    := p_recipient;
         v_cc                  := p_cc;
         v_smtp_host           := 'xxxxxx'; --My mail server, replace it with yours.
         v_subject             := p_subject;
          
         l_message      := 'tets';
        
       
          /*Preparing the LOB from file for attachment. */
          DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
          DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
          v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
          DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
          l_blob_len := DBMS_LOB.getlength(l_blob);
          
          l_filename:=p_filename;
          
          
          IF l_blob_len > 7000000 THEN
          BEGIN
          SSSL_FILE_ZIP.COMPRESSFILE(
          P_IN_FILE => '/mfundb/prdapex/BROKER-REPORTS/'||p_filename,
          P_OUT_FILE => '/mfundb/prdapex/BROKER-REPORTS/'||REPLACE(UPPER(p_filename),'XLS','ZIP')
        );
        l_filename:=NULL;
        l_filename:= REPLACE(UPPER(p_filename),'XLS','ZIP');
        v_src_loc := BFILENAME('BROKERREPORTS',l_filename);
        DBMS_LOB.OPEN(v_src_loc, DBMS_LOB.LOB_READONLY); --Read the file
        DBMS_LOB.CREATETEMPORARY(l_blob, TRUE); --Create temporary LOB to store the file.
        v_amount := DBMS_LOB.GETLENGTH(v_src_loc); --Amount to store.
        DBMS_LOB.LOADFROMFILE(l_blob, v_src_loc, v_amount); -- Loading from file into temporary LOB
        l_blob_len := DBMS_LOB.getlength(l_blob);
        
        
       EXCEPTION WHEN OTHERS THEN
       sssl_internal_error_track(sqlcode,sqlerrm,'SSSL_FILE_ZIP',l_filename||'-'||p_recipient);
      END; 
      END IF;
          
        
       /*UTL_SMTP related coding. */
          v_connection_handle := UTL_SMTP.OPEN_CONNECTION(host => v_smtp_host);
          UTL_SMTP.HELO(v_connection_handle, v_smtp_host);
          UTL_SMTP.MAIL(v_connection_handle, v_from_email_address);
          UTL_SMTP.RCPT(v_connection_handle, v_to_email_address);
          UTL_SMTP.RCPT(v_connection_handle, v_cc);
          UTL_SMTP.OPEN_DATA(v_connection_handle);
          
          send_header('From', v_from_email_address);-- || '<'||'>');
         
          send_header('TO', v_to_email_address);--|| '<'||'>');
          
          send_header('CC', v_cc );    
             
            
          send_header('Subject', v_subject);
      
        
          --MIME header.
         UTL_SMTP.WRITE_DATA(v_connection_handle,
                            'MIME-Version: 1.0' || UTL_TCP.CRLF);
         UTL_SMTP.WRITE_DATA(v_connection_handle,
                              'Content-Type: multipart/mixed; ' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              ' boundary= "' || 'SAUBHIK.SECBOUND' || '"' ||
                              UTL_TCP.CRLF);
         UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
        
          -- Mail Body
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              'Content-Type: text/plain;' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              ' charset=US-ASCII' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle, l_message || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
        
          -- Mail Attachment
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              '--' || 'SAUBHIK.SECBOUND' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                             'Content-Type: application/octet-stream' ||
                              UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              'Content-Disposition: attachment; ' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              ' filename="' || l_filename || '"' || --My filename
                              UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              'Content-Transfer-Encoding: base64' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
        /* Writing the BLOL in chunks */
          WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.READ(l_blob, l_amount, l_pos, l_buffer);
            UTL_SMTP.write_raw_data(v_connection_handle,
                                    UTL_ENCODE.BASE64_ENCODE(l_buffer));
            UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
            l_buffer := NULL;
            l_pos    := l_pos + l_amount;
          END LOOP;
          UTL_SMTP.WRITE_DATA(v_connection_handle, UTL_TCP.CRLF);
        
          -- Close Email
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              '--' || 'SAUBHIK.SECBOUND' || '--' || UTL_TCP.CRLF);
          UTL_SMTP.WRITE_DATA(v_connection_handle,
                              UTL_TCP.CRLF || '.' || UTL_TCP.CRLF);
        
          UTL_SMTP.CLOSE_DATA(v_connection_handle);
          UTL_SMTP.QUIT(v_connection_handle);
          DBMS_LOB.FREETEMPORARY(l_blob);
          DBMS_LOB.FILECLOSE(v_src_loc);
       
        EXCEPTION
          WHEN OTHERS THEN
            UTL_SMTP.QUIT(v_connection_handle);
            DBMS_LOB.FREETEMPORARY(l_blob);
            DBMS_LOB.FILECLOSE(V_SRC_LOC);
           sssl_internal_error_track(sqlcode,sqlerrm,'SSSL_SEND_MAIL',l_filename||'-'||p_recipient);
        END;
      Kindly help me out to add the group or one or more mail id's


      Thanks in Advance

      Cheers,
      Shan.
        • 1. Re: UTL_SMTP mail with attachment( Problem in adding multiple recipient)
          Saubhik
          As, I said earlier (in some of your posts), you have to add UTL_RCPT for each recepient. For example, if you have CC list into some variable p_cc as comma separated list, then you can do something like this:
          ll_cc := 'a@a.com,b@b.com,c@c.com';
             loop
                 exit when l_to is null;
                 n := instr( l_cc, ',' );
                 IF n =0 THEN exit; end if;
                 l_tmp := substr( l_cc, 1, n-1 );
                 l_cc := substr( l_cc, n+1 );
                 utl_smtp.rcpt( l_tmp );
             end loop;
          PS.: Not tested.
          Read this excellent explanation from Billy Verreynne
          To expand on what was said - the SMTP server does not parse the mail data you give it. It does not look at that to see the recipients, CC and BC list. The mail data is just that - raw data. This is the payload that the SMTP server will deliver.
          Where will it deliver it to? That you need to explicitly tell it via the RCPT TO SMTP command. So you need to build a complete list of names that includes all recipients, including CC and BC.

          And then use this list of names to instruct the SMTP SERVER, via the RCPT TO command, who must receive the message payload.>
          Re: Problem in sending email from oracle