-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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mercadante, Thomas F
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).
