oracle ODCIVARCHAR2LIST 和 ODCINUMBERLIST 有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34479090/
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
what is the difference between ODCIVARCHAR2LIST and ODCINUMBERLIST?
提问by user1508682
I'm using ODCIVARCHAR2LIST and ODCINUMBERLIST on varchar characters and both functions works and the result is the same. I was wondering if you could explain why i'm not getting an error when using ODCINUMBERLIST? what is my motivation to use ODCIVARCHAR2LIST ?
我在 varchar 字符上使用 ODCIVARCHAR2LIST 和 ODCINUMBERLIST 并且这两个函数都有效并且结果相同。我想知道您是否可以解释为什么我在使用 ODCINUMBERLIST 时没有收到错误消息？我使用 ODCIVARCHAR2LIST 的动机是什么？
Thanks
谢谢
Sorry for not adding my code. I'm using the above functions as a way to brake xml values into rows. after extracting them and "clean" them from all unnecessary characters, each singl value need to looks like this: abc-xyz-aaa-ab1-2db-a-bbb. Then I use this code:
抱歉没有添加我的代码。我使用上述函数作为将 xml 值转换为行的一种方式。在提取它们并从所有不必要的字符中“清除”它们之后，每个单值需要看起来像这样：abc-xyz-aaa-ab1-2db-a-bbb。然后我使用这个代码：
select *
from TABLE(CAST (MULTICAST (SELECT LEVEL FROM DUAL
CONNECT BY LEVEL < REGEX_COUNT (my_field, 'regex_exp')) as SYS.ODCIVARCHAR2LIST
and if I use SYS.ODCINUMBERLIST i get the same result:
如果我使用 SYS.ODCINUMBERLIST 我得到相同的结果：
abc-xyz-aaa-ab1-2db-a-bbb
abc-www-ffs-cvx-azx-a-ggg
...
...
abc-xyz-aaa-ab1-2db-a-bbb
abc-www-ffs-cvx-azx-a-ggg
...
...
回答by Marmite Bomber
There is probably typo in your snippet,I assume you use MULTISETnot MULTICAST
您的代码段中可能有错字，我假设您MULTISET没有使用MULTICAST
Here your simplified example
这是您的简化示例
select dump(COLUMN_VALUE,16)
from TABLE(CAST (MULTISET(SELECT LEVEL FROM DUAL
CONNECT BY LEVEL <= 2) as SYS.ODCIVARCHAR2LIST ))
returning
回来
Typ=1 Len=1: 31
Typ=1 Len=1: 32
The typ = 1means that a VARCHAR2 was returned
的典型值= 1个的装置，一个VARCHAR2返回
The similar example with OdciNumberList
类似的例子 OdciNumberList
select dump(COLUMN_VALUE,16)
from TABLE(CAST (MULTISET(SELECT LEVEL FROM DUAL
CONNECT BY LEVEL <= 2) as SYS.OdciNumberList ))
returns
回报
Typ=2 Len=2: c1,2
Typ=2 Len=2: c1,3
here type 2 is NUMBER as expected.
这里的类型 2 是预期的 NUMBER。
The implicit conversion rules are valid here as well, i.e. string with well formatted number may be processed as numbers.
隐式转换规则在这里也有效，即具有格式良好的数字的字符串可以作为数字处理。
SELECT dump(COLUMN_VALUE,16)
FROM TABLE(sys.OdciNumberList('42','43'));
Typ=2 Len=2: c1,2b
Typ=2 Len=2: c1,2c
