Home » SQL & PL/SQL » SQL & PL/SQL » A Question about this error ORA-06502 (Oracle10g)
A Question about this error ORA-06502 [message #315145] Sat, 19 April 2008 08:20 Go to next message
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 #315146 is a reply to message #315145] Sat, 19 April 2008 08:26 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>is there a datatype can be use instead of varchar2(32767) and clob,
NO
Re: A Question about this error ORA-06502 [message #315148 is a reply to message #315145] Sat, 19 April 2008 08:29 Go to previous messageGo to next message
snake_eyes
Messages: 10
Registered: April 2008
Junior Member
Hello,

Thanks for your reply,

So... What I suppose to do with this stupid error?

what you suggest!!!

W. Regards
Re: A Question about this error ORA-06502 [message #315150 is a reply to message #315145] Sat, 19 April 2008 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
To hold more the 32K, a CLOB must be used.
Same is true for some/many/most RDBMS.
Re: A Question about this error ORA-06502 [message #315151 is a reply to message #315145] Sat, 19 April 2008 08:45 Go to previous messageGo to next message
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 #315154 is a reply to message #315145] Sat, 19 April 2008 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Nice formatting!

>Do you have an example?
Example of what exactly?

http://asktom.oracle.com contains many fine coding examples.


>select * from table;
VERY, VERY bad code.


Re: A Question about this error ORA-06502 [message #315156 is a reply to message #315154] Sat, 19 April 2008 08:58 Go to previous messageGo to next message
snake_eyes
Messages: 10
Registered: April 2008
Junior Member
>Nice formatting!
thanks for you...

>>Do you have an example?
>Example of what exactly?
about clob as you mentioned to use it

http://asktom.oracle.com contains many fine coding examples.


>>select * from table;
>VERY, VERY bad code.

I know it's very bad, it's not the correct query its just like an example.

Thanks
Re: A Question about this error ORA-06502 [message #315159 is a reply to message #315156] Sat, 19 April 2008 09:11 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
snake_eyes wrote on Sat, 19 April 2008 06:58

>>Do you have an example?
>Example of what exactly?
about clob as you mentioned to use it



Are both the Search function on this forum & GOOGLE broken for you?

[Updated on: Sat, 19 April 2008 09:11] by Moderator

Report message to a moderator

Re: A Question about this error ORA-06502 [message #315166 is a reply to message #315145] Sat, 19 April 2008 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

What is the line that raises this error?

Regards
Michel
Re: A Question about this error ORA-06502 [message #315168 is a reply to message #315145] Sat, 19 April 2008 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> 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('length='||length(v));
  9  end;
 10  /
length=10240000

Far beyond 32K.

Regards
Michel

[Updated on: Sat, 19 April 2008 10:51]

Report message to a moderator

Re: A Question about this error ORA-06502 [message #315224 is a reply to message #315166] Sun, 20 April 2008 02:21 Go to previous messageGo to next message
snake_eyes
Messages: 10
Registered: April 2008
Junior Member
Hello,

There is no specific line, some time in the sys.dbms_output, some time in my package Sad

Really I don't know what's the stupid error.

W. Regards
Re: A Question about this error ORA-06502 [message #315232 is a reply to message #315224] Sun, 20 April 2008 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is easier to diagnose if you copy and paste the execution with error than telling us what you see. It is obvious you missed something.
Use SQL*Plus for the test.

Regards
Michel
Re: A Question about this error ORA-06502 [message #315279 is a reply to message #315224] Sun, 20 April 2008 14:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
If you comment out or remove the exception section, then run it, it will tell you what line the error occurs on. If you copy and paste a run of compilation of your code, followed by execution, it will show the corresonding line numbers.

Re: A Question about this error ORA-06502 [message #317879 is a reply to message #315145] Sun, 04 May 2008 09:56 Go to previous messageGo to next message
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 #317880 is a reply to message #317879] Sun, 04 May 2008 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session as I did.
Post your Oracle version with 4 decimals.

Regards
Michel
Re: A Question about this error ORA-06502 [message #317893 is a reply to message #317879] Sun, 04 May 2008 14:39 Go to previous message
Barbara Boehmer
Messages: 8635
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).

Previous Topic: Error in output sending error
Next Topic: Updating objects
Goto Forum:
  


Current Time: Wed Dec 07 05:16:21 CST 2016

Total time taken to generate the page: 0.07115 seconds