[R] Error while connecting to Oracle using RODBC package

[Daniel Nordlund]

> -----Original Message-----
> From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org]
> On Behalf Of Marc Schwartz
> Sent: Wednesday, July 14, 2010 7:05 AM
> To: vikrant
> Cc: r-help at r-project.org
> Subject: Re: [R] Error while connecting to Oracle using RODBC package
> 
> On Jul 13, 2010, at 11:57 PM, vikrant wrote:
> 
> >
> > Dear All,
> > I want to connnect R with oracle. I am using RODBC package for this
> > connection. Following is my code to connect
> >
> > library(RODBC)
> > channel <- odbcConnect("Vikrant")
> >
> > I am getting following error.
> > 1: In odbcDriverConnect("DSN=Vikrant") :
> >  [RODBC] ERROR: state NA000, code 12288, message [Microsoft][ODBC driver
> > for Oracle][Oracle]ORA-12514: TNS:listener does not currently know of
> > service requested in connect descriptor
> > 2: In odbcDriverConnect("DSN=Vikrant") :
> >  [RODBC] ERROR: state 01000, code 0, message [Microsoft][ODBC Driver
> > Manager] The driver doesn't support the version of ODBC behavior that
> the
> > application requested (see SQLSetEnvAttr).
> > 3: In odbcDriverConnect("DSN=Vikrant") : ODBC connection failed
> >
> > I also tried by using another command
> >
> >
> > library(RODBC)
> > connection = odbcDriverConnect("driver=ORACLE
> > Server;server=01hw0038336";DSN=vikrant;UID=system;PWD=password)
> >
> > Then I am getting folllowing error.
> > 1: In odbcDriverConnect("driver=ORACLE Server;server=01hw088089") :
> >  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver
> > Manager] Data source name not found and no default driver specified
> > 2: In odbcDriverConnect("driver=ORACLE Server;server=01hw088089") :
> >  ODBC connection failed
> >
> >
> > Any help would be greatly appreciated..
> 
> 
> What OS are you on? Presumably Windows.
> 
> Have you correctly configured the System or User DSN for the Oracle
> connection? The error messages suggest perhaps not. Verify the DSN
> connection information with your Oracle Admin.
> 
> See:
> 
>   vignette("RODBC")
> 
> for more information.
> 
> Are you by chance on a 64 bit Windows platform, running 32 bit R? If so,
> be sure that your ODBC driver for Oracle is 32 bit and not 64 bit, which
> could also result in a conflict.
> 
> HTH,
> 
> Marc Schwartz
> 

I would second Marc's advice about checking your setup.  In particular, I believe you need to make sure you have an Oracle client installed on your local machine and you need to edit your tnsnames file to include the database that you wish to connect to.  So do check with your Oracle admin.

Hope this is helpful,

Dan

Daniel Nordlund
Bothell, WA USA