How to convert CLOB to varchar , and separate them by comma

Question

Writing a plSQL script in oracle . DM_CATEGORY column's data type is varchar . othertext column's datatype is CLOB, which return a comma separated data. e.g. FGOH,functional-parameter,disability-parameter,cognitive-parameter

Script where DM_CATEGORY in (select othertext from Preslists where tablename='tablename' and functionname='functionname' )

How to pass this data in this in cluse? getting this below error with this script ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s"

Writing a plSQL script in oracle . DM_CATEGORY column's data type is varchar . othertext column's datatype is CLOB, which return a comma separated data. e.g. FGOH,functional-parameter,disability-parameter,cognitive-parameter

Script where DM_CATEGORY in (select othertext from Preslists where tablename='tablename' and functionname='functionname' )

How to pass this data in this in cluse? getting this below error with this script ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s"

Answer 1

Try to convert the CLOB to varchar: Script: where to_char(DM_CATEGORY) in (select othertext from Preslists where tablename='tablename' and functionname='functionname' )

or: dbms_lob.substr(DM_CATEGORY, 4000, 1)

while "4000" is the Varchar defined size.

Answer 2

Maybe DBMS_LOB.INSTR() function could help:

WITH    --  S a m p l e    D a t a :
    categories (ID, DM_CATEGORY) AS
        ( Select 1, 'Category 1' From Dual Union All
          Select 2, 'Category 2' From Dual Union All
          Select 3, 'Category 3' From Dual Union All
          Select 4, 'Category 4' From Dual 
        ),
    preslists AS
        ( Select 101 "ID", 'TBL_1' "TABLENAME", 'FUNC_1' "FUNCTIONNAME", TO_CLOB('Category 1,FGOH,Category 2, functional-parameter, something else') "OTHERTEXT" From Dual Union All
          Select 102 "ID", 'TBL_2' "TABLENAME", 'FUNC_2' "FUNCTIONNAME", TO_CLOB('Category A,Category B,Category 3,something else') "OTHERTEXT" From Dual
        )

Used in Where clause

--    S Q L   (condition in Where clause)
Select     c.ID, c.DM_CATEGORY
From       categories c
Inner Join preslists p ON(p.TABLENAME = 'TBL_2' And 
                          p.FUNCTIONNAME = 'FUNC_2' )
Where      DBMS_LOB.InStr(',' || p.OTHERTEXT || ',', ',' || c.DM_CATEGORY || ',') > 0

... or with condition in Join's ON clause

--    S Q L   (condition in Join's ON clause)
Select     c.ID, c.DM_CATEGORY
From       categories c
Inner Join preslists p ON(p.TABLENAME = 'TBL_2' And 
                          p.FUNCTIONNAME = 'FUNC_2' And 
                          DBMS_LOB.InStr(',' || p.OTHERTEXT || ',', ',' || c.DM_CATEGORY || ',') > 0)

/*    R e s u l t :
        ID DM_CATEGORY
---------- -----------
         3 Category 3     */

NOTE:
There are commas added at start and at the and of CLOB to be used when compared to your DM_CATEGORY column value (also with commas before and after) to avoid accidentaly wrong comparisons.Without it you could match eg. 'Category 3' with 'Category 333'. This way you are looking for ',Category 3,'.

Answer 3

You can use LIKE with a sub-query and EXISTS:

SELECT *
FROM   table_name t
WHERE  EXISTS(
         SELECT 1
         FROM   Preslists p
         WHERE  p.tablename = 'tablename' 
         AND    p.functionname='functionname'
         AND    ',' || p.othertext || ',' LIKE '%,' || t.DM_CATEGORY || ',%'
       );

or DBMS_LOB.INSTR:

SELECT *
FROM   table_name t
WHERE  EXISTS(
         SELECT 1
         FROM   Preslists p
         WHERE  p.tablename = 'tablename' 
         AND    p.functionname='functionname'
         AND    DBMS_LOB.INSTR(
                  ',' || p.othertext || ',',
                  ',' || t.DM_CATEGORY || ','
                ) > 0
       );

Which, for the sample output:

CREATE TABLE table_name (dm_category) AS
SELECT 'W' FROM DUAL UNION ALL
SELECT 'X' FROM DUAL UNION ALL
SELECT 'Y' FROM DUAL UNION ALL
SELECT 'Z' FROM DUAL;

CREATE TABLE preslists (othertext, tablename, functionname) AS
SELECT EMPTY_CLOB()
       || RPAD('A', 4000, 'A')
       || RPAD(',', 4000, 'B')
       || ',X,Y',
       'tablename',
       'functionname'
FROM   DUAL UNION ALL
SELECT EMPTY_CLOB()
       || RPAD('C', 4000, 'C')
       || RPAD(',', 4000, 'D')
       || ',Z,Y',
       'tablename',
       'functionname'
FROM   DUAL;

Both outputs:

DM_CATEGORY
X
Y
Z

Note: The Y value is not repeated even though it appears in both matching lists in Preslists; which is the same behaviour as if a value appears twice in an IN list.

fiddle