Oracle CAST 到 NVARCHAR2 上的奇怪行为
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16735793/
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
Strange behavior on Oracle CAST to NVARCHAR2
提问by Teejay
I have the following query:
我有以下查询：
SELECT (CAST("META_SECT_ORDER" AS NVARCHAR2(3)) || '#' || "CODE") AS "SECTION"
FROM "BMAN_TP2"."LOADER_TEMPLATE_SECTIONS"
META_SECT_ORDERis obviously in integer, while CODEis a string.
META_SECT_ORDER显然是整数，CODE而是字符串。
It outputs 700 rows like these:
它输出 700 行，如下所示：
SECTION
-------
0#F01
0#I05
1#I05
2#I05
etc...
等等...
I know that CASTis not necessary but the query is composed by an underlying querybuilder that also outputs for MsSQL Server (where the CASTis needed) and Postgres.
我知道这CAST不是必需的，但查询是由底层查询构建器组成的，该查询构建器还为 MsSQL Server（CAST需要的地方）和 Postgres 输出。
The strange fact is that if I raise the NVARCHAR2length I obtain different results. For example:
奇怪的事实是，如果我增加NVARCHAR2长度，我会得到不同的结果。例如：
with values <= 80, I get the correct result (see above)
with a value of 81, I get 700 rows of
??醴???凨?昁菄??譕貉??莀鐋with a value of 82, I get 700 rows of
??with odd values, in [83-127], I get 700 empty rows
with even values, in [84-128], I get 700 rows of
?with values >= 129, I get
ORA-03113: end-of-file on communication channel
值 <= 80，我得到正确的结果（见上文）
值为 81，我得到 700 行
??醴???凨?昁菄??譕貉??莀鐋值为 82，我得到 700 行
??对于奇数值，在 [83-127] 中，我得到 700 个空行
使用偶数值，在 [84-128] 中，我得到 700 行
?值 >= 129，我得到
ORA-03113: end-of-file on communication channel
What's that??
那是什么？？
EDIT :
编辑 ：
Actually, It seems that it depends only on the CAST, the concatenation is not relevant.
实际上，它似乎只取决于CAST，连接无关紧要。
采纳答案by Jon Heller
This looks like "Bug 9949330 - ORA-7445 or garbled data casting a NUMBER to NVARCHAR2". You've already discovered the official work-around, use values <= 80.
这看起来像“错误 9949330 - ORA-7445 或将 NUMBER 转换为 NVARCHAR2 的乱码数据”。您已经发现了官方的解决方法，使用值 <= 80。
You should contact support to either download the patch or request one for your platform.
您应该联系支持人员下载补丁或为您的平台申请补丁。
Here's an easier way to reproduce the issue without using any of your data. It still fails as of 11.2.0.3.
这是在不使用任何数据的情况下重现问题的更简单方法。从 11.2.0.3 开始，它仍然失败。
SQL> select cast(level as nvarchar2(130)) from dual connect by level <= 1;
CAST(LEVELASNVARCHAR2(130))
--------------------------------------------------------------------------------
?????????┐┐┐ ┐┐ ┐┐A ┐┐ ┐┐A ┐┐ A ┐┐ ┐ ┐┐ ┐┐A
