Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Retrieving table-type variable from procedure in oracle

662456Sep 29 2008 — edited Feb 25 2009
Hi!
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 25 2009
Added on Sep 29 2008
6 comments
3,621 views