System.ArgumentException: Invalid parameter binding error
717685Nov 5 2009 — edited Nov 9 2009In a C# program, I'm trying to pass an entire datatable as a refcursor to a stored procedure. I'm getting the runtime error in the subject line to this message when I hit the ExecuteNonQuery()., My guess is that it does not like my assignment of of the WEATHER_SET datatable to the OracleParameter's Value. The C# source code is below, and the stored procedure is below that.
Any suggestions? I've seen this done at another job, but unfortunately, I don't access to that code anymore...
Thanks,
Randy
//C# code:
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
...
this.conn.Open();
if (conn.State == ConnectionState.Open)
{
OracleCommand cmd = new OracleCommand("WEATHER.PutWeatherSet", conn);
cmd.CommandType = CommandType.StoredProcedure;
//Create parameter object for the cursor
OracleParameter p_input = new OracleParameter();
//Easy to forget following (won't work without)
p_input.OracleDbType = OracleDbType.RefCursor;
p_input.Direction = ParameterDirection.Input;
p_input.Value = windsDS.WEATHERSET; //Assign entire datatable to sys_refcursor
cmd.Parameters.Add(p_input);
cmd.ExecuteNonQuery();
p_input.Dispose();
cmd.Dispose();
conn.Dispose();
}
--sql code:
create or replace package body weather as
procedure PutWeatherSet(p_input in sys_refcursor) is
declare
-- type cur is CURSOR;
-- cur p_input; --assign ref cursor to cursor.
wx_row weather_set%rowtype;
begin
open cur for select * from weather_set;
loop
fetch p_input into wx_row;
exit when p_input%notfound;
insert into weather_set values (wx_row.weather_set_name,
wx_row.weather_type, wx_row.weather_source, wx_row.model,
wx_row.eff_datetime, wx_row.run_datetime);
end loop;
close c_wx;
end;
end;
/