Sadaf Mansuri’s Blog

August 17, 2010

order of parameter while calling oracle stored procedure from odp.net

Filed under: Uncategorized — admin @ 11:35 am

I found strange behavior while calling stored procedure of oracle through odp.net. I have store proc which has first parameter as output parameter. (Generally output param should be last but this is my company’s recommendation to put OUT first then IN/OUT and IN ). While building parameters, when I add output parameter as last parameter, it gives me wrong result.

in oracle I have procedure…

procedure GetValue(p1 out number, p2 in number, p3 in number)

adding parameter to command object

m_Params = new OracleParameter[]
{
MakeInParam(”p2″ ,OracleDbType.Int32,16, v2),
MakeInParam(”p3″ ,OracleDbType.Int32,16, v3),
MakeOutParam(”p1″ ,OracleDbType.Int32,16)
};

MakeInParam and makeOutParam are my own function which is creating param object and returning

OracleCommand cmd = CreateCommand(”GetValue”, m_Params);

cmd.ExecuteNonQuery();

When I am keeping Out param last on list, I am getting wrong value. instead of 1 I am getting 0. when I am keeping Out param as first on list, I am getting proper value.

m_Params = new OracleParameter[]
{
MakeOutParam(”p1″ ,OracleDbType.Int32,16),
MakeInParam(”p2″ ,OracleDbType.Int32,16, v2),
MakeInParam(”p3″ ,OracleDbType.Int32,16, v3)
};

It should consider parameter name while returning value instead of its order.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress