Invalid parameter type between C# and Oracle

Question

I have a function in Oracle that returns a sequence number. When I try to get that value in C# by a parameter it always tells me that the parameter type is invalid. A sample value of that returned value is 115545. If indeed this error is correct then what is the correspondent in ODBCType for this value? If it isn't correct then what is the problem?

Oracle function is:

FUNCTION Get_Next_Message_Id__ 
RETURN NUMBER IS

  temp_ NUMBER;
  CURSOR get_in_message_id_seq IS
    SELECT in_message_id_seq.NEXTVAL
      FROM dual;

BEGIN

   General_SYS.Init_Method(lu_name_, 'IN_MESSAGE_API', 'Get_Next_Message_Id__', TRUE);

   OPEN get_in_message_id_seq;
   FETCH get_in_message_id_seq INTO temp_;
   CLOSE  get_in_message_id_seq;

   RETURN(temp_);

END Get_Next_Message_Id__;

C# Code:

OdbcConnection myConnection = new OdbcConnection(ODBC_CLass.ifsconnectionstring);
OdbcParameter next_id = new OdbcParameter();
next_id.Direction = ParameterDirection.Output;

myConnection.Open();
OdbcCommand command = new OdbcCommand("{? = call ifsapp.in_message_api.Get_Next_Message_Id__}", myConnection);
command.CommandType = CommandType.StoredProcedure;
next_id = command.Parameters.Add("temp_", OdbcType.Int);
int k = command.ExecuteNonQuery();
MessageBox.Show("next_id: " + next_id.Value);

The cursor is completely unnecessary in your function - it only needs to be SELECT in_message_id_seq.NEXTVAL INTO temp_ FROM dual; And don't use tabs when posting code, pls. — OMG Ponies
– OMG Ponies, Commented May 25, 2011 at 6:26

Jon Skeet · Accepted Answer · 2011-05-25 06:24:08Z

1

I believe the problem is in the way you're setting next_id. You're first creating an OdbcParameter and setting its direction - but then completely ignoring it, and reassigning the variable using the Add method, which will be creating an "in" parameter by default. Try this instead:

OdbcParameter nextId = command.Parameters.Add("temp_", OdbcType.Int);
nextId.Direction = ParameterDirection.Output;

Note that you should also be using using statements for the connection and command, to ensure they're cleaned up at the end of the code, whatever happens.

answered May 25, 2011 at 6:24

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pacosino Over a year ago

You were perfectly right. Instead of creating in 2 steps, I created this parameter in one step, because between those 2 steps there was a problem with type assigning. Not it works perfectly. Thanks alot Jon.

Collectives™ on Stack Overflow

Invalid parameter type between C# and Oracle

C# Code:

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

C# Code:

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related