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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).