[R-sig-DB] RODBC and Oracle 11g Issue.....

[Marc Schwartz]

On Mar 4, 2010, at 7:07 PM, Joe O wrote:

> I am running CentOS release 5.3. This is the 64 bit version.
> 
> R Version is "R version 2.10.0 (2009-10-26)"
> RODBC version is "1.3-1"
> 
> I have installed unix-odbc version unixODBC-2.2.14, and configured it (odbcinst.ini and odbc.ini).
> 
> I've got the 11.1 Oracle instant client installed, and configured (and all the environment variables set).
> 
> iSQL works:
> 
> [ruser using akoyavsrv05 instantclient_11_2]$ isql -v OracleLocal parts parts
> +---------------------------------------+
> | Connected!                            |
> |                                       |
> | sql-statement                         |
> | help [tablename]                      |
> | quit                                  |
> |                                       |
> +---------------------------------------+
> SQL> select distinct segment from parts
> +-----------------------------------------+
> | SEGMENT                                 |
> +-----------------------------------------+
> | BTS                                     |
> | FABRICATIONS                            |
> | BAR STOCK                               |
> | FORGINGS                                |
> | TUBES                                   |
> | CASTINGS                                |
> | STATUS6                                 |
> +-----------------------------------------+
> SQLRowCount returns -1
> 7 rows fetched
> 
> as does sqlplus:
> 
> [ruser using akoyavsrv05 instantclient_11_2]$ ./sqlplus parts/parts using VSRV06
> 
> SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 4 19:02:47 2010
> 
> Copyright (c) 1982, 2009, Oracle.  All rights reserved.
> 
> 
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
> With the Partitioning, OLAP, Data Mining and Real Application Testing options
> 
> SQL> select distinct segment from parts;
> 
> SEGMENT
> ----------------------------------------
> BTS
> FABRICATIONS
> BAR STOCK
> FORGINGS
> TUBES
> CASTINGS
> STATUS6
> 
> 7 rows selected.
> 
> SQL> 
> 
> 
> But R does not seem to want to work:
> 
> R version 2.10.0 (2009-10-26)
> Copyright (C) 2009 The R Foundation for Statistical Computing
> ISBN 3-900051-07-0
> 
> [...]
> 
> Type 'q()' to quit R.
> 
>> library(RODBC)
>> channel <- odbcConnect("OracleLocal", uid="parts", pwd="parts")
>> sqlQuery(channel, "select distinct segment from parts", errors=TRUE)
> character(0)
>> sqlTables(channel)
>> 
> 
> Any ideas? The sqlQuery should return 7 rows, right?
> 
> Thanks in advance

Hi,

In your calls to odbcConnect() and sqlQuery() try using 'rows_at_time = 1':

  channel <- odbcConnect("OracleLocal", uid="parts", pwd="parts", rows_at_time = 1)
  sqlQuery(channel, "select distinct segment from parts", errors=TRUE, rows_at_time = 1)

In the recent versions of RODBC, the default for rows_at_time is now 100, which has been known to cause problems on certain systems, Oracle being one. On my system, which is OSX 10.6.2 connecting to Oracle 11g on RHEL with RODBC, I need to make this adjustment in order to get reliable query results. This is touched on in the Details section of ?sqlQuery.

HTH,

Marc Schwartz