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

UTL_SMTP - Write_data is not proper in excel !!

Lazar Aug 12, 2019 10:29 AM

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

1. Re: UTL_SMTP - Write_data is not proper in excel !!

Lazar Aug 13, 2019 5:32 AM (in response to Lazar)

Hi All,

Can someone help on this ?? Im stuck up here.

Regards,
Lazar
Like Show 0 Likes(0)

Actions
2. Re: UTL_SMTP - Write_data is not proper in excel !!

AndrewSayer Aug 13, 2019 6:46 AM (in response to Lazar)

If you want to create an excel file then you need to figure out how the file is formatted. There’s lots online about this, depending on what type of excel file you want to create you will need to write different stuff to the file. If your Pl/sql is not up to scratch then it’s recommended you use 3rd party code/software to do it for you, there is plenty of decent code that will do this all for you, google is your friend (And reading the code and the documentation for it is advised to make sure it does what you want).

It is obvious that you’ve actually tried to write a html file instead of a traditional excel file. You don’t get that by accident so perhaps you really want a html file and there’s something a little wrong with the output - tell us what.

Im not sure how many times you’ve been told this and failed to take note but your exception handling here is awful. It will swallow the important things about the exception and stick text into a buffer that may or may not be looked at by the caller. Importantly, calling the code will always (Assuming it compiles) result in a “successful“ execution even if it errors on the first line.
Like Show 0 Likes(0)

Actions
3. Re: UTL_SMTP - Write_data is not proper in excel !!

Marwim Aug 13, 2019 7:10 AM (in response to Lazar)

Why do you want to fix something when the basic approach is wrong. You will never get rid of the security warnings.
Split creating the attachment and sending the mail into two separate units.
Don't reinvent the wheel. For both tasks there are free solutions available.

7. Re: UTL_SMTP - __.xls is showing some security error message. __.xlsx is showing corrupted
8. Re: Sending mail with UTL_SMTP (help needed ...)
Marcus
Like Show 0 Likes(0)

Actions
4. Re: UTL_SMTP - Write_data is not proper in excel !!

Lazar Aug 13, 2019 10:43 AM (in response to Marwim)

Marwim AndrewSayer @all..

Give me one example with a simple UTL_SMTP procedure --> to send an mail with .xlsx as an attachment, in which the attachment data needs to be pulled from a one single table.

Please assist.

I ended up in a no-where now after going through all the posts and throughs other googling and blogs.

It will be much helpful and appreciated . Please assist.
Like Show 0 Likes(0)

Actions
5. Re: UTL_SMTP - Write_data is not proper in excel !!

Lazar Aug 13, 2019 10:56 AM (in response to Lazar)

Adding to the above ....

I have gone through lot of examples in which the data populated in mail attachments from a DIRECTORY.

I can't find anything/sample scripts based on my requirement.

Help to suggest simple one, in which data needs to be populated from a table as an XLSX attachment.
Like Show 0 Likes(0)

Actions
6. Re: UTL_SMTP - Write_data is not proper in excel !!

Marwim Aug 13, 2019 11:03 AM (in response to Lazar)

Take one of the mail packages out there - in the example I use mine from http://matzberger.de/oracle/mail-en.html
And install a package to generate the Excel files, e.g. https://technology.amis.nl/2011/02/19/create-an-excel-file-with-plsql/

DECLARE
    v_sql          VARCHAR2(32000);
    attachments    mam_email.tbl_attachments;
BEGIN
    v_sql := q'[SELECT t.xyz
FROM    your_table t
WHERE t.id = 'ccc'
ORDER BY p.beginn DESC]';
    as_xlsx.query2sheet(v_sql);

    attachments(1).fileBLOB := as_xlsx.finish();
    attachments(1).fileName := 'xyz.xlsx';
    mam_email.setMailserver('my.mailserver.example.com');
    mam_email.send(
        'recipient@example.com'-- Recipient
        ,'cc@example.com'-- Recipient of copy
        ,'bcc@example.com'-- Recipient of blind copy
        ,'sender@example.com'-- Sender
        ,'Testmail'-- Subject
        ,'Mail with BLOB attachment'
        ,'<div style="font: 1em/100% Arial, sans-serif;">'||
        '<p>Mail with BLOB attachment</p>'||
        '</div>'
        ,attachments-- Attachments
        );
END;

Of course you can control the layout of the xlsx file or the mail body in more detail - this is only the most simple example.
Just look at the documentation of the packages.

Marcus
1 person found this helpful
Like Show 0 Likes(0)

Actions
7. Re: UTL_SMTP - Write_data is not proper in excel !!

Lazar Aug 13, 2019 11:28 AM (in response to Marwim)

Hi Marwim,

Step 1 - Executed the as_xlsx package.

Step 2- Cant find the mam_email package in that said link and i can see only the specifications even after downloading!!!.

Can you post it here ?
Like Show 0 Likes(0)

Actions
8. Re: UTL_SMTP - Write_data is not proper in excel !!

Lazar Aug 13, 2019 11:50 AM (in response to Lazar)
Like Show 0 Likes(0)

Actions
9. Re: UTL_SMTP - Write_data is not proper in excel !!

Marwim Aug 13, 2019 12:08 PM (in response to Lazar)

I can open it with 7-zip ?-(
I will upload a new zip-file today after work and post a message here.
Like Show 1 Likes(1)

Actions
10. Re: UTL_SMTP - Write_data is not proper in excel !!

Lazar Aug 13, 2019 1:38 PM (in response to Marwim)

Thanks and awaiting for the package..
Like Show 0 Likes(0)

Actions
11. Re: UTL_SMTP - Write_data is not proper in excel !!

Marwim Aug 13, 2019 6:44 PM (in response to Lazar)

http://matzberger.de/oracle/mam_email.zip

Just in case it still doesn't work you can download the files directly
Temporary link only!
http://matzberger.de/oracle/mam_email.pcb
http://matzberger.de/oracle/mam_email.pck
1 person found this helpful
Like Show 0 Likes(0)

Actions
12. Re: UTL_SMTP - Write_data is not proper in excel !!

Lazar Aug 14, 2019 10:59 AM (in response to Marwim)

Many Thanks i am able to generate it..

Thanks again Marcus.....

One small issue with the dates as shown below.

Im calling the Anton Scheffer - as_xlsx package ---> https://technology.amis.nl/wp-content/uploads/2011/02/as_xlsx18.txt

Do i need to create a separate thread for this issue ???

Regards,
Lazar
Like Show 0 Likes(0)

Actions
13. Re: UTL_SMTP - Write_data is not proper in excel !!

Gaz in Oz Aug 14, 2019 11:20 AM (in response to Lazar)

That's an excel display issue. You need to highlight the column -> Format cells -> date...
Like Show 0 Likes(0)

Actions
14. Re: UTL_SMTP - Write_data is not proper in excel !!

Marwim Aug 14, 2019 11:46 AM (in response to Lazar)

Yes, you should open a new thread.
Include the code/sample data so we can see what happens.
Like Show 0 Likes(0)

Actions