Connect to Oracle DB from Excel

Steps to connect to oracle db using TNS on windows xp & excel 2010

[A. create and configure TNS]

  1. create file named tnsnames.ora under c:\oracle\tnsnames\ and store tns definition as per below (as example only):

    LOCALORCL11R2V = (
    DESCRIPTION=(
    ADDRESS_LIST=(LOAD_BALANCE=ON)(FAILOVER=ON)(
    ADDRESS=(PROTOCOL=TCP)(HOST=<server1>)(PORT=1521)
    )(
    ADDRESS=(PROTOCOL=TCP)(HOST=<server2>)(PORT=1521)
    )(
    ADDRESS=(PROTOCOL=TCP)(HOST=<server3>)(PORT=1521)
    )(
    ADDRESS=(PROTOCOL=TCP)(HOST=<server4>)(PORT=1521)
    )
    )(
    CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=<service_name>)
    )
    )

  2. set up global variable TNS_ADMIN to point to folder where tnsnames.ora file has been created
    1. click on start menu > right click on computer name > select “properties” as per below screenshot
      tns_admin_step1
    2. go to “Advanced” tab > click on “Environment Variables” > “New” and create variable TNS_ADMIN to make it point to folder where file tnsnames.ora has been created, as per below:
      tns_admin_step2

[B. Set up Oracle]

  1. download oracle instant client from oracle: http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html
    important: 2 parts are necessary, normal bundle + ODBC bundle as per below
    oracle_instantclient_step1
  2. unzip both files in the same folder (C:\Oracle\instantclient_11_2 for instance)
  3. go to folder where files were unziped, and run odbc_install.exe
  4. add path to oracle driver into global variable PATH
    1. click on start menu > right click on computer name > select “properties” (see above section explaining how to set up TNS_ADMIN)
    2. go to “Advanced” tab > click on “Environment Variables”
    3. if you can edit PATH variable, edit it otherwise click on “New”/”Edit” if already existing and append “;<path_to_oracle_drive>” where <path_to_oracle_driver> needs to point to folder where driver was unzipped, as per below:
      oracle_instantclient_step2
      important: make sure to start with a semicolon “;” even when creating the PATH variable
    4. Go to Start menu > Control Panel > Administrative tools > Data Sources (ODBC) as per below:
      oracle_instantclient_step3
    5. Go to tab “User DSN” > “Add…” then select “Oracle in instanclient_11_2” as per below:
      oracle_instantclient_step4note: if oracle instant client is not visible, make sure to repeat above steps, esp. ensure PATH variable does point to oracle driver folder (run “echo %PATH%” in command line, restart computer if need be)
    6. Fill in the fields as required (customize own data source name, specify own db user) and select TNS_NAME from drop down list:
      oracle_instantclient_step5note: if TNS drop down is empty, ensure to repeat part [A. create and configure TNS] and ensure content of tnsnames.ora is valid
    7. Click on “Test” button to test connection and ensure connection is successful as per below:
      oracle_instantclient_step6oracle_instantclient_step7

[C. Connect from within Excel]

In excel, go to:

  1. Tab “Data” > “From Other Sources” > “From Data Connection Wizard” > Other/Advanced > “Microsoft OLE DB Provider for ODBC Drivers > “Next” then fill in connection name, db user and pwd as per below:
    odbc_excel_part3

 

to be completed. right now i am stopped due to firewall issues

sources

Post a Comment

Your email is never published nor shared. You're allow to say what you want...