[R-sig-DB] DB updates with ROracle - ORA-01000
Denis Mukhin
den|@@x@mukh|n @end|ng |rom or@c|e@com
Mon Oct 8 04:57:21 CEST 2012
David,
You are missing a call to dbClearResult right after dbSendQuery. This call will release the cursor. However, a better way to do this is to use bulk binds. I had an example in my previous post to this list. The question was about doing this in RPostgreSQL. If you have trouble finding it I can resend it to you.
Denis
On Oct 7, 2012, at 5:16 PM, David <david using serendipityscience.com> wrote:
> I have an R script that makes an ROracle connection to a DB and pulls data.. analyzes and then updates approx 7,000 records.
>
> I have two functions defined in my script that look like this:
>
> # Create the updateTC function
> updateTC <- function(dfrm) {
> with(dfrm, {
> updateDB(paste("update SIMETRA.RTA_MARKET_STATS set CLOSEST_LOC_TRAVEL_COEFFICIENT = '",format(GEOGRAPHIC_COEFFICIENT, scientific=FALSE, digits=6),"' where RTA_MARKET_STATS_ID = '",MSID,"'", sep = ""))
> })
> }
>
> # Create the updateDB function
> updateDB <- function(stmt) {
> for (update in stmt) {
> res <- dbSendQuery(conn, update)
> res <- dbCommit(conn)
> summary(drv)
> }
> }
>
> and I call it like so (cr is a dataframe with my results):
>
> # Update the DB with the new geographic coefficients
> updateTC(cr)
> res <- dbCommit(conn)
>
> You can tell I wrote this code while still familiarizing myself with vector-based functions, but I still don't know the most efficient way to get the fields updated properly. Still, when I had this same code using the RJDBC driver, I had no issues with cursors (I switched for other reasons). But now I immediately get an ORA-01000 from the database, which is "Maximum open cursors exceeded"...
>
> Thoughts? I don't think I should increase the maximum cursors on the DB to > 7,000. I put in the dbCommit after the update in an attempt to release cursors, but I feel I am missing something important.
>
> Any assistance is greatly appreciated,
>
> David
[[alternative HTML version deleted]]
More information about the R-sig-DB
mailing list