| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: refcursor rowcount check
Madhu,
I agree that the suggestion I proposed performs two queries. And I'm glad you have found a work-around (having your application do what it should do).
I am guessing that my proposal would not cost very much to run. If you think about, the first query (select count(*)) would certainly use any indexes it could (and, as a by-product, they index segments would be sitting in the SGA). The second query would then re-use these same index segments. Since they have already been loaded in the SGA, their re-use would not cost all that much - unless of course, you are returning *millions* of rows. Most on-line applications do not return that much data to the screen, so I would guess that it would work just fine.
Glad I could help in any (small) way.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Monday, April 22, 2002 12:55 PM
To: Multiple recipients of list ORACLE-L
Tom,
Thanks for taking time off to reply.
I had wanted two things - To check rowcount (to enable returning a code for no-rows-found) and secondly, to avoid hitting the database more than once for the same kind of query.
I hope you agree that your method also hits db twice. (I open the cursor twice, you do a count once and then open the cursor). This method would not help me scale for bigger data sets and more complex queries.
I posted the same question to Thomas Kyte (asktom.oracle.com) and he advises
to pass on 'No-rows-found' checking to the calling program. This would avoid
any redundant db hits and help scalability.
As of now I have decided to adopt this approach -
* Perform validation of input parameter * Do a normal fetch of all candidate rows into a temporary table * check the temporary table for count. (this would be a comparitivelysmaller set)
This would allow me to (a) avoid redundant hits (b) adhere to the pre-agreed
interface of passing either recordsets or business-rule-error-codes.
Thanks again.
Madhu
>From: "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: refcursor rowcount check
>Date: Mon, 22 Apr 2002 05:03:22 -0800
>
>-Madhu
>
>How about the following:
>
>create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp,
>Nstr Varchar2) IS
>cname Emp.Name%type;
>rec_count number; -- <== I added this
>
>BEGIN
>
> select count(*) into rec_count -- <== I added these
> FROM Emp where name = Nstr;
>
>-- OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr;
>-- FETCH EmpCur into cname;
>-- DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test
>
> IF rec_count = 0 then --- EmpCur%rowcount=0 then -- I changed this
> OPEN EmpCur FOR SELECT 'W001' from dual;
> ELSE
> OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr;
> End If;
>END Get_Emp_Rows;
>
>
>Hope this helps
>
>Tom Mercadante
>Oracle Certified Professional
>
>
>-----Original Message-----
>Sent: Saturday, April 20, 2002 3:18 AM
>To: Multiple recipients of list ORACLE-L
>
>
>List,
>
>I'm having a small problem while checking row count parameter in a
>refcursor.
>
>A stored procedure accepts parameters and returns refcursors; if no
>candidate rows are found, then an error code is returned to the calling
>program. The same cursor variable is used to retrun the rowset or error
>code.
>
>To check if any rows are returned, I use the ROWCOUNT attribute of the
>cursor variable. Rowcount is not available till I do the first fetch.
>However the fetch removes the first row from the recordset, in case any
>rows
>
>are present. The 'OUT' variable returned to the calling program has one row
>less than actual. How to prevent this? Is there any other better way to
>check if rows are present?
>
>Presently, I work around by opening the cursor again. But surely this won't
>hold out for bigger data sets and complex queries.
>
>create or replace PROCEDURE Get_Emp_Rows (EmpCur IN OUT GenPack.GenCurTyp,
>Nstr Varchar2) IS
>cname Emp.Name%type;
>
>BEGIN
> OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr;
> FETCH EmpCur into cname;
> DBMS_OUTPUT.PUT_LINE(cname); --displays first row for test
> IF EmpCur%rowcount=0 then
> OPEN EmpCur FOR SELECT 'W001' from dual;
> ELSE
> OPEN EmpCur FOR SELECT name FROM Emp where name = Nstr;
> End If;
>END Get_Emp_Rows;
>
>Thanks for your time.
>
>regards
>-Madhu
>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Madhusudhanan Sampath INET: madhulist_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: NDATFM_at_labor.state.ny.us Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Mon Apr 22 2002 - 12:38:24 CDT
 |
 |