1

I have a scenario in which i need to pass 'name ids' in CSV format string as bind parameter to a SQL Query, and check for if any of that id is present in a table. Its kind of XOR of array of ids with the table name-id in a column in table .

I am not able to get how this can be done(array to column comparision). Could you point me in that direction?

I cannot use Java , or PL/Sql as my query would be used by another team which does processing in runtime.

Himz
  • 523
  • 1
  • 5
  • 16

2 Answers2

2

if you bind multiple values, not single string, you can make a table from your csv values:

select column_value as id 
from table(sys.dbms_debug_vc2coll('linia 1','linia 2','linia 3'))

And use a statement like this to make the xor

select a.id,b.id 
from 
  table a
  full outer join
    (query)b
  on a.id=b.id

Hope this helps you.

UPDATE: I found here a query making a table from a value in csv format:

with query2 as (
    select substr(:mylist,
                  instr(','||:mylist||',', ',', 1, rn),
                  instr(','||:mylist||',', ',', 1, rn+1)
                  - instr(','||:mylist||',', ',', 1, rn) - 1) value
    from (select rownum rn from dual 
         connect by level 
                       <= length(:mylist)-length(replace(:mylist,',',''))+1)
    )
  select value
  from query2
Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
0

in oracle 10g and above You can convert a single csv parameter (mycsv) to columns using :

SELECT REGEXP_SUBSTR(mycsv,'[^,]+',1,LEVEL) element                           
FROM dual                       
CONNECT BY LEVEL < LENGTH(REGEXP_REPLACE(mycsv,'[^,]+')) + 1

Then use the IN clause and the above as a sub-query

SELECT * FROM t1 
WHERE id IN ( ....the query above... )
Kevin Burton
  • 11,676
  • 2
  • 24
  • 37