what is the difference between ODCIVARCHAR2LIST and ODCINUMBERLIST?

Question

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 ?

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:

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:

abc-xyz-aaa-ab1-2db-a-bbb
abc-www-ffs-cvx-azx-a-ggg
...
...

Answer 1

There is probably typo in your snippet,I assume you use MULTISET not 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 = 1 means that a VARCHAR2 was returned

The similar example with 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.

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