473,246 Members | 1,928 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 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 5663
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Pavel Stehule | last post by:
Hello I need use ADODB client via ODBC. I can call stored proc without problems. I am sure so proc is called (I am loging params) but retern value is always null. dim cn as new...
4
by: Learner | last post by:
Hi there, I have a storec proc that schedules a Sql job and finally it returns 0 then it was successfull and if it returns 1 then its unsuccessful. Now when i run the stored proc in the query...
2
by: philip | last post by:
hello, i am new to asp.net and sql server, and i have 3 questions for asking: 1. i am writing a store procedure of login validation for my asp.net application and wondering what the different...
0
by: Tom | last post by:
Looking for some help with stored procedure call issues. Conceptually, I need to pass a data structure as the sole parameter to the Oracle stored procedure. Sounds simple enough....but how? ...
0
by: balaji krishna | last post by:
Hi, I need to handle the return set from COBOL stored procedure from my invoking Java program. I do not know, how many rows the stored proc SQL fetches.I have declared the cursor in that proc, but i...
8
by: jbonifacejr | last post by:
Hi. I'm sorry to bother all of you, but I have spent two days looking at code samples all over the internet, and I can not get a single one of them to work for me. I am simply trying to get a value...
1
by: Eric Effer | last post by:
Hi I am a newbie with vb.net. I am working with vb.net 2.0 and sql server 2005. I am trying to get the return value from my insert stored proc.Does anyone know how to do this? thanks E
8
by: colmkav | last post by:
Can someone tell me how I can access the return value of a function called from Oracle as opposed to a store proc from oracle? my oracle function is get_num_dates_varposfile. I am only used to...
23
by: Gloops | last post by:
Hello everybody, Is anyone able to give me some indications about how to develop an Access interface for an Oracle database ? I dispose of Access 2003 (11.6566.8107) SP2, Oracle 9i 9.2.0.1.0...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...

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.