Re: Help needed with "ORA-01000: maximum open cursors exceeded" error

From: Jim Smith <jim_at_jimsmith.demon.co.uk>
Date: Fri, 7 Apr 2006 11:54:53 +0100
Message-ID: <BrHloZn9TkNEFwgm@jimsmith.demon.co.uk>

In message <1144263297.531530.285580_at_g10g2000cwb.googlegroups.com>, "g_chime_at_yahoo.com" <g_chime_at_yahoo.com> writes
>>Every SQL statement requires at least one cursor, whether you explicitly
>>declare it or not. Some cursors are implicitly defined.
>
>Is there a way of reusing the cursors?
>
>I am parsing a flatfile in my application and insert the data one row
>at a time.
>Some flatfiles might contain millions of rows, i.e. I need to be able
>to insert millions of rows in one session.
>

It looks as if you are creating a new statement for each line processed. Not knowing the programming language you are using I can't be more specific, but you should be doing something like this.

conn=getConnection();

stmt=conn.prepareStatement('insert int x(a,b,c) values (?,?,?); while (readline <> EOF)
loop

  stmt.setParam(1,<value from file>);
  stmt.setParam(2,<value from file>);
  stmt.setParam(2,<value from file>);
  stmt.execute();

end loop;

stmt.close();
conn.commit();
conn.close();

I suspect you are doing
while (readline <> EOF)
loop
 stmt.execute('insert into x(a,b,c) values' + <data from file>) end loop

-- 
Jim Smith
I'm afraid you've mistaken me for someone who gives a damn.

Received on Fri Apr 07 2006 - 05:54:53 CDT