long dynamic SQL statement (Pro*C)

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 Received on Thu Sep 29 2005 - 10:25:14 CDT