return value from Oracle stored proc

[Guest]

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.

 

[Nicholas Paldino [.NET/C# MVP]]

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.

 

[Guest]

Thanks Nicholas, I was expecting a parameter that is et for output.

Thanks again.

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]
- (e-mail address removed)

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.