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.
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.
For installation instructions, see the Salesforce.com ODBC driver documentation.
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
%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
.
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.
#HS_FDS_TRACE_LEVEL = <trace_level>
%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) ) )
%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.
cd %ORACLE_HOME%\bin lsnrctl stop lsnrctl start
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.
%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.C:\Windows\Temp\SQL.log
.If you run into any issues trying to read / write data to Salesforce from Oracle® please do the following:
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.$ORACLE_HOME/hs/admin/initsid.ora
file.$ORACLE_HOME/hs/log
folder your listener has not been restarted or Oracle® has not been configured correctly._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.
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.
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.
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
.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
./.bash_profile
cd $ORACLE_HOME/hs/admin cp initdg4odbc.ora initsalesforce.ora
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.
#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
$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/
.
$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.
cd $ORACLE_HOME/bin ./lsnrctl stop ./lsnrctl start
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
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.
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.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.