Pass subquery value to IN statement

In one table named prefs, I have a column named “Value” of type clob that holds this value: ‘T’, ‘L’

I need to query table attendance_code to retrieve the records where column att_code values are either T or L. The att_code column is of type varchar2.

As a manual model query, this works fine:

SELECT id FROM attendance_code ac WHERE ac.att_code IN ('T', 'L')

Result:
  ID
1 4903
2 4901

Attempt 1

SELECT id FROM attendance_code ac WHERE ac.att_code IN (SELECT value from prefs WHERE name = 'AEADS|SAR|tdyCodes')

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

Attempt 2

SELECT id FROM attendance_code ac WHERE ac.att_code IN (SELECT dbms_lob.substr(value, 4000, 1) from prefs WHERE name = 'AEADS|SAR|tdyCodes')

This yields no error, but returns no rows.

Attempt 3 (Based on https://blogs.oracle.com/aramamoo/how-to-split-comma-separated-string-and-pass-to-in-clause-of-select-statement)

select id from attendance_code ac where ac.att_code IN (
    select regexp_substr(value,'[^,]+', 1, level) from prefs WHERE name = 'AEADS|SAR|tdyCodes' 
    connect by regexp_substr(value, '[^,]+', 1, level) is not null );

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

Attempt 4

select id from attendance_code ac where ac.att_code IN (
    select regexp_substr(dbms_lob.substr(value, 4000, 1),'[^,]+', 1, level) from prefs WHERE name = 'AEADS|SAR|tdyCodes' 
    connect by regexp_substr(dbms_lob.substr(value, 4000, 1), '[^,]+', 1, level) is not null );

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

I have also tried changing the value in the prefs table to just T,L (removing the single quotes) and running all of the above against that, to no avail.

What is the proper way to do this, please?

Answer

You can use hierarchical query with removed single quotes as follows:

SELECT ID
FROM ATTENDANCE_CODE AC
WHERE AC.ATT_CODE IN (
        SELECT
            REPLACE(TRIM(REGEXP_SUBSTR(
            (SELECT value from prefs WHERE name = 'AEADS|SAR|tdyCodes'), 
            '[^,]+', 1, LEVEL)), '''', '') FROM DUAL
        CONNECT BY REGEXP_SUBSTR(
            (SELECT value from prefs WHERE name = 'AEADS|SAR|tdyCodes'), 
            '[^,]+', 1, LEVEL) IS NOT NULL
    );

or simplify the above query with the CTE as following:

WITH DATAA ( VALS ) AS (
    SELECT VALUE
      FROM PREFS
     WHERE NAME = 'AEADS|SAR|tdyCodes'
)
SELECT ID
FROM ATTENDANCE_CODE AC,
     DATAA D
WHERE AC.ATT_CODE IN (
        SELECT
            REPLACE(TRIM(REGEXP_SUBSTR(D.VALS, '[^,]+', 1, LEVEL)), '''', '')
        FROM DUAL
        CONNECT BY
            REGEXP_SUBSTR(D.VALS, '[^,]+', 1, LEVEL) IS NOT NULL
    );

Example with Dual as following:

SQL> SELECT DUMMY FROM DUAL AC
  2  WHERE
  3      'T' IN (
  4          SELECT
  5              REPLACE(TRIM(REGEXP_SUBSTR(
  6              (SELECT q'#'T', 'L'#' FROM DUAL), '[^,]+', 1, LEVEL)), '''', '')
  7              FROM DUAL
  8          CONNECT BY
  9              REGEXP_SUBSTR((
 10                  SELECT q'#'T', 'L'#' FROM DUAL), '[^,]+', 1, LEVEL) IS NOT NULL
 11      );

DUMMY
-------
X

SQL>

Cheers!!