How do I connect Oracle to Salesforce.com?

Download ODBC Drivers for Oracle®, SQL Server, Salesforce, MongoDB, Access, Derby, InterBase & DB2.

Easysoft ODBC-Salesforce Driver

How do I connect Oracle® to Salesforce.com?

Article:: 01051

Last Reviewed:: 31st July 2013

Revision:: 1

You can use Oracle’s Database Gateway for ODBC (DG4ODBC) and the Salesforce.com ODBC Driver to connect Oracle® to Salesforce.com.

DG4ODBC comes as part of Oracle® 12c / Oracle® 11g, at no additional cost, and supports both these versions of Oracle® and Oracle® 10g.

DG4ODBC interacts with Heterogeneous Services (an Oracle® database component) to allow Oracle® client applications to access non-Oracle databases. The non-Oracle data is transparently integrated, and so Oracle® client applications are not aware that the data is stored in a remote database from another vendor.

How to connect Oracle® on Windows to Salesforce.com

The following instructions show you how to connect Oracle® on Windows to Salesforce.com. For further information about DG4ODBC, refer to our DG4ODBC for Windows tutorials.

Download the Salesforce.com ODBC driver for your Windows platform. (Registration required.)
Install and license the Salesforce.com ODBC driver on the machine where DG4ODBC is installed.
For installation instructions, see the Salesforce.com ODBC driver documentation.
In ODBC Data Source Administrator on your DG4ODBC machine, configure a System DSN that connects to your Salesforce.com server.
For instructions on configuring data sources, see the Salesforce.com ODBC driver documentation.

64-bit Windows You need to check whether your version of DG4ODBC is 32-bit or 64-bit. To do this, start the Windows Task Manager and choose the Processes tab. In a Command Prompt window, type dg4odbc --help. In the Windows Task Manager, look for the DG4ODBC process. If the Image Name is "dg4odbc.exe *32" DG4ODBC is 32-bit. If the Image Name is "dg4odbc.exe" DG4ODBC is 64-bit. Press CTRL+C in the Command Prompt window, when you have used the Windows Task Manager to find out DG4ODBC's architecture.

If you have the 64-bit version of DG4ODBC, you need to run 64-bit version of ODBC Administrator. To do this, open Administrative Tools in Control Panel, and then open Data Sources (ODBC). (On Windows Server 2003 and earlier, the Control Panel applet that launches ODBC Administrator is labelled Data Sources. On Windows 8 and later, the Control Panel applet is labelled ODBC Data Sources (64-bit).)

If you have the 32-bit version of DG4ODBC, you need to run 32-bit version of ODBC Administrator. To do this, in the Windows Run dialog box, type:
```
%windir%\syswow64\odbcad32.exe
```
Create a DG4ODBC init file. To do this, change to the %ORACLE_HOME%\hs\admin directory. Create a copy of the file initdg4odbc.ora. Name the new file initsalesforce.ora.
Note In these instructions, replace %ORACLE_HOME% with the location of your Oracle® HOME directory. For example, C:\oraclexe\app\oracle\product\11.2.0\server.
Ensure these parameters and values are present in your init file:
```
HS_FDS_CONNECT_INFO = my_salesforce_odbc_dsn;UID=my_salesforce_user_name;DOMAIN=my_salesforce_user_domain
```
Replace my_salesforce_odbc_dsn with the name of a Salesforce.com ODBC driver data source that connects to the target Salesforce.com server. Replace my_salesforce_user_name with the user portion of your Salesforce.com user name. Replace my_salesforce_user_domain with the domain portion of your Salesforce.com user name. For example:
```
HS_FDS_CONNECT_INFO = "32-bit Force System;UID=myuser;DOMAIN=mydomain"
```
```
HS_FDS_SUPPORT_STATISTICS = FALSE
```
This cuts down the number of calls made from Oracle® to Salesforce so you do not use up your Salesforce API calls allowance as quickly.
Comment out the line that enables DG4ODBC tracing. For example:
```
#HS_FDS_TRACE_LEVEL = <trace_level>
```

Add an entry to %ORACLE_HOME%\network\admin\listener.ora that creates a SID_NAME for DG4ODBC. For example:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
     (SID_NAME=salesforce)
     (ORACLE_HOME=%ORACLE_HOME%)
     (PROGRAM=dg4odbc)
   )
 )

Add a DG4ODBC entry to %ORACLE_HOME%\network\admin\tnsnames.ora that specifies the SID_NAME created in the previous step. For example:
```
SALESFORCE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_host)(PORT = 1521))
    (CONNECT_DATA =
      (SID = salesforce)
    )
    (HS = OK)
  )
```
Replace oracle_host with the host name of your Oracle® machine.

Start (or restart) the Oracle® Listener:

cd %ORACLE_HOME%\bin
lsnrctl stop
lsnrctl start

Connect to your Oracle® database in SQL*Plus.
In SQL*Plus, create a database link for the target Salesforce.com server. For example:
```
CREATE PUBLIC DATABASE LINK salesforcelink CONNECT TO
"my_salesforce_user" IDENTIFIED by "my_salesforce_password" USING 'salesforce';
```
Replace my_salesforce_user and my_salesforce_password with a valid username and password for the target Salesforce.com server.

Notes

If you have problems connecting to Salesforce.com from Oracle®, enable DG4ODBC tracing and check the trace files written to the %ORACLE_HOME%\hs\trace directory. To enable DG4ODBC tracing, add the line HS_FDS_TRACE_LEVEL = DEBUG to initsalesforce.ora and then start / restart the Oracle® listener. If the trace directory does not exist, create it.
If you enable ODBC Driver Manager tracing, but do not get a trace file or get an empty trace file, change the trace file location to the Windows TEMP directory. For example, C:\Windows\Temp\SQL.log.

Problems Accessing Your Data

If you run into any issues trying to read / write data to Salesforce from Oracle® please do the following:

Make sure the issue is not with the application you are using, for example SQL Developer, Toad and so on. Test the issue using SQLPlus on the Oracle® machine. If the issue only happens for example under Toad and not in SQLPlus, please report the issue to the people that support Toad.
Try to limit the issue to the problem column / table. Let's say, for example, you are running a select * from table@link and you have only 1 column that is causing the problem, try running select column from table@link and see if that gives the same error. This helps us to diagnose the issue.
Turn tracing on within your $ORACLE_HOME/hs/admin/initsid.ora file.
Stop and start your Oracle® listener.
Reproduce the issue in SQL Plus. If you do not get an Oracle® trace file in your $ORACLE_HOME/hs/log folder your listener has not been restarted or Oracle® has not been configured correctly.
Send the Easysoft support team ():
1. Output showing the login to SQLPlus, the query being executed and the error displayed.
2. A copy of your Oracle® initsid log file. Please zip / compress this file if more than 1 MB.
3. A copy of your initsid.ora file.
4. A copy of the files ending in _install.info from /usr/local/easysoft.
Once we have all items, the Easysoft support team should be able to quickly work out if this is an Oracle® configuration issue / bug, limitation in Oracle®, Easysoft configuration / bug or if we simply need more information.

How to connect Oracle® on Linux/UNIX to Salesforce.com

The following instructions show you how to connect Oracle® on Linux/UNIX to Salesforce.com. For further information about DG4ODBC, refer to our DG4ODBC for Linux/UNIX tutorial.

Check whether your version of DG4ODBC is 32-bit or 64-bit:
```
cd $ORACLE_HOME/bin
file dg4odbc
```
If the file command’s output contains "ELF 64-bit LSB executable", or something similar, DG4ODBC is 64-bit, and you need to download the 64-bit Salesforce.com ODBC driver for your platform. (Registration required.). Otherwise, download the 32-bit Salesforce.com ODBC driver for your platform.
Install, license and test the Salesforce.com ODBC driver on the machine where DG4ODBC is installed.
For installation instructions, see the Salesforce.com ODBC driver documentation. Refer to the documentation to see which environment variables you need to set (LD_LIBRARY_PATH, LIBPATH, LD_RUN_PATH or SHLIB_PATH depending on the platform and linker).

Note In your ODBC data source, you need to use both the User and Domain attributes to specify your Salesforce user name. For example:
```
User = myuser
Domain = mydomain
```
rather than:
```
User = myuser@mydomain
```
Log in as the Oracle® user and set your library path in your .profile or .bash_profile files. For example:
```
LD_LIBRARY_PATH=/usr/local/easysoft/lib:/usr/local/easysoft/unixODBC/lib:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH
```
Note On HP-UX, replace LD_LIBRARY_PATH with SHLIB_PATH. On AIX, replace LD_LIBRARY_PATH with LIBPATH.

If you want to use isql to verify the connection to SQL Server whilst logged in as the Oracle® user, add the following entry as well:
```
PATH=/usr/local/easysoft/unixODBC/bin:$PATH
export PATH
```
Either log in again as the Oracle® user or run:
```
./.bash_profile
```

Create a DG4ODBC init file. For example:

cd $ORACLE_HOME/hs/admin
cp initdg4odbc.ora initsalesforce.ora

Ensure these parameters and values are present in your init file:
```
HS_FDS_CONNECT_INFO = my_salesforce_odbc_dsn
HS_FDS_SHAREABLE_NAME = /usr/local/easysoft/unixODBC/lib/libodbc.so
HS_LANGUAGE = language_territory.AL32UTF8
HS_NLS_NCHAR = UCS2
HS_FDS_SUPPORT_STATISTICS=FALSE
#
# ODBC specific environment variables 
# 
set ODBCINI=/etc/odbc.ini
```
Replace my_salesforce_odbc_dsn with the name of a Salesforce.com ODBC driver data source that connects to the target Salesforce.com server.

Replace language and territory with the Oracle® language and territory that correspond with your Salesforce Organisation's language and locale. (Your Salesforce Organisation's language and locale are accessible from Setup > Company Profile > Company Information.) For example, if your Salesforce Organisation's language and locale were English and United Kingdom, your HS_LANGUAGE parameter would look like this:
```
HS_LANGUAGE = "ENGLISH_UNITED KINGDOM.AL32UTF8"
```
The HS_NLS_NCHAR parameter value tells DG4ODBC to pass UCS-2 encoded data to the Unicode ODBC APIs, rather than UTF-8, which is the default for DG4ODBC. The unixODBC Driver Manager does not support UTF-8 encoded data being passed to the Unicode ODBC APIs.
Comment out the lines that set environment variables in this file. For example:
```
#set ODBCINI=<full path name of the odbc initialization file>
#set <envvar>=<value>
```
AIX You need to extract the libodbc.so shared object from the libodbc.a file. As the user that installed the Easysoft SQL Server ODBC driver, please run:
```
$ cd /usr/local/easysoft/unixODBC/lib
$ ar -X32_64 -xv libodbc.a
x - libodbc.so.1
$ mv libodbc.so.1 libodbc.so
```
Add an entry to $ORACLE_HOME/network/admin/listener.ora that creates a SID_NAME for DG4ODBC. For example:
```
SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
     (SID_NAME=salesforce)
     (ORACLE_HOME=oracle_home_directory)
     (PROGRAM=dg4odbc)
   )
 )
```
Replace oracle_home_directory with the value of $ORACLE_HOME. For example, /u01/app/oracle/product/11.2.0/dbhome_1/.
Add a DG4ODBC entry to $ORACLE_HOME/network/admin/tnsnames.ora that specifies the SID_NAME created in the previous step. For example:
```
SALESFORCE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_host)(PORT = 1521))
    (CONNECT_DATA =
      (SID = salesforce)
    )
    (HS = OK)
  )
```
Replace oracle_host with the host name of your Oracle® machine.

Start (or restart) the Oracle® Listener:

cd $ORACLE_HOME/bin
./lsnrctl stop
./lsnrctl start

Connect to your Oracle® database in SQL*Plus.
Ensure that you have set NLS_LANG before starting SQL*Plus. NLS_LANG lets Oracle® know what character set your client machine is using. For example:
```
$ echo $LANG
en_US.UTF-8
$ NLS_LANG=AMERICAN_AMERICA.AL32UTF8 ./sqlplus
```
You can now test that the Oracle® configuration is correct by using tnsping:
```
$ tnsping salesforce
```
If you get an error at this point there is something wrong with your Oracle® configuration. Please be aware this only tests the configuration of Oracle® and not that Oracle® can connect to Easysoft.
In SQL*Plus, create a database link for the target Salesforce.com server. For example:
```
CREATE PUBLIC DATABASE LINK salesforcelink CONNECT TO
"my_salesforce_user" IDENTIFIED by "my_salesforce_password" USING 'salesforce';
```
Replace my_salesforce_user and my_salesforce_password with a valid username and password for the target Salesforce.com server.

Easysoft ODBC-Salesforce Driver