I am creating a package to use with Jasper reports where I learnt that I need SYS_REFCURSOR
but I cannot seem to be able to Loop my cursors:eg
create or replace PACKAGE BODY fin_statement_spool
AS
PROCEDURE fin_main_spool(vacid in VARCHAR2, vfromdate in date, vtodate in date,c1 out SYS_REFCURSOR,c2 out SYS_REFCURSOR)
AS
cramount NUMBER;
dramount NUMBER;
countcr NUMBER;
countdr NUMBER;
BEGIN
OPEN c1 FOR
SELECT
.......;
OPEN c2 FOR
SELECT ........;
BEGIN
FOR i IN c1--Error is here
LOOP
rnum := 0;
cramount := 0;
dramount := 0;
countdr := 0;
countcr := 0;
..........
Isn't this the right way?
You appear to have confused explicit cursors, e.g.:
declare
cursor cur is
select dummy from dual;
begin
for rec in cur
loop
dbms_output.put_line(rec.dummy);
end loop;
end;
/
with a ref cursor - which is a pointer to an open cursor.
You would typically use a ref cursor to open a cursor in the db and pass it back to the calling app for it to loop through.
The way you have declared the ref cursors as out parameters and then tried to loop through them in the same procedure does not make sense - once you have fetched a record from a cursor, you cannot re-fetch it.
If you absolutely must loop through a ref cursor, you'd use this sort of syntax:
declare
cur sys_refcursor;
rec dual%rowtype;
begin
open cur for select dummy from dual;
loop
fetch cur into rec;
exit when cur%notfound;
dbms_output.put_line(rec.dummy);
end loop;
end;
/
but as I said, in general, you wouldn't be looping through ref cursors in the db, you'd be doing that in the calling code.
Perhaps if you updated your question with the requirements you're trying to fulfil, we could suggest a better way of doing it.