[R-sig-DB] DB updates with ROracle - ORA-01000
David
d@v|d @end|ng |rom @erend|p|ty@c|ence@com
Sun Oct 7 23:16:54 CEST 2012
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