RODBC and Oracle 11g Issue.....
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 at 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 at akoyavsrv05 instantclient_11_2]$ ./sqlplus parts/parts at 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