| How to count number of returning rows (with an example) [message #39719] |
Sun, 04 August 2002 23:05  |
Andrey
Messages: 21
Registered: January 1999
|
Junior Member |
|
|
This is not my first question. Rather not the first its formulation. All simple methods I know, so I ask for PL/SQL Experts. I thank all in advance who gave consideration to my message.
Why do I have to check the size of selected recordset? This is an internet project. If size of returned rows is too large then ADO is pending infinite. So I can not get the result from ADO which call my procedure. I guess it's clear. To get the result I must to pass to ADO the recordset with the size < 500. That is why I have to count it.
Now. Why not to apply a pl/sql table to return the recordset?
First. I don't know how to implement a concurrent access to this table.
Second. ADO can not get pl/sql table as an output parameter. So I use the cursor.
Why not to use FETCH to count rows? If I use FETCH I can count a number of rows and that's it. I can not pass the recordset to ADO, because its size is nothing after applying FETCH. I even can not say that its size is zero because in this case ORACLE raise the exception "Fetch is out of sequence" when trying to return the selected recordset. I made FETCH one time, two times to the recordset and its number of rows was reducing by one row, two rows accordingly.
If I use a second query "SELECT COUNT(*)...", starting first, it slows down an executing of the procedure as a whole.
I hope I explained my problem correctly at this time. It is especially important for internet projects to limit the size of
output data. In the last case you can pass message for example: 'this query returns too much of rows'
Thank you.
And this is an extract from my package which shows my problem inside the comments ( -!- ).
CREATE OR REPLACE PACKAGE IShop
AS
TYPE cursortype is REF CURSOR;
PROCEDURE ShowResult (SQLWhere VARCHAR2, QueryFlag NUMBER, exitcode OUT NUMBER, RS OUT cursortype);
END;
/
CREATE OR REPLACE PACKAGE BODY IShop AS
PROCEDURE ShowResult (SQLWhere VARCHAR2, QueryFlag NUMBER, exitcode OUT NUMBER, RS OUT cursortype) IS
SQLString VARCHAR2(1000);
init VARCHAR2(1000);
initCount VARCHAR2(200);
initSim VARCHAR2(1000);
initCart VARCHAR2(1000);
SQLWher VARCHAR2(500);
i INTEGER;
v_Var NUMBER;
BEGIN
EXECUTE IMMEDIATE 'SELECT cdcaux.ctenvinit(''chemnet'') from dual' INTO v_Var;
init:='SELECT /*+ORDERED*/ to_char(a.molregno) as regno,chime_string(a.ctab) as structure,a.molname as name, molfmla(a.ctab) as formula,to_char(a.molweight) as weight,b.*';
initSim:=' FROM chemnet_moltable a,i_shop b ';
initSim:= initSim || ' WHERE b.molregno=a.molregno AND ';
SQLString := init||initSim;
OPEN RS FOR SQLString || SQLWhere;
-!- here I must return the recordset, but first I have to check its size ----
-!- If size is more than 500 rows then do not return this recordset. --
-!-- Return instead: ----
OPEN RS FOR SELECT 1 FROM DUAL;
-!- else keep and return selected recordset -----
-!- but how to do this? -----
----------------------------------------------------------
END ShowResult;
END;
/
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
