ODP.NET

Hello dear experts,

I was just playing around with C# and a little stored procedure I deployed to my Oracle 11.

My questions are:

Is it possible to call this "CLR c#" stored procedure in a different way than I call a standard pl/sql stored procedure from a C# client, maybe by something like referring to the same c# classname which I chose to wrap the stored procedure/function in?

Is it possible to eg. return c# classes or c# arrays / own datatypes created within the CLR stored procedure to a C# client?

Can I build up a c# array within my oracle c# stored procedure and return it to a C# client directly, or do I still have to do all the marshalling based on the standard pre-defined data types (number, varchar2 etc) myself and copy them to c# class members?

Example: I have c# stored procedure like this:

namespace oracle
{
public class Crosssorter
{
public static int GetChute(int parcelNo)
{
int nChute=0;

OracleConnection dbcon = new OracleConnection();
dbcon.ConnectionString = "context connection=true"; // Wir benutzen die Session des Aufrufers
dbcon.Open();
OracleCommand cmd = dbcon.CreateCommand();
cmd.CommandText = "select ChuteNo ...[blabla] ";
cmd.Parameters.Add(":1", OracleDbType.Int32, parcelNo, ParameterDirection.Input);
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
nChute = reader.GetInt32(0);
}
reader.Close();
cmd.Dispose();
return (nChute);
}

}
}


Can I reuse the class "Crosssorter" within my c# client application in order to access the function 'GetChute' more easily or is this classname only chosen because it has to have a name?

Can I write an oracle C# stored proc so that a complex c# object is returned?

So instead of a simple thing like 'public static int GetChute(int parcelNo)' maybe something like

'public static int GetChute(
int parcelNo,
CChuteObject myChute)' <---- !!

where CChuteObject is a C# class?

In case all this is not possible, is there a solution to achieve this easily?


Thank you for reading and thinking.

Bernd.