How do I connect Oracle to Salesforce?

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

How to connect Oracle on Linux or UNIX to Salesforce

The following instructions show you how to connect Oracle on Linux or 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, refer to the Salesforce ODBC driver documentation. Refer to the documentation to find out 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 while 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, 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. 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 user name and password for the target Salesforce server.

Further information

• Updating Salesforce from Oracle