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?

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

Don't store delimited values in a string; put them in another table (where you can have one row per item) with a foreign key referencing to the row in the parent table (for example: db<>fiddle) or use a collection and a nested table.

An example of this second option is:

CREATE TYPE char_list IS TABLE OF CHAR(1);

Then

CREATE TABLE prefs (
  id   NUMBER(10,0) PRIMARY KEY,
  name VARCHAR2(50) NOT NULL UNIQUE,
  SchoolId NUMBER(10,0),
  UserId   NUMBER(10,0),
  Value    char_list,
  YearID   NUMBER(4,0)
) NESTED TABLE value STORE AS prefs__value;

So for your sample data:

INSERT INTO prefs ( id, name, schoolId, UserId, value, YearID )
VALUES ( 262806, 'AEADS|SAR|tdyCodes', 109, 0, char_list( 'T', 'L' ), 29 );

CREATE TABLE attendance_code ( id, att_code ) AS
SELECT 4903, 'L' FROM DUAL UNION ALL
SELECT 4902, 'X' FROM DUAL UNION ALL
SELECT 4901, 'T' FROM DUAL;

Your query would be:

SELECT *
FROM   attendance_code
WHERE  att_code MEMBER OF ( SELECT value FROM prefs WHERE name = 'AEADS|SAR|tdyCodes' );

Which outputs:

> <pre>
> | ID |
> | ---: |
> | 4903 |
> | 4901 |
> </pre>

db<>fiddle here