Tom, Thanks again ! sorry to keep harping on this topic..we read your reply and we are having an argument out here : whether we can really depend on data and index segments being present in the SGA while writing a query-logic ?
(actually we are writing a stock broker app and one of the immediate reqts is to return 'input' customer's trading activity - which can vary widely) regards Madhu >From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> >Reply-To: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> >Subject: RE: RE: refcursor rowcount check >Date: Mon, 22 Apr 2002 09:38:24 -0800 > >Madhu, > >I agree that the suggestion I proposed performs two queries. And I'm glad >you have found a work-around (having your application do what it should >do). > >I am guessing that my proposal would not cost very much to run. If you >think about, the first query (select count(*)) would certainly use any >indexes it could (and, as a by-product, they index segments would be >sitting >in the SGA). The second query would then re-use these same index segments. >Since they have already been loaded in the SGA, their re-use would not cost >all that much - unless of course, you are returning *millions* of rows. >Most on-line applications do not return that much data to the screen, so I >would guess that it would work just fine. > >Glad I could help in any (small) way. > >Tom Mercadante >Oracle Certified Professional > > >-----Original Message----- >Sent: Monday, April 22, 2002 12:55 PM >To: Multiple recipients of list ORACLE-L > > >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). >-- >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). _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- 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).
