Skip to content

DB updates with ROracle - ORA-01000

2 messages · David Winsemius, Denis Mukhin

#
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
cursors, but I feel I am missing something important.

Any assistance is greatly appreciated,

David
#
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 at serendipityscience.com> wrote: