I'm not a Pro*C coder so please bear with me.

There's a Pro*C program that basically does the following:
1. Read a line from an input file (into an array, parse out each column
into 1 piece of the array)
2. For that line do a lookup query to the database
3. Send the results to an insert statement.

When I run this and get a trace file I see the following:

1. The lookup query in step 2 shows the number of executes as 1 time per
row being processed
2. The insert statement shows 1 execution per 1000 rows inserted (implying
to me that it's doing an array insert)

Is it possible for the lookup query to execute in the same fashion as the
insert query (i.e. execute once but
process 1000 rows)? If so is there a "standard" way to do this?

A developer I spoke to said his idea is to create a procedure in the
database for the lookup query that accepts
the array size and conditions in the where clause of the query (as an array
with "array size" conditions). The procedure would
still execute the query 1000 times but would store the results in an array
and pass the entire array back to Pro*C
which would get the results into an array variable on that side.

Does that sound like the way this needs to be done? If this works I would
expect to see a savings on the overhead of not needing
to go back and forth between the database and Pro*C program but it seems
like I'd still see the number of executes in the trace file
equal the number of rows processed. I'd expect the see the fetches go down.
Is there some other way to do this?

Any feedback is much appreciated.
Thanks in advance.
Oracle LazyDBA home page