Oracle-L: RE: RE: refcursor rowcount check

From: Madhusudhanan Sampath <madhulist_at_hotmail.com>
Date: Mon, 22 Apr 2002 08:54:34 -0800
Message-ID: <F001.0044AFF7.20020422085434@fatcity.com>

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 comparitively

smaller set)
* return ref cursor with either error code or result 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
>

Chat with friends online, try MSN Messenger: http://messenger.msn.com

-- 
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).

Received on Mon Apr 22 2002 - 11:54:34 CDT