Oracle-L: refcursor rowcount check

From: Madhusudhanan Sampath <madhulist_at_hotmail.com>
Date: Fri, 19 Apr 2002 23:18:18 -0800
Message-ID: <F001.0044A24A.20020419231818@fatcity.com>

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 Sat Apr 20 2002 - 02:18:18 CDT