将数字转换为字符串后，TO_char返回斜杠值 [英] TO_char returning slash value after converting a number to String
问题描述
我有一个数据库列amount [Data type Number(32,12)].当我在amount字段上使用to_char时，我会在输出中附加一个斜杠值.
I am having a database column amount [Data type Number(32,12)].When i use to_char on the amount field i get a slash value appended in the output.
当我直接使用存储在金额字段中的值时，我得到了正确的值
When i directly used the value stored in the amount field ,i am getting the correct value
select TO_Char(0.000000000099,'FM99999999999999999999999999999990.099999999999') from dual;
输出:-
0.000000000099
推荐答案
似乎您的表中的数据已损坏.这就引出了一些问题，包括它是如何到达那里的，您能对此做些什么?
It looks like you have corrupted data in your table. Which leads to a few questions including how did it get there, and what can you do about it?
错误的数字(或日期)值通常来自OCI程序，但是有些错误报告建议
Corrupt numeric (or date) values often come from OCI programs, but there are some bug reports that suggest imp has been known to cause corruption. The internal representation is documented in support note 1007641.6, but I find something like this explanation easier to work with when recreating problems, and using a PL/SQL block is possible in place of an OCI program.
遇到问题的两个数字应该在内部这样表示:
The two numbers you're having problems with should be represented internally like this:
select dump(0.000000000099, 16) as d1,
dump(0.000000001680, 16) as d2
from dual;
D1 D2
------------------ ---------------------
Typ=2 Len=2: bb,64 Typ=2 Len=3: bc,11,51
我还没有弄清楚表中到底有什么值，但是我可以显示类似的结果:
I haven't figured out exactly what values you have in your table, but I can show a similar result:
create table t42 (amount number(32,12)) nologging;
declare
n number;
begin
dbms_stats.convert_raw_value('bb65', n);
insert into t42 (amount) values (n);
dbms_stats.convert_raw_value('bc100000', n);
insert into t42 (amount) values (n);
end;
/
转储值表明它们看起来有些奇怪:
Dumping the values shows they look a bit odd:
column d1 format a25
column d2 format a25
select amount, dump(amount) d1, dump(amount, 16) d2
from t42;
AMOUNT D1 D2
--------------------------- ------------------------- -------------------------
0.00000000010 Typ=2 Len=2: 187,101 Typ=2 Len=2: bb,65
0.000000001499 Typ=2 Len=3: 188,16,0 Typ=2 Len=3: bc,10,0
对其进行格式化会得到类似的结果:
Running your formatting against that gives similar results:
select amount as actual__________amount,
TO_CHAR(amount,'FM99999999999999999999999999999990.099999999999')
as amount__________Changed
from t42
order by amount;
ACTUAL__________AMOUNT AMOUNT__________CHANGED
--------------------------- ----------------------------------------------
0.00000000010 ##############################################
0.000000001499 0.00000000150/
如果您可以将自己的数据的dump()输出添加到问题中，那么我可以确定是否可以完全重新创建您所看到的值.
If you can add the dump() output for your own data to the question then I can see if I can recreate exactly the values you're seeing.
也许，可以通过更新数据来纠正"此问题，例如:
Anecdotally, it might be possible to 'correct' this by updating the data, e.g.:
update t42 set amount = amount * 1;
select amount, dump(amount) d1, dump(amount, 16) d2
from t42;
AMOUNT D1 D2
--------------------------- ------------------------- -------------------------
0.0000000001 Typ=2 Len=2: 188,2 Typ=2 Len=2: bc,2
0.000000001499 Typ=2 Len=3: 188,15,100 Typ=2 Len=3: bc,f,64
select amount as actual__________amount,
TO_CHAR(amount,'FM99999999999999999999999999999990.099999999999')
as amount__________Changed
from t42
order by amount;
ACTUAL__________AMOUNT AMOUNT__________CHANGED
--------------------------- ----------------------------------------------
0.0000000001 0.0000000001
0.000000001499 0.000000001499
但是，您必须询问实际的正确值是多少，这可能会回溯到如何/为什么/何时损坏它.如果这些数据非常重要，我将非常谨慎，并且真的必须接受@DazzaL的建议才能让Oracle支持部门对其进行整理.
However, you have to ask what the actual correct value is, which probably comes back to how/why/when it was corrupted. I would be very wary of touching this data if it is at all important, and would really have to second @DazzaL's advice to get Oracle Support involved to sort it out.
这篇关于将数字转换为字符串后，TO_char返回斜杠值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
