(Oracle) 'not all variables bound' error when executing stored procedure with output parameter

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Using Oracle.DataAceess provider
2. Trying to execute stored procedure with output parameter
3. Getting Error ORA-01008: not all variables bound

   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, IDbTransaction tranaction, String sql, Action`2 paramReader, Object obj, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\bridgestone\PrintServer\PrintServer.Model\Dapper\SqlMapper.cs:line 1119
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable`1 commandTimeout, Nullable`1 commandType) in C:\projects\bridgestone\PrintServer\PrintServer.Model\Dapper\SqlMapper.cs:line 326
   at PrintServer.Models.Repository.PoolRepository.GetNumberOfBusyPools() in C:\projects\bridgestone\PrintServer\PrintServer.Model\Repository\PoolRepository.cs:line 37
   at PrintServer.Models.BL.PoolManager.AquireFreePool() in C:\projects\bridgestone\PrintServer\PrintServer.Model\BL\PoolManager.cs:line 21
   at IncomingRequestListener.Console.Program.Main(String[] args) in C:\projects\bridgestone\PrintServer\IncomingRequestListener.Console\Program.cs:line 49
   at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
   at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean ignoreSyncCtx)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()

What is the expected output? What do you see instead?

The following Code generates an errror

                var p = new DynamicParameters();
                p.Add(":p1", dbType: DbType.Int32, direction: ParameterDirection.Output);
                connection.Execute("PSRV.number_of_busy_pools", p, commandType: CommandType.StoredProcedure);

                int a = p.Get<int>(":p1");
                return a;

Whereas analogous solution using ado.net generates no error.

                var command = connection.CreateCommand();
                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "PSRV.number_of_busy_pools";
                var prm = new Oracle.DataAccess.Client.OracleParameter(":p1", Oracle.DataAccess.Client.OracleDbType.Int32, ParameterDirection.Output);
                command.Parameters.Add(prm);
                command.ExecuteNonQuery();
                int res = ((OracleDecimal)prm.Value).ToInt32();
                return res;

Original issue reported on code.google.com by Rybacki....@gmail.com on 8 Sep 2011 at 6:39

[GoogleCodeExporter]

I found where the problem was!

In method  void SqlMapper.IDynamicParameters.AddParameters(IDbCommand command)
You need to consider adding the following line!

Clean(param.Name)

You invoke Clean in other places but not in this case.

Original comment by Rybacki....@gmail.com on 16 Sep 2011 at 12:08

[GoogleCodeExporter]

I'm confused - in the example given (int a = p.Get<int>(":p1")) - where *is* it 
meant to obtain a value for :p1 from? (looking at the Clean thing, btw)

Original comment by marc.gravell on 16 Sep 2011 at 12:20

[GoogleCodeExporter]

Added; passes tests; committed.

Good spot, thanks

Original comment by marc.gravell on 16 Sep 2011 at 12:27

• Changed state: Fixed