0 Replies Latest reply: Jan 5, 2012 9:47 PM by 909301 RSS

    Data access via stored procedure - Return type as PLSQLAssociate array

    909301
      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