Oracle-L: RE: refcursor rowcount check

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Mon, 22 Apr 2002 05:03:22 -0800
Message-ID: <F001.0044AC80.20020422050322@fatcity.com>

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

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