Re: Error in Dynamic SQL

In article l2u_at_karuna.tcs.com, qing_at_karuna.tcs.com (Cathy Lin) writes:
> I have a question for Pro*C users:
>
> I'm using Dynamic SQL to execute some very straightfoward 'FETCH',
> 'INSERT INTO' and 'DELETE' statements(no PL/SQL stuff) and I got
> an error:
>
> ORA-01001: invalid cursor
>
> The place it points to DOES NOT use cursors, just PREPARE and
> EXECUTE. The program works for some of the cases, yet this error
> occurs in some other cases followed by a core dump.
>
> I couldn't find any explanation for this error from 'oerr'. Any
> idea what's happening? I'm using Oracle 7 on SunOS.
First, you must have some 'implied' cursor or you could't 'FETCH'. There is an 'implied' cursor in every select, even one-row selects (that per precompiler manual).

The oracle error messages manual discusses possibilities of AREASIZE and MAXOPENCURSORS too small. If that isn't the case, could it be you're looping, repetitively deleting and/or inserting records based on those 'fetched'? I would look into the sequence of events you are performing, looking at it from the perspective of cursor I/O (even if it is only implied). Try removing pieces of the statement and see whether there is a single statement causing it. You might get a more informative message. (My initial suspicion dealt with the possibility you were committing after each statement, but that is an error ORA-01002). Received on Sat May 20 1995 - 00:00:00 CEST