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

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:  

1. TNS_ADMIN
2. 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