CODEWITHSUNDEEP
c#oracle-databaseoracle10g
Sachu
Sachu

Reputation: 7766

calling oracle function from c# giving error

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

Upvotes: 0

Views: 469

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59455

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;

Upvotes: 1

Related Questions