OracleDataReader returning no rows or data even though data exists

G

Guest

Hi All,

I am trying to get result from Oracle Function that returns a number. When I
rum from SQL Plus I could see the result. When I call from my C# application
I get no data. I am using System.data.OracleClient to access oracle function
on Database 8.1.7.
I am using Oracle 9i client from my application to connect to 8.1.7 Database.
I am able to get results if i use query like select * from table name.

I have paseted my code and Oracle function below.
I would really appreciate any help As I have spent couple of days trying to
get it work.

//Oracle Function
create function test_func return number as
begin
return 5;
end;

C# code
StringBuilder commandText = new StringBuilder("TEST_FUNC");
string str = "Data Source=EXP_DUP;User Id=test;Password=tst;Integrated
Security=no;" ;
System.Data.OracleClient.OracleConnection Conn = new OracleConnection(str);

OracleCommand DSCmd = new OracleCommand(commandText.ToString(),Conn);
Conn.Open();

DSCmd.CommandType = CommandType.StoredProcedure;
//Set OUT parameter direction
DSCmd.Parameters.Add("RC", OracleType.Number).Direction =
ParameterDirection.ReturnValue;
try
{
OracleDataReader dataReader = DSCmd.ExecuteReader();
if( dataReader.HasRows)
{
while (dataReader.Read())
{
System.Windows.Forms.MessageBox.Show(dataReader.GetString(0).ToString());
}
}
dataReader.Close();
}
catch( Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.ToString());
}


Please any suggestions on how to achieve this or what am I missing.

Thanks in Advance,
MS
 
D

David Browne

MS said:
Hi All,

I am trying to get result from Oracle Function that returns a number. When
I
rum from SQL Plus I could see the result. When I call from my C#
application
I get no data. I am using System.data.OracleClient to access oracle
function
on Database 8.1.7.
I am using Oracle 9i client from my application to connect to 8.1.7
Database.
I am able to get results if i use query like select * from table name.

I have paseted my code and Oracle function below.
I would really appreciate any help As I have spent couple of days trying
to
get it work.

//Oracle Function
create function test_func return number as
begin
return 5;
end;

C# code
StringBuilder commandText = new StringBuilder("TEST_FUNC");
string str = "Data Source=EXP_DUP;User Id=test;Password=tst;Integrated
Security=no;" ;
System.Data.OracleClient.OracleConnection Conn = new
OracleConnection(str);

OracleCommand DSCmd = new OracleCommand(commandText.ToString(),Conn);
Conn.Open();

DSCmd.CommandType = CommandType.StoredProcedure;
//Set OUT parameter direction
DSCmd.Parameters.Add("RC", OracleType.Number).Direction =
ParameterDirection.ReturnValue;
try
{
OracleDataReader dataReader = DSCmd.ExecuteReader();
Click to expand...

Don't use ExecuteReader. Use ExecuteNonQuery, then examine the parameter.

David
 
Top