how does a SQL query (Pro*C) be able to find set o

Question

I have a C/C++ program with embedded Oracle SQL calls in proc (11.2+ version). I have a list of values (int) as an array. I would like to check through a SQL query (as proc code) which values are not in a DB table. For example, say, I have values: 10, 20, 30, 40, 50 stored in an array and my DB table t1 has column col1 that has 10, 20, 40. So,

Select col1 from t1

will provide me:

10
20
40

So, I am looking for the excluded values, i.e. 30, 50. Can I do this through an embedded Pro*C SQL query? My list is quite large and the DB table has many values.

Answer 1

You need to set up your values in a table. Here is a typical way what will work in most databases:

with list as (
      select 10 as n union all select 20 union all select 30 union all
      select 40 union all select 50
     )
select l.*
from list l
where not exists (select 1 from t1 where col1 = l.n);

The syntax might vary depending on the database (from dual, a subquery instead of a CTE), but the idea is the same.

Answer 2

In Oracle you could use a collection as an alternative to creating a CTE/inline view using unioned queries:

select l.column_value as missing
from table(sys.odcinumberlist(10, 20, 30, 40, 50)) l
where not exists (select 1 from t1 where col1 = l.column_value);

   MISSING
----------
        20
        30
        50

SQL Fiddle.

Here sys.odcinumberlist is a built-in collection type, to save you creating your own table type.

Aside from being slightly easier to type the values in like that (IMO), you can also use this approach from a client application by passing the values as an array, which can be useful. There are examples here for OCI (C/C++) or here for Java.

Answer 3

SELECT regexp_substr('10|20|30|40|50', '[^|]+', 1, ROWNUM) var
FROM dual
CONNECT BY LEVEL <= length(regexp_replace('10|20|30|40|50', '[^|]', '')) + 1
MINUS
SELECT col1 FROM t1;