Oracle .NET Data Provider and positional parameters

  • Thread starter Thread starter John
  • Start date Start date
J

John

I'm trying to call a stored procedure adding parameters by specifying
their names. I used to do this in VB with the NamedParameters option
on a recordset I believe. If someone could tell me how to do this i
would be very gratefull.

Anyway while I was trying some stuff out to get this to work I found
this weird behavior. I have a stored procedure that combines two
varchar2 variables and returns it in a third. I called it from .NET
like this.

OracleCommand wCom = new OracleCommand();
wCom.CommandText = "begin test( :one, :two, :three ); end;";
wCom.Parameters.Add( "two", "2" );
wCom.Parameters.Add( "one", "1" );
wCom.Parameters.Add( "three", OracleDbType.Varchar2, 200 );
wCom.Parameters["three"].Direction = ParameterDirection.Output;
wCom.Connection = wCon;
wCom.Connection.Open();
wCom.ExecuteNonQuery();
Console.WriteLine( wCom.Parameters["three"].Value );

I expexted to get "12" back, but instead I got "21". Why are these
parameters positional instead of named? Is this something specific to
ODP?
 
John said:
I'm trying to call a stored procedure adding parameters by specifying
their names. I used to do this in VB with the NamedParameters option
on a recordset I believe. If someone could tell me how to do this i
would be very gratefull.

Anyway while I was trying some stuff out to get this to work I found
this weird behavior. I have a stored procedure that combines two
varchar2 variables and returns it in a third. I called it from .NET
like this.

OracleCommand wCom = new OracleCommand();
wCom.CommandText = "begin test( :one, :two, :three ); end;";
wCom.Parameters.Add( "two", "2" );
wCom.Parameters.Add( "one", "1" );
wCom.Parameters.Add( "three", OracleDbType.Varchar2, 200 );
wCom.Parameters["three"].Direction = ParameterDirection.Output;
wCom.Connection = wCon;
wCom.Connection.Open();
wCom.ExecuteNonQuery();
Console.WriteLine( wCom.Parameters["three"].Value );

I expexted to get "12" back, but instead I got "21". Why are these
parameters positional instead of named? Is this something specific to
ODP?
Click to expand...

Yes. In ODP.NET, the default behavior of OracleCommands is to bind by
position.

To change this, set
OracleCommand.BindByName=true
..

David
 
Back
Top