I. To test ODBC connection and run an SQL script on an Oracle server you should configured our “SQL Databases“ page like this:
- image1.jpg (199.05KiB)Viewed 242707 times
More exactly using a custom connection string like of this format:
Code: Select all
Driver=Oracle in instantclient_19_12;DBQ=<TNS Service Name>;Uid=<username>;Pwd=<password>The <TNS Service Name> can be found in the tnsnames.ora file located in the OracleHome\network\admin folder on the machine where the Oracle ODBC driver is installed. For instance, here it is the content of an tnsnames.ora file:
Code: Select all
# tnsnames.ora Network Configuration File: C:\Users\Caphyon\Desktop\ztzt\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.caphyon.net)
)
)
ORCL_CUSTOM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = DESKTOP-NUE74LC)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl.caphyon.net)
)
)The TNS Service name entries in the above files are ORACLR_CONNECTION_DATA, LISTENER_ORCL, ORCL and ORCL_CUSTOM. So, a working ODBC connection string would be:
- Driver=Oracle in instantclient_19_12;DBQ=ORCL_CUSTOM;Uid=system;Pwd=caphyon!1A
II. For troubleshooting purposes, to double check and test the ODBC connection string you can also use the “ODBC Data Source Administrator” manager of Windows OS. Just proceed like this:
1. Open “ODBC Data Source Administrator” and go to “System DSN”
- image2.jpg (113.44KiB)Viewed 242707 times
- image3.jpg (50.1KiB)Viewed 242707 times
4. Use the [Test Connection] button and fill in your TNS service name, username and password -> click [OK] and you should get a “Connection successful” message
- image4.jpg (57.5KiB)Viewed 242707 times
III. When it comes to connecting to an Oracle server installed on a remote machine you may want to make sure that:
1. On host machine Oracle Database service is running
2. On host machine there is an Oracle listener configured to run and listen on a TCP port (e.g. default port is 1521) - for troubleshooting purposes you can check the configuration settings of OracleHome\network\admin\listener.ora file
3. On host machine there is a firewall exception inbound rule for TCP protocol on port number the above Oracle listener service is configured to run
4. There is a TCP/IP network connection between the client and host machine
5. On the client machine there is installed and configured an Oracle ODBC driver
6. On the client machine the Oracle client is configured to use a Net Service Name connections (i.e. the sqlnet.ora and tnsnames.ora files are configured in OracleHome\network\admin\ folder on client machine)