Initial setup: Embedded SQL/C Client (Pro*C) accessing Oracle Server (8, 9,
or 10)

Problem: In my client program I have (from earlier processing) a very long
list of values for given WHERE criteria (10s of thousands or more). I would
like to run a dynamic SQL statement like "SELECT * FROM table WHERE
criterion IN (value0, value1, ..., valueN)". I realize this won't be
possible because the length of a dynamic SQL statement is limited. I
definitely don't want to run 10s of thousands of individual SQL statements
because of performance reasons.

Any ideas how this could be done efficiently and elegantly? In other
projects I have worked around this problem by keeping the list of values in
a temporary table to be joined with on the server side. However, this only
makes sense if the list of values has been determined on the server side in
the first place; I doubt that it would make sense to set up a temporary
table to be filled from the client side just for this one statement.

How is this commonly done? I mean it sounds like such an everyday problem
that someone must have come up with a simple solution. Awfully hard to
google for because the search terms are so generic. Any hints are greatly
appreciated

Marcel