CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlcursor
Phyore
Phyore

Reputation: 41

Wierd PL SQL invalid cursor

When running the following code I get a ORA-01001: invalid cursor Not sure whats going on, I converted it to a procedure and it started happening;

CREATE OR REPLACE PROCEDURE p_student_from_class_list (p_stu_id IN NUMBER) 
IS
--Declaring a variable --
    v_date date; 

    --Creating a Cursor to find data from using the p_stu_id paramenter--
    CURSOR enrollments_cursor IS
        SELECT enrollment_date, stu_id, class_id, status
        FROM enrollments 
        WHERE stu_id = p_stu_id;


BEGIN
    /*Changing the date so the code looks for the classes for the student 
    which was entered in the bind variable above for the last 10 years*/
    v_date := add_months(SYSDATE, -120);
    FOR v_enrollment_record IN enrollments_cursor
    LOOP 
--Displays the student's enrollment date, class ID and Current Status for each    class >they taken in last 10 years,from the value which was entered in the bind    variable--
        IF v_enrollment_record.enrollment_date 
        between v_date AND SYSDATE THEN
            DBMS_OUTPUT.PUT_LINE('Enrollment Date: '
            ||v_enrollment_record.enrollment_date
            ||' Class ID: '||v_enrollment_record.class_id
            ||' Status: '||v_enrollment_record.status);
        END IF;
    END LOOP;
    --Closing the cursor --
    CLOSE enrollments_cursor;

    --Application Express processes the statement--
    COMMIT; 
    --Telling Application Express the procedure has finished--
END p_student_from_class_list;

  --Anonymous Block to run the Procedure--

BEGIN
    p_student_from_class_list(--Student ID--);
END;

as I said the code was working when it was got in a procedure but for some reason creating it as a procedure now gives this error. I have been racking my brain trying to work this out.

Upvotes: 0

Views: 1632

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

You don't need a manual CLOSE for a cursor you use as FOR <record> IN <cursor> LOOP. You only need to CLOSE a cursor you manually OPENed (and then FETCHed).

Just remove the line:

    CLOSE enrollments_cursor;

Compare the documentation for FOR LOOP and the OPEN-FETCH-CLOSE pattern.

Upvotes: 2

Related Questions