1 Reply Latest reply: Mar 12, 2009 1:12 PM by 502182 RSS

    .NET stored procedure, capabilities...

    552675
      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
          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.