| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: refcursor rowcount check
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" <NDATFM_at_labor.state.ny.us>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>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" <NDATFM_at_labor.state.ny.us>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >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: madhulist_at_hotmail.com
>
>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: 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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Mercadante, Thomas F
> INET: NDATFM_at_labor.state.ny.us
>
>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: 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).
-- 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 Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: 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 - 22:08:40 CDT
 |
 |