Search

FME and Oracle Troubleshooting Guide

Liz Sanderson
Liz Sanderson
  • Updated

Introduction

The Oracle formats allow FME to store data in and retrieve data from an Oracle Database.

There are a number of tips for troubleshooting connection issues when working with an Oracle database in the Documentation

Also, the Quick Tips for Oracle is a useful resource.

We recommend making use of the Oracle instant client, as it takes up less disk space and you can easily configure it. FME works with the full Oracle Client and the Oracle Instant Client. The most common issue we encounter is often related to a mismatch of the bit version (32bit vs 64bit) of the Oracle Client and FME. Please read on for lots of helpful tips when working with Oracle formats in FME.
 

Checklist

Are you having problems finding the Oracle formats in FME?

Are the Oracle formats available in the Format Gallery?

  • Why are my Oracle formats greyed out in the formats gallery
  • Do you have the correct license edition? FME Desktop Pricing and Editions
  • Oracle clients for FME running on a 64 bit OS
  • Do you have a question about FME and Oracle Compatibility? Review this article.
  • Make sure that the Oracle client is the correct client for your bit version of Windows and application (FME).
  • There are many versions of Oracle Database and Oracle Clients available.  For the OCI clients, FME typically supports all of them.  Older versions of FME are better suited to older versions of the Oracle Clients, however, newer versions of Oracle Database may block the older clients.  Try to match the Oracle Client version with the Oracle Database version.
  • JDBC Format & Oracle JDBC Driver: If you are using FME with the JDBC Format and making use of ojdbc#.jar be aware that ojdbc10.jar will not work due to incompatibility with Java versions used by FME. FME 2020 and older will be affected by this.  We are working to move FME to a newer Java version. 
  • FME Server System Database on Oracle: If you are hosting the FME Server database on an Oracle database this will require the ojdbc#.jar file to be copied to the server.  Find more details in this article and official documentation here. At this time ojdbc10.jar is not supported.
  • On Windows OS, confirm that the PATH environment variable includes the path that points to the folder containing the Oracle client. This applies to both the full client and instant client.
    • In general, the full Oracle Client path is typically added to the PATH during installation.
    • Since the instant client is not 'installed', it is necessary to add its folder location to the PATH once the client files have been copied to the local system.
  • If on Linux, use the LD_LIBRARY_PATH environment variable to point to the location of the instant Oracle client folder. This applies to both the full client and instant client.
  • If on macOS, see the following articles macOS & FME 2019.1 and older, macOS & FME 2019.2 and newer, and TNS_ADMIN Environment Variable on macOS.


Are you having problems connecting to an Oracle database?

  • ORA-12154 Error
  • ORA-12638 Error: try setting sqlnet.ora file entry SQLNET.AUTHENTICATION_SERVICES= (NONE)
  • How to set up an Oracle Instant Client for use with FME
  • FME can use the TNS_ADMIN environment variable to find your TNSNAMES file. If you have more than one of these files on your machine, it is important to use this variable to ensure FME is using the tnsnames.ora file you have updated.
    • Some users may have both 64bit and 32bit Full Oracle Client on the same system. Each of these clients will have their own tnsnames.ora file. If the user typically uses the 64bit client, then the 32bit tnsnames.ora file may not contain the same database alias. Using TNS_ADMIN ensures you are pointing the FME software to the tnsnames file you wish to use.
  • Use some third party tool to check that the connection can be made. Test with SQL Developer using the TNS connection option. This is the best test option. If this works then FME should work. If this fails then the problem is definitely with the location or contents of the tnsnames.ora file.
  • Check the tnsnames.ora file and remove the xxxx. com part of the names - eg on our machines, we get safe.com amended to service names. Sometimes the connection can't be made while this is present.
  • When editing the tnsnames.ora file, be careful with syntax as an error can cause only part of the file to be read by the Oracle Client, and FME will report an Oracle error of an unknown database alias.
  • Connect to an Oracle Service Name

 

Are you having problems reading from Oracle?

 

Are you having problems writing to Oracle?

 

Are you have issues with Oracle from an FME Server workspace?


Additional Resources

Advanced Writing Techniques

 

Performance Tuning

If you are not having a specific problem with your Oracle but simply find that the translation performance is lagging, please refer to this article for a detailed guide on Writer performance improvements.

Also Performance Tuning FME has some useful tips.
 

Request Technical Support

If you were not able to resolve the issue and need further technical support, please contact our support team by completing and submitting this Support Request Form.

When submitting a support request, please provide the following information (as applicable) in order to help us to provide a timely response:

  • FME log file
  • Oracle version
  • Source and destination datasets, or a small sample of data. It may be necessary to capture the data via a Recorder transformer
  • FME Workspace
  • FME version and build number (preferably a screenshot of Help > About)
  • FMEReport.html (See here)

Once the form has been submitted, we will contact you to quickly resolve your technical issues.

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.