In one table named prefs, I have a column named "Value" of type clob that holds this value: 'T', 'L'

[![Screenshot showing prefs record which holds lookup values](https://i.stack.imgur.com/M4h5j.png)](https://i.stack.imgur.com/M4h5j.png)

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?