sqloracle-databaseora-00932

convert string into rows with SQL

I have a string, like:

'one,two,three'

and I want to convert it in rows and use it into IN clause in an SQL:

one
two
three

I tried something like :

SELECT column_value 
  FROM XMLTable('"one","two","three"');

and it worked fine but in a join condition it fails.

SELECT 1 
  FROM dual 
 WHERE 'one' IN (SELECT column_value 
                   FROM XMLTable('"one","two","three"'));

it gaves me the error:

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

Can anyone help me on this, please?

NOTE: I would like not use PLSQL

Solution

  • What you need is nothing but just casting a CLOB value to a [VAR]CHAR[2] data type such as

    SELECT 1 
  FROM dual 
 WHERE 'one' IN (SELECT CAST(column_value AS VARCHAR2(20))
                   FROM XMLTable('"one","two","three"'))

1
---
  1

    in order to make it comparable with a literal(such as 'one').

    Moreover, CAST might be replaceable with XMLCast as well.