This discussion is archived
0 Replies Latest reply: Jan 5, 2012 7:47 PM by 909301 RSS

Data access via stored procedure - Return type as PLSQLAssociate array

909301 Newbie
Currently Being Moderated
Hi,

I get an error as array bind type must match pl/sqldata type when i execute a stored proc.
My stored proc looks like this:

PROCEDURE Datasearch
(p_type in varchar2,
p_empid in varchar2,
p_empcode out tempid,
p_lname out tlname,
p_fname out tfname,
p_nickname out tnickname,
p_position out tposition,
p_startdt out tstartdt,
p_status out tstatus,
p_loastart out tloastart,
p_loaend out tloaend,
p_xchange out txchange,
p_trx out ttrx,
p_enddt out tenddt,
p_hfind out thfind)
is
... end Empsearch

And I am trying to fire this stored proc using the following C# code:

/////////////////////////////////////////////////////////////////////////////////////////////////////////

OracleConnection con = new OracleConnection();
con.ConnectionString = "ValidConnectionString";
con.Open();
Console.WriteLine("Connected to Oracle" + con.ServerVersion);

string cmdTxt = "begin MyPack.Datasearch(:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15); end;";
OracleCommand myCMD = new OracleCommand(cmdTxt, con);

//Input params
myCMD.Parameters.Add("1", OracleDbType.Varchar2).Value = "All";
myCMD.Parameters.Add("2", OracleDbType.Varchar2).Value = "1129";
myCMD.Parameters[0].Direction = ParameterDirection.Input;
myCMD.Parameters[1].Direction = ParameterDirection.Input;


//Output params
OracleParameter Param3 = myCMD.Parameters.Add("3", OracleDbType.Int16);
OracleParameter Param4 = myCMD.Parameters.Add("4", OracleDbType.Varchar2);
OracleParameter Param5 = myCMD.Parameters.Add("5", OracleDbType.Varchar2);
OracleParameter Param6 = myCMD.Parameters.Add("6", OracleDbType.Varchar2);
OracleParameter Param7 = myCMD.Parameters.Add("7", OracleDbType.Varchar2);
OracleParameter Param8 = myCMD.Parameters.Add("8", OracleDbType.Date);
OracleParameter Param9 = myCMD.Parameters.Add("9", OracleDbType.Varchar2);
OracleParameter Param10 = myCMD.Parameters.Add("10", OracleDbType.Varchar2);
OracleParameter Param11 = myCMD.Parameters.Add("11", OracleDbType.Varchar2);
OracleParameter Param12 = myCMD.Parameters.Add("12", OracleDbType.Varchar2);
OracleParameter Param13 = myCMD.Parameters.Add("13", OracleDbType.Varchar2);
OracleParameter Param14 = myCMD.Parameters.Add("14", OracleDbType.Date);
OracleParameter Param15 = myCMD.Parameters.Add("15", OracleDbType.Varchar2);

//test ends


for (int i = 2; i < 15; i++)
{
myCMD.Parameters.Direction = ParameterDirection.Output;
myCMD.Parameters[i].CollectionType = OracleCollectionType.PLSQLAssociativeArray;
myCMD.Parameters[i].Value = null;
myCMD.Parameters[i].Size = 3;
myCMD.Parameters[i].ArrayBindSize = new int[3] { 20, 20, 20 };

}


myCMD.ExecuteNonQuery();

/////////////////////////////////////////////////////////////////////////////////////////////////////////

I get an error on the above ExecuteNonQuery statement..

Any help/pointer will be highly appreciated.

-Cheers

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points