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