7 Replies Latest reply on Aug 12, 2019 3:17 PM by Marwim

    UTL_SMTP - __.xls is showing some security error message. __.xlsx is showing corrupted

    Lazar

      Hi All,

       

       

      While opening .xls is showing some security error message. How to bye pass that ? Also XLSX will not work here, seen that as well showing error ?? PFB screenshot, Please advise.

       

      screenshot 1(.XLS)

       

      screenshot 2(.XLSX)

       

       

      --Procedure:-
      
      
      CREATE OR REPLACE PROCEDURE CUST_AUTOMAIL AS  
      CURSOR CUR_REPORT IS  
      SELECT *  
      FROM cust_master;  
        
        
      msg_from          VARCHAR2(50);
      msg_to            VARCHAR2(4000);
      message_to        VARCHAR2(50);
      msg_subject       VARCHAR2(250);
      message           VARCHAR2(32767);
      mainmsg           VARCHAR2(32767);
      v_errortext       VARCHAR2(200);
      attachment_text   clob;
      v_messageatt      clob;
      
      
      l_mail_conn   UTL_SMTP.connection;
      l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
      l_step        PLS_INTEGER  := 12000; 
      begin  
        
        l_mail_conn := UTL_SMTP.open_connection('localhost', 25);
        UTL_SMTP.helo(l_mail_conn, 'localhost');
        UTL_SMTP.mail(l_mail_conn, 'abc@gmail.com');
        UTL_SMTP.rcpt(l_mail_conn, 'abc@gmail.com');
      
      
        UTL_SMTP.open_data(l_mail_conn);
        
        UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, 'To: ' || 'abc@gmail.com' || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, 'From: ' || 'abc@gmail.com' || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || 'Report -'||TRUNC(SYSDATE) || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || 'abc@gmail.com' || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
        
        
        UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
        UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
      
      
        UTL_SMTP.WRITE_DATA(l_mail_conn, UTL_TCP.CRLF || '<HTML>');
        UTL_SMTP.WRITE_DATA(l_mail_conn, UTL_TCP.CRLF || '<BODY>Hello All,<br><br>Please find Report as on <b>'||TRUNC(SYSDATE)||'</b>.'||'<br><br>');
        UTL_SMTP.WRITE_DATA(l_mail_conn, UTL_TCP.CRLF || '<br>'||  'Regards,<br>'||  'Lazar <br><br> ');   
        UTL_SMTP.WRITE_DATA(l_mail_conn, UTL_TCP.CRLF || '</BODY>');
        UTL_SMTP.WRITE_DATA(l_mail_conn, UTL_TCP.CRLF || '</HTML>');
      
      
          UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
      
      
        --IF p_attach_name IS NOT NULL THEN
          v_messageatt := '<html><head></head>';
              v_messageatt := v_messageatt || '<body><br>';
              v_messageatt := v_messageatt || '<table border="1" cellspacing="0" cellpadding="4">';
      
      
      v_messageatt := v_messageatt ||'<style>
                                       p.b1 
      {
         border-style: solid;
         border-width: 5px;
      } 
      p.b2 
      {
         border-style: solid;
         border-width: medium;
      }
      </style>';
        
        
        
              v_messageatt := v_messageatt || '<tr><th style="background-color:#34c6eb;"><p class="b1">SNO</p></th><th style="background-color:#34c6eb;"><p class="b1">CUS_NAME</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">ADDRESS_TYPE</p></th><th style="background-color:#34c6eb;"><p class="b1">ADDRESS</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">STATE</p></th><th style="background-color:#34c6eb;"><p class="b1">KID</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">CSTATE</p></th><th style="background-color:#34c6eb;"><p class="b1">LAST_NAME</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">FIRST_NAME</p></th><th style="background-color:#34c6eb;"><p class="b1">MIDDLE_NAME</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">DATE_OF_BIRTH</p></th><th style="background-color:#34c6eb;"><p class="b1">C_TYP</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">CLIENT_CAT</p></th><th style="background-color:#34c6eb;"><p class="b1">CNTRY_CODE</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">READ_DT</p></th><th style="background-color:#34c6eb;"><p class="b1">CUST_SCORE</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">CUST_RATING</p></th><th style="background-color:#34c6eb;"><p class="b1">STATUS</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">CUST_FLAG</p></th><th style="background-color:#34c6eb;"><p class="b1">PRE_STATUS</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">VERS_NUM</p></th><th style="background-color:#34c6eb;"><p class="b1">TIER1</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">TIER2</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">TIER3</p></th><th style="background-color:#34c6eb;"><p class="b1">TIER4</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">TIER5</p></th><th style="background-color:#34c6eb;"><p class="b1">TIER6</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">TIER7</p></th><th style="background-color:#34c6eb;"><p class="b1">TIER8</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">TIER9</p></th><th style="background-color:#34c6eb;"><p class="b1">TIER10</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">TIER11</p></th><th style="background-color:#34c6eb;"><p class="b1">TIER_FIRST_VALUE</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">TIER_LAST_VALUE</p></th><th style="background-color:#34c6eb;"><p class="b1">TIER_STATE</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">TIER_ID</p></th><th style="background-color:#34c6eb;"><p class="b1">TIER_VALUE</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">TIER_RATING</p></th><th style="background-color:#34c6eb;"><p class="b1">CREATED_BY</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">CREATE_DATE</p></th><th style="background-color:#34c6eb;"><p class="b1">LST_MODIFY_BY</p></th>  
      <th style="background-color:#34c6eb;"><p class="b1">LST_MODIFY_DATE</p></th><th style="background-color:#34c6eb;"><p class="b1">COMMENTS</p></th></tr>';  
        
      FOR REC IN CUR_REPORT   
      LOOP  
        
                  v_messageatt := v_messageatt || '<tr>';  
                  v_messageatt := v_messageatt||  '<td>'|| REC.SNO||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.CUS_NAME||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.ADDRESS_TYPE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.ADDRESS||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.STATE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.KID||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.CSTATE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.LAST_NAME||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.FIRST_NAME||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.MIDDLE_NAME||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.DATE_OF_BIRTH||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.C_TYP||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.CLIENT_CAT||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.CNTRY_CODE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.READ_DT||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.CUST_SCORE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.CUST_RATING||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.STATUS||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.CUST_FLAG||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.PRE_STATUS||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.VERS_NUM||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER1||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER2||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER3||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER4||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER5||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER6||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER7||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER8||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER9||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER10||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER11||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER_FIRST_VALUE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER_LAST_VALUE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER_STATE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER_ID||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER_VALUE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.TIER_RATING||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.CREATED_BY||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.CREATE_DATE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.LST_MODIFY_BY||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.LST_MODIFY_DATE||  '</td>';  
      v_messageatt := v_messageatt||  '<td>'|| REC.COMMENTS||  '</td>';  
                  v_messageatt := v_messageatt || '</tr>';  
        
      END LOOP;  
      
      
      
      
      
      
      DBMS_OUTPUT.PUT_LINE(6);
      
              v_messageatt := v_messageatt || '</table>';
      
      attachment_text := v_messageatt;
      
      DBMS_OUTPUT.PUT_LINE(7);
      
      
      UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
          UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; name="' || 'Report.xls' || '"' || UTL_TCP.crlf);
          UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || 'Report.xls' || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
      
          FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(attachment_text) - 1 )/l_step) LOOP
            UTL_SMTP.write_data(l_mail_conn, DBMS_LOB.substr(attachment_text, l_step, i * l_step + 1));
          END LOOP;
      
      
          UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
        --END IF;
        
        UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
        UTL_SMTP.close_data(l_mail_conn);
      
      
        UTL_SMTP.quit(l_mail_conn);
        
      
      
      
      EXCEPTION 
        WHEN OTHERS THEN 
      DBMS_OUTPUT.PUT_LINE(SQLCODE||'-'||SQLERRM);
      DBMS_OUTPUT.PUT_LINE('Error at line no-'||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
      END;
      /
      
      
      EXEC CUST_AUTOMAIL;
      

       

       

      Regards,

      Lazar