Follow

Prerequisites for setting up an Oracle Data Source

Introduction

Due to discontinuation of the direct Oracle Support in the Microsoft .Net framework, the requirements for using Oracle databases as a data source in TX has changed. You will have to install the ODP.Net package with .Net 4 support on the machine. The package is only available in the newer versions of the Oracle Client (11g).

Step by step

Our experience so far indicates that the easiest way to get it setup is to follow these steps:

  1. Ensure a copy of the files TNSNames.ora and SQLNet.ora, either by using the Net Configuration assistant (if the full Oracle Client is already installed) or by copying the files from another installation.
  2. Download and extract ODP.Net (ODAC 11.2 Release 3). We have successfully tested ODAC 11.2.0.2.1 with Xcopy Deployment. Make sure to get the correct bit version:
  3. Run the Command Prompt as administrator (Right click -> Run as Administrator). This is very important!
  4. Navigate to the unzipped installation files and run the installation by typing "install.bat all c:\oracle odac"
  5. Create a directory called TNS in your installation folder (i.e. c:\oracle\TNS)
  6. Change the Environment Variable PATH to include your installation folder and its bin directory (ie C:\oracle;C:\Oracle\bin)
  7. Add an environment Variable called TNS_ADMIN pointing to the directory you created (i.e. C:\oracle\TNS)
  8. Copy the SQLNet.Ora and TNSNAMES.ora to the TNS folder.

TX are using the .Net providers when you test the data source connection, for preview of data source data and for the data transfer if SSIS transfers are disabled on the data source.

For advice on troubleshooting your Oracle data source, please visit the Oracle Troubleshooting Hub


This article applies to: timeXtender 4.5, tX2012, TX2014. Last updated: 2014-04-01.

Was this article helpful?
3 out of 3 found this helpful
Have more questions? Submit a request

8 Comments

  • 0
    Avatar
    Ronny Luyten

    Hello Thomas, is this also necessary when SSIS transfer is enabled ?

    Thanks and have a nice day,

    Ronny

  • 0
    Avatar
    Thomas Lørup Duun

    Hi Ronny,

    Yes - this is a prerequisite for all data transfer methods. 

  • 0
    Avatar
    Ronny Luyten

    Hello Thomas,

    I just got a question from the client asking where he needs to install this ?

    Ronny

  • 0
    Avatar
    Thomas Lørup Duun

    Hi Ronny,

    Together with the TX software and on the SSIS server if these are not the same. 

  • 0
    Avatar
    Frans van Harmelen

    Hello,

    I am at a customer right now where we try to connect to a Oracle database V8 and 9i.

    Followed the steps listed on this page. This results in the error in the attached file. 

    Do you know if it is possible at all to connect to this version of Oracle using ODAC 11.2.0.2.1?

     

  • 0
    Avatar
    Thomas Lørup Duun

    According to my memory, the 11G client should be able to connect to oracle 8i (8.1.7) and later.

  • 0
    Avatar
    Frans van Harmelen

    Hello Thomas,

    Thank you for answering this question even though it a celebration day  today in dk? 

    I've done some research on this. The version we are working with is 8i, 8.1.7. According to the text below I found on the i-net, this 

    can still give errors. 

    "No, error is correct, you have to upgrade.

    If You would have 8.1.7.4 version - then no problems to connect.The problem is that tehnicaly only versions bellow 8.1.7.0 should have this error, but appears that this error has 8.1.7.0 -8.1.7.3 as well. "

    Now, looking at the solutions what we found is all about upgrading the client... Which we are not working with, instead it's the ODP.Net we are working with. So, so far we are not making any progress on this... Maybe you have a little bit experience here on you can point us to a direction?

    Frans

  • 0
    Avatar
    Thomas Lørup Duun

    Hi Frans, 

    As I read it, the Oracle database server needs to be patched to at least the 8.1.7.3 patchset - maybe even 8.1.7.4 - there are different views on this to be found on the web.

Please sign in to leave a comment.