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 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.
Installing an instant client
Here are the steps to follow on Windows:
* First, unzip the instant client into a directory
* Add this directory into the PATH environment variable (My Computer->Properties->Advanced->Environment Variables).
* Add it to the beginning of the PATH. Do not place quotation marks around the path.
* e.g. C:\oracle\instantclient_11_1
* On UNIX platforms, 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. For example on Solaris issue the commands:
* > LD_LIBRARY_PATH=/export/home/sd/instantclient_10_2/lib:${LD_LIBRARY_PATH} > export LD_LIBRARY_PATH
* Linux is as above, use SHLIB_PATH on HP-UX, LIBPATH for AIX, or DYLD_LIBRARY_PATH on Mac OS X.
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. The fix for this is to create a sybmolic link in the instant client directory to what FME is expecting: ln -s libclntsh.so.10.1 libclntsh.so
That is all you need to do if you are willing to use this type of connection string as your "dataset" in FME for connecting to Oracle:
user/password@//hostname:port/sid
For example
scott/tiger@//amidala:1521/orcl
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 tnsnames.ora
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.