cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Writing to Oracle Database using the Execute SQL action

I need to create a workflow that will write some information to an Oracle 11g table. I am having an extremely difficult time simply connecting to the database from within the Nintex Execute SQL action.

Here is what I've done so far...

I have successfully downloaded and installed the Oracle 11g 64-bit client on my Web Front End. I know the client installation was successful because I was able to setup my TNSNames.ora file and by using SQLPlus, I am able to perform CRUD operations on the table I need to write to.

I have also used the Connection String Tester I found at Vadim Tabakman's blog here --> Connection String Tester - Vadim Tabakman

Using the tester I was able to once again connect and read from the Oracle table verifying my Connection String was correct.

Now here is my problem. When I fire up Nintex and use the Execute SQL, I am getting different error messages depending on the client that I have installed. I've tried several clients and several combinations and while I am able to get the connection working with SQLPlus and the Connection String Tester, I have not been able to connect from within the Nintex Action.

I have done the following:

  1. Installed 64bit Oracle client, getting this error: "BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed"
  2. Installed 32bit Oracle client, getting an error that says "OracleClient requires Oracle client software version 8.1.7 or greater"
  3. Tried to install both clients, getting same error about requiring client version 8.1.7
  4. Found a blog post saying I should enable 32-Bit Applications to True for ASP.net in IIS but this brings down my whole site.
  5. Found a blog post saying I should install ODP.net (Oracle Data Provider) from ODAC (Oracle Data Access Components) but I'm back to requiring client version 8.7.1 error message.
  6. Found a blog post saying I should reset the security on the Oracle client home folder, I followed the steps but am still getting the same error message.

I am pulling my hair out on this one and could use your help. I forgot how many times I've installed and uninstalled the oracle clients. Has anyone here been able to successfully connect to an Oracle database?

I've spent 2 weeks on this and as I explained, everything works via SQLPlus and the connection string tester, but not from Nintex.

Thanks.

Tags (2)
Reply
2 Replies
Highlighted
Not applicable

Re: Writing to Oracle Database using the Execute SQL action

Hello everyone,

I finally got this working. This is what finally worked for me...

  1. Download and install the Oracle Data Access Components (ODAC) for your version of Oracle (in my case 11g Release 2). The ODAC download that finally worked for me was "64-bit ODAC 11.2 Release 5 (11.2.0.3.20) for Windows x64". Found here --> 64-bit Oracle Data Access Components (ODAC) for Windows
  2. Follow the steps in the following blog post to reset the security to the Oracle Home folder that is created when installing the ODAC. Blog Post --> System.Data.OracleClient requires Oracle client software version 8.1.7 or greater - Fadi Abdulwahab'...
  3. If you have a tnsnames.ora file, add it to the %OracleHomeFolder%/Network/Admin directory as you would normally do with the Oracle Client.
  4. Reboot the servers

Other thoughts/gotchas/important things:

  • You must install the ODAC on all Web Front End servers. I'm not an Oracle expert but the ODAC is different from the actual Oracle Client, I did not have to install the Oracle Client for this to work.
  • When downloading the ODAC do not download the version that says XCopy this version does not have a GUI and the installation is via command line. I tried that first and failed miserably, just let the GUI do the work of installation.
  • The ODAC will also install SQLPlus so you can query the Oracle table for testing etc.
  • For some reason, using the Connection String Tester I mention in my original post does not work with the ODAC installed. The tester returns "BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client." Not sure why this is, maybe because the ODAC is not an actual Oracle Client. Or it may be residue from a previous installed client...don't know but Nintex is happy and that's all I cared about.
  • The connection string that I used in the Execute SQL action was: Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=YouServerNameOrIP)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=YourServiceName)));User Id=YourUserName;Password=YourPassword;

Hope this helps someone else, although no two environments are the same. Just for reference I'm on SharePoint 2010, Nintex 2010 running on Windows Server 2008 R2 with SP1

Phew!

G

Reply
Highlighted
Not applicable

Re: Writing to Oracle Database using the Execute SQL action

Thanks for sharing Giovanni!

0 Kudos
Reply