Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: refcursor rowcount check
-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;
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 Liststo: 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).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: 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 - 08:03:22 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
![]() |
![]() |