CODEWITHSUNDEEP
oracle-database
Thomas Carlton
Thomas Carlton

Reputation: 5958

How to solve : ORA-06502: PL/SQL: numeric or value error?

I have a long text in CLOB and I'm trying to use the function htp.prn

Here is my code :

n := dbms_lob.getlength(html);      -- n is 31745 characters

if n<32000 then
    htp.prn(HTML);                  -- << Error raised here 
else
    do something else
end if;

When using the function htp.prn, I'm getting the error:

ORA-06502: PL/SQL: numeric or value error

I know he text size allowed is 32672 characters. And the text contains some Chinese characters. So, I assume the problem is coming from dbms_lob.getlength

How can I solve that?

Upvotes: 0

Views: 561

Answers (2)

Littlefoot
Littlefoot

Reputation: 142720

Multibyte characters, eh?

Perhaps you could handle it by splitting that string into smaller chunks.

My example uses not that long string which is then split into 20 characters in length. I'm just displaying them (line #10); you'd htp.prn it (line #11).

SQL> set serveroutput on
SQL> declare
  2    html   clob := 'How to solve: ORA-06502: PL/SQL: numeric or value error';
  3    l_var  varchar2(20);  --> you'd use e.g. 5000
  4    l_size number := 20;  --> you'd use e.g. 5000
  5    l_pos  number := 1;
  6  begin
  7    while dbms_lob.getlength(html) >= l_pos
  8    loop
  9      l_var := dbms_lob.substr(html, l_size, l_pos);
 10      dbms_output.put_line(l_var);
 11      htp.prn(l_var);
 12      l_pos := l_pos + length(l_var);
 13    end loop;
 14  end;
 15  /
How to solve: ORA-06
502: PL/SQL: numeric
or value error

PL/SQL procedure successfully completed.

SQL>

Upvotes: 0

Paul W
Paul W

Reputation: 11264

If you have Unicode characters (you mentioned Chinese), and your database is using a multibyte characterset, then the number of bytes is greater than the number of characters. 32767 is a hard limit of bytes. Whereas, dbms_lob.getlength for CLOBs and NCLOBs counts characters. So you probably are over the 32K limit in bytes.

You cannot easily get the length of a CLOB in bytes. There are various unpleasant workarounds, but probably the easiest thing for you to do is simply trap the exception rather than predict it:

n := dbms_lob.getlength(html);

if n<32000 then
    declare
      value_error exception;
      pragma exception_init(value_error,-06502);
    begin
      htp.prn(HTML);
    exception
       when value_error then
         null;
    end;
else
    do something else
end if;

Upvotes: 0

Related Questions