none
ODP.NET ignoring parameter names RRS feed

  • Question

  • Hello all,

    We have a stored procedure, in Oracle, that has optional parameters; we have the defaults set to null.  We are importing data and, when found, we insert said data into a table via this procedure.  We only add parameters to the command when the data is found, per row. 

    So let's say that we have 4 parameters in our stored procedure:

    FName
    LName
    UserName
    Password

    with the ODP.NET driver, if I only send it:

    UserName

    it maps UserName to FName; basically, it throws out the parameter name and goes by ordinal. 
    The old driver (System.Data.OracleClient) didn't do that; it respected the parameter names.

    Has anyone come across this before?  If so, how can I fix it?

    Wednesday, August 10, 2016 7:22 PM

Answers

All replies

  • It might depend on both the Oracle provider you're using as well as the version of Oracle you're connecting to. I use the Oracle.DataAccess.Client (it's an older application, that I don't deal with anymore, but it worked fine at the time). I can't remember which version of Oracle it went with. Basically, I never used the System.Data.OracleClient.

    Are you prefacing your Parameter names with a "@"? I'm assuming you are, and you should if you're not. And, are you sure that you have all the correct Oracle DLLs that you need?


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, August 11, 2016 1:34 AM
  • I am using Oracle database 12c, Release 12.1.0.1.0.

    As far as the driver, I am using Oracle.ManagedDataAccess from NuGet; verson 4.121.2.0 (latest version).

    The NuGet package says:

    "ODP.NET, Managed Driver is a 100% native .NET code driver.  No additional Oracle Client software is required to be installed to connect to Oracle Database."

    Their website also confirms that this is the latest driver for oracle 12c.

    It doesn't matter whether or not I include the @ in front of the parameter, I get the same results.  It simple goes off ordinal and not parameter name.

    To get this working, we've reverted back to the one built into .NET (System.Data.OracleClient); however, we really need to get off that legacy driver and move to the new one from NuGet as, per MSDN:

    "This types in System.Data.OracleClient are deprecated and will be removed in a future version of the .NET Framework."




    Thursday, August 11, 2016 12:06 PM
  • Your code?

    See the below link. The default behavior is for the ODP.NET provider to bind parameters by ordinal position. You have to specify the BindByName argument:

    http://www.codeproject.com/Articles/208176/Gotcha-sharp-Using-Named-Parameters-with-Oracl


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by da.3vil.coder Thursday, August 11, 2016 12:23 PM
    Thursday, August 11, 2016 12:09 PM
  • That worked!  Thank you so much!

    We use an in-house library that wraps ADO.NET, so we would have never found that property on the command object.

    I have to say, that's pretty dumb how they have it set to false by default.


    Thursday, August 11, 2016 12:23 PM
  • I wonder why we never had an issue with that ... probably using something different than you are (and different from what's described in that article). I have a reference to "Oracle.DataAccess.dll", which looks like it's the Oracle Data Provider for .NET (at least that's what it says). Probably an old version? Product version is 4.112.3.0 and the date is 12/5/2011.

    You've got your problem solved, so my question doesn't matter really ... I'm just curious.

    (We're primarily a SQL Server shop ... we only ever had one customer who used Oracle).


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, August 12, 2016 1:06 AM