Oracle - 数字到 varchar
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1588581/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle - Number to varchar
提问by Tom
I have a table containing a column of type Number
我有一个包含类型为 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
在 PL SQL 脚本中，我想将该字段转换为 varchar。但是，我不知道它的长度，所以我得到了一个例外
Exception message is ORA-06502: PL/SQL: numeric or value error: character string buffer too small
异常消息是 ORA-06502：PL/SQL：数字或值错误：字符串缓冲区太小
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?
我应该如何声明 v_some_field 缓冲区？将它设置为 varchar(32767) 似乎很残忍，有没有其他选择？
回答by Vincent Malgrat
you're getting an error not because the number is too large but because the result of your to_charis 22 characters long (21x"9"+one character for the sign):
您收到错误不是因为数字太大，而是因为您的结果to_char是 22 个字符长（21x"9"+ 一个字符作为符号）：
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
回答by Juergen Hartelt
You could determine the maximum length of your converted varchar2 by converting a negative value with integral and fractional digits:
您可以通过转换带有整数和小数位的负值来确定转换后的 varchar2 的最大长度：
set serveroutput on
declare
n number;
begin
n := -4/3;
dbms_output.put_line(length(to_char(n)));
end;
/
Output is 41 for me.
输出对我来说是 41。
