Using a TNSNAMES Connection to an Oracle Database

Use this type of connection to use a service definition in the tnsnames.ora file for the connection information to use. The connection information is substituted in parameters in the JDBC driver when the connection is attempted.

Using tnsnames.ora is common in Oracle database installations.

1. Create a Directory for Oracle Configuration Files

Create a directory on the host where you run Datical DB to use for the wallet file and Oracle configuration files.

The example places it in the oracle_tnsnames directory under the <project> directory in the default location for Datical DB projects. If Oracle is installed on the host, another common location might be $ORACLE_HOME/network/admin.

Linux

/datical/oracle_tnsnames

Windows

C:\datical\oracle_tnsnames

3. Add tnsnames.ora to the Directory

File Desription

tnsnames.ora

Configuration file that defines database addresses.

Create the file manually or copy it from the Oracle database server and alter it.

4. Create the Service Definition in tnsnames.ora

Add the following section to tnsnames.ora. Substitute values for these tokens:

<oracledb-hostname> - the host name of the machine (or VM or AWS instance) where Oracle is running
<oracledb-port> - the port configured for the listener on the database server. Default is 1521.
<oracledb-servicename> - the host name of the machine (or VM or AWS instance) where the Oracle database server is running. If using a SID to connect, specify it instead.

In the following example, the section is named ORA_THROUGH_TNS. You may name it as you wish. Use this name for the TNS Alias setting in Datical DB projects.

ORA_THROUGH_TNS =
(DESCRIPTION =
   (ADDRESS =
      (PROTOCOL = TCP)
      (HOST = <oracledb-hostname>)
      (PORT = <oracledb-port>)
   )
   (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <oracledb-servicename>)
   )
)

5. Set Environment Variables to Locate tnsnames.ora

Set an environment variable to the location of the tnsnames.ora file. When attempting a connection, Datical DB uses environment variables to find the configuration files, then gets information it needs from them.

You have some flexibility in the environment variable to use. Datical DB searches in the following order:

TNS_ADMIN
ORACLE_HOME/network/admin

If a tnsnames.ora file is not found, the connection fails.

6. Set Up Datical Project DbDefs to Use the TNS Alias

When you create a DbDef in a project and choose Oracle as the database type, you set the following property:

Connection Type - Choose TNSNAMES/LDAP
TNS Alias - Specifies the service name in tnsnames.ora that contains configuration information for the connection.

See also Configuring Step Settings (DbDefs)#OracleDatabaseStepSettings.

7. Testing the Connection

See Testing Oracle Connections

Browser not supported