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

Easysoft ODBC-Salesforce Driver

How do I connect Oracle to Salesforce?

Article:
01051
Last Reviewed:
31st July 2013
Revision:
1

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

DG4ODBC comes as part of Oracle 11g and later, at no additional cost, and supports Oracle 10g and later.

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

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

  1. Download the Salesforce ODBC driver for your Windows platform.
  2. Install and license the Salesforce ODBC driver on the machine where DG4ODBC is installed.

    For installation instructions, see the Salesforce ODBC driver documentation.

  3. In ODBC Data Source Administrator on your DG4ODBC machine, configure a System DSN that connects to your Salesforce server.

    For instructions on configuring data sources, see the Salesforce ODBC driver documentation.

    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, in the Windows Run dialog box, type:

    %windir%\system32\odbcad32.exe

    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
  4. 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.

  5. 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 ODBC driver data source that connects to the target Salesforce server. Replace my_salesforce_user_name with the user portion of your Salesforce user name. Replace my_salesforce_user_domain with the domain portion of your Salesforce user name. For example:

    HS_FDS_CONNECT_INFO = "Saleforce-System-ODBC-DSN;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.

  6. Comment out the line that enables DG4ODBC tracing. For example: 
    #HS_FDS_TRACE_LEVEL = <trace_level>
  7. 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)
   )
 )
  8. 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.

  9. Start (or restart) the Oracle Listener: 
    cd %ORACLE_HOME%\bin
lsnrctl stop
lsnrctl start
  10. Connect to your Oracle database in SQL*Plus.
  11. In SQL*Plus, create a database link for the target Salesforce 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 server.

Notes

Problems Accessing Your Data

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

  1. 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.
  2. 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.
  3. Turn tracing on within your $ORACLE_HOME/hs/admin/initsid.ora file.
  4. Stop and start your Oracle listener.
  5. 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.
  6. 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

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

  1. 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 ODBC driver for your platform. Otherwise, download the 32-bit Salesforce ODBC driver for your platform.

  2. Install, license and test the Salesforce ODBC driver on the machine where DG4ODBC is installed.

    For installation instructions, see the Salesforce 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
  3. 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
  4. Either log in again as the Oracle user or run: 
    ./.bash_profile
  5. Create a DG4ODBC init file. For example: 
    cd $ORACLE_HOME/hs/admin
cp initdg4odbc.ora initsalesforce.ora
  6. 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 ODBC driver data source that connects to the target Salesforce 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 some versions of DG4ODBC. The unixODBC Driver Manager does not support UTF-8 encoded data being passed to the Unicode ODBC APIs.

  7. 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
  8. 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/.

  9. 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.

  10. Start (or restart) the Oracle Listener: 
    cd $ORACLE_HOME/bin
./lsnrctl stop
./lsnrctl start
  11. 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
  12. 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.

  13. In SQL*Plus, create a database link for the target Salesforce 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 server.

See Also

Applies To

Knowledge Base Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)