Re: Invalid Cursor Error

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 19 Apr 1998 18:19:57 GMT
Message-ID: <353c3c32.3612594@192.86.155.100>

A copy of this was sent to eric_at_deptj.demon.co.uk (Eric Junkermann) (if that email address didn't require changing) On Sat, 18 Apr 1998 16:46:53 GMT, you wrote:

>We have the following as type 2 dynamic SQL:
>
>insert into my_table (date_field, other_field)
>select sysdate, other_field from my_table
>where rowid = :bind_variable
>;
>

I don't know why it would get this. Can you share the code with us? I tried:

    EXEC SQL WHENEVER SQLERROR CONTINUE;     EXEC SQL DROP TABLE TESTING ;     EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL CREATE TABLE TESTING ( X INT PRIMARY KEY );     EXEC SQL INSERT INTO TESTING VALUES (1);     sprintf( stmt.arr, "insert into testing values (:t)" );     stmt.len = strlen( stmt.arr );

    EXEC SQL PREPARE S from :stmt;
    for( t = 1; t < 3; t++ )
    {

        EXEC SQL WHENEVER SQLERROR CONTINUE;
        EXEC SQL EXECUTE S using :t;

        printf( "t = %d sqlcode = %d\n", t, sqlca.sqlcode );

    }

    EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();     EXEC SQL DECLARE C CURSOR FOR SELECT X FROM TESTING;     EXEC SQL OPEN C;
    for(;;)
    {

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH C INTO :t;
        printf( "value = %d\n", t );

    }

And always get:

t = 1 sqlcode = -1
t = 2 sqlcode = 0
value = 1
value = 2

as the output, this shows the static insert of 1 works, the second one does not and the 3'rd insert of 2 succeeded...

>It is prepared, then executed, but gets "duplicate key in index".
>The statement is then executed again immediately, and gets
>"invalid cursor (ORA-1001)".
>
>Why?
>
>This is very old code, but we know what it's trying to do, and how we
>would re-write it if we were allowed (we're not), but what we don't
>understand is why it gets the ORA-1001.
>
>TIA
>
>Eric
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Apr 19 1998 - 13:19:57 CDT