A Question about this error ORA-06502 [message #315145] |
Sat, 19 April 2008 08:20  |
snake_eyes
Messages: 10 Registered: April 2008
|
Junior Member |
|
|
Hello,
I'm getting the following error when I run my procedure:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
My question, is there a datatype can be use instead of varchar2(32767) and clob, because after my tracing I noticed that when the data has exceed the 32K it gives the above error.
Is there any suggestion...
W. Regards
|
|
|
|
|
|
Re: A Question about this error ORA-06502 [message #315151 is a reply to message #315145] |
Sat, 19 April 2008 08:45   |
snake_eyes
Messages: 10 Registered: April 2008
|
Junior Member |
|
|
Do you have an example?
below is my procedure....
procedure alert_daily_attendance is
cursor parent_cur is
select *
from table;
l_org_parent_email varchar2(25);
l_organization_name varchar2(20);
l_loop_msg clob;
l_loop_msg2 clob;
l_loop_total_emp number;
l_loop_over_all_emp number default 0;
l_msg_body clob;
begin
l_org_parent_email := null;
l_loop_msg := null;
for parent_rec in parent_cur loop
l_organization_name := null;
l_loop_msg2 := null;
l_loop_total_emp := 0;
for child_rec in (select *
from table2 where fld =
parent_rec.org_child_id) loop
l_organization_name := child_rec.org_name;
l_loop_msg2 := l_loop_msg2 || '<tr><td>' ||
to_char(child_rec.emp_number, '0000') ||
'</td><td>' || child_rec.first_name || ' ' ||
child_rec.last_name || '</td><td>' ||
child_rec.position_name || '</td><td>' ||
child_rec.assignment_status || '</td></tr>';
l_loop_total_emp := l_loop_total_emp + 1;
l_loop_over_all_emp := l_loop_over_all_emp + 1;
end loop;
l_org_parent_email := parent_rec.org_parent_email;
if Length(l_organization_name) > 0 then
l_loop_msg := l_loop_msg || chr(9) || '<b>Organization Name: ' ||
l_organization_name || '</b>' || chr(13) || chr(10) ||
'<blockquote><table border=0>' ||
'<tr><td width=30 bgcolor=#808080>Emp#</td>' ||
'<td width=125 bgcolor=#808080>Full Name</td>' ||
'<td width=125 bgcolor=#808080>Positiona Name</td>' ||
'<td width=125 bgcolor=#808080>Assignment Status</td></tr>' ||
l_loop_msg2 || '
<tr><td colspan=4><br>Total Empployees: ' ||
to_char(l_loop_total_emp, '00') ||
'</td></tr></table></blockquote>';
end if;
end loop;
l_org_parent_email := substr(l_org_parent_email,
0,
instr(l_org_parent_email, '@') - 1);
l_msg_body := '<table border=0><tr><td>' || 'Dear ' ||
l_org_parent_email || ' ,' || '<br><br>' ||
l_loop_msg || '<br><br>' ||
'Employees Over All: ' ||
to_char(l_loop_over_all_emp, '000') || '<br><br>' ||
'Sincerely,' || '</td></tr></table>';
submit_to_mail(param1,pram2,param3....);
--dbms_output.put_line(l_msg_body);
exception
when others then
dbms_output.put_line('Error number: ' || sqlerrm);
end alert_daily_attendance;
W. Regards
[Updated on: Sat, 19 April 2008 08:47] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: A Question about this error ORA-06502 [message #317879 is a reply to message #315145] |
Sun, 04 May 2008 09:56   |
snake_eyes
Messages: 10 Registered: April 2008
|
Junior Member |
|
|
Hello,
Yeah sure this is my code; the error in the following area:
--> Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_html),
l_offset,
p_html);
The error is: Error number: ORA-06502: PL/SQL: numeric or value error.
please advice...
procedure submit_email(p_to varchar2,
p_from varchar2,
p_subject varchar2,
p_html clob,
p_smtp_hostname varchar2,
p_smtp_portnum varchar2) is
l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_body_html clob := empty_clob; --This LOB will be the email message
l_offset number;
l_ammount number;
l_temp clob default null;
begin
l_connection := utl_smtp.open_connection(p_smtp_hostname,
p_smtp_portnum);
utl_smtp.helo(l_connection, p_smtp_hostname);
utl_smtp.mail(l_connection, [email]'oracle10g@site.com'[/email]);
utl_smtp.rcpt(l_connection, p_to);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'X-Priority: 1' || chr(13) || chr(10);
l_temp := l_temp || 'X-MSMail-Priority: Normal' || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) || chr(10);
--> Write the headers
dbms_lob.createtemporary(l_body_html, false, 10);
dbms_lob.write(l_body_html, length(l_temp), 1, l_temp);
--> Write the HTML boundary
l_temp := chr(13) || chr(10) || chr(13) || chr(10) || '--' ||
l_boundary || chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/html; charset=windows-1256' ||
chr(13) || chr(10) || chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
--> Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_html),
l_offset,
p_html);
--> Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);
--> Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_data(l_connection,
dbms_lob.substr(l_body_html, l_ammount, l_offset));
l_offset := l_offset + l_ammount;
l_ammount := least(1900, dbms_lob.getlength(l_body_html) - l_ammount);
end loop;
utl_smtp.close_data(l_connection);
utl_smtp.quit(l_connection);
dbms_lob.freetemporary(l_body_html);
exception
when utl_smtp.transient_error or utl_smtp.permanent_error then
begin
utl_smtp.quit(l_connection);
exception
when utl_smtp.transient_error or utl_smtp.permanent_error then
null;
/* when the smtp server is down or unavailable, we don't
have a connection to the server. the quit call will
raise an exception that we can ignore.*/
end;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' ||
sqlerrm);
end submit_email;
by the way try to run the following code and see the error:
declare
v clob;
begin
v := lpad('A', 10000);
for i in 1 .. 10 loop
v := v || v;
end loop;
dbms_output.put_line(v);
end;
[Updated on: Sun, 04 May 2008 09:59] Report message to a moderator
|
|
|
|
Re: A Question about this error ORA-06502 [message #317893 is a reply to message #317879] |
Sun, 04 May 2008 14:39  |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
snake_eyes wrote on Sun, 04 May 2008 07:56 |
by the way try to run the following code and see the error:
declare
v clob;
begin
v := lpad('A', 10000);
for i in 1 .. 10 loop
v := v || v;
end loop;
dbms_output.put_line(v);
end;
|
SCOTT@orcl_11g> declare
2 v clob;
3 begin
4 v := lpad('A', 10000);
5 for i in 1 .. 10 loop
6 v := v || v;
7 end loop;
8 dbms_output.put_line(v);
9 end;
10 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8
SCOTT@orcl_11g>
You get this error because dbms_output.put_line has a limit of 255 characters per line. Please notice the manner in which I posted the run of this code, so that it inlcudes the line numbers and says that the error was raised on line 8 and we can see which line is line 8. This is what we are expecting you to provide for us to see after you run your code.
If the error is occurring here as you say:
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_html),
l_offset,
p_html);
then you should use dbms_output to show the values of l_offset and length(p_html).
|
|
|