Re: Pro*C host arrays: how big?

In article <CJxpy3.7Cu_at_uk.ac.brookes>, p0070621_at_oxford-brookes.ac.uk (Tommy Wareing) writes:
|> Software: Oracle 6.0.36, PRO*C 1.4.11.
|>
|> Anyone know how to transfer data from the database into a PRO*C
|> host array, when the size of the array is not known at compile
|> time?
|>

Yup. what you do us include a variable to indicate the maximum number of rows to return from a select.

What you do is declare an integer (or long) and use that in the FOR part of the embedded select, like so:

	EXEC SQL FOR :max_no_of_rows
		SELECT stuff 
		INTO :host_array
		FROM table 
		WHERE appropriate = 'Y';

You can then keep doing this until the number of rows return < max_no_of_rows, processing the batch each time. This can bring big performance gains if the batch size is large enough - depending on the memory & other limitations, go for about 100 rows +. (diminishing returns of course)

Hope this helps.

Simon.

(My opinion only.) Received on Mon Jan 24 1994 - 11:41:22 CET