Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Cursor

Cursor

From: Suganya <ukumsu02_at_shafika.vetri.com>
Date: Tue, 09 Jan 2001 19:32:08 +0530
Message-Id: <10736.126120@fatcity.com>

--------------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 <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

--------------7415E049805D5AF595029066

Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit

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

Hi all
<br>&nbsp;&nbsp; Tell me the syntax of passing cursor as a parameter
<br>create or replace procedure proc1
<br>is
<br>&nbsp; cursor c1 is select customer_id from customer;
<br>&nbsp; cursor c2(c1)&nbsp; is select pricing_method from customer_bill
where
<br>customer_id = c1.customer_id;
<br>begin
<br>&nbsp;&nbsp;&nbsp; for i in c1
<br>&nbsp;&nbsp;&nbsp; loop
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line('c1');
<br>&nbsp;&nbsp;&nbsp; for i in c2
<br>&nbsp;&nbsp;&nbsp; loop
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_output.put_line('c2');
<br>&nbsp;&nbsp; end loop;
<br>end loop;
<br>end;
<br>&nbsp;
<p>Error
<br>4/15&nbsp;&nbsp;&nbsp;&nbsp; PLS-00103: Encountered the symbol ")"
when expecting one of the
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; following:
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; in out &lt;an identifier>
&lt;a double-quoted delimited-identifier>
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; LONG_ double ref char
time timestamp interval binary national
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; character nchar
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; The symbol "&lt;an
identifier>" was substituted for ")" to continue.
<br>Regards
<br>Suganya
<p>"Baylis, John" wrote:
<blockquote TYPE=CITE>&nbsp;
<p><font face="Arial"><font color="#0000FF"><font size=-1>We have an application
that allows users to enter various selection criteria for a query.</font></font></font>
<p><font face="Arial"><font color="#0000FF"><font size=-1>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?</font></font></font>
<p><font face="Arial"><font color="#0000FF"><font size=-1>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'.</font></font></font>
<p><font face="Arial"><font color="#0000FF"><font size=-1>I do not think
using&nbsp; rownum will work since rownum is assigned to rows that meet the selection criteria not to all the rows read.</font></font></font>
<p><font face="Arial"><font color="#0000FF"><font size=-1>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.</font></font></font>
<p><font face="Arial"><font color="#0000FF"><font size=-1>Is there another
way?</font></font></font>
<p><b><font face="Tahoma"><font color="#000080"><font size=-1>John Baylis</font></font></font></b>
<br><font face="Tahoma"><font size=-1>DBA / Systems Administrator</font></font>
<br><font face="Tahoma"><font size=-1>Canadian Forest Products Ltd.</font></font>
<br><font face="Tahoma"><font size=-1>Vancouver B.C. Canada</font></font></blockquote>
Received on Tue Jan 09 2001 - 08:02:08 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US