Problem solve Get help with specific problems with your technologies, process and projects.

Errors executing Oracle recursive query

I'm frequently getting ORA-00604 combined with ORA-01000 (max cursor opens). However, when I checked the code, all the result set and statement objects are closed in the final block (I am using JDBC). The query I am executing is an Oracle recursive query (start with and connect by). Can you tell me what could be wrong and under what circumstances the above error occurs?
It could be that the open_cursors parameter is set to a very low number in init .ora file. The default for this is very low (50). It should be set to 200 or higher.

It could be that even if you are closing the result set you are not closing the SQL statements in your JAVA code.

3. Please ensure that your Enable Connection Pooling if set to yes (for performance reason), is set to no.

Please have your DBA monitor the database and ask to see entries using V$OPEN_CURSORS and V$SYSSTAT data dictionary views.

This was last published in March 2004

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.