Discussion:
How to avoid "ORA-01000: maximum open cursors exceeded" error while Connector Loop
(too old to reply)
Sergey P
2018-12-09 16:12:54 UTC
Permalink
Hello all

I use Connector Loop (JDBC Connector) for lookup user's permissions by Iterate Mode by this useful guide - http://www.tdiingoutloud.com/2013/09/using-connector-loop-to-replace.html.

All work fine till number iterations less than 1000, after these number I have SQLexeption: "ORA-01000: maximum open cursors exceeded".

I've tried to empty work.removeAllAttributes() in Before GetNext hook as explained by http://www.tdiingoutloud.com/2013/11/connector-loops.html
but not luck.

May be exist someone solution for this?

Regards,
Sergey
Eddie Hartman
2018-12-09 21:56:29 UTC
Permalink
There is a drop-down in the connection tab of the connector where you can control when to free locks. Try setting this to 'After all operations including Select'. If you still get this error then click on the rightmost More... Button for the Connector Loop and configure it to 'Initialize and terminate each time'. Let me know what works for you.

/Eddie
Sergey P
2018-12-10 09:57:49 UTC
Permalink
Post by Eddie Hartman
There is a drop-down in the connection tab of the connector where you can control when to free locks. Try setting this to 'After all operations including Select'. If you still get this error then click on the rightmost More... Button for the Connector Loop and configure it to 'Initialize and terminate each time'. Let me know what works for you.
/Eddie
Thank for your answer Eddie
Maybe I miss something in your guides, but in Connector Loop I can't to find 'drop-down' tab and 'initialize and terminate each time' , because in my Connector Loop branch is empty(dampentry script) and all configurations of JDBC connector I've configured in Connector Loop.

Hope I didn't mixed up all things, but if so, let me know about my mistake please

Sergey

Sergey
Sergey P
2018-12-10 10:42:33 UTC
Permalink
Post by Sergey P
Post by Eddie Hartman
There is a drop-down in the connection tab of the connector where you can control when to free locks. Try setting this to 'After all operations including Select'. If you still get this error then click on the rightmost More... Button for the Connector Loop and configure it to 'Initialize and terminate each time'. Let me know what works for you.
/Eddie
Thank for your answer Eddie
Maybe I miss something in your guides, but in Connector Loop I can't to find 'drop-down' tab and 'initialize and terminate each time' , because in my Connector Loop branch is empty(dampentry script) and all configurations of JDBC connector I've configured in Connector Loop.
Hope I didn't mixed up all things, but if so, let me know about my mistake please
Sergey
UPD Eddie, my mistake, I've found 'drop-down' option in 'Advanced' configuration and selected option 'After every database operation(including Select), but AL failed with same error.
Unfortunately , I can't to find option - 'initialize and terminate each time' in 'more' button, I have only three this - 'Do Nothing', 'Initialize and Select/Lookup', 'Select/Lookup only'
Eddie Hartman
2018-12-10 20:46:01 UTC
Permalink
Post by Sergey P
Post by Sergey P
Post by Eddie Hartman
There is a drop-down in the connection tab of the connector where you can control when to free locks. Try setting this to 'After all operations including Select'. If you still get this error then click on the rightmost More... Button for the Connector Loop and configure it to 'Initialize and terminate each time'. Let me know what works for you.
/Eddie
Thank for your answer Eddie
Maybe I miss something in your guides, but in Connector Loop I can't to find 'drop-down' tab and 'initialize and terminate each time' , because in my Connector Loop branch is empty(dampentry script) and all configurations of JDBC connector I've configured in Connector Loop.
Hope I didn't mixed up all things, but if so, let me know about my mistake please
Sergey
UPD Eddie, my mistake, I've found 'drop-down' option in 'Advanced' configuration and selected option 'After every database operation(including Select), but AL failed with same error.
Unfortunately , I can't to find option - 'initialize and terminate each time' in 'more' button, I have only three this - 'Do Nothing', 'Initialize and Select/Lookup', 'Select/Lookup only'
Sorry, you are right. The choice to Init & Terminate Every Time is only available for the Initialize dropdown of a Connector. However, if you choose Initialize & Select/Lookup it means the Loop re-initializes the Connector each time. Have you tried that?

/Eddie
Sergey P
2018-12-16 12:22:53 UTC
Permalink
Post by Eddie Hartman
Post by Sergey P
Post by Sergey P
Post by Eddie Hartman
There is a drop-down in the connection tab of the connector where you can control when to free locks. Try setting this to 'After all operations including Select'. If you still get this error then click on the rightmost More... Button for the Connector Loop and configure it to 'Initialize and terminate each time'. Let me know what works for you.
/Eddie
Thank for your answer Eddie
Maybe I miss something in your guides, but in Connector Loop I can't to find 'drop-down' tab and 'initialize and terminate each time' , because in my Connector Loop branch is empty(dampentry script) and all configurations of JDBC connector I've configured in Connector Loop.
Hope I didn't mixed up all things, but if so, let me know about my mistake please
Sergey
UPD Eddie, my mistake, I've found 'drop-down' option in 'Advanced' configuration and selected option 'After every database operation(including Select), but AL failed with same error.
Unfortunately , I can't to find option - 'initialize and terminate each time' in 'more' button, I have only three this - 'Do Nothing', 'Initialize and Select/Lookup', 'Select/Lookup only'
Sorry, you are right. The choice to Init & Terminate Every Time is only available for the Initialize dropdown of a Connector. However, if you choose Initialize & Select/Lookup it means the Loop re-initializes the Connector each time. Have you tried that?
/Eddie
Hello Eddie.
I've tried this too. Unfortunately this not work for me, so I used JDBC connector with LookUp mode instead...
I made this for custom adapter for IGI , so it's could work with big amount of data and I should be sure that exceptions like "ORA-01000: maximum open cursors exceeded" not coming .

Thanks,
and Regards,
Sergey

Loading...