Oracle 18c

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:39:36.900659 +00:00


/*
This causes the run to fail as it is too slow.

with cte (n) as (
  SELECT term
  FROM   clob_tab
         CROSS JOIN LATERAL (
           SELECT to_number(regexp_substr(col1, '(.*?)(,|$)', 1, level, null, 1))
                    AS term
           FROM   DUAL
           connect by level < regexp_count(col1, '(.*?)(,|$)')
         )
)
select *
from abc
where col2 not in (
  select n from cte
);
*/
ORA-01742: comment not terminated properly

SELECT SYSTIMESTAMP FROM DUAL;


SYSTIMESTAMP


2024-03-27 11:39:36.902271 +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:39:37.572460 +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:39:37.578360 +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:39:37.585134 +00:00