[prev in list] [next in list] [prev in thread] [next in thread] 

List:       r-sig-db
Subject:    Re: [R-sig-DB] DB updates with ROracle - ORA-01000
From:       Denis Mukhin <denis.x.mukhin () oracle ! com>
Date:       2012-10-08 2:57:21
Message-ID: 27154A36-3F16-476B-9D0D-580DE71000EC () oracle ! com
[Download RAW message or body]

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@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]]

_______________________________________________
R-sig-DB mailing list -- R Special Interest Group
R-sig-DB@r-project.org
https://stat.ethz.ch/mailman/listinfo/r-sig-db


[prev in list] [next in list] [prev in thread] [next in thread]