Re: ORA-01001

In article <ak0q9a$1ep5g7$1_at_ID-87429.news.dfncis.de>, "DC" says...
>
>
>Hi, I'm working with 8i 8.1.6 on Windows 2000 Server.
>
>I'm getting ORA-01001 error while trying to execute a
>stored procedure in SQL*Plus:
>
>
>
>SQL> EXEC MY_FUNC(sysdate);
>BEGIN MY_FUNC(sysdate); END;
>
>*
>ERROR at line 1:
>ORA-01001: invalid cursor
>ORA-06512: at "MAIN_USER.MY_FUNC", line 87
>
>
>I also searched METALink. They recommend increasing AREASIZE
>and MAXOPENCURSORS. However I am using SQL*Plus. No
>precompilers here. What can I do? MAXOPENCURSORS and AREASIZE
>are mentioned only three (3) times in the whole Oracle8i
>documentation set, and even those entries are of little help.
>
>
>
>
>
>CODE --------> (breaks at FETCH CUR_MYTABLE INTO REC_MYTABLE;
>line)
>
>
>
>CREATE PROCEDURE MY_FUNC(TARGETDAY DATE) AS
>
> CURSOR CUR_MYTABLE IS SELECT COL1, COL2, COL3 FROM MYTABLE;
> REC_MYTABLE MYTABLE%ROWTYPE;
>
>BEGIN
> OPEN CUR_MYTABLE;
> LOOP
> FETCH CUR_MYTABLE INTO REC_MYTABLE;
> EXIT WHEN CUR_MYTABLE%NOTFOUND;
> CLOSE CUR_MYTABLE;
> END LOOP;
>

your bug is that you close the cursor in the loop. If table MYTABLE has more then zero rows -- you'll always get invalid cursor -- cause the second iteration through the loop attempts to fetch from a closed cursor.

put the close OUTSIDE the loop

>EXCEPTION
> WHEN OTHERS THEN
> <exception processing>
>
>END;
>/
>
>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp