14 Replies Latest reply on Aug 14, 2019 11:46 AM by Marwim

    UTL_SMTP - Write_data is not proper in excel !!

    Lazar

      Hi ,

       

      Based on all the suggestions, have now moved to UTL_SMTP - Thanks for all valuable suggestions.

       

       

      Please help me in correcting the code in procedure on the below aspect with Oracle DB : 12C

       

       

      1. Excel data is not written properly - write_data . PFB screenshot. - Please help to highlight the code to be corrected here.

       

       

      --Create Table
      
      
      create table cust_master(sno number,  
      cus_name varchar2(100),  
      address_type  varchar2(100),  
      address  varchar2(100),  
      state    varchar2(100),  
      KID              VARCHAR2(36),     
      CSTATE           VARCHAR2(30),    
      LAST_NAME                               VARCHAR2(100),  
      FIRST_NAME                              VARCHAR2(100),   
      MIDDLE_NAME                             VARCHAR2(100),  
      DATE_OF_BIRTH                              DATE,        
      C_TYP                               VARCHAR2(100),  
      CLIENT_CAT                         VARCHAR2(50),  
      CNTRY_CODE                              VARCHAR2(2),      
      READ_DT                               DATE,             
      CUST_SCORE                              NUMBER,           
      CUST_RATING                             VARCHAR2(50),     
      STATUS                       VARCHAR2(1000),   
      CUST_FLAG                          VARCHAR2(1000),          
      PRE_STATUS                       VARCHAR2(1000),  
      VERS_NUM                         NUMBER,           
      TIER1                         VARCHAR2(150),    
      TIER2                         VARCHAR2(150),    
      TIER3                         VARCHAR2(150),    
      TIER4                         VARCHAR2(150),    
      TIER5                         VARCHAR2(150),    
      TIER6                         VARCHAR2(150),  
      TIER7                         VARCHAR2(150),    
      TIER8                         VARCHAR2(150),   
      TIER9                         VARCHAR2(150),    
      TIER10                         VARCHAR2(150),    
      TIER11                         VARCHAR2(150),  
      TIER_FIRST_VALUE NUMBER,  
      TIER_LAST_VALUE NUMBER,  
      TIER_STATE  VARCHAR2(150),  
      TIER_ID     VARCHAR2(150),  
      TIER_VALUE  NUMBER,  
      TIER_RATING VARCHAR2(150),  
      CREATED_BY   VARCHAR2(150),  
      CREATE_DATE   DATE,  
      LST_MODIFY_BY VARCHAR2(150),  
      LST_MODIFY_DATE DATE,  
      COMMENTS  VARCHAR2(1000));  
        
      --Inserts:-
        
      INSERT INTO cust_master VALUES (1,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (2,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (3,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (4,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (5,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (6,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (7,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (8,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (9,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (10,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (11,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (12,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (13,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (14,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (15,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (16,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (17,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (18,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (19,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (20,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (21,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (22,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (23,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (24,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (25,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (26,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (27,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (28,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (29,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      INSERT INTO cust_master VALUES (30,'MATT DAMON','PRIMARY','NO23 LANG STREET','ACTIVE',  
      '87854assd23-sdf45','ACTIVE','DAMON',  
      'MATT','','22-Dec-87','IMP',  
      'SPECIAL','US',TRUNC(SYSDATE),1,'HIGH','VALID','PRIM','CLOSED',5,  
      'PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','PRESENT','CLOSED',  
      'CLOSED','PRESENT','CLOSED','CLOSED',  
      18,69,'ACTIVE','kjkjj-8787asd',10,'95','MATT',sysdate,'DAMON',sysdate,'This is an high customer woth valued among the others and to be specified in priority');  
      COMMIT;  
      
      
      --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