FME, macOS and the Oracle Client - a tip for setting the TNS_ADMIN Env Variable

Introduction

If you make use of FME on the macOS and want to use the Oracle formats with a TNS Alias it is necessary to create and set the TNS_ADMIN environment variable. With this environment variable set on the macOS, FME will be able to find the correct tnsnames.ora file and utilize the TNS Aliases within that file.

An entry in the tnsnames.ora file may look like this:

easyalias =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 123.123.123.123)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mypb1.mycorpz1.com)
    )
  )

This allows you to use the value, for example, easyalias to refer to the database and it removes the requirement to enter the IP (or hostname) and service name (or SID) of the Oracle database when adding a new Database Connection in FME (or other applications using Oracle).

So what's the problem?

The problem is if you are not a terminal junkie (command line) or don't know your way around the macOS system, creating and setting this new environment variable is challenging.

What's the tip then?

So the question is, "Does a GUI exist for the macOS to help set Environment Variables?". The answer is, yes, and it is called EnvPane.

This is one tool I've found that still works today in Catalina. EnvPane is a macOS preference pane for environment variables. You can download and learn more about EnvPane here.

Once you have installed EnvPane, you'll see a new icon in your System Preferences (cmd+space then type 'pref' to locate System Preferences).

Double-clicking on this icon will open EnvPane.

From here you can create the new environment variable called TNS_ADMIN and point it to the folder where you have saved your local tnsnames.ora file. Restarting FME Workbench, you should now be able to make use of the database alias in the Database Connection.

A shout out to the small team of developers behind EnvPane! Thank you for this tool.

I hope this was helpful.

If you have other solutions for creating this environment variable (or others) please comment below.