Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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


VALUE2


b


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


VALUE2


b


WITH data (list) AS (
  SELECT sys.odcivarchar2list('a', 'b', 'c') FROM DUAL
)
SELECT v.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


VALUE2


b


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;


VALUE2


b


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
)
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


VALUE2


b

b

b


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
)
SELECT (
         SELECT COLUMN_VALUE
         FROM   TABLE(d.list)
         OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
       ) AS value2
FROM   data d


VALUE2


b

b

b


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
)
SELECT v.value
FROM   data d
       CROSS JOIN LATERAL (
         SELECT COLUMN_VALUE AS value
         FROM   TABLE(d.list)
         OFFSET 1 ROW FETCH NEXT 1 ROW ONLY
       ) v


VALUE


b

b

b


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
)
SELECT v.value
FROM   data d
       CROSS JOIN LATERAL (
         SELECT ROWNUM AS idx, COLUMN_VALUE AS value
         FROM   TABLE(d.list)
       ) v
WHERE  idx = 2;


VALUE


b

e

h