Oracle - Number to varchar

Question

Hi,

I have a table containing a column of type Number

create table tmp (
    /*other fields*/
    some_field Number
)

and in a PL SQL script, I want to convert that field to a varchar. However, i don't know its length, so I get an exception

Exception message is ORA-06502: PL/SQL: numeric or value error: character string buffer too small

v_some_field varchar(21);
/*...*/
v_some_field := TO_CHAR(some_field,'999999999999999999999');

How should i declare the v_some_field buffer? Setting it to varchar(32767) seems quite brute, is there any alternative?

Answer 1

Hi Tom,

you're getting an error not because the number is too large but because the result of your to_char is 22 characters long (21x"9"+one character for the sign):

SQL> DECLARE
  2     some_field   NUMBER := 123;
  3     v_some_field VARCHAR(21);
  4  BEGIN
  5     v_some_field := TO_CHAR(some_field, '999999999999999999999');
  6  END;
  7  /

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 6

SQL> DECLARE
  2     some_field   NUMBER := 123;
  3     v_some_field VARCHAR(22);
  4  BEGIN
  5     v_some_field := TO_CHAR(some_field, '999999999999999999999');
  6  END;
  7  /

PL/SQL procedure successfully completed

Answer 2

You could determine the maximum length of your converted varchar2 by converting a negative value with integral and fractional digits:

set serveroutput on
declare
   n number;
begin
   n := -4/3;
   dbms_output.put_line(length(to_char(n)));
end;
/

Output is 41 for me.