FetchSize and Cursor

bencas · Post by **bencas** » Thu 26 May 2005 17:33

Hi,

I have two questions.
1. Is it possible to enable prefetching of any sort using OraDirect?
2. I am primarily using Ref Cursors to return data, however my understanding is that this is a server side cursor and therefore can only fetch one result at a time. If this is true, does the FetchSize property have any affect at all? I've done a few experiments and as far as I can tell the FetchSize property seems to have no impact on any of my database read operations.

Tks,
Ben

Paul · Post by **Paul** » Fri 27 May 2005 07:07

1. Prefetch works with OCI connection starting from Oracle client 9i, with SELECT queries. It does not work with cursor parameters. The number of prefetched rows is OracleCommand.FetchSize. If OracleCommand.FetchSize is set then OCI loads these rows to client side after executing statement.
2. Rows are fetched by blocks at one time when you call OracleDataReader.Read consequently. The number of rows in block is OracleCommand.FetchSize. This works with SELECT statements and cursor parameters

Guest · Post by **Guest** » Fri 27 May 2005 08:17

Sorry, I dont quite understand with regard to FetchSize.

In point 1, you say "It does not work with cursor parameters." However in point two you say "This works with SELECT statements and cursor parameters".

Please correct me?

Paul · Post by **Paul** » Fri 27 May 2005 08:31

Prefetch does not work with cursor parameters (loading rows to client side during execute operation, see Oracle documentation about prefetch). But you can change the size of block that will be fetched for cursor parameters during OracleDataReader.Read.