Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql cursors
pl/sql cursors [message #37218] Thu, 24 January 2002 20:05 Go to next message
hnetscape
Messages: 30
Registered: December 2001
Location: India
Member

Hi,

I have a procedure as below. When Iam using it in sql it is giving the correct result as below. But when i use the same in d2k reports it is giving result only the first row and remaining rows it is not showing. Can some one help me in getting the same result as in at sql prompt.

declare
cursor ao2 is
select compcode,branchcd,seriescd,aono,slno,deliveryqty,deliverydt
from saao2
where compcode='02'
and branchcd= 'A1'
and seriescd= 'AO'
and aono= '20010390'
order by compcode,branchcd,seriescd,aono,slno;
Begin
for ao2rec in ao2 loop
dbms_output.put_line(ao2rec.deliveryqty);
exit when ao2%notfound;
end loop;
End;
sql> /
15
12
15
10
15
30
56
97

Thanks.
Raju.
Re: pl/sql cursors [message #37224 is a reply to message #37218] Fri, 25 January 2002 01:17 Go to previous messageGo to next message
dinakar shetty
Messages: 29
Registered: January 2002
Junior Member
can u please send me the code u are using in
reports
Re: pl/sql cursors [message #37249 is a reply to message #37218] Sun, 27 January 2002 19:52 Go to previous messageGo to next message
hnetscape
Messages: 30
Registered: December 2001
Location: India
Member

hi,
following is the code in which iam getting only one value but i want to have all the row result.

function CF_1Formula return Number is
vcocd char(2);
vbrcd char(2);
vscd char(3);
vaono char(8);
vslno number(3);
vdqty number(9);
vddt date;

cursor ao2 is
select compcode,branchcd,seriescd,aono,slno,deliveryqty,deliverydt
from saao2
where compcode='02'
and branchcd= 'A1'
and seriescd= 'AO'
and aono= '20010390'
order by compcode,branchcd,seriescd,aono,slno;
Begin
open ao2;
loop
fetch ao2 into vcocd,vbrcd,vscd,vaono,vslno,vdqty,vddt;
return(vdqty);
exit when ao2%notfound;
end loop;
close ao2;
End;

thanks.
Raju.
Re: pl/sql cursors [message #37258 is a reply to message #37218] Mon, 28 January 2002 05:26 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
formula trigger in reports executes one time for each row. i think, you have to modify select statement in function by passing current row (other column values) values.
Previous Topic: 8i lite
Next Topic: Is it possible somehow to call stored procedures with usage of the SELECT expression ?
Goto Forum:
  


Current Time: Thu Aug 06 08:54:04 CDT 2020