Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UTL_SMTP - Write_data is not proper in excel !!

LazarAug 12 2019 — edited Aug 16 2019

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.

pastedImage_1.png

--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

This post has been answered by Marwim on Aug 13 2019
Jump to Answer

Comments

Sudipto Desmukh
Answer
Marked as Answer by greenly · Sep 27 2020
1 - 1

Post Details

Added on Aug 12 2019
14 comments
889 views