getting the oracle stored proc value in .net

[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.

 

[Cowboy \(Gregory A. Beamer\)]

For most things, in Oracle, it is easiest to return a ref cursor, which
creates a Dataset. YOu have to declare this output type to link it up.

THe other option is to focus on ODP.NET instead of the built in Microsoft
stuff. As it was created by Oracle, it seems to work better in most
instances.

As for why you are not seeing anything, it is simple. You are not polling
the output parameter, you are trying to read the same value from a Reader.
If you just need a simple value, as an output parameter, just execute non
query and get the value from the parameter.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com
Co-author: Microsoft Expression Web Bible (upcoming)

************************************************
Think outside the box!
************************************************