By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR';
CREATE TABLE clob_tab (col1) as
SELECT EMPTY_CLOB()
|| '27,88,100,25,26,210,2,3'
|| LISTAGG(','||(2999+LEVEL)) WITHIN GROUP (ORDER BY LEVEL)
FROM DUAL
CONNECT BY LEVEL <= 800
UNION ALL
SELECT EMPTY_CLOB() || LISTAGG(LEVEL, ',') WITHIN GROUP (ORDER BY LEVEL)
FROM DUAL
WHERE LEVEL < 20
OR LEVEL > 30
OR LEVEL IN (23, 25, 27)
CONNECT BY LEVEL <= 200;
2 rows affected
create table abc (col2) as
select * from table(sys.odcinumberlist(21,22,23,24,25,26,27,28,29,30));
10 rows affected
SELECT SYSTIMESTAMP FROM DUAL;
| SYSTIMESTAMP |
|---|
| 2024-03-27 11:53:02.652081 +00:00 |
WITH bounds ( value, start_pos, end_pos ) AS (
SELECT col1, 1, INSTR( col1, ',' ) FROM clob_tab
UNION ALL
SELECT value, end_pos + 1, INSTR( value, ',', end_pos + 1 )
FROM bounds
WHERE end_pos > 0
),
terms (term) AS (
SELECT TO_CHAR(
CASE end_pos
WHEN 0
THEN SUBSTR(value, start_pos)
ELSE SUBSTR(value, start_pos, end_pos - start_pos)
END
)
FROM bounds
)
select *
from abc
where col2 not in (SELECT term FROM terms);
| COL2 |
|---|
| 29 |
| 22 |
| 21 |
| 28 |
| 30 |
| 24 |
SELECT SYSTIMESTAMP FROM DUAL;
| SYSTIMESTAMP |
|---|
| 2024-03-27 11:53:02.821624 +00:00 |
select *
from abc
where col2 not in (
select to_number(x.column_value)
from clob_tab
cross join xmltable (col1) x
);
| COL2 |
|---|
| 21 |
| 22 |
| 24 |
| 28 |
| 29 |
| 30 |
SELECT SYSTIMESTAMP FROM DUAL;
| SYSTIMESTAMP |
|---|
| 2024-03-27 11:53:03.478771 +00:00 |
select *
from abc
where col2 not in (
select x.n
from clob_tab
cross join json_table (json_array(col1 format json RETURNING CLOB), '$[*]' columns n number path '$') x
);
| COL2 |
|---|
| 29 |
| 22 |
| 21 |
| 28 |
| 30 |
| 24 |
SELECT SYSTIMESTAMP FROM DUAL;
| SYSTIMESTAMP |
|---|
| 2024-03-27 11:53:03.488398 +00:00 |
SELECT *
FROM Abc a
WHERE NOT EXISTS(
SELECT 1
FROM clob_tab c
WHERE ',' || c.col1 || ',' LIKE '%,' || a.Col2 || ',%'
)
| COL2 |
|---|
| 21 |
| 22 |
| 24 |
| 28 |
| 29 |
| 30 |
SELECT SYSTIMESTAMP FROM DUAL;
| SYSTIMESTAMP |
|---|
| 2024-03-27 11:53:03.498004 +00:00 |