How to connect to Oracle DB via ODBC

Question

I am attempting to connect to a remote Oracle DB via ODBC. I am totally inexperienced and fail to connect.

What I have installed:

1. Oracle 'ODBC Driver for RDB'
2. A program I want to connect from (Altova Mapforce, an ETL)

What I do:

1. Under Administrative tools I open the Windows "ODBC Data Source Administrator
2. I click 'Add..' and select the Oracle ODBC Driver

3. The Window 'Oracle RDB Driver Setup' opens. I fill in:

   • Data source name: free choice
   • Description: I leave blank
   • Transport: I choose TCP/IP
   • Server: I input the IP address of the server
   • Service: I leave 'generic'
   • UserID: I enter the user name (that belongs to the password I have)
   • Attach Statement: no idea what do do here??

Upon choosing 'OK', the 'Oracle RDB ODBC Driver Connect' opens and I am prompted the password. I enter the password and the connection fails.

Questions

• Do I need further programs on my computer, e.g. the Oracle client of Instant client?
• I am never prompted the port of the server - isn't this relevant?
• I am never prompted SID - isn't this relevant?
• I connected from SQL developer easily - it prompted only server IP, port, username, password and SID.

Answer 1

As the original poster said...

For future users: Instructions on how to install instant client and sqlplus correctly.

1. Download and unzip in same folder instant client ODBC package.
2. Execute odbc_install.exe.
3. Configure ODBC connection in Windows.
4. Done.

Answer 2

Rdb was a database system created by DEC (Digital) running on OpenVMS that Oracle acquired in 1994. So "ODBC Driver for RDB" is not right driver.

The solution is to get the Oracle ODBC driver.

Also note that some applications require the ODBC datasource to be as a system DSN while others prefer a user DSN.