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

> Is there a way of reusing the cursors?

When you explicitly open a cursor, then explicitly close it. If you issue a SQL statement that implicitly opens a cursor for you, then the implicit cursor will be closed once the SQL statement is done being processed.

When a cursor is opened (implicitly or explicitly) the open cursor count is increased. The cursor cannot be opened if doing so would cause more than OPEN_CURSORS cursors to be opened at that time. This results in the   ORA-1000 error you are receiving. When the cursor is closed, the open cursor count is decreased. The number of open cursors goes up and down during the lifetime of the instance, but does not exceed the value defined by the OPEN_CURSORS initialization parameter.

> 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.

Have you looked at using SQL*Loader or External Tables to quickly and easily load data from a flat file into a table? Once you have the SQL*Loader control file or the external table set up, then inserting into another table is pretty easy, even for millions of rows of data.

HTH,
Brian

-- 
===================================================================

Brian Peasland
oracle_dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown