Retrieving table-type variable from procedure in oracle
662456Sep 29 2008 — edited Feb 25 2009Hi!
I'm trying to retrieve a variable from procedure. Procedure in PLSQL has this construction:
TYPE NPV_Table IS TABLE OF NUMBER(5);
Procedure GetUserNPV(usrLogin IN VARCHAR2, usrTable OUT NPV_Table) IS {...
Here is code which fills in loop from cursor the usrTable variable. (single column)
}
Now i would like to get this variable with ODP.NET:
private object OraGetNpv(string login){
OracleCommand comm = new OracleCommand("GetUserNPV");
comm.Parameters.Add(new OracleParameter("usrLogin", OracleDbType.Varchar2, 6, (object)login, System.Data.ParameterDirection.Input));
object o = new object();
OracleParameter pTable = new OracleParameter();
pTable.DbType = System.Data.DbType.AnsiString; // if I put "Object" type here, C# founds an error.
pTable.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
pTable.Direction = System.Data.ParameterDirection.Output;
pTable.OracleDbType = OracleDbType.Decimal;
pTable.ParameterName = "usrTable";
pTable.Size = 1000;
pTable.Value = o;
comm.Parameters.Add(pTable);
comm.ArrayBindCount = 1;
comm.CommandType = System.Data.CommandType.StoredProcedure;
comm.Connection = new OracleConnection(connectionString);
comm.Connection.Open();
comm.ExecuteNonQuery();
comm.Connection.Close();
return o;
}
error is at line with comm.ExecuteNonQuery() : "Unable to cast object of type 'System.String' to type 'System.Array'."
which object is of type System.String? - I don't understand this. Please help.
PS. I tried with all other Execute options, problem was the same.
MC