Home » SQL & PL/SQL » SQL & PL/SQL » How to obtain a recordcount
How to obtain a recordcount [message #229058] Thu, 05 April 2007 04:09 Go to next message
mestebanpt
Messages: 4
Registered: April 2007
Location: Spain
Junior Member
Hello,

Does any body know how to obtain a record count for a query in a ref cursor like this?


CREATE OR REPLACE package PLOCALTYPES
as
   type cursorType is ref cursor;
end;

CREATE OR REPLACE
PROCEDURE Get_Articles_List
(
   p_resultset  		IN OUT	PLOCALtypes.cursorType,
   p_total_rows 		OUT 	INTEGER
)IS
BEGIN

   OPEN p_resultset FOR
      SELECT * FOR ARTICLES
      WHERE ARTICTYPE=1;

END Get_Articles_List;


I would like to put the record count of this query into the out param p_total_rows

Thanks in advance,

Miguel.

Re: How to obtain a recordcount [message #229069 is a reply to message #229058] Thu, 05 April 2007 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can't have the row count without fetching all the rows.

Regards
Michel
Re: How to obtain a recordcount [message #229128 is a reply to message #229069] Thu, 05 April 2007 08:36 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Add count(*) over ()
as a column.
Re: How to obtain a recordcount [message #229136 is a reply to message #229128] Thu, 05 April 2007 09:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still have to fetch at least one record and so modify the result you return through the cursor.

Regards
Michel
Re: How to obtain a recordcount [message #229145 is a reply to message #229136] Thu, 05 April 2007 09:20 Go to previous messageGo to next message
fcatak
Messages: 16
Registered: March 2007
Location: turkey
Junior Member
CREATE OR REPLACE package PLOCALTYPES
as
type cursorType is ref cursor;
end;

CREATE OR REPLACE
PROCEDURE Get_Articles_List
(
p_resultset IN OUT PLOCALtypes.cursorType,
p_total_rows OUT INTEGER
)IS
CURSOR GetCount is
select count(*) from ARTICLES
WHERE ARTICTYPE=1;
BEGIN
open GetCount;
fetch GetCount into p_total_rows;
close GetCount;

OPEN p_resultset FOR
SELECT * FOR ARTICLES
WHERE ARTICTYPE=1;

END Get_Articles_List;
Re: How to obtain a recordcount [message #229146 is a reply to message #229145] Thu, 05 April 2007 09:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't guarantee that the count you then return is the number of rows you'll get in the cursor you return.

Regards
Michel
Re: How to obtain a recordcount [message #229178 is a reply to message #229146] Thu, 05 April 2007 13:14 Go to previous message
gintsp
Messages: 118
Registered: February 2007
Senior Member
Michel Cadot wrote on Thu, 05 April 2007 17:22
You can't guarantee that the count you then return is the number of rows you'll get in the cursor you return.



Also doing separate count(*) will impact your performance.
Normally your client will do all fetches and counts returned records and so get the count - precise and without performance overhead (OK depending on client you may need to count it manually that of course adds a small overhead anyway Smile

Gints Plivna
http://www.gplivna.eu
Previous Topic: calculate breakfast,lunch and dinner time between 2 times
Next Topic: ERROR : PLS-00455: cursor 'CUR' cannot be used in dynamic SQL OPEN statement
Goto Forum:
  


Current Time: Tue Dec 06 14:30:20 CST 2016

Total time taken to generate the page: 0.10488 seconds