Hi,
Want to add some notes. Did some further reading on the issue. If a jdbc
callable statement is started on a jdbc connection object.
Say: cs = Conn.prepareCall()
Then it must be put into a begin / end block and under ensure part do
"cs.close"
Begin
cs = Conn.prepareCall("some procedure");
cs.executeQuery
Ensure
cs.close
End
This makes sure that the cursor is released on oracle side.
Tested this and have reduced the open_cursors parameter in oracle from 1000
to 100 and it works fine.
Hope it helps.
Dylan
On Sat, Jan 29, 2022, 00:23 Ho Gi <[email protected]> wrote:
> We ran into some cursor issues as well. I had to make some adjustments
> like:
>
> after_conn = proc do |c|
>
> stat = c.create_statement
>
> stat.execute_query "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD
> HH24:MI:SS'"
>
> *stat.execute_query "ALTER SESSION SET CURSOR_SHARING=FORCE" #
> <-------- CURSOR ******
>
> stat.execute_query "ALTER SESSION SET QUERY_REWRITE_ENABLED=FALSE"
>
> end
>
> db_connect =
> "jdbc:oracle:thin:#{db_cfg['user']}/#{db_cfg['password']}@#{db_cfg['host']}:#{db_cfg['port']}/#{db_cfg['database']}"
>
> DB = Sequel.connect(db_connect, :max_connections =>
> db_cfg['max_connections'],
>
> :pool_timeout => db_cfg['pool_timeout'],
> :after_connect => after_conn,
>
> :autosequence => true)
>
>
> I think we may have also bumped cursors at some point:
>
> ALTER SYSTEM SET OPEN_CURSORS = 400 SCOPE=BOTH;
>
>
> Hope this helps.
>
>
>
> On Fri, Jan 28, 2022 at 12:42 PM Jeremy Evans <[email protected]>
> wrote:
>
>> On Fri, Jan 28, 2022 at 12:54 AM Dylan Camilleri <[email protected]>
>> wrote:
>>
>>> Hi Jeremy!
>>>
>>> I am encountering this error using sequel with jdbc connected to oracle.
>>> Is there a way I can commit / control the amount of open cursors?
>>>
>>> Caused by:
>>> oracle.jdbc.OracleDatabaseException: ORA-01000: maximum open cursors
>>> exceeded
>>>
>>
>> Not sure. I've not seen that error in my testing with Oracle. Maybe
>> look at the Oracle JDBC driver and see if they offer a setting to control
>> it, or discuss with your database administrator?
>>
>> Thanks,
>> Jeremy
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sequel-talk" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To view this discussion on the web visit
>> https://groups.google.com/d/msgid/sequel-talk/CADGZSSeekLROk%3DJa%2BLyWbEHyvyhUJbw44CuFg4O_AL6YTYfemg%40mail.gmail.com
>> <https://groups.google.com/d/msgid/sequel-talk/CADGZSSeekLROk%3DJa%2BLyWbEHyvyhUJbw44CuFg4O_AL6YTYfemg%40mail.gmail.com?utm_medium=email&utm_source=footer>
>> .
>>
> --
> You received this message because you are subscribed to the Google Groups
> "sequel-talk" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sequel-talk/CAF%3DXP2JJS0P3ECP6OYLk3foeqfH%3D7bOGf6czFBbPjao2-dZUHA%40mail.gmail.com
> <https://groups.google.com/d/msgid/sequel-talk/CAF%3DXP2JJS0P3ECP6OYLk3foeqfH%3D7bOGf6czFBbPjao2-dZUHA%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CAFQwEfvRC7cy3tOO1_PRpao_YA99f_ptbE6GTcp9Woqx_pUMYA%40mail.gmail.com.
