Re: Dynamic array fetching in Pro*C

In article <1993Sep8.025735.12946_at_g2syd.genasys.com.au>, Robert Swan <roberts_at_g2syd.genasys.com.au> wrote:
>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.
>
 [stuff deleted]
>
>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.

Yes you can perform this dynamically. I don't have the code I used to do it in front of me (and I wrote it several years ago) but in general:

1. for each column you're retrieving, allocate the appropriate space (to get 10 rows of float the space would be 40 bytes)
2. for each column, assign the address of that space to the appropriate sqlda element
3. Use the "FOR :number" construct when performing the operation. If you are retrieving 10 rows at a time, the :number would be 10. This can be either a substitution variable or just a value.

Sorry if this is a bit obscure. If you need more detail, let me know and I'll see if I can hunt down the actual code.

Mike Marolda (mmarolda_at_netcom.com, mmarolda_at_arco.com)

p.s. When we implemented this, we saw a ten times performance increase. Your mileage may vary... Received on Wed Sep 08 1993 - 20:14:49 CEST