ORA-01001: invalid cursor

Question

I am getting an oracle error ORA-01001: invalid cursor in the production where a number of transactions are processed in bulk. However the same code works fine in development.

I need to know when can one have ORA-01001: invalid cursor in an update query. I did some googling and found that there are two possibilities of getting this error:

1. Numbers of cursors opened becomes greater than MAXCURSOR permitted?
2. An attempt to fetch is made without opening a cursor.

Has anyone faced the same problem I had described above? Please suggest solutions.

Answer 1

Yes, these are the common causes (see also this if you don't have already).

Considering you are using two different environments (dev/prod) have you verified that the MAXCURSOR parameter is the same (or that Prod MAXCURSOR > Dev MAXCURSOR)?

You should also investigate your batch process and see if the number of data could cause your process to open more cursor in prod. Example: your batch launches a stored procedure for every department code in a departments table, and every instance of this procedure opens N cursors.

If you have - say - 3 dep. codes in dev because it is enough for your tests, and 34 department codes in Prod, you could use 10 times the cursor and get in the same situation...