Calling Oracle function with .net 2003

[Guest]

Hi,
I am trying to call an oracle function using Visual Studio .Net 2003. The
function runs fine Stand Alone. Only when I call it using my C# Code I get
the following error: "ORA-06550: wrong number or types of arguments in call
to FUNCTIONNAME". Hence I am assuming it has something to do with the way I
am calling the function. I am providing the same parameters as I am providing
it while running stand alone. So the parameter value should not be a problem.

decimal SchMSum;
string mySql;
conn = this.ConnObj;
DataSet ds = new DataSet();
OracleCommand comm = new OracleCommand();


mySql = "FunctionName";

comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = mySql;
comm.Parameters.Clear();

comm.Parameters.Add(new OracleParameter("n_SRCE_SYS_NBR",
OracleType.Char)).Value = n_SRCE_SYS_NBR;
comm.Parameters.Add(new OracleParameter("n_PERIOD_NBR",
OracleType.Number)).Value = n_PERIOD_NBR;
comm.Parameters.Add(new OracleParameter("n_LE_AOO_SC",
OracleType.VarChar)).Value = n_LE_AOO_SC;

comm.Parameters.Add(new OracleParameter("SchMSum",
OracleType.Number)).Direction = ParameterDirection.ReturnValue;

try
{
SchMSum = decimal.Parse(comm.ExecuteScalar().ToString());
conn.Close();
}
catch (Exception e)
{
throw new Exception (e.Message );
}
finally
{
conn.Close();
}
return SchMSum;

Please help...the field SchMSum in the database is a number and I am using
"Decimal" for the associated .Net type.

Thanks

 

[David Browne]

<DIV>&quot;manika02&quot; &lt;[email protected]&gt; wrote

I am trying to call an oracle function using Visual Studio .Net 2003. The
function runs fine Stand Alone. Only when I call it using my C# Code I get
the following error: "ORA-06550: wrong number or types of arguments in
call
to FUNCTIONNAME". Hence I am assuming it has something to do with the way
I
am calling the function. I am providing the same parameters as I am
providing
it while running stand alone. So the parameter value should not be a
problem.

decimal SchMSum;
string mySql;
conn = this.ConnObj;
DataSet ds = new DataSet();
OracleCommand comm = new OracleCommand();


mySql = "FunctionName";

comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = mySql;
comm.Parameters.Clear();

comm.Parameters.Add(new OracleParameter("n_SRCE_SYS_NBR",
OracleType.Char)).Value = n_SRCE_SYS_NBR;
comm.Parameters.Add(new OracleParameter("n_PERIOD_NBR",
OracleType.Number)).Value = n_PERIOD_NBR;
comm.Parameters.Add(new OracleParameter("n_LE_AOO_SC",
OracleType.VarChar)).Value = n_LE_AOO_SC;

comm.Parameters.Add(new OracleParameter("SchMSum",
OracleType.Number)).Direction = ParameterDirection.ReturnValue;

try
{
SchMSum = decimal.Parse(comm.ExecuteScalar().ToString());
conn.Close();
}
catch (Exception e)
{
throw new Exception (e.Message );
}
finally
{
conn.Close();
}
return SchMSum;

Please help...the field SchMSum in the database is a number and I am using
"Decimal" for the associated .Net type.

When in doubt use CommandType.Text and set the Command.CommandText to a
complete tested PL/SQL block with bind parameters.

David

 

[Guest]

I used command text and it works fine. The problem is when I make a function
call and specify an output parameter. I return a "Number" value and i specify
the parameter as below:

comm.Parameters.Add(new OracleParameter("SchMSum",
OracleType.Number)).Direction = ParameterDirection.ReturnValue;

Thanks
Manika

 

[David Browne]

<DIV>&quot;manika02&quot; &lt;[email protected]&gt; wrote
in message

call and specify an output parameter. I return a "Number" value and i
specify
the parameter as below:

comm.Parameters.Add(new OracleParameter("SchMSum",
OracleType.Number)).Direction = ParameterDirection.ReturnValue;

For CommandType.Text and PL/SQL blocks you never use
ParameterDirection.ReturnValue. You just bind an output parameter in the
correct place.

EG

CommandText = "begin :rv := myfuncinput1,:input2); end;"

:rv is just an output paramter.

David

 

[Guest]

David,
This is giving me an error. I did

comm.CommandText = "begin :rv := myfuncinput1,:input2); end;"
comm.CommandType = CommandType.Text;

I get the error : "Not all variables bound".

Thanks
Manika

 

[David Browne]

<DIV>&quot;manika02&quot; &lt;[email protected]&gt; wrote

This is giving me an error. I did

comm.CommandText = "begin :rv := myfuncinput1,:input2); end;"
comm.CommandType = CommandType.Text;

I get the error : "Not all variables bound".

Well bind the variables. There are three :rv, :input1 and :input2. For
each bind variable, add an ADO.NET parameter to the command with an
appropriate data type size and direction.

David