ORA-06502: PL/SQL: numeric or value error:character string buffer too small [message #477591] |
Sat, 02 October 2010 00:48 |
lancer26
Messages: 52 Registered: May 2006 Location: Pakistan
|
Member |
|
|
Dear gurus, i am generating html format mail from oracle 10g database.
For displaying data into html format, message body data is exceeding more than 32k.
can you please guide me how to handle this error?
ORA-06502: PL/SQL: numeric or value error:character string buffer too small
i am using long data type for message body data.
|
|
|
Re: ORA-06502: PL/SQL: numeric or value error:character string buffer too small [message #477844 is a reply to message #477591] |
Mon, 04 October 2010 15:17 |
sunroser
Messages: 16 Registered: September 2010
|
Junior Member |
|
|
Hi,
First, using long data type is not smart choice. But any way, in oracle database, the long field is about 2G large (or 4G?), but doesn't mean the variable of long type is the same, and you have to use either long type variable/varchar2 to update the long column type, or varchar2. NOT allowed as long||varchar2 (vachar2 is 4000 bytes), why, don't ask me. Depends on how your database is created, your long type variable is about 16k (in my case), so it is supposed that error occurs.
Try this: using long type variable as varchar2 to update your email long field by stages which each of stage's size is about 16k. like update long column A = long column A||long variable B.
Let me know if it works.
[EDITED by LF: removed unnecessary quote of the whole previous message]
[Updated on: Tue, 05 October 2010 01:15] by Moderator Report message to a moderator
|
|
|
Re: ORA-06502: PL/SQL: numeric or value error:character string buffer too small [message #477868 is a reply to message #477844] |
Tue, 05 October 2010 00:32 |
lancer26
Messages: 52 Registered: May 2006 Location: Pakistan
|
Member |
|
|
i am not using oracle table for updating long column...
-- this is my procedure to generate html mail
DECLARE
CURSOR CUR IS SELECT SO.ORD_NO,SO.CUST_PO,SO.DISP_DATE,SO.CA_DATE,CO.CONTACT_PERSON_ID,
(SELECT PD_CONTACT_PERSON FROM CD WHERE CD.TRANS_ID = CO.CONTACT_PERSON_ID) PD_ID,
(SELECT NAME FROM PROFILE WHERE TRANS_ID = CO.PROFILE_CODE) CUST_NAME
FROM SO, CO
WHERE CO.TRANS_ID = SO.OC_TRANS_ID AND SO.ORD_NO IS NOT NULL
AND TO_DATE(SO.CA_DATE - 10,'DD/MM/RR') = TO_DATE(SYSDATE,'DD/MM/RR')
AND NOT EXISTS (SELECT 'X' FROM ASW WHERE ASW.OC_TRANS_ID = SO.OC_TRANS_ID)
ORDER BY 4,1;
V_HTM_MSG LONG;
BEGIN
V_HTM_MSG := '<html><head><title>Before 10 days expected CA Date MS Generation Notification</title><style type="text/css"><!--.style1 {font-family: tahoma; font-size: 11px; font-weight: bold; color: #000066;}.style3 { font-family: tahoma; font-size: 11px;}.style5 {color: #990000; font-family: tahoma; font-size: 10px; font-weight: bold; } .style7 {font-family: tahoma; font-weight: bold;}
.style8 {font-size: 12px; font-style: italic; color: #999999; font-family: Verdana, Arial, Helvetica, sans-serif;}--></style></head>
<body>
MS Generation Notification before 10 days of expected CA Date
<table border="0" align="center">
<tr><td COLSPAN="2" VALIGN="TOP">
<table border="1">
<tr><td bgcolor="#CCCCCC">
ANL Order#
</td>
<td bgcolor="#CCCCCC" class="style3">
Customer P.O
</td>
<td bgcolor="#CCCCCC" class="style3">
Exp. CA Date
</td>
<td bgcolor="#CCCCCC" class="style5">
Req. Dlvry Date
</td>
</tr>';
FOR REC IN CUR LOOP
CNT := NVL(CNT,0) + 1;
v_htm_msg:=v_htm_msg||'<tr><td align = "left">'||REC.ORD_NO||'</td>
<td align = "left">'||REC.CUST_PO||'</td>
<td align = "left">'||REC.CA_DATE||'</td>
<td align = "left">'||REC.DISP_DATE||'</td>
</tr> ';
END LOOP;
v_htm_msg:=v_htm_msg||'</table></td></tr></table>Note: This is system generated email</body></html>';
hMAIL(from_name => 'a@b.COM',
to_names => 'c@b.com',
subject => ' MS GENERATION NOTIFICATION ',
message => V_HTM_MSG);
end;
|
|
|
Re: ORA-06502: PL/SQL: numeric or value error:character string buffer too small [message #477991 is a reply to message #477868] |
Tue, 05 October 2010 09:27 |
sunroser
Messages: 16 Registered: September 2010
|
Junior Member |
|
|
Hi, use CLOB data type instead, I have gotton 45K and without issue as below:
Connected to Oracle9i Enterprise Edition Release 9.2.0.7.0
Connected as dsadmin
SQL> set serveroutput on;
SQL>
SQL> declare
2 src_email clob;
3 cursor email is
4 select a.nm nm1,b.nm nm2 from
5 (select 'TEST1' nm from dual
6 union all
7 select 'TEST2' from dual
8 union all
9 select 'TEST4' from dual
10 union all
11 select 'TEST5' from dual
12 union all
13 select 'TEST6' from dual
14 union all
15 select 'TEST7' from dual
16 ) a,
17 (select 'GAS1' nm from dual
18 union all
19 select 'GAS2' from dual
20 union all
21 select 'GAS4' from dual
22 union all
23 select 'GAS5' from dual
24 union all
25 select 'GAS6' from dual
26 union all
27 select 'GAS7' from dual
28 ) b;
29
30 begin
31 src_email := 'here it is';
32 for cur_email in email loop
33 src_email := src_email ||' Aha again!'||cur_email.nm1||cur_email.nm2||'this is the approach,Tried something like this way (There might be much more optimized /better sql.But this is what came to my mind. Thought about considering min and max value to define the range)(There might be much more optimized /better sql.But this is what came to my mind. Thought about considering min and max value to define the range)(There might be much more optimized /better sql.But this is what came to my mind. Thought about considering min and max value to define the range)(There might be much more optimized /better sql.But this is what came to my mind. Thought about considering min and max value to define the range)ried something like this way (There might be much more optimized /better sql.But this is what came to my mind. Thought about considering min and max value to define the range)(There might be much more optimized /better sql.But this is what came to my mind. Thought about considering min and max value to define the range)(There might be much more optimized /better sql.But this is what came to my mind. Thought about considering min and max value to define the range)(There might be much more optimized /better sql.But this is what came to my mind. Thought about considering min and max value to define the range)';
34 end loop;
35 dbms_output.put_line(to_char(length(src_email)));
36 end;
37
38 /
45658
PL/SQL procedure successfully completed
SQL>
[EDITED by LF: removed unnecessary quote of the whole previous message]
[Updated on: Tue, 05 October 2010 15:32] by Moderator Report message to a moderator
|
|
|
|