title: Oracle function return rows into c#
body: <p>I want to create a <strong>procedure</strong> or <strong>function</strong> in oracle that returns more than one row of different types to my application. I found some examples on the internet but i can't get it to work...</p>
  
  <p>Example:
  I created a custom type in oracle:</p>
  
  <pre><code>create type proc_selectall is object 
      (Mjera varchar2(50),
      Status number(10,0),
      Naziv varchar2(50),
      Stat number(10,0));
  </code></pre>
  
  <p>Then:</p>
  
  <pre><code>create type ret_selectall is table of proc_selectall;
  </code></pre>
  
  <p>Then created function:</p>
  
  <pre><code>create or replace function fsp_SelectAll return ret_selectall
  is
  l_ret_selectall ret_selectall := ret_selectall();
  n integer := 0;
  BEGIN 
      for r in(SELECT JEDINICAMJERE.Mjera,
      JEDINICAMJERE.Status, 
      KATEGORIJE.Naziv, 
      KATEGORIJE.Status as Stat
  FROM JEDINICAMJERE, KATEGORIJE)
  loop
    l_ret_selectall.extend;
    n := n + 1;
    l_ret_selectall(n) := proc_selectall(r.Mjera, r.Status, r.Naziv, r.Stat);
  end loop;
  return l_ret_selectall;
  END;
  </code></pre>
  
  <p>When I type this line:</p>
  
  <pre><code>select * from table (fsp_SelectAll)
  </code></pre>
  
  <p>I get a nice and expected output:</p>
  
  <pre><code>MJERA | STATUS | NAZIV | STAT
  tr      0        name1   1
  fd      1        name2   1
  ds      1        name3   0
  .....
  </code></pre>
  
  <p>Later i tried to get those values in my app.
  I tried something like this:</p>
  
  <pre><code>//cmd.Parameters.Add("proc_selectall ", OracleDbType.Object);
  //cmd.Parameters.Add("ret_selectall ", OracleDbType.Array);
  //cmd.Parameters["ret_selectall "].Direction = ParameterDirection.ReturnValue;
  //if (connection.State == ConnectionState.Closed)
  //    connection.Open();
  //cmd.ExecuteNonQuery();
  </code></pre>
  
  <p>Later this...</p>
  
  <pre><code>//OracleCommand objCmd = new OracleCommand("select * from table fsp_SelectAll", connection);
  //objCmd.CommandType = CommandType.StoredProcedure;
  //objCmd.ExecuteNonQuery();
  //int size=objCmd.ExecuteReader().FieldCount;
  </code></pre>
  
  <p>I also tried with </p>
  
  <pre><code>//adapter = new OracleDataAdapter(cmd);...
  </code></pre>
  
  <p>But there are always some errors about type, parameters...
  (Connection to oracle work. I executed Insert, Update, Delete procedures and they work fine)</p>
  
  <p>Also i tried to create this procedure:  (this was my first try)</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE osp_SelectAll (Mjera OUT VARCHAR2,
  Status OUT NUMBER, Naziv OUT VARCHAR2,Stat OUT NUMBER) AS 
  BEGIN 
      SELECT JEDINICAMJERE.Mjera,
      JEDINICAMJERE.Status, 
      KATEGORIJE.Naziv, 
      KATEGORIJE.Status as Stat INTO Mjera, Status, Naziv, Stat
  FROM JEDINICAMJERE, KATEGORIJE;
  END osp_SelectAll;
  </code></pre>
  
  <p>But it doesn't return any row or rows... and i give up.</p>
  
  <p>Please help me, i need to solve this.</p>