No. Until you actually retrieve the records you will not know how many you
have. You can get an approximate count by doing an explain plan, but it is
not going to be accurate.
Jim
"george" <user0214_at_yahoo.com> wrote in message
news:sgKZ8.4006$_C2.292065_at_newsread2.prod.itd.earthlink.net...
> How can I retrieve the count of records in a cursor in a stored procedure?
>
> The following stored procedure works fine. It returns the 2 out
parameters
> to the calling client, a cursor and a count of the number of records in
that
> cursor. But, as you can see in the code snippet below, I'm executing the
> same select statement twice ( the SQL code in the snippet is abbreviated
for
> clarity). Once for the cursor and once again to retrieve the record count
> that I presume to have in the cursor. Not good.
>
> Instead of executing the second select statement, how can I retrieve the
> count of record from the cursor?
>
> 00001 PROCEDURE get_table (out_record_count out number, out_cursor out
> types.cursorType) AS
> 00002
> 00003 -- Pass a record count and an open cursor to the calling program.
> 00004
> 00005 BEGIN
> 00006
> 00007 -- Open cursor for selected table.
> 00008 OPEN out_cursor FOR ' Select * FROM mytable';
> 00009
> 00010 -- Get record count from select table.
> 00011 EXECUTE IMMEDIATE
> 00012 'SELECT COUNT(1) FROM mytable' INTO out_record_count;
> 00013 END;
>
> Thanks in advance for any suggestions that you may have.
>
>
Received on Thu Jul 18 2002 - 21:27:48 CDT
