This discussion is archived
1 Reply Latest reply: Mar 12, 2009 11:12 AM by 502182 RSS

.NET stored procedure, capabilities...

552675 Newbie
Currently Being Moderated
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 (without using a self-written service layer)?

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.
  • 1. Re: .NET stored procedure, capabilities...
    502182 Explorer
    Currently Being Moderated
    Hi Bernd,

    Not sure if my answers are going to help. Anyway here they are -

    1. 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?
    Answer: NO

    2. Is it possible to eg. return c# classes or c# arrays / own datatypes created within the CLR stored procedure to a C# client (without using a self-written service layer)?
    Answer: NO

    3. 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?
    Answer: You can return only the .NET types supported by .NET stored procedures. These are listed in the user doc. I can not think of an easy solution for returning complex object types using .NET stored procedures.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points