ORA-00932: inconsistent datatypes: expected – got CLOB – while using clob in regexp_substr function

I am trying to use CLOB variable in regexp_substr function as below

UPDATE TableName
SET Tab_DATE = SYSDATE
WHERE Tab_ID IN (
    select regexp_substr(clob_variable,'[^~]+', 1, level) from dual
    connect by regexp_substr(clob_variable, '[^~]+', 1, level) is not null
)

clob_variable is of type CLOB and contains ~ separated ids.

While executing the update statement I am getting below error:

ORA-00932: inconsistent datatypes: expected - got CLOB

Can we use CLOB with RegExp? If not, is there any way to convert CLOB values to tabular format?

Answer

Try this:

UPDATE TableName
SET Tab_DATE = SYSDATE
WHERE Tab_ID IN (
    select replace(dbms_lob.substr(regexp_substr(clob_variable,'[^~]+~', 1, level)), '~', '') from dual
    connect by dbms_lob.compare(regexp_substr(clob_variable,'[^~]+~', 1, level), empty_clob() ) != 0
)