By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 902 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

return value from Oracle stored proc

P: n/a
Hello Everyone,

I can successfully insert and update the oracle database by calling a
oracles stored proc from my .net code. This oracle stored proc is returning
some value. I cannot see that value in my .net code

Below is my .net code

OracleParameter mbrid = new OracleParameter("RET_MBRID", OracleType.Number);
mbrid.Direction = ParameterDirection.Output;

OracleDataReader reader;
reader = command.ExecuteReader();
while (reader.Read())
{
memberId = (string)reader["RET_MBRID"];

}

I tested the oracle stored proc in sqlPlus and it is returning me value, but
when i go to .net code, i cannot see the value of memberId. Am I doing
something wrong here. Although my insert and update are working fine.

Insert and update statements are defined in the same stored proc.

I spent lot of time on this, but still cannot figure out why am I not
getting the return value from oracle stored proc. I cannot even go inside the
while(Reader.red()) statement

Any help will be grealty appreciated.
Jun 12 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Vinki,

Are you expecting a result set, or a return value, or a parameter that
can be set on output?

If you are expecting a resultset, then using a reader will work, and you
should be able to get the values from the reader.

If you are expecting a parameter that is set on output, then you should
call the ExecuteNonQuery method on the command and then check the value of
the parameter upon return.

If you are expecting a return value, then you should set the Direction
property on the parameter to ParameterDirection.ReturnValue and then call
ExecuteNonQuery, and check the parameter when the call completes.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Vinki" <Vi***@discussions.microsoft.comwrote in message
news:0B**********************************@microsof t.com...
Hello Everyone,

I can successfully insert and update the oracle database by calling a
oracles stored proc from my .net code. This oracle stored proc is
returning
some value. I cannot see that value in my .net code

Below is my .net code

OracleParameter mbrid = new OracleParameter("RET_MBRID",
OracleType.Number);
mbrid.Direction = ParameterDirection.Output;

OracleDataReader reader;
reader = command.ExecuteReader();
while (reader.Read())
{
memberId = (string)reader["RET_MBRID"];

}

I tested the oracle stored proc in sqlPlus and it is returning me value,
but
when i go to .net code, i cannot see the value of memberId. Am I doing
something wrong here. Although my insert and update are working fine.

Insert and update statements are defined in the same stored proc.

I spent lot of time on this, but still cannot figure out why am I not
getting the return value from oracle stored proc. I cannot even go inside
the
while(Reader.red()) statement

Any help will be grealty appreciated.

Jun 12 '07 #2

P: n/a
Thanks Nicholas, I was expecting a parameter that is et for output.

Thanks again.

"Nicholas Paldino [.NET/C# MVP]" wrote:
Vinki,

Are you expecting a result set, or a return value, or a parameter that
can be set on output?

If you are expecting a resultset, then using a reader will work, and you
should be able to get the values from the reader.

If you are expecting a parameter that is set on output, then you should
call the ExecuteNonQuery method on the command and then check the value of
the parameter upon return.

If you are expecting a return value, then you should set the Direction
property on the parameter to ParameterDirection.ReturnValue and then call
ExecuteNonQuery, and check the parameter when the call completes.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Vinki" <Vi***@discussions.microsoft.comwrote in message
news:0B**********************************@microsof t.com...
Hello Everyone,

I can successfully insert and update the oracle database by calling a
oracles stored proc from my .net code. This oracle stored proc is
returning
some value. I cannot see that value in my .net code

Below is my .net code

OracleParameter mbrid = new OracleParameter("RET_MBRID",
OracleType.Number);
mbrid.Direction = ParameterDirection.Output;

OracleDataReader reader;
reader = command.ExecuteReader();
while (reader.Read())
{
memberId = (string)reader["RET_MBRID"];

}

I tested the oracle stored proc in sqlPlus and it is returning me value,
but
when i go to .net code, i cannot see the value of memberId. Am I doing
something wrong here. Although my insert and update are working fine.

Insert and update statements are defined in the same stored proc.

I spent lot of time on this, but still cannot figure out why am I not
getting the return value from oracle stored proc. I cannot even go inside
the
while(Reader.red()) statement

Any help will be grealty appreciated.


Jun 12 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.