RODBC query falsely returning zero rows

Question

I have come across similar posts, but my issue looks different. I haven't been able to fix the results to these queries with "rows_at_time=1" or "believeNRows = False".

I am trying to connect to an Oracle database using RODBC within R. I am able to connect to the database, but I am not retrieving any data, except the column headings. For example I will have zero observations on 18 variables from one table. I do this at work on my PC all the time, but am trying to do this on my macbook pro at home.

Below is the connection code

db<-odbcConnect(dsn="DW", uid="XXXXXX", pwd="XXXXX", rows_at_time = 1, believeNRows=FALSE)

Here is some odbc info:

> odbcGetInfo(db)
       DBMS_Name         DBMS_Ver  Driver_ODBC_Ver Data_Source_Name      Driver_Name       Driver_Ver         ODBC_Ver      Server_Name 
        "Oracle"     "11.01.0000"          "03.00"               ""     "atoradb.so"         "3.1.12"     "03.52.0000"               "" 

As you can see the dsn name and the server name are blank.

Here is my session info:

R version 3.1.1 (2014-07-10)
Platform: x86_64-apple-darwin10.8.0 (64-bit)

locale:
[1] en_US.UTF-8/en_US.ISO8859-1/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RSQLite_0.11.4 DBI_0.3.1      RODBC_1.3-10  

loaded via a namespace (and not attached):
[1] tools_3.1.1

EDIT

Here is one of many queries I am running:

cal <-sqlQuery(db,"select * from d_calendar")

This results in 3 observations from 18 variables, when there are 19,401 observations in total.

Explanation

can you provide an example of the query and the table you are trying to query? – Ochoa

I've provided you the query above. The table I am querying 18 variables. These variables include calendar_key, calendar_date etc. I don't think the issue is with the query, because they work on another machine. – Explanation

This is not an answer. Why are you not using ROracle package to connect to an oracle data base? it is faster and more convenient. – Cattleman

I don't believe ROracle is available for 3.1.1. Also, the method I'm reporting above has worked with another machine. > install.packages("ROracle") package ‘ROracle’ is available as a source package but not as a binary Warning in install.packages : package ‘ROracle’ is not available (for R version 3.1.1) – Explanation

I see you're using OS X. Are you by any chance using the ODBC drivers from Actual Technologies? – Sang

...the reason I ask is that the trial version of the Actual Tech ODBC drivers will only return three rows at a time. If you want them to function fully you'd need to purchase a license. – Sang

I was using ODBC Administrator, but this program was extremely buggy. Are there free ODBC drivers available for OS X? – Explanation

I'm afraid that you are confused. ODBC Administrator is an application (that I think isn't even shipped with OS X any longer; perhaps you have an old OS?) that has nothing to do with the actual drivers. OS X comes with something called ODBC Manager, which is used to set up and configure DSNs, but again, that is entirely separate from the drivers themselves. You could look at either iODBC or unixodbc. There is an extensive discussion of these issues in the RODBC vignette, which I suggest you read carefully. – Sang

Sorry it is an Actual Tech ODBC driver. Any thoughts on what to do from here? – Explanation

Either (1) buy a license from Actual, or (2) switch to using iODBC/unixodbc drivers, or (3) build ROracle from source (I've done it, it's possible on OS X; follow the Linux instructions, but be prepared to Google and tinker) – Sang