Re: Determining number of rows returned by a cursor

One way to get the count before actually displaying the rows is to fetch the cursor into a plsql index-by table. Then use the count method of the table to find out how many rows were fetched. After this count is displayed, the rows themselves may be displayed by looping through the plsql table.

This way you would be able to display a count of the number of rows fetched prior to actually displaying the rows and without incurring the cost, and possible inaccuracy, of another sql statement to perform the count.

Frank Hubeny
Wheaton, IL

rickowen_at_my-deja.com wrote:

> I am writing some procedure in pl/sql. Assuming I have a cursor such as:
>
> cursor mycursor (mysearch in varchar2) is
> select name, id
> from employee
> where upper(name) like '%' || upper(mysearch) || '%'
>
> is there any way to determine the number of rows returned by such a
> cursor so that I could do something like:
>
> if mycursor%RECORDCOUNT then
> -- do something that involves the cursor
> else
> -- let the user know their search failed
> end if;
>
> I know about %ROWCOUNT, but I would like to know before I actually
> execute the cursor whether or not it will return rows.
>
> Thanks.
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Sep 03 1999 - 23:21:36 CDT