Oracle 18c SQL如何按指定索引获取ODCIVARCHAR2LIST元素值

问题说明 #

学习Oracle 18c的ODCIVARCHAR2LIST类型时，需要从定义好的列表中提取指定索引位置的元素，例如从以下查询返回的列表中取出第二个值b：

select 
    sys.odcivarchar2list('a', 'b', 'c') as my_list
from 
    dual

直接使用my_list(2)的写法会执行失败，报错信息如下：

select
    my_list(2)
from
    cte

ORA-00904: "MY_LIST": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 8 Column: 5

可用实现方案 #

SYS.ODCIVARCHAR2LIST是Oracle内置的VARCHAR2类型嵌套表，按下标取值有两种常用写法：

• 直接按下标读取（最简洁）
  报错的核心原因是访问嵌套表列的元素时，必须加表别名做限定，否则解析器会把列名(下标)的格式识别成自定义函数调用，找不到对应函数就会抛标识符无效的错误。修正后的写法如下，注意Oracle嵌套表默认下标从1开始：

with cte as (
    select sys.odcivarchar2list('a', 'b', 'c') as my_list from dual
)
select c.my_list(2) as second_value
from cte c;

执行以上语句会直接返回第二个元素b。

• 拆表后按序号取值（适合复杂场景）
  如果需要同时取多个位置的元素、或者需要处理动态长度的数组，可以用table()函数把数组拆成多行，再结合序号筛选：

with cte as (
    select sys.odcivarchar2list('a', 'b', 'c') as my_list from dual
),
unnested_list as (
    select
        column_value as item,
        rownum as item_index
    from cte, table(cte.my_list)
)
select item as second_value
from unnested_list
where item_index = 2;

注意：不要按照多数编程语言从0开始计数的习惯传下标，传1会取到第一个元素a，传2才是第二个元素b。

内容的提问来源于stack exchange，提问作者User1974