Sunday, October 14, 2007

ORA-1000 maximum open cursors exceeded

i wrote a while back about a bug i was hitting with cached metadata and cfqueryparam

Tonight I hit the old ORA-1000 bug because i was still using CFQUERYPARAM which means there is an open cursor for each query.

What I was doing a row count and inserting a different table name into the SQL. The problem was for each different table name (plus the random value i was inserting to avoid caching) was generating a cursor in oracle.

Solution? Sometimes you shouldn't use bind parameters!

Just be sure to validate your variables to avoid sql injection.

1 comment:

Anonymous said...

drop the ColdFusion config parameter
max-pooled statements to less than the number of cursors allowed in the Oracle config file.

If CF has 1000 max-pooled statements it tries to open a cursor for each one and if Oracle is at 300 cursors it will exceed that number. Just set the max-pooled statements to less than 300, like 200 or so.