Dynamic array fetching in Pro*C

Greetings all.

I am on the trail of a performance problem with an Oracle Pro*C program. In its simplest form, it uses full dynamic SQL to enable users to retrieve data from any table they nominate. I've recently had one user complain that, on a simple table with 12,000 rows, our program is outperformed by SQL*Plus by about 3:1 (43 to 16 seconds). Not too impressive.

I've tracked the performance bottleneck down (principally) to communications between our client program and the Oracle server. It appears that Oracle's back-end passes the rows back to us one at a time.

Our equivalent program running with Ingres has a `FOR READONLY' clause appended to the `OPEN CURSOR' directive. This tells the server that we will neither be updating nor deleting rows as they are retrieved, so it can bundle them up in fewer packets.

Oracle doesn't appear to have an equivalent construct, and the nearest approach I can find is fetching into array variables. This is definitely fast enough, but static. I need to be able to retrieve multiple rows whose form I will only know at run-time... i.e. an array of SQLDAs.

How can I do this? Alternatively, how does SQL*Plus manage it?

Have fun,

Robert.

-- 
Stop, catch your breath and relax.| Robert Swan, roberts_at_g2syd.genasys.com.au
Now ...                           | Genasys II Pty. Ltd., North Sydney.