Sometimes on a ColdFusion page which does heavy db lifting (ie: a huge number of inserts) even though the queries are auto-commited, you will run out of cursors on the database. This seems to happen mainly in my migration scripts, and nightly update scripts, but could easily happen to anyone.
Here is the error:
Error Executing Database Query.
[Macromedia][Oracle JDBC Driver][Oracle]ORA-01000: maximum open cursors exceeded
One solution which a number of people have offered up is to up the CURSORS number in the oracle config (go get your dba). This however isn’t always practical, especially because you have to kick the database to get that setting to take.
The problem occurs when your max-pooled statements setting on the datasource is higher than the CURSORS setting in the oracle database. By default in ColdFusion this is 1000 statements. In my experience the normal setting in Oracle 8.1.6 is 150 or 300. Big difference between the two.
ColdFusion will ‘pool’ a query when you use cfqueryparam, so that when the paramater changes (and the rest of the query stays the same) it executes faster. To ‘pool’ a statement though, it must keep a cursor open on the database.
Simply lowering the setting in the administrator so your max-pooled statements is lower than your database cursors, or removing cfqueryparam from your queries is sufficient. A setting of 100 for max-pooled statements works for me.
To check how many cursors are in use run this sql statement:
SELECT v.value as numopencursors ,s.machine ,s.osuser,s.username
FROM V$SESSTAT v, V$SESSION s
WHERE v.statistic# = 3 and v.sid = s.sid