Tom, Thanks for taking time off to reply.
I had wanted two things - To check rowcount (to enable returning a code for no-rows-found) and secondly, to avoid hitting the database more than once for the same kind of query. I hope you agree that your method also hits db twice. (I open the cursor twice, you do a count once and then open the cursor). This method would not help me scale for bigger data sets and more complex queries. I posted the same question to Thomas Kyte (asktom.oracle.com) and he advises to pass on 'No-rows-found' checking to the calling program. This would avoid any redundant db hits and help scalability. As of now I have decided to adopt this approach - * Perform validation of input parameter * Do a normal fetch of all candidate rows into a temporary table * check the temporary table for count. (this would be a comparitively smaller set) * return ref cursor with either error code or result set. This would allow me to (a) avoid redundant hits (b) adhere to the pre-agreed interface of passing either recordsets or business-rule-error-codes. Thanks again. Madhu >From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: refcursor rowcount check >Date: Mon, 22 Apr 2002 05:03:22 -0800 > >-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).
