Connecting to Oracle (ODBC)
This example illustrates a common scenario where you connect from DatabaseSpy to an Oracle database server on a network machine, through an Oracle database client installed on the local operating system.
The example includes instructions for setting up an ODBC data source (DSN) using the database connection wizard in DatabaseSpy. If you have already created a DSN, or if you prefer to create it directly from the ODBC Data Source administrator in Windows, you can do so, and then select it when prompted by the wizard. For more information about ODBC data sources, see Setting up an ODBC Connection.
•The Oracle database client (which includes the ODBC Oracle driver) must be installed and configured on your operating system. For instructions on how to install and configure an Oracle database client, refer to the documentation supplied with your Oracle software.
•The tnsnames.ora file located in Oracle home directory contains an entry that describes the database connection parameters, in a format similar to this:
ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server01)(PORT = 1521)) ) (CONNECT_DATA = (SID = orcl) (SERVER = DEDICATED) ) ) |
The path to the tnsnames.ora file depends on the location where Oracle home directory was installed. For Oracle database client 11.2.0, the default Oracle home directory path could be as follows:
C:\app\username\product\11.2.0\client_1\etwork\admin\tnsnames.ora |
You can add new entries to the tnsnames.ora file either by pasting the connection details and saving the file, or by running the Oracle Net Configuration Assistant wizard (if available). If you want these values to appear in dropdown lists during the configuration process, then you may need to add the path to the admin folder as a TNS_ADMIN environment variable.
To connect to Oracle using ODBC:
1.Start the database connection wizard.
2.Select Oracle (ODBC / JDBC), and then click Next.
3.Select ODBC.
4.Click Edit Drivers.
5.Select the Oracle drivers you wish to use (in this example, Oracle in OraClient11g_home1). The list displays the Oracle drivers available on your system after installation of Oracle client.
6.Click Back.
7.Select Create a new data source name (DSN) with the driver, and then select the Oracle driver chosen in step 4.
Avoid using the Microsoft-supplied driver called Microsoft ODBC for Oracle driver. Microsoft recommends using the ODBC driver provided by Oracle (see http://msdn.microsoft.com/en-us/library/ms714756%28v=vs.85%29.aspx) |
8.Click Connect.
9.In the Data Source Name text box, enter a name to identify the data source (in this example, Oracle DSN 1).
10.In the TNS Service Name box, enter the connection name as it is defined in the tnsnames.ora file (see prerequisites). In this example, the connection name is ORCL. Note: If you wish to have the dropdown list of the combo box populated with the values of the tnsnames.ora file, then you may need to add the path to the admin folder as a TNS_ADMIN environment variable.
11.Click OK.
12.Enter the username and password to the database, and then click OK.