Problem to use FOR loop to fetch record from a ref cursor

[Debabrata Jana]

Hi,
Please go through the code given below.

PROCEDURE generateDetailReport(reportId IN NUMBER,DIR IN Varchar2,FileName IN Varchar2) As
  REPORT_NAME VARCHAR2(100);
  REPORT_HDR VARCHAR2(500);
  REPORT_FOOTER VARCHAR2(500);
  REPORT_SQL VARCHAR2(4000);
  RECORDS_PER_PAGE NUMBER;
  TYPE CURSOR_TYPE IS REF CURSOR;
  REPORT_CUR CURSOR_TYPE;
  BEGIN
  
    SELECT REPORT_NAME, NVL(REPORT_HEADER,' '), NVL(REPORT_FOOTER, ' '), REPORT_SQL, RECORDS_PER_PAGE 
    INTO REPORT_NAME, REPORT_HDR, REPORT_FOOTER, REPORT_SQL, RECORDS_PER_PAGE
    FROM DSTAGEDEV.M_REPORT_INFO WHERE REPORT_ID = reportId;    
    OPEN REPORT_CUR FOR REPORT_SQL;
    
    FOR REPORT_REC IN REPORT_CUR
    LOOP
        DBMS_OUTPUT.PUT_LINE('');
    END LOOP;
  END;

When I try to compile this is shows error like :
"PLS-00221: 'REPORT_CUR' is not a procedure or is undefined"

Please try to solve the problem. Thanks.

[amitpatel66]

Check this:

[code=oracle]
SQL> ed
Wrote file afiedt.buf

1 declare
2 type abcd is ref cursor;
3 ab abcd;
4 emp_data emp%rowtype;
5 my_sql varchar2(4000);
6 begin
7 my_sql:='select * from emp';
8 open ab for my_sql;
9 loop
10 exit when ab%notfound;
11 fetch ab into emp_data;
12 --for i in ab loop
13 dbms_output.put _line(emp_data. empno);
14 end loop;
15 CLOSE ab;
16* end;
SQL> /
1
100
300
400
600
2000
200
200

PL/SQL procedure successfully completed.

SQL>
[/code]

You cannot OPEN a REF CURSOR and use it in FOR LOOP. Instead you need to use OPEN FETCH CLOSE logic as above.

[debasisdas]

you cam also try like the following sample code

DECLARE
TYPE EmpCurTyp IS REF CURSOR;
TYPE NameList IS TABLE OF emp.ename%TYPE;
TYPE SalList IS TABLE OF emp.sal%TYPE;
emp_cv EmpCurTyp;
names NameList;
sals SalList;
BEGIN
OPEN emp_cv FOR SELECT ename,sal FROM emp WHERE sal < 3000;
FETCH emp_cv BULK COLLECT INTO names, sals;
CLOSE emp_cv;
FOR i IN names.FIRST .. names.LAST
LOOP
dbms_output.put_line('Name = ' || names(i) || ', salary = ' ||
sals(i));
END LOOP;
END;