Click to See Complete Forum and Search --> : .Net reader with Oracle table function return type


l.wolf
November 22nd, 2006, 07:43 AM
Hi,
I have a problem to solve: with .Net i shoud call an Oracle function that return a table type. I tested my packege directly and it's right: it returns what i want. The c# method returns instead error.

Thank for all tips :)

The package is like that.
Package:
CREATE OR REPLACE PACKAGE myPck AS
TYPE myRecord IS RECORD
(
IDPR NUMBER (12),
DESPR VARCHAR2 (40)
-- altri campi ...
);

TYPE myTable IS TABLE OF myRecord
INDEX BY BINARY_INTEGER;

FUNCTION Elabora(p_utente IN VARCHAR2)
RETURN myTable;

END myPck;

My class have a method like this
C# da applicativo .Net:
public List<Elenco> CallProc1(string idPr)
{
using (OracleConnection cn = new OracleConnection(this.ConnectionString))
{
List<Elenco> elenco = new List<Elenco>();
OracleCommand cmd = cn.CreateCommand();
cmd.CommandText = "myPck.Elabora";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new OracleParameter("IdPr", OracleType.VarChar)).Direction = ParameterDirection.Input;
cmd.Parameters[0].Value = idPr;

/* do something here ??? */

OracleDataReader reader;

cn.Open();

reader = command.ExecuteReader();
while (reader.Read())
{

/* Are right ??? */
Elenco ele = new Elenco();
ele.idPr = (int)reader["IDPR"];
ele.DesPr = reader["DESPR"].ToString();
elenco.Add(ele);
/**/

}
reader.Close();

}

return elenco;

}