Connecting to Oracle database in a Unity script

[Rob_Biddle]

Are there any guidelines for how to connect to an external Oracle database in a Unity script?

I'm familiar with using SqlClient objects and System.DataSets to connect to SQL Server databases, but this is the first time I'm trying to work with an Oracle database.  I know I don't want to use System.Data.OracleClient since it's deprecated.

My ultimate goal is to have a Unity script that will run in a workflow timer.  Do I need to have an Oracle client installed on the AppServer and/or on the server running the Timer Service?  I'm trying to come up with a solution that would require a minimal amount of Oracle software installed on our various AppServers and Timer Service servers.  This is a new project which needs to retrieve data from (and update data to) an Oracle database.  Other than that, our OnBase installation doesn't use Oracle at all.

I installed the 'Oracle Developer Tools for Visual Studio' on my machine, and created a Windows Form which successfully connects to an Oracle database (with hard-coded user id, password, and datasource port, service name, and host, so as to not use the tnsnames.ora file) and retrieves data from a table.  So that was one small success.  Next step was to try to duplicate this in a Unity script.  Our OnBase Configuration module is on a server, so, instead of installing 'Oracle Developer Tools for Visual Studio' on the server, I copied the 'Oracle.DataAccess.dll' to the server and used the Unity 'Import Assembly Wizard' to import it.  I can now reference 'Oracle.DataAccess' in my Unity project, my code compiles.  I basically copied and pasted my VS code into my Unity project, with its OracleConnection, OracleCommand, and OracleDataReader.  But I get a runtime exception "The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception." with an InnerException "Oracle.DataAccess.Client.OracleException The provider is not compatible with the version of Oracle client".

As I'm wading through various Google search results, it appear I may need to have an Oracle client installed on the server, or perhaps some additional Oracle dll's.  But before continuing I thought I'd ask the advice of anyone who may have done this before.

Thank you in advance for any help!

Rob

[Robert_Russ]

Hello Rob

Have you thought about using the Oracle ODBC driver for your connection.  You would only need to install the driver and configure it on the appserver and reduce the need to install the Oracle client.  Also you could use the Unity configuration editor Connection strings to connect to the driver.

[Rob_Biddle]

Hi Robert,

Thanks for your suggestion.  I think I've found the solution.  I attempted to download just the Oracle ODBC drivers to our dev AppServer, and from what I can tell, the only way to download the ODBC drivers is to download the whole 'Oracle Developer Tools for Visual Studio' package which I originally installed on my PC.  So once I did that, I had the drivers, and I also had an 'Oracle.ManagedDataAccess' library in the GAC.

So I logged onto Config from our dev AppServer, and added the reference to 'Oracle. ManagedDataAccess' to my project.  I set up a test 'System Task' which calls this Unity script, and I can run it successfully from thick clients (in core mode) on different machines.

In order for this script to run from a timer, I'll need the Oracle.ManagedDataAccess library installed on our appserver and also on the server that the Timer Service is running on, is that correct?

Thanks,

Rob

[Robert_Russ]

You are correct.  You will need the 'Oracle.ManagedDataAccess'  driver on the app and timer server

[Rob_Biddle]

OK, thanks!