CODEWITHSUNDEEP
sqlrodbcwindows-7-x64rodbc
Jordan C
Jordan C

Reputation: 60

How to setup DSN for Oracle database in Windows 7?

Let me preface this by stating that I have absolutely no idea what I'm doing with regard to setting up or connecting to an Oracle database. I know SQL syntax, but I've never had to worry about connections myself.

My end goal is to connect to a specific database using the RODBC package in R. If you can instead provide me an alternative package to use in order to connect to my database in R, I'd be happy with that too. I am currently connecting to this database through Oracle SQL Developer, but would like to access the data directly through R. Generic connection details to this database that I have setup in SQL Developer are listed below:

Connection Name: connection1
Username: user1
Password: password1
Connection Type: Basic
Hostname: ABCDEF
Port: 51500
SID: sid1

After my initial failure with RODBC, I read that I need to create a DSN in Windows. I've tried to create the DSN using MySQL, Oracle in XE, and SQL Server drivers, using every combination of the connection details listed above that makes sense. I've failed every time. Am I going about this wrong? Is there a simple set of instructions I can follow to set up the DSN, or is there a way around that while still being able to access the database in R? Any help would be greatly appreciated.

Upvotes: 0

Views: 1708

Answers (1)

daniel
daniel

Reputation: 1070

1. Install Instant Client (Basic + ODBC)

32-bit http://www.oracle.com/technetwork/topics/winsoft-085727.html

64-bit http://www.oracle.com/technetwork/topics/winx64soft-089540.html

All files unzip in C:\oracle\instantclient_12_1\

cmd.exe run C:\oracle\instantclient_12_1\odbc_install.exe

2. TNS

Create C:\oracle\instantclient_12_1\NETWORK\ADMIN\tnsnames.ora

Add TNS configuration in tnsnames.ora

Add system Environment Variable TNS_ADMIN=C:\oracle\instantclient_12_1\NETWORK\ADMIN

Restart computer

3. ODBC

32-bit C:\Windows\SysWOW64\odbcad32.exe

64-bit C:\Windows\system32\odbcad32.exe

Add system data source

Driver is "Oracle in instantclient_12_1"

4. Connection

DSN=(data source name);Uid=(user);Pwd=(password);

Please try this.

Upvotes: 1

Related Questions