0

When executing a oracle function from C# we are getting this error. please help to solve.

ORA-06550: line 1, column 15:

PLS-00306: wrong number or types of arguments in call to 'LIST_WITHOUT_DUBLICATES'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

My c# code

comm.Connection = conn;
comm.CommandText = "LIVE.list_without_dublicates";
comm.CommandType = CommandType.StoredProcedure;

 comm.Parameters.Add("p_str", to_list);
comm.Parameters.Add("p_sep", ",");
comm.Parameters.Add("result", OracleDbType.Varchar2);
 comm.Parameters["result"].Direction = ParameterDirection.ReturnValue;

comm.ExecuteNonQuery();

Function signature

LIVE.list_without_dublicates(
p_str IN VARCHAR2,
p_sep IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2
Sachu
  • 7,555
  • 7
  • 55
  • 94
  • 1
    Is the function name really `list_without_dublicates` and not `list_without_duplicates` ? – Gordon Linoff Oct 25 '16 at 11:45
  • @GordonLinoff its `list_without_dublicates` only – Sachu Oct 25 '16 at 11:46
  • Can you post your function body as well? – Rahul Oct 25 '16 at 11:56
  • and what is 'to_list' in your c#? – Ian of Oz Oct 25 '16 at 11:59
  • You're adding parameter result `comm.Parameters.Add("result", OracleDbType.Varchar2)`; and this is not parameter of function. – Kacper Oct 25 '16 at 11:59
  • @Kacper, yes it is since it's the return value of the function – Rahul Oct 25 '16 at 12:00
  • Have you tried creating your ReturnValue parameter completely before adding it to the Parameters collection? As in this answer: http://stackoverflow.com/questions/11419722/ora-06550-wrong-number-or-type-of-arguments-error-when-calling-a-function-insid?rq=1 – Ian of Oz Oct 25 '16 at 12:17
  • Is it "@ReturnVal" though, not "result"? http://stackoverflow.com/questions/6210027/calling-stored-procedure-with-return-value. Also, use ExecuteReader if you want some kind of list back – doctorlove Oct 25 '16 at 12:17

1 Answers1

1

As far as I remember you have to specify the (max) length of an Varchar2 when it is the return value.

Try this one:

comm.Parameters.Add("result", OracleDbType.Varchar2, 4000, null, ParameterDirection.ReturnValue);

instead of

comm.Parameters.Add("result", OracleDbType.Varchar2);
comm.Parameters["result"].Direction = ParameterDirection.ReturnValue;

Also try

comm.CommandText = "BEGIN :result := LIVE.list_without_dublicates(:p_str, :p_sep); END;";
comm.CommandType = CommandType.Text;

instead of 

comm.CommandText = "LIVE.list_without_dublicates";
comm.CommandType = CommandType.StoredProcedure;
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • @Wernfriend thanks for ur answer..it also gave me same error..so i called the funcation with select functionname from dual and it worked..im upvoting ur answer – Sachu Nov 07 '16 at 07:32