just an update on my progress. It seems that doesn't matter which vers. of ODP.net you install it will always have problems with SQLDataSource, it simly doesn't work, so if you try any other DataSource like ObjectDataSource with DataSet or other implementation, it's working and parametrized too but remember to use :PARAM , instead of @PARAM .
Jusst a piece of advice: remember to condifure your Network/Admin .ora files properly otherwise it won't work.
The way i did it was to install the v10 over the 9 then the 11g , then configure it. And this time it worked , no Data provider internal error(-3000) , but still with ORA-01036: illegal variable name/number' on SQLDataSource, so my advice don't uset it , never, just for demos, for a real project , think different.
Anyone has a different oppinion on how to do things with oracle differently ?
I feel your pain, just went through something similar in a deployment situation. You probably have multiple clients installed, and your environment is pulling dlls for older releases (even if you have a latest oracle.dataaccess.dll correctly referenced in your project). Fixing this on your dev environment is one thing, a prod deployment server is another. Not sure what your deployment situation is, but here's what worked for me.
After struggling with trying to upgrade odp.net in existing oracle home, adding new oracle home, etc., I found the easiest way to fix everything is to download the latest odac with xcopy deployment from Oracle, and follow the readme (and see here for an older article on this also). Basically you'll run an install.bat file to setup locally (in separate folder, mine was c:\oracle_odac), then change your project reference to point to the oracle.dataaccess.dll in this new folder (I used 4 instead of 2.x), and add the new folder's bin dirs to front of your path (c:\oracle_odac\bin and c:\oracle_odac\odp.net\bin\4). On your deployment server, you'll just need to copy the entire c:\oracle_odac folder over (via xcopy or however), and setup the path.
That said, I anxiously await the production release of the fully managed odp.net from Oracle (in beta now).
EDIT: Just to add that you can avoid messing with PATHs if you setup in your app or web config file the dllpath. For example:
<configuration>
...
<configSections>
<section name="oracle.dataaccess.client" type="System.Data.Common.DbProviderConfigurationHandler, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
...
<oracle.dataaccess.client>
<settings>
<add name="DllPath" value="c:\oracle_odac\bin"/>
</settings>
</oracle.dataaccess.client>
...
This will override other settings such as registry or machine.config. And it will allow multiple odp.net configurations to exists peacefully, and allow each app to point to the version it needs on the same server.
Best Answer
You've pretty much got it.
Here's the Oracle writeup I followed when doing this: http://www.oracle.com/technetwork/topics/dotnet/code-154692.html
Two other things to do:
Fix your connection string.
Tell your OracleCommand instances you want to bind your parameters by name rather than position, using
OracleCommand.BindByName = true
Suggestion: When you fix your connection string, get rid of any dependence on TNSNAMES.ORA by putting the whole connection string right in your program.