Re:Cursor

From: <dgoulet_at_vicr.com>
Date: Tue, 9 Jan 2001 12:37:43 -0500
Message-Id: <10736.126177@fatcity.com>

Suganya

I'm not totally sure of what your trying to do, but it would appear that the following is what you want:

create or replace procedure proc1
is
cursor c1 is select customer_id from customer; cursor c2 is select pricing_method from customer_bill where customer_id = c1.customer_id;
begin

for i1 in c1
loop

dbms_output.put_line(i1.customer_id); for i2 in c2
loop

dbms_output.put_line(i2.pricing_method); end loop;
end loop;
end;

You can't pass a cursor to a cursor. If your looking to do array processing, then you'll need to look in the book some more because I don't think that is possible in PL/SQL.

Dick Goulet

____________________Reply Separator____________________

Subject: Cursor
Author: Suganya <ukumsu02_at_shafika.vetri.com> Date: 1/9/2001 6:10 AM

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

<!doctype html public "-//w3c//dtd html 4.0 transitional//en">
<html>

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:
<blockquote TYPE=CITE> 
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
Received on Tue Jan 09 2001 - 11:37:43 CST