Oracle – Number to varchar

oracleplsqlto-char

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?

Best Solution

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