calling oracle function from c# giving error

Question

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

Is the function name really list_without_dublicates and not list_without_duplicates ? — Gordon Linoff
– Gordon Linoff, Commented Oct 25, 2016 at 11:45
You're adding parameter result comm.Parameters.Add("result", OracleDbType.Varchar2); and this is not parameter of function. — Kacper
– Kacper, Commented Oct 25, 2016 at 11:59

Wernfried Domscheit · Accepted Answer · 2016-10-25 12:33:07Z

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;

answered Oct 25, 2016 at 12:33

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sachu Over a year ago

@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

Collectives™ on Stack Overflow

calling oracle function from c# giving error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related