Fbomg

Step 1: Where to install the Files

Do you think oracle will make a version of it's database for Mac OS X Server? What about their application server which is a modified apache and tomcat? They are also planning a new 100% java IDE (jdeveloper 9i) do you think we'll be seeing that as well? Anand, I believe you’re saying that you want to install Oracle 10g on Mac OS X, not Mac OS X Server. If that’s correct, you can’t. The binaries aren’t compatible and you’ll raise signal 10/11 because of the incompatibilities.

Mac Os Sql Client

Opinions differ about the best location for installing the downloaded files.
In practice you can install the Oracle instant client files anywhere on your file system so long as the PATH environment variable is updated to include that directory in the search path

Torrent Client For Mac

• Some install instructions suggest copying the install files into the directories:
  /usr/lib or /usr/bin
  This works - but is best avoided. My concern with this approach is that these are the default locations for OS X libraries and commands. Copying in non-standard items gets messy when things go wrong - complicating the process to diagnose and fix issues because of the difficulty identifying which items should (and shouldn’t) be there. If you upgrade Instant Client in future, copying in the new version’s files over top may result in a mixture of old/new libraries which triggers unexpected behaviour. However, if you have issues defining environmental variables then this may be your only choice.
  
• In Oracle’s installation example, they’ve created a directory:
  /opt/oracle/instantclient_11_2
  This gives you a separate (clearly specified) directory for your Instant Client files. It’s also good choice as /opt is the base directory favoured for non-standard (i.e. optional) software installs in Unix operating systems …but it’s just not commonly used in Mac circles.
  
• My preferred option is:
  /Library/Oracle/instantclient_11_2
  This has the advantage of the choice above - but is a more Mac-like alternative. This is the directory I’ll specify for these instructions going forward (but feel free to use whichever location you think is most appropriate for you).

After downloading the three packages above:

1. Extract instantclient-basic and instantclient-plus zip files into single folder (e.g. /Library/Oracle/instantclient_11_2), then
   
2. Create a subfolder named “sdk” inside that folder, and extract instantclient-sdk into it.

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 onand 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:

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:oracleinstantclient_11_1

Linux

1. Download and unzip the Oracle Instant Client into a directory.
2. Set the ORACLE_HOME environment variable to the unzipped instant client. - e.g. export ORACLE_HOME=/home/bbuilder/instantclient_11_1
3. Create a lib directory in $ORACLE_HOME.
4. Copy (or symlink) all of the .so libraries into $ORACLE_HOME/lib.
5. Create a symlink of the instant client's libclntsh.so.11.1 library named libclntsh.so in $ORACLE_HOME/lib. - e.g. ln -s libclntsh.so.11.1 libclntsh.so
6. If you get an 'Oracle client libraries could not be loaded...' error, install libaio1.
7. If FME Server is started as system service, the ORACLE_HOME must be exported in the service script. - e.g. ORACLE_HOME=/home/bbuilder/instantclient_11_1 , export ORACLE_HOME

If FME or FME Server doesn't find the /lib folder with the correct files it will not recognize the Oracle Client. Directly setting the LD_LIBRARY_PATH may overcome some issues - some customers have reported success doing it this way. A note on Oracle Full Client Installations: If using an Oracle full client installation the install folder will contain a /lib subfolder and the ORACLE_HOME should be sufficient to set as FME will use the ORACLE_HOME path, locate and confirm the existence of the 'lib' folder and apply it to the LD_LIBRARY_PATH appending /lib to it. In the case of the Instant client the 'lib' folder doesn't exist and so that is the reason for creating the lib folder and copying the files to this folder (step 3-5). For FME Server these environment variables should be added to the startEngines.sh script found in /fmeserver/Server directory.

Mac OS X

• Refer to Mac OS, FME and Oracle

UNIX (FME 2012 or prior only)

• This directory will have to be added onto the library search path rather than the command search path. In other words, it should go onto LD_LIBRARY_PATH on Solaris:

• Use SHLIB_PATH on HP-UX
• Use LIBPATH for AIX

With certain platforms, the name of the Oracle client library distributed with the instant client contains versioning information that FME doesn't expect. For example, on Solaris it is called something like libclnsh.so.10.1. Fix this by creating a symbolic link in the instant client directory to what FME is expecting: ln -s libclntsh.so.10.1 libclntsh.so

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.

For example

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:

For example:

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,

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.

Note2: For some scenarios, users reported that having the tnsnames.ora file in the oracleinstantclient_xx_xclientnetworkadmin directory saved them from having to define the TNS_ADMIN environment variable.

Note3: 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.

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.