tags:

views:

295

answers:

1

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.

+1  A: 

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

p.marino
@marino, The MAXCURSOR is same in both production and development environment
Sachin Chourasiya
Are the data the same, too? Assuming you don't correctly release cursors, if you have more "work" done in prod, you will exhaust them sooner.
p.marino