CODEWITHSUNDEEP
c#oracle-database
Mathews Varghese
Mathews Varghese

Reputation: 33

C# Oracle Function call

Can anybody suggest what wrong with this function call. There is no name for the return variable, not sure that makes any issue. This works correctly in pl sql.

oracle function code:
   FUNCTION GETSTATUS (
   p_param1         in varchar2,
   p_param2    in number,
   p_param3       out varchar2)
   return boolean
   is
   return true;
   END GETSTATUS;

ODP.Net version - 32 bit Oraclient10201_win32

C# Code

var cmd = new OracleCommand("tk_ccc.GETSTATUS ", connweb);
cmd.CommandType = CommandType.StoredProcedure;
cmd.BindByName = true;
cmd.Parameters.Add("Return_Value", OracleDbType.Char, 1,    ParameterDirection.Output);
var prm1 = new OracleParameter("p_param1", OracleDbType.Varchar2, 20,  ParameterDirection.Input) { Value = "1649983" };
cmd.Parameters.Add(prm1);
var prm2 = new OracleParameter("p_param2", OracleDbType.Int32,  ParameterDirection.Input) { Value = 1 };
cmd.Parameters.Add(prm1);
var prm3 = new OracleParameter("p_param3", OracleDbType.Varchar2, 100,  ParameterDirection.Output);
cmd.Parameters.Add(prm1);
var ret = cmd.ExecuteNonQuery();

Error message:

ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'GETSTATUS'\nORA-06550: line 1, column 7:

PL/SQL: Statement ignored

Upvotes: 0

Views: 5007

Answers (3)

Mathews Varghese
Mathews Varghese

Reputation: 33

SYS.diutil.bool_to_int procedure can be used to convert bool return value to an integer and use CommandType.Text, this solved my issue.

var cmd = new OracleCommand
{
Connection = connweb,
CommandText = "begin " +
":ret_val:= SYS.diutil.bool_to_int(tk_ccc.GETSTATUS(:p_param1,:p_param2,:p_param3)); " +
" end; ",
CommandType = CommandType.Text
};
var returnVal = new OracleParameter("ret_val", OracleDbType.Int32,1);
returnval.Direction=ParameterDirection.ReturnValue;
cmd.Parameters.Add(returnVal);
var p_param1 = new OracleParameter("p_param1", OracleDbType.Varchar2, 20);
p_param1.Direction=ParameterDirection.Input;
p_param1.Value = "1649983" ;
cmd.Parameters.Add(p_param1);
var p_param2 = new OracleParameter("p_param2", OracleDbType.Varchar2, 20);
p_param2.Direction=ParameterDirection.Input;
p_param2.Value = "1" ;
cmd.Parameters.Add(p_param2);
var p_param3 = new OracleParameter("p_param3", OracleDbType.Varchar2, 200);
p_param3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(p_param3);
var ret = cmd.ExecuteNonQuery();

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

ODP.NET provider does not support BOOLEAN data types. You must return a different data type, e.g. INTEGER (OracleDbType.Byte) using 0 and 1.

Upvotes: 0

Jack A.
Jack A.

Reputation: 4453

I think your return value parameter is the problem. It should use ParameterDirection.ReturnValue, like so:

cmd.Parameters.Add("Return_Value", OracleDbType.Char, 1, ParameterDirection.ReturnValue);

Upvotes: 0

Related Questions