title: How to call Oracle stored procedure which returns ref cursor
body: <p>I am trying to call Oracle stored procedure which returns ref cursor, and i need to generate tree view from that returned data. I am new at this and i have two problems.</p>
  
  <p>First problem is that i am not able to call that procedure. I am getting this error: "wrong number or types of arguments in call to 'OBJECT_HIERARCHY'"</p>
  
  <p>And my second problem is that i don't understand how am i gonna get that data when this procedure returns a ref cursor value? There are more then 5000 records in that table and i am not getting that data, but a ref cursor value. Can someone please explain how can i get that data with ref cursor value. I have no experience with Oracle.</p>
  
  <p>This is the procedure definition in oracle:</p>
  
  <pre><code>CREATE OR REPLACE PROCEDURE SAD.object_hierarchy  
  (nAppId IN NUMBER,
  nParentId IN NUMBER DEFAULT -1, 
  o_cRefCursor OUT SYS_REFCURSOR)
  IS
  BEGIN
  IF NOT o_cRefCursor%ISOPEN THEN
  
    OPEN o_cRefCursor FOR 
       SELECT
          h.PARENT_ID, h.CHILD_ID, h.H_LEVEL,
          o.OBJECT_IDENTIFIER, o.OBJECT_TYPE_ID
       FROM
       (
          SELECT
             PARENT_ID, CHILD_ID, LEVEL AS H_LEVEL
          FROM OBJECT_RELATIONSHIPS
          START WITH PARENT_ID = nParentId --&gt; -1 --= 60170
          CONNECT BY PRIOR CHILD_ID = PARENT_ID
       ) h
       INNER JOIN
          OBJECTS o
          ON
             o.OBJECT_ID = h.CHILD_ID AND
             O.APPLICATION_ID = nAppId;   
   END IF;
  END object_hierarchy;
  </code></pre>
  
  <p>these are the table field definitions</p>
  
  <pre><code>Column Name               Data Type            
  
   OBJECT_REL_ID            NUMBER (14)                    
   PARENT_ID                NUMBER (14)                    
   CHILD_ID                 NUMBER (14)                    
   OBJECT_IDENTIFIER        VARCHAR2 (255 Byte)    
   OBJECT_TYPE_ID           VARCHAR2 (5 Byte)
  </code></pre>
  
  <p>and this is my code which returns error:</p>
  
  <pre><code>            string oradb = "Data Source=(DESCRIPTION="
           + "(ADDRESS=(PROTOCOL=TCP)(HOST=tnt33)(PORT=1521))"
           + "(CONNECT_DATA=(SERVICE_NAME=ORCL)));"
           + "User Id=xxx;Password=xxxxx;";
          OracleConnection con = new OracleConnection(oradb);
  
          try
          {
              con.Open();
              OracleCommand cmd = new OracleCommand();
              cmd.Connection = con;
              cmd.CommandType = System.Data.CommandType.StoredProcedure;
              cmd.CommandText = "SAD.object_hierarchy";
              cmd.Parameters.Add("nAppId", OracleDbType.Int16).Value = 1;
              OracleParameter oraP = new OracleParameter();
              oraP.OracleDbType = OracleDbType.RefCursor;
              oraP.Direction = System.Data.ParameterDirection.Output;
              cmd.Parameters.Add(oraP);
              OracleDataReader reader = cmd.ExecuteReader();
  
              while (reader.Read())
              {
  
              }
              reader.Close();    
          }
          catch (Exception ex)
          {
  
              con.Close();
          }
  </code></pre>
  
  <p>Can someone please help me and explain to me why is my code returning this error: "wrong number or types of arguments in call to 'OBJECT_HIERARCHY'"</p>