Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

System.ArgumentException: Invalid parameter binding error

717685Nov 5 2009 — edited Nov 9 2009
In 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;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 7 2009
Added on Nov 5 2009
6 comments
7,419 views