How to setup an Oracle Instant Client for use with FME

Question

Can I connect to Oracle using FME and an Oracle Instant Client?
 

Answer

Yes - however be aware that you need the same platform level - i.e. if you have 32-bit FME, then you need a 32-bit Oracle Client. If you have 64-bit FME, then you need a 64-bit Oracle client. This is regardless of what platform your Oracle database is running on and is especially important when using a 64-bit OS. More details on platform issues are available here.
 

Using 32-bit Oracle with 64-bit FME may produce the following error in the FME Workspace log:

Could not open the Enterprise Geodatabase. The error number from ArcObjects is: '-2147155644'. The error message from ArcObjects is: {Cannot connect to database because the database client software failed to load.  Be sure the database client software is installed and configured correctly.}

Also, if you plan to use tns aliases via the tnsnames.ora file, then it is often necessary to create an environment variable called TNS_ADMIN that points to the folder containing the tnsnames.ora file. This is discussed below in this article. If you use easy connect strings then this environment variable will not be required.

Please note that each time the path or other environment variable is changed, FME needs to be restarted. Outside changes will not affect any running processes, or any newly created subprocess of a running process.
 

Windows

1. Download and unzip the Oracle Instant Client into a directory.
2. Add this directory into the PATH environment variable (My Computer->Properties->Advanced->Environment Variables).
3. Add the Instant Client directory to the beginning of the PATH. Do not place quotation marks around the path. - e.g. C:\oracle\instantclient_11_1

Linux

FME Desktop or FME Form

1. Download and unzip the Oracle Instant Client into a directory.
2. Create a symlink of the instant client’s libclntsh.so.x.y library named libclntsh.so if necessary. Newer versions of the client may have this symlink.
   e.g. ln -s libclntsh.so.12.2 libclntsh.so
3. Set the LD_LIBRARY_PATH environment variable and point to the unzipped instant client folder.
    e.g. export LD_LIBRARY_PATH=/home/fmeuser/instantclient_12_2
4. To set the LD_LIBRARY_PATH permanently
   • To set it per-user add it to the ~/.profile file. This will require a logout/login.
   • To set it system-wide, add it to the /etc/environment file. This will require a reboot.
5. If you get an "Oracle client libraries could not be loaded..." error, install libaio1.

FME Server or FME Flow

1. If FME Flow is started as a system service, the LD_LIBRARY_PATH must be exported in the service script. - e.g. LD_LIBRARY_PATH=/home/fmeserviceuser/instantclient_12_2 , export LD_LIBRARY_PATH
2. Otherwise, the LD_LIBRARY_PATH environment variable should be added to /etc/environment file.  This will be prepended to the LD_LIBRARY_PATH set in the startEngines.sh script found in /fmeserver/Server directory

In previous versions of FME, it appears FME may have needed to find a /lib folder with the correct symbolic links but recent tests with FME Desktop 2021 to FME Form 2023.2 are not required for the instant client libraries to load correctly.  
The instructions above should also work for the Full Oracle Client but testing has not been performed in recent releases.  NOTE: Testing was done with Oracle Instant Client 12.2 and 19.6 on Linux Ubuntu 20.04.
 

Mac OS X

• For 2019.2.3.2 and newer refer to macOS Catalina+, FME 2020 and Oracle Client
  • Note: Oracle libraries are not yet available for the macOS M1 ARM architecture (Apple Silicon), please use another platform such as Intel macOS. 
• For 2019.2.3.1 and older refer to macOS, FME and Oracle
• For a helpful tip on creating environment variables on macOS refer to this article

Easy Connect Connection String

The Easy Connect connection string is the easiest way of connecting to your Oracle database. Use this type of connection string as your "dataset" in FME.

user/password@//hostname:port/sid

For example

  scott/tiger@//amidala:1521/orcl

Using this string it is not necessary to enter a username or password - these are included in the Easy Connect string.

Note: If you get a complaint about accessing NLS data files, you will have to set up the NLS_LANG environment variable for your locale. Following a similar method to setting the PATH above, define a new environment variable called NLS_LANG with a value such as AMERICAN_AMERICA.WE8ISO8859P1 or JAPANESE_JAPAN.JA16EUC Sometimes this variable is defined in the Windows registry, and might have an invalid value as far as Oracle is concerned.
 

Setting Up Service Names With a TNSNAMES file

If you prefer not to use the syntax above for specifying your connection, you have the option to create a tnsnames.ora file to configure your connections. tnsnames.ora could reside anywhere on the system, but for convenience it can be placed in the same directory as where the instant client was installed. This text file consists of a series of service definitions, which are of the form:

<net_service_name> = (DESCRIPTION =  
	(ADDRESS_LIST =   
		(ADDRESS = (PROTOCOL = TCP)(HOST = <hostname>)(PORT = <1521>))  
	)  
	(CONNECT_DATA =(SERVICE_NAME=<oracle_sid>)		
	)
)

For example:

AMIDALA = (DESCRIPTION =  
	(ADDRESS_LIST =   
		(ADDRESS = (PROTOCOL = TCP)(HOST = AMIDALA)(PORT = 1521))  
	)  
	(CONNECT_DATA =(SERVICE_NAME = ORCL)  
	)	
) 

Once you've created the tnsnames.ora file, you must then set the TNS_ADMIN environment variable to be the path to the tnsnames.ora, but NOT including the tnsnames.ora filename itself. For example,

TNS_ADMIN=C:\win32apps\oracle\instantclient_10_2

Note: It seems that TNS_ADMIN doesn't like path names with spaces in them. If you run into problems, try placing tnsnames.ora in a directory that does not have spaces in the name. Also, there can be an issue with tab characters in the text so rather than copying and pasting this example entry it is better to create one from scratch.
 

Note 2: For some scenarios, users reported that having the tnsnames.ora file in the oracle\instantclient_xx_x\client\network\admin directory saved them from having to define the TNS_ADMIN environment variable.
 

Note 3: If you have a requirement for both 32bit and 64bit clients, TNS_ADMIN environment variable can be used to inform both clients of the one tnsnames.ora file. This allows you to maintain only one file for both clients.
 

Note 4: Mac users, refer to this article for a helpful tip in creating the TNS_ADMIN environment variable.
 

Further troubleshooting

If the Oracle formats are still greyed out in your Formats Gallery see here for more troubleshooting tips. In addition, we recommend using a third-party tool such as SQLDeveloper or Toad to test your connection. If you can access the Oracle database via these tools using a TNS connection then FME should also work. Testing from SQLPlus is not sufficient.
 

If you receive an "fmeocilink.so" error when running a workspace on Linux, see here for troubleshooting tips.

If you are using tns aliases (tnsnames.ora) and are still having issues connecting, try testing with the easy connection as indicated above. Instead of the tns alias value try the hostname and service name.

hostname:port/sid or hostname:port/servicename