[R-sig-DB] [RODBC] Error (ORA-12560 ) when trying to connect to Oracle database

[Marc Schwartz]

On Jul 11, 2013, at 2:29 PM, Daniel Reis <Daniel_Reis using cargill.com> wrote:

> Hello,
> 
> 
> I installed the Oracle 11g client on my machine, and tested the
> connection to my target Oracle database through MS Access. All seems to
> be working fine.
> 
> When I try to connect to the very same DB using R with:
> 
> 
> channel <- odbcConnect(dsn="MyDSN", uid="XXXXX", pwd="XXXXXXXX",
> believeNRows=FALSE)
> 
> 
> I get the following error:
> 
> In odbcDriverConnect("DSN=MyDSN;UID=XXXX;PWD=XXX",  :
> 
> [RODBC] ERROR: state HY000, code 12560, message
> [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error
> 
> 
> 
> Am I missing the installation of some component/driver? I read something
> about installing the Oracle Instant Client (free) but thought the Oracle
> 11g client would be sufficient. What am I missing?
> 
> 
> 
> Dan


Presuming that you are on a 64 bit version of Windows, my first guess is that this might be a 32 bit/64 bit architecture conflict. 

I am guessing that Access is 32 bit and so is the ODBC driver, hence it works. 

In the case of R/RODBC, if you are running 64 bit R, you need to be running 64 bit ODBC drivers. See:

  vignette("RODBC")

and pay attention to Appendix B on Specifying DSNs, notably footnote 15 at the bottom of page 26. Also the second full paragraph on page 23, discussing Oracle and Windows.

A Google search for the Oracle error code above also brings up some other possibilities if this is not a 32/64 bit issue.

Regards,

Marc Schwartz