topic Re: Oracle stored procedure error: maximum open cursors exceeded in Talend Studio

Oracle stored procedure error: maximum open cursors exceeded

musicola — Thu, 02 Aug 2018 17:48:54 GMT

I have a job that iterates off a tHashInput and for each row executes an Oracle stored procedure that uses a in/out cursor. It has about 2000 rows to process. At the stored proc step in the flow, it's processing at about 40 rows/second.

About halfway through, I get this error from the database:

ORA-01000: maximum open cursors exceeded

Is there multi-threading going on? Anything I can adjust?

Or perhaps it's not closing the result sets appropriately or quickly enough?

In general, I do a bunch of other Oracle access with no problems, but this is the first time I've hit this rate/volume.

Re: Oracle stored procedure error: maximum open cursors exceeded

Jesperrekuh — Fri, 03 Aug 2018 12:42:45 GMT

I cant tell if your tOracleSP is creating a connection to Oracle or you use a shared connection (which I suggest doing), however ... there's a max cursor on db/oracle you could increase it by asking your DBA.

I found this thread, which is quite interesting to read.
https://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded

Re: Oracle stored procedure error: maximum open cursors exceeded

musicola — Fri, 03 Aug 2018 14:34:45 GMT

Thanks for your reply. Yes, I'm using a shared connection. I was just suspicious that there might be a threading aspect to Talend or something else that I don't understand.