Re: Cursor

From: Veera Prasad <vprasad_at_olf.com>
Date: Tue, 09 Jan 2001 12:44:09 -0500
Message-Id: <10736.126179@fatcity.com>

--------------6DC1AA39997293CAF13D67C7
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Suganya,
Here I go with an example how to pass cursor as a parameter

declare
cursor c1 is select deptno from dept where deptno = 10; cursor c2(v_deptno number) is select * from emp where deptno = v_deptno;

begin
for i in c1 loop
for j in c2(i.deptno) loop
dbms_output.put_line('the ename is '||j.ename); end loop;
end loop;
end;
/

Veera

Suganya wrote:

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

--------------6DC1AA39997293CAF13D67C7

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

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

<br>Here I go with an example how to pass cursor as a parameter
<p>declare
<br>cursor c1 is select deptno from dept where deptno = 10;
<br>cursor c2(v_deptno number) is select * from emp where deptno = v_deptno;
<br>begin
<br>for i in c1 loop
<br>for j in c2(i.deptno) loop
<br>dbms_output.put_line('the ename is '||j.ename);
<br>end loop;
<br>end loop;
<br>end;
<br>/
<p>Veera
<p>Suganya wrote:
<blockquote TYPE=CITE>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
          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.

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

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

Received on Tue Jan 09 2001 - 11:44:09 CST