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 |