Problem with stored proc parameters

marcc · Post by **marcc** » Wed 02 Jun 2010 13:16

Hello,

I'm reviewing dotConnect for Oracle to replace ODP.NET.
I've encountered a strange problem with stored proc parameters, when the name of the parameter ends with a $-sign.

When we call a stored procedure, we cache the parameters for subsequent calls. Below is an example of this:

Code: Select all

public ArrayList ExecuteOutputValues(string commandText, params object[] parameterValues)
{
  OracleParameter[] commandParameters = getParameterSet(commandText);
  
  AssignParameterValues(commandParameters, parameterValues, false);

  using (OracleCommand cmd = new OracleCommand())
  {
    PrepareCommand(cmd, CommandType.StoredProcedure, commandText, commandParameters);

    cmd.ExecuteNonQuery();

    ArrayList outputValues = new ArrayList();

    foreach (OracleParameter p in commandParameters)
    {
      if (p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Output)
      {
        if (p.Value is DBNull)
          outputValues.Add(null);
        else
          outputValues.Add(p.Value);
      }
    }

    return outputValues;
  }
}

getParameterSet retrieves the parameters from the cache (the parameters are cloned when retrieving them).

AssignParameterValues assigns input values to the parameters.

In Preparecommand, the parameters are assigned to the command object.

The command is then executed.

The next line is important:

Code: Select all

foreach (OracleParameter p in commandParameters)

When the stored proc parameters do not end with a '$-sign', the parameters in cmd.Parameters and commandParameters are the same:
cmd.Parameters[0] == commandParameters[0], etc.

But when at least one of the storec proc parameters ends with a '$-sign',
it seems as if cmd.Parameters is cleared, and replaced with a new set of parameters. As a consequence cmd.Parameters[0] != commandParameters[0], etc. I must add to this that the problem goes away if I set cmd.ParameterCheck = true;

Can you please look into this?

Thanks,

Marc

StanislavK · Post by **StanislavK** » Thu 03 Jun 2010 16:24

I will send you a test project in a letter, please check that it isn't blocked by your mail filter. The DDL script for the table and stored procedure used in the test are available in comments of program.cs.

I was unable to reproduce the issue with this project. Please tell us what should be changed in it to reproduce the problem or send us a complete sample solution.

marcc · Post by **marcc** » Mon 07 Jun 2010 10:11

I should have mentioned that I was using 'Direct' mode. If you change your test project to use Direct mode, the problem will occur.

Regards,

Marc

StanislavK · Post by **StanislavK** » Mon 07 Jun 2010 15:50

We've analyzed the situation. The problem is that dotConnect for Oracle uses the '$' sign as a parameter delimiter, and using it provokes parameter check. We recommend you to either avoid using '$' in parameter names or set the ParameterCheck property to true.