Retrieve RecordCount from cursor - How

From: george <user0214_at_yahoo.com>
Date: Fri, 19 Jul 2002 01:51:20 GMT
Message-ID: <sgKZ8.4006$_C2.292065@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 - 20:51:20 CDT