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?
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