How to Test Oracle Connectivity

Connecting to an Oracle database via ODBC requires two things:

• An Oracle Service Name (known as an Alias in versions prior to Oracle 8.0) defines the location (server name and port) and database (instance) for the connection.
• An ODBC Data Source simply defines which service name should be used for the ODBC connection.

Each of these items must be set up correctly for a connection to work. When connection problems occur, it is best to test each piece of the connection to determine where the problem lies.

Testing the Oracle Service Name

The TNSPING utility can be used to test an Oracle Service Name. To use it:

1.Open a Command Prompt (click Start, click Run, type cmd, and then click OK).

2.Type tnsping <service name> (for Oracle 7.3 or Oracle 8i and later) or tnsping80 <service name> (for Oracle 8.0), and then press enter.

The TNS Ping Utility will result in an "OK" or a "Connect failed" message. In the event of a "Connect failed" message, a cause or reason will be included (for example, "TNS-12545: Connect failed because target host or object does not exist"). If the TNS Ping fails, there is a problem with the Oracle Service Name. The configuration for the service name must be checked using the Oracle Net Assistant or Net Configuration Assistant. An Oracle DBA may need to provide the correct connection information.

Testing the ODBC Data Source

The Oracle ODBC Test utility may be used to test an ODBC data source. To use it:

1. Click Start, select Programs, and then select the Oracle program folder (the name for this folder may vary).
2. In earlier versions of Oracle, the Oracle ODBC Test program may be listed in the Oracle program folder. If it is, click on it. If it is not, select the Network Administration folder, and then click on Oracle ODBC Test. An Oracle ODBC 32Bit Test window appears.
3. Click the Connect button. The Select Data Source window appears.
4. Click the Machine Data Source tab, and then select the data source you want to test and click OK.
5. In the login box, type the User Name and Password in the appropriate boxes, and then click OK.
   
   After clicking OK, the Connect button is unavailable and the Disconnect, All Tables, User Tables, and Execute buttons should be enabled. Otherwise, an error will occur. If the TNS Ping succeeded, but the ODBC Test fails, there is a problem either with the ODBC Data Source or with the login and password provided to make the connection. Verify that both are correct, then try again.