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)