Oracle pro*c way to query DB for difference of array values and DB column values

Question

I have c/c++ code with embedded SQL for Oracle through Pro*C. Is there any mechanism to get the difference of values of array values and DB column values? For example, say, I have an array like this:

int nums[] = {10,20,35,45};
vector<int> vnums (nums, nums + sizeof(nums) / sizeof(int) );

Now, I have a DB table tbl1 with col1 containing values:

20
40
60

I would like to get the unmatched array values that are not present in tbl1. So, result should be:

10
35
45

I know one way. I may run the following SQL query:

select col1 from tab1

And store the results in an vector say, vec2. Now, I see the difference of these two vectors vnums and vec2. Can you suggest a better way?

Answer 1

Unfortunately, there isn't a way to directly do that and your approach is fine.

What you can do is to wrap the call to the DB with a stream and create a procedure which loading your input values in using something like:

CREATE OR REPLACE TYPE numArray_t AS TABLE OF NUMBER
CREATE OR REPLACE FUNCTION extractDif (p_data IN numArray_t) return numArray_t;

and in the function you will perform a MINUS of the passed numArray_t with the values from your table and stream the differences.