How to call an Oracle stored procedure that returns a ref cursor

Question

I am trying to call an Oracle stored procedure that returns a ref cursor, and I need to create a tree view from the returned data. I am new to this and I have two problems.

The first problem is that I cannot name this procedure. I get this error: "wrong number or argument types when calling" OBJECT_HIERARCHY "

And the second problem is that I don’t understand how I will get this data when this procedure returns the value of the ref cursor? There are more than 5000 records in this table, and I do not get this data, but the value of the cursor is ref. Can someone explain how I can get this data with the cursor. I have no experience with Oracle.

This is the procedure definition in oracle:

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 --> -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; 

these are table field definitions

 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) 

and this is my code that returns an error:

  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(); } 

Can someone please help me and explain to me why my code is returning this error: "Wrong number or types of arguments when calling OBJECT_HIERARCHY"

Answer 1

If you intend to provide OUT , you also need to provide nParentId , since .NET will not name these parameters when the statement is sent to the server.

 cmd.Parameters.Add("nParentId", OracleDbType.Int16).Value = -1; 

Answer 2

Example:

 string connStr = "Data Source=..."; DataSet dataset = new DataSet(); string connStr = ConfigurationManager.ConnectionStrings["OracleConn"].ToString(); using (OracleConnection objConn = new OracleConnection(connStr)) { OracleCommand cmd = new OracleCommand(); cmd.Connection = objConn; cmd.CommandText = "Oracle_PkrName.Stored_Proc_Name"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("Emp_id", OracleType.Int32).Value = 3; // Input id cmd.Parameters.Add("Emp_out", OracleType.Cursor).Direction = ParameterDirection.Output; try { objConn.Open(); cmd.ExecuteNonQuery(); OracleDataAdapter da = new OracleDataAdapter(cmd); da.Fill(dataset); } catch (Exception ex) { System.Console.WriteLine("Exception: {0}", ex.ToString()); } objConn.Close(); }