Keeping a cursor open 1

[BJCooperIT]

I really could use a pair of fresh eyes to look at what should be a simple matter. I have a package that opens a cursor and should return data from one row at a time. However, in testing it does not recognize that the cursor is open on the second time in and reopens the cursor.

CREATE OR REPLACE PACKAGE BODY pkg_test IS

    PROCEDURE get_a_state 
    (
        p_name      IN OUT state_table.NAME%TYPE,
        p_return       OUT VARCHAR2 
    )
    IS
        CURSOR my_cursor IS
                SELECT  NAME
                FROM    state_table
                ORDER BY NAME;
    BEGIN
        p_return := 'OK';

        IF  NOT my_cursor%ISOPEN THEN   --If not open, then open
            OPEN my_cursor;
        END IF;
        
        FETCH my_cursor INTO p_name;    --Get next row

        IF my_cursor%NOTFOUND THEN      --See if at end of rows
            CLOSE my_cursor;
            p_return := 'EF';
        END IF;
    END get_a_state;
BEGIN
    NULL;
END pkg_test;

DECLARE
    v_name   VARCHAR2(100) := NULL;
    v_return VARCHAR2(100) := NULL;
BEGIN
    FOR i IN 1 .. 2
    LOOP
        pkg_test.get_a_state(v_name, v_return);
        upk.dopl(v_name || ' ' || v_return);
    END LOOP;
END;

Alabama OK
Alabama OK

What have I missed?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[jimirvine]

Hi,
put the cursor definition in the Spec.
HTH

 

[BJCooperIT]

Like a charm! Thanks Jim.

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[jimirvine]

Glad I could offer another pair of eyes