Oracle - Number to varchar

oracle, plsql, to-char

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

Problem

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?

Original source