Hi All,
Please help to assist on the below.
I can able to trigger mail when it was 20 records as attachment and when it was 30 records ,oracle throws an error via UTL package. Please assist.
Oracle version : 12C
Error Snippet:-
6
7
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 747
ORA-06512: at "CUST_AUTOMAIL", line 140
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));
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;
begin
msg_subject := ' report -'||trunc(sysdate);
message := '<html><head></head>';
message := message || '<body>';
message := message || 'Hello All,<br><br>';
message := message || 'Please find attached Report for this month.'||'<br><br>';
message := message ||'<style>table, th, td p.solid {border-style: solid;} </style>';
message := message ||'<table border="1" cellspacing="0" cellpadding="4">';
message := message||'</table>';
message := message|| '<br>'|| 'Regards,<br>'|| 'Team <br><br> ';
message := message || '</body></html>';
mainmsg := message;
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;
attachment_text := v_messageatt;
DBMS_OUTPUT.PUT_LINE(6);
v_messageatt := v_messageatt || '</table>';
DBMS_OUTPUT.PUT_LINE(7);
UTL_MAIL.send_attach_raw(
sender => 'lazar@gmail.com',
recipients => 'lazar@gmail.com',
cc => '',
bcc => '',
subject => msg_subject,
message => mainmsg,
mime_type => 'text/html',
priority => 1,
attachment => utl_raw.cast_to_raw(attachment_text),
att_inline => true,
att_mime_type => 'text/plain; charset=us-ascii',
att_filename => 'Report_'||to_char(sysdate,'DD_MON_YYYY')||'.xls',
replyto => '');
DBMS_OUTPUT.PUT_LINE(8);
dbms_output.put_line('Send Sucessfully ........');
EXCEPTION
WHEN others THEN
v_errortext := substr(sqlerrm,1,200);
dbms_output.put_line(sqlerrm);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);
DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);
END;
--Error Snippet:-
6
7
25460
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_MAIL", line 662
ORA-06512: at "SYS.UTL_MAIL", line 747
ORA-06512: at "CUST_AUTOMAIL", line 140
PL/SQL procedure successfully completed.
Regards,
Lazar