[R] RODBC and Oracle
Hi WizaRds, I'm experiencing a problem connecting to an Oracle 10g database via RODBC (I'm getting this on Microsoft XP). The same SQL queries via PL/SQL Developer work just fine, but when I pump the query through sqlQuery in RODBC then I get a data frame back with 0 rows. I cut the query down alternating between PL/SQL and RODBC until I figured that it's some kind of row limit or buffer limit thing going on. I searched the archives and Brian Ripley suggested playing with believeNRows parameter (setting it to FALSE) as Oracle is not well behaved. I've tried various combinations of this and delving into the lower level functions like odbcQuery and sqlGetResults but I'm still getting the same thing: 0 rows back unless I cut the query down to a more return a more reasonable range. Obviously I can't reproduce the problem here because of the database, but does anyone have any suggestions/tips/pointers to get me going toward a solution? Maybe it's a settings problem in the Microsoft ODBC thing under Control Panel->Administrative Tools->Data Sources Any suggestions on this? All help gladly received. Thanks! Jack. platform i386-pc-mingw32 arch i386 os mingw32 system i386, mingw32 status major 2 minor 5.1 year 2007 month 06 day27 svn rev42083 language R version.string R version 2.5.1 (2007-06-27) - [[alternative HTML version deleted]] __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC and Oracle
jack, i had a similar problem with Oracle at work. i don't have access to my yahoo email account at work but i'll take a stab at directing you to the solution here and now FWIW. open Data Sources then click on the System DSN (i think?) tab. highlight Oracle 10g and hit configure. there's a default buffer size set to something like 64000; make the buffer something like 500 (or some big number). close the RODBC connection and re-connect. you should be good to go---at least, i was when i did this. (if i got a tab or something wrong in the above just hunt around until you find that Oracle buffer thingy.) the client that you used (PL/SQL) is probably not connecting to the database via ODBC, so you're not comparing apples with apples. cheers, thomas. > Message: 82 > Date: Fri, 28 Sep 2007 17:26:24 -0700 (PDT) > From: John McHenry <[EMAIL PROTECTED]> > Subject: [R] RODBC and Oracle > To: r-help@r-project.org > Message-ID: <[EMAIL PROTECTED]> > Content-Type: text/plain > Hi WizaRds, > I'm experiencing a problem connecting to an Oracle 10g database via RODBC > (I'm getting this on Microsoft XP). > The same SQL queries via PL/SQL Developer work just fine, but when I pump the > query > through sqlQuery in RODBC then I get a data frame back with 0 rows. > I cut the query down alternating between PL/SQL and RODBC until I figured > that it's some kind of row limit or buffer limit thing going on. > I searched the archives and Brian Ripley suggested playing with believeNRows > parameter (setting it to FALSE) as Oracle is not well behaved. I've tried > various > combinations of this and delving into the lower level functions like odbcQuery > and sqlGetResults but I'm still getting the same thing: 0 rows back unless > I cut the query down to a more return a more reasonable range. > Obviously I can't reproduce the problem here because of the database, but does > anyone have any suggestions/tips/pointers to get me going toward a solution? > Maybe it's a settings problem in the Microsoft ODBC thing under > Control Panel->Administrative Tools->Data Sources > Any suggestions on this? > All help gladly received. > Thanks! > Jack. > platform i386-pc-mingw32 > arch i386 > os mingw32 > system i386, mingw32 > status > major 2 > minor 5.1 > year 2007 > month 06 > day27 > svn rev42083 > language R > version.string R version 2.5.1 (2007-06-27) __ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.
Re: [R] RODBC and Oracle
I just joined and though I did quickly read the posting guide it is quite
possible not going to be a perfect posting. For one I tried to figure out how
to reply to an existing message on the topic. I gave up but figured I keep at
least the same subject.
In a gist I get an empty data frame after issuing the following select query
from R 2.9.1 to Oracle 10.2.0.1 64bit via RODBC 1.3 and Oracle 11g 32bit driver
-- all on the same Windows 2003 Server 64bit machine.
sqlTxnFvs = "select txn.*,pan.*,mcc.*,tmzip.*
from mddsg100bp txn
inner join mdd_panfvs pan on (txn.seq_no = pan.seq_no)
inner join mdd_mccfvs mcc on (txn.seq_no = mcc.seq_no)
inner join mdd_tmzipfvs tmzip on (txn.seq_no = tmzip.seq_no)
where is_test = '%d' and is_pos = '%d' and ora_hash(txn.seq_no,%d,%d)=0"
dbConn =
odbcConnect("mysid",uid="myuser",pwd="mypass",case="toupper",believeNRows=FALSE)
tfg = sqlQuery(dbConn, sprintf(sqlTxnFvs,0,0,1/sratg-1,seeds[i]))
dbErr = odbcGetErrMsg(dbConn)
All of the tables except mddsg100bp are IOT tables (index organized tables aka
clustered index in MSSQL).
I set the believeNRows=FALSE.
I tried increasing the ODBC DSN config buffer from the default 64k to first 5M
and then 10M.
I started with columns from the narrower table mcc.* (38) and kept adding the
rest of the tables (tmzip.* 43, pan.* 62, and txn.* 64) one at a time - tfg is
correctly populated in all but the last case.
Selecting just txn.* fields works as well.
Furthermore it cannot be the buffer/data size as "select txn.field1,
pan.field2, mcc.field3, tmzip.field4" returns an empty data frame as well.
It cannot be the Oracle ODBC driver - tested query via ODBC Query util and it
works.
Also same query works over RJDBC (and of course all of the Oracle client tools).
I tried to trace at the ODBC and SQLNET levels but can't seem to make sense of
the latter or collect anything out of the former.
Thanks.
Edi Bice
[[alternative HTML version deleted]]
__
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
