[R] RODBC and Oracle

[Thomas Harte]

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 5000000 (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 <john_d_mchenry at yahoo.com>
> Subject: [R] RODBC and Oracle
> To: r-help at r-project.org
> Message-ID: <961118.63176.qm at web35408.mail.mud.yahoo.com>
> 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                          
> day            27                          
> svn rev        42083                       
> language       R                           
> version.string R version 2.5.1 (2007-06-27)