Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: long dynamic SQL statement (Pro*C)
"Marcel Hohenberger" <appshome_at_geocities.com> wrote in message
news:3q2fasFcr33kU1_at_individual.net...
> 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
>
>
You could put the values in a global temporary table and then join to that
table.
Jim
Received on Thu Sep 29 2005 - 20:20:15 CDT