Connecting to Oracle with Crystal Reports

Actually, this could be used from anything that uses OLEDB/ODBC. Here are the steps:

1. Go to Oracle’s web site and download the (massive) package to install the ODBC/OLEDB drivers. At this point, it’s something like a 250MB download.

2. Note where you install the Oracle content, this is very important (Mine installed into the 'C:app\my username' folder).

3. You need to add the oracle admin folder to the system path (I don’t know why Oracle can’t do this for you on the install). You’ll need to find the directory. In my case, it is located here 'C:app\my username
   product11.2.0client_1NetworkAdmin'. This directory will be empty of files but there is a 'Sample' directory in it. Go into the sample directory and copy the 'tnsnames.ora' file, and then copy it to the 'Admin' directory one level up. Edit this file, and swap in your values to connect to your oracle instance… mine looks something like this (KEM is the name that you will use to reference this connection, you will want to name yours appropriately):

        KEM =
            (DESCRIPTION =
                (ADDRESS = (PROTOCOL = TCP)(HOST = <my oracle server address>)(PORT = 1521))
                (CONNECT_DATA =
                (SERVER = DEDICATED)
                (SERVICE_NAME = <oracle service name>)))
   

4. We need to add a 'TNS_ADMIN' entry to the system path in the environment variables. Click the Windows Orb or the Start button (no clue what this will be in Windows 8 yet), right click 'Computer' and select 'Properties'. In Windows 7, now choose 'Advanced System Properties'. Click on the 'Advanced' tab of this window and then click the 'Environment Variables' button towards the lower right hand side of the box. In the 'Environment Variables' window you will want to click 'Add' in the 'System variables' group box. The variable name to add is 'TNS_ADMIN' and the variable value is the path to the admin folder buried into the Oracle install folder that we found above in step #3. Click 'Ok' to until all of the boxes you opened disappear.

5. Open the 'ODBC Data Source Administrator' program. You can find this through the Windows Control Panel, or by click the Windows Orb (Start button) in Windows 7/Vista and typing 'ODBC'. Click on the 'System DSN' tab in this dialog, then choose 'Add'. You should now have some variable of 'Oracle in OraClient11g_home' in this dialog, find it and choose that. The box will contain a Data Source Name, this will be the name you will use to reference your service from your ODBC enabled application. If you setup the 'tnsnames.ora' file correct and also setup the 'TNS_ADMIN' environment variable correct then the 'TNS Service Name' drop down will have the name of the Oracle instance to connect to in it’s drop down (screenshot included below). You will want to also enter your 'User ID'. Once you’ve done this, click 'Test Connection' to test your connection. If it works, it will tell you. If it doesn’t, in my experience, you will at best get a cryptic error message and at worst crash the 'ODBC Administrator' dialog.

Random rant: It’s 2012 Oracle, why do we still have to find and setup a tnsnames.ora file?…. since this is one of the most important and must do steps to connect to your databases I would think you would have some kind of front end to streamline this. You have a robust database server but your tools are lacking (a lot). Take a cue from some of the tools you’ve put out with MySQL as of recent. The MySQL Workbench is a great offering… replicate that for your Oracle product.