Question: Updating a database table

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Question: Updating a database table

Avinash Nanda
Hi All,

This is my first post here,I am using groovy 2.4.13

Problem: I need to update a value in the oracle database for large table(754508 records)

Error: Max cursors exceeded

Here is a same code i am using

selectQuery = "select stament goes here"
updateQuery = "Update query with parameters"

sql.row(selectQuery).each{it ->
             sql.execute(updateQuery, [it.name])}
sql.close()

This throws max cursors exceeded and i dont know how to resolve it. Any help would be appreciated.

Also let me know if there is any additional information is needed.

--
Regards
Avi
Reply | Threaded
Open this post in threaded view
|

Re: Question: Updating a database table

Daniel.Sun
Hi Avinash,

FYI,
https://issues.apache.org/jira/browse/GROOVY-8397?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel&focusedCommentId=16273333#comment-16273333
https://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded

In addition, I suggest you try to combine update statement  and select
statement and execute only one statement, or write an anonymous block
`BEGIN...END` to finish your query and update.

Cheers,
Daniel.Sun



--
Sent from: http://groovy.329449.n5.nabble.com/Groovy-Users-f329450.html
Daniel Sun
Apache Groovy committer

Blog: http://blog.sunlan.me
Twitter: @daniel_sun
Reply | Threaded
Open this post in threaded view
|

Re: Question: Updating a database table

Avinash Nanda
Unfortunately i couldnt combine the queries as i have logic which determines what data to update.

Also i tried looking at the below link but could know where i need to update the flag: 

Sorry i tried googling around the issue but didnt find anything relavent

On Thu, May 10, 2018 at 12:08 PM, Daniel.Sun <[hidden email]> wrote:
Hi Avinash,

FYI,
https://issues.apache.org/jira/browse/GROOVY-8397?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel&focusedCommentId=16273333#comment-16273333
https://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded

In addition, I suggest you try to combine update statement  and select
statement and execute only one statement, or write an anonymous block
`BEGIN...END` to finish your query and update.

Cheers,
Daniel.Sun



--
Sent from: http://groovy.329449.n5.nabble.com/Groovy-Users-f329450.html



--
Regards
Avinash
MG
Reply | Threaded
Open this post in threaded view
|

Re: Question: Updating a database table

MG
final Sql sql = ...
sql.setEnableMetaDataChecking(false)

(Also see the comment by Saravanan Subiramaniam at the end of the comments section; have not tried this myself...)

Cheers,
mg


On 10.05.2018 18:15, Avinash Nanda wrote:
Unfortunately i couldnt combine the queries as i have logic which determines what data to update.

Also i tried looking at the below link but could know where i need to update the flag: 

Sorry i tried googling around the issue but didnt find anything relavent

On Thu, May 10, 2018 at 12:08 PM, Daniel.Sun <[hidden email]> wrote:
Hi Avinash,

FYI,
https://issues.apache.org/jira/browse/GROOVY-8397?page=com.atlassian.jira.plugin.system.issuetabpanels%3Acomment-tabpanel&focusedCommentId=16273333#comment-16273333
https://stackoverflow.com/questions/12192592/java-sql-sqlexception-ora-01000-maximum-open-cursors-exceeded

In addition, I suggest you try to combine update statement  and select
statement and execute only one statement, or write an anonymous block
`BEGIN...END` to finish your query and update.

Cheers,
Daniel.Sun



--
Sent from: http://groovy.329449.n5.nabble.com/Groovy-Users-f329450.html



--
Regards
Avinash