ORA-01001: invalid cursor . Passing cursor to a procedure-CodePudding

When I execute the code below I got ORA-01001: invalid cursor and ORA-06512 error. It was not always the case.I thought I pass the cursor to MY_READ procedure by reference and can close it there. But as I started to get the error above suddenly I concluded, that maybe I'm not passing a reference but the copy of the cursor. Am I right? Is it possible to read close the cursor in MY_READ function?

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PROCEDURE REP_HELPER1 (myIdx IN BINARY_INTEGER, from_d IN DATE, rep_table IN OUT rep_table_T) IS
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
           
myCUR1 SYS_REFCURSOR; 
            
BEGIN              
         OPEN myCUR1 FOR SELECT     myField1,
                                    myField2,
                                    myField3,
                                    myField4,
                                    myField5,
                                    myField6,
                                    myField7,
                                    myField8,
                                    myField9,
                                    myField10,
                                    myField11,
                                    myField12,
                                    myField13,
                                    myField14,
                                    myField15,
                                    myField16,
                                    myField17,
                                    myField18,
                                    myField19,
                                    myField20,
                                    myField21,
                                    myField22,
                                    myField23,
                                    myField24,
                                    myField25,
                                    myField26,
                                    myField27,
                                    myField28,
                                    myField29,
                                    myField30,
                                    myField31
                               FROM myTable;
     MY_READ(myIdx , myCUR1, rep_table) 
 END  REP_HELPER1; 

--Am I passing here a copy of a cursor and not a reference?        
PROCEDURE MY_READ(myIdx IN BINARY_INTEGER, cur IN SYS_REFCURSOR, rep_table IN OUT rep_table_T) IS
BEGIN
         
      FETCH cur INTO rep_table(myIdx).day1,  rep_table(myIdx).day2,  rep_table(myIdx).day3,  rep_table(myIdx).day4,  rep_table(myIdx).day5,
                                      rep_table(myIdx).day6,  rep_table(myIdx).day7,  rep_table(myIdx).day8,  rep_table(myIdx).day9,  rep_table(myIdx).day10,
                                      rep_table(myIdx).day11, rep_table(myIdx).day12, rep_table(myIdx).day13, rep_table(myIdx).day14, rep_table(myIdx).day15,
                                      rep_table(myIdx).day16, rep_table(myIdx).day17, rep_table(myIdx).day18, rep_table(myIdx).day19, rep_table(myIdx).day20,
                                      rep_table(myIdx).day21, rep_table(myIdx).day22, rep_table(myIdx).day23, rep_table(myIdx).day24, rep_table(myIdx).day25,
                                      rep_table(myIdx).day26, rep_table(myIdx).day27, rep_table(myIdx).day28, rep_table(myIdx).day29, rep_table(myIdx).day30,
                                      rep_table(myIdx).day31;
       
       IF cur%NOTFOUND THEN  -- here comes ORA-06512 in a stack 
            dbms_output.put_line('ERROR' || nIndex);
       END IF;
       CLOSE cur;  
END MY_READ;

When I do FETCH and close in REP_HELPER1 I'm not getting the error.

CodePudding user response：

This simple example seems equivalent to yours and works fine:

declare
   mycur sys_refcursor;
   procedure my_read (cur sys_refcursor) is
      job long;
      sal number;
   begin
      fetch cur into job, sal;
      dbms_output.put_Line(job||' '||sal);
      close cur;
   end;
begin
   open mycur for select job, sal from emp where ename = 'KING';
   my_read (mycur);
end;

So I don't think that is the issue.

According to https://www.techonthenet.com/oracle/errors/ora01001.php:

You tried to reference a cursor that does not yet exist. This may have happened because:

You've executed a FETCH cursor before OPENING the cursor.

You've executed a CLOSE cursor before OPENING the cursor.

You've executed a FETCH cursor after CLOSING the cursor.