Re: PRO*C Find Set Size

Mark Miller wrote:
>
> ghharrac_at_neodata.com wrote:
> >
> > Can I determine the number of rows/tuples that
> > a query (select) will return ?

Mark answered this question (as far as predicting the number of rows) But to be sure, the answer to this question is: YES simply fetch all the rows and count them.

> >
> > I know that I can obtain the current row, but
> > not the number of resultant rows from the query.
> >

Now George gets to the real question:

> > Does ORACLE complete/determine the find set before
> > the fetch cycle and return one tuple of the set
> > for every fetch ???????
> >
> > George H
> > ghharrac_at_neodata.com
> >
 

> There's always the count() function, as in:
>
> exec sql select count(*) into :count from some_table;
>
> This will show you how many rows are in the table as of that instant in
> time. You can then at least make a good approximation of how many rows
> there are.
>
> ---Mark
> mmiller_at_nyx.net

There is actually two ways of answering the question: 1. Can my program determine the number of rows to be returned

   BEFORE it fetches them? The answer to that is NO. Mark's    method cannot guarantee that a commit does not occur between the    time the count is made and the fetch query is made. So you    always have to be prepared to fetch a number of rows differrent    than expected.

2. Answering the literal question: does ORACLE determine the

   result set before returning the first row? The answer here    is usually, but not always! There are some cases where oracle    can return the first row to the fetch before it has found all the    rows. It is dependent on the query and often requires the FIRST ROWS    hint to the optimizer.

So, in general you cannot find the number of rows to be returned before you actually fetch all of them.

-- 
Ed Prochak      Magic Interface, Ltd.
1-440-498-3702  "hardware/software alchemy"
Contract services from embedded systems to ORACLE applications.