PL/SQL: ORA-01001 in cursor use

Question

I'm programming a PL/SQL function to check datesand return a date. The problem is that testing the function isolated I've obtained error about the cursor use. ORA-01001: cursor no válido or in english, not valid cursor.

I use two arrays to save different kind of dates. But the error comes in the FOR ... LOOP loop. A priori is well defined the cursor and the rowvariable. And I've checked and the row variable contains rows, the query returns at least 5 rows. Here the code of the function :

create or replace FUNCTION OBTAIN_DATE_FIN_ABSENT
            (combination IN VARCHAR2 DEFAULT '',
            otarif1 IN VARCHAR2 DEFAULT '',
            otarif2 IN VARCHAR2 DEFAULT '',
            indicador IN NUMBER DEFAULT 0) 
           RETURN DATE 
        AS 
            i NUMBER := 1;
            solution DATE := '01/01/1970';
            previousOTARi VARCHAR2(3 CHAR) := '';

            TYPE varray_type IS VARRAY(50) OF DATE; -- Création deux arrays des dates.
            v1 varray_type;                         -- array date_begin
            v2 varray_type;                         -- array date_end

            -- Requete pour obtenir tous les lignes pour la combinaison passé.
            cursor cursorDate is            
                select cin.cin_value cin_combi, cin.date_begin date_begin, cin.date_end date_end, gin.code gcode, gin.otari_file otari_file 
                from rs2qtcin cin, rs2qtgin gin
                where cin.group_id = gin.id
                and cin.cin_value = combination
                and substr(gin.otari_file, 1, 1) = substr(otarif1, 1, 1)
                order by cin.date_begin, gin.otari_file;
            my_cursorDate cursorDate%rowtype;
        BEGIN
           --open cursorDate;
           DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Dentro!');
           FETCH cursorDate INTO my_cursorDate;
           DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Dentro1!');
           IF cursorDate%FOUND THEN         -- El cursor contiene info, sino %NOTFOUND
                DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Dentro2!');
                IF cursorDate%ROWCOUNT != 0 THEN
                    DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- cursor NO vacío!');
                ELSE
                    DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- cursor VACÍO!!!');
                END IF;
            ELSE
                DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Cursor NOTFOUND!');
            END IF;

            FOR my_cursorDate IN cursorDate LOOP
                DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Dentro3!');
                --v1(i) := my_cursorDate.date_begin;
                --v2(i) := my_cursorDate.date_end;
                DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Guardadas ambas fechas en array!.');

                IF v2(i-1) = NULL THEN
                    --solution := v1(i); -- Guardamos date_begin de la siguiente iteración

                    IF indicador = 1 and previousOTARi = otarif1 THEN
                        DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- OK!');
                    ELSE 
                        IF indicador = 2 and previousOTARi = otarif2 THEN
                            DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- ¡CHECK! this case.');
                        END IF;
                    END IF;
                END IF;
                --solution := 1;
                previousOTARi := my_cursorDate.otari_file;
                i := i+1;
           END LOOP;

           --close cursorDate;

        RETURN solution;

        EXCEPTION WHEN OTHERS THEN
            raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);    
        END;

The Open and close cursor statements are commented because I'm debugging the code. But when they are uncommented the error is the same.

Answer 1

After you open your cursor you need to close it.

Also, I have changed your cursor to use modern explicit join.

I have also changed your code in this part FOR rec IN cursorDate LOOP

create or replace FUNCTION OBTAIN_DATE_FIN_ABSENT
            (combination IN VARCHAR2 DEFAULT '',
             otarif1 IN VARCHAR2 DEFAULT '',
             otarif2 IN VARCHAR2 DEFAULT '',
             indicador IN NUMBER DEFAULT 0) 
RETURN DATE 
AS 

i NUMBER := 1;
solution DATE := '01/01/1970'; 
previousOTARi VARCHAR2(50) := '';

TYPE varray_type IS VARRAY(50) OF DATE; 
v1 varray_type;                
v2 varray_type;                         


cursor cursorDate is            
select cin.cin_value cin_combi
       , cin.date_begin date_begin
       , cin.date_end date_end
       , gin.code gcode
       , gin.otari_file otari_file 
from rs2qtcin cin
join rs2qtgin gin on cin.group_id = gin.id
                 and cin.cin_value = gin.combination
                 and substr(gin.otari_file, 1, 1) = substr(cin.otarif1, 1, 1)
order by cin.date_begin, gin.otari_file;

my_cursorDate cursorDate%rowtype;

BEGIN
    open cursorDate;
    FETCH cursorDate INTO my_cursorDate;

    IF cursorDate%FOUND THEN         -- El cursor contiene info, sino %NOTFOUND
        DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Dentro2!');
        IF cursorDate%ROWCOUNT != 0 THEN
            DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- cursor NO vacío!');
        ELSE
            DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- cursor VACÍO!!!');
       END IF;
    ELSE
       DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Cursor NOTFOUND!');
    END IF;
    close cursorDate;

    FOR rec IN cursorDate LOOP
       DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Dentro3!');
       DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Guardadas ambas fechas en array!.');

       IF v2(i-1) = NULL THEN
          IF indicador = 1 and previousOTARi = otarif1 THEN
             DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- OK!');
          ELSE 
             IF indicador = 2 and previousOTARi = otarif2 THEN
                DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- ¡CHECK! this case.');
             END IF;
          END IF;
       END IF;

       previousOTARi := my_cursorDate.otari_file;
       i := i+1;
    END LOOP;

    RETURN solution;

EXCEPTION WHEN OTHERS THEN
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);    
END;
/

Here is a demo showing the code works

Answer 2

Your procedure has some structural issues. The most immediate issue is "open and close cursor statement are commented because i'm debugging the code". Because the open in commented the FETCH fails with invalid cursor - fetch requires the cursor to be open. You claim the error was issued on the cursor FOR loop. I guess that was before you commented the open. The cursor FOR issues an open for the cursor. Which, if the cursor is open throws the invalid cursor. You cannot use a cursor FOR on an open cursor. So you either open the cursor, process that 1st row (fetch), then close the cursor, then enter the FOR. Do not close the cursor after exiting the loop.

You have a logical problem within the nested if the structure.

IF cursorDate%FOUND THEN         -- El cursor contiene info, sino %NOTFOUND
   DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- Dentro2!');
   IF cursorDate%ROWCOUNT != 0 THEN
      DBMS_OUTPUT.PUT_LINE ('OBTAIN_DATE_FIN_ABSENT -- cursor NO vacío!');
   ELSE

The "if cursorDate%Rowcount != 0" will always return True. The cursor attribute %rowcount returns the number of rows fetched form the cursor. The attribute %Found returns True if the last fetch returned a row. Since here %Rowcount is subordinate %Found means it can never return less than 1.

Another immediately obvious is the statement

IF v2(i-1) = NULL THEN 

It has 2 problems. First, your variable i is initialized to 1 so the above statement then reads v2(0). That is invalid, index values in pl/sql begin with 1. Once that is correct the resulting statement will never be True. You cannot use relational operators with Null; the result when you do is always Null. You need "v2(i) is Null".
Fix those issues then if you still have problems post another question.