Get value from ODCIVARCHAR2LIST at a specific index position (in a SQL query)

Question

I'm trying to learn about ODCIVARCHAR2LISTs in Oracle 18c.

With the following list, how can I get a value at a specific index position (in a SQL query)?

For example, get the second value in the list: b.

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

---

My understanding is:

It's not possible to extract list elements by index directly in SQL, like this:

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

Answer 1

You can use:

WITH data (list) AS (
  SELECT sys.odcivarchar2list('a', 'b', 'c') FROM DUAL
)
SELECT (
         SELECT *
         FROM   (
           SELECT COLUMN_VALUE
           FROM   TABLE(d.list)
           WHERE  ROWNUM <= 2
           ORDER BY ROWNUM DESC
         )
         WHERE ROWNUM = 1
       ) AS value2
FROM   data d

or

WITH data (list) AS (
  SELECT sys.odcivarchar2list('a', 'b', 'c') FROM DUAL
)
SELECT (
         SELECT COLUMN_VALUE
         FROM   TABLE(d.list)
         OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
       ) AS value2
FROM   data d

or:

WITH data (list) AS (
  SELECT sys.odcivarchar2list('a', 'b', 'c') FROM DUAL
)
SELECT value2
FROM   data d
       CROSS JOIN LATERAL (
         SELECT COLUMN_VALUE AS value2
         FROM   TABLE(d.list)
         OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
       ) v

or

WITH data (list) AS (
  SELECT sys.odcivarchar2list('a', 'b', 'c') FROM DUAL
)
SELECT v.value AS value2
FROM   data d
       CROSS JOIN LATERAL (
         SELECT ROWNUM AS idx, COLUMN_VALUE AS value
         FROM   TABLE(d.list)
       ) v
WHERE  idx = 2;

Which all output:

VALUE2
b

However

If you have multiple input rows:

WITH data (list) AS (
  SELECT sys.odcivarchar2list('a', 'b', 'c') FROM DUAL UNION ALL
  SELECT sys.odcivarchar2list('d', 'e', 'f') FROM DUAL UNION ALL
  SELECT sys.odcivarchar2list('g', 'h', 'i') FROM DUAL
)
...

then the first 3 queries all repeat the value from the first row and would output:

VALUE2
b
b
b

It is only the last query that will return:

VALUE2
b
e
h

db<>fiddle here

Answer 2

One option would be:

Use a custom PL/SQL function.

with 
function get_list_val(my_list sys.odcivarchar2list, idx pls_integer) return varchar2
is
begin
  return my_list(idx);
end;

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

5 PL/SQL Collections and Records

In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: variable_name(index).