Connecting to Oracle

There are 3 native Oracle Data Providers. You can check which you have installed by choosing the 'About' option on the 'Help' menu and clicking the 'View Providers' button.

  1. Microsoft Oracle Data Provider

    • This is slow and Apteco do not recommend it's use for this reason.

    • The Oracle Client must be installed.

    • This provider is supplied as part of the .Net Framework

    • The provider has been deprecated by Microsoft and will be removed in a future version of the .Net Framework

  2. Oracle ODP.Net (Unmanaged), also called Oracle.DataAccess.dll

    • This is the fastest data provider for Designer.

    • This provider is contained within the file Oracle.DataAccess.dll that is installed as part of Oracle ODAC.

    • The Oracle Client must be installed.

    • The version of ODP.NET must match the version of the Oracle Client you have installed.

    • To install the ODP.NET provider copy the file 'Oracle.DataAccess.dll' from the \ODP.NET\bin\4.x subdirectory of your oracle home directory to the FastStats Designer application directory (this is usually located at \Program Files\Apteco\FastStats Designer\).

  3. Oracle ODP.Net (Managed), also called Oracle.ManagedDataAccess.dll

    • This provider is approximately ~10% slower than the unmanaged provider.

    • This provider works in both 32 and 64 bit versions of Designer.

    • This provider is contained within the file Oracle.ManagedDataAccess.dll.

    • The Oracle Client does not have to be installed.

    • To install the ODP.NET managed provider copy the file 'Oracle.ManagedDataAccess.dll' from the \ODP.NET\managed\ subdirectory of your oracle home directory to the FastStats Designer application directory (this is usually located at \Program Files\Apteco\FastStats Designer\).

You can also download the Oracle.ManagedDataAccess.dll as a nuget package (see below)

TNSNames.ora tips

You can use the Oracle utility 'tnsping' to verify that your TNSNames.ora file is set up correctly and that the Oracle Client can find and connect to the database.

The ODP.Net (Managed) provider does not use environment variables to find the TNSNames.ora file. It looks in the following locations only:

  • TNS alias in the .NET config files (app/web/machine)

  • TNS alias in the tnsnames.ora at the location specified by 'TNS_ADMIN' in the .NET config files.

  • TNS alias in the tnsnames.ora file present in the same directory as that of the application's working directory.

If you are having trouble connecting with this provider, try copying your tnsnames.ora file into the Designer Program Files directory.

Or, alternatively you can specify the full connection details in the connection string like this:

Copy 
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;

Where to get the Oracle ODP.Net Managed Provider

The Oracle.ManagedDataAccess.dll is available standalone as a NuGet Package from here: https://www.nuget.org/packages/Oracle.ManagedDataAccess/

Download the .nupkg file, rename the file to .zip and extract the package file. Then find the \lib\net40\Oracle.ManagedDataAccess.dll in the extract files. Make sure that the file is not 'Blocked' by Windows by right-clicking on the file, choosing Properties and ensure that the 'Unblock' check box is not visible:

Copy the Oracle.ManageDataAccess.dll into the Designer Program Files directory and it should appear in the View Data Providers list: