Message-Id: <10736.126120@fatcity.com> From: Suganya Date: Tue, 09 Jan 2001 19:32:08 +0530 Subject: Cursor --------------7415E049805D5AF595029066 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi all Tell me the syntax of passing cursor as a parameter create or replace procedure proc1 is cursor c1 is select customer_id from customer; cursor c2(c1) is select pricing_method from customer_bill where customer_id = c1.customer_id; begin for i in c1 loop dbms_output.put_line('c1'); for i in c2 loop dbms_output.put_line('c2'); end loop; end loop; end; Error 4/15 PLS-00103: Encountered the symbol ")" when expecting one of the following: in out LONG_ double ref char time timestamp interval binary national character nchar The symbol "" was substituted for ")" to continue. Regards Suganya "Baylis, John" wrote: > > > We have an application that allows users to enter various selection > criteria for a query. > > Is there any way to programatically stop processing the query after x > numbers of records read in order to tell the user his selection > criteria is not restrictive enough? > > In other words, I may have to read more than 1,000,000 records before > it matches my selection. I would like to say to the user 'You have > read 100,000 rows so far, ... please be more selective'. > > I do not think using rownum will work since rownum is assigned to > rows that meet the selection criteria not to all the rows read. > > I do not want to use a user profile with logical_reads_per_session > specified since I do not want the session to be terminated after > reaching that limit. > > Is there another way? > > John Baylis > DBA / Systems Administrator > Canadian Forest Products Ltd. > Vancouver B.C. Canada --------------7415E049805D5AF595029066 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi all
   Tell me the syntax of passing cursor as a parameter
create or replace procedure proc1
is
  cursor c1 is select customer_id from customer;
  cursor c2(c1)  is select pricing_method from customer_bill where
customer_id = c1.customer_id;
begin
    for i in c1
    loop
       dbms_output.put_line('c1');
    for i in c2
    loop
         dbms_output.put_line('c2');
   end loop;
end loop;
end;
 

Error
4/15     PLS-00103: Encountered the symbol ")" when expecting one of the
         following:
         in out <an identifier> <a double-quoted delimited-identifier>
         LONG_ double ref char time timestamp interval binary national
         character nchar
         The symbol "<an identifier>" was substituted for ")" to continue.
Regards
Suganya

"Baylis, John" wrote:

 

We have an application that allows users to enter various selection criteria for a query.

Is there any way to programatically stop processing the query after x numbers of records read in order to tell the user his selection criteria is not restrictive enough?

In other words, I may have to read more than 1,000,000 records before it matches my selection. I would like to say to the user 'You have read 100,000 rows so far, ... please be more selective'.

I do not think using  rownum will work since rownum is assigned to rows that meet the selection criteria not to all the rows read.

I do not want to use a user profile with logical_reads_per_session specified since I do not want the session to be terminated after reaching that limit.

Is there another way?

John Baylis
DBA / Systems Administrator
Canadian Forest Products Ltd.
Vancouver B.C. Canada