471,078 Members | 831 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,078 software developers and data experts.

return value from Oracle stored proc

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
2 5484
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
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.

Similar topics

2 posts views Thread by Pavel Stehule | last post: by
2 posts views Thread by philip | last post: by
1 post views Thread by Eric Effer | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.