user2718165 user2718165 - 1 month ago 7
C# Question

How to call Oracle stored procedure which returns ref cursor

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.

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'"

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.

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 the 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 which returns 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 is my code returning this error: "wrong number or types of arguments in call to 'OBJECT_HIERARCHY'"

Answer

If you're going to provide the OUT, you'll need to provide nParentId as well because .NET isn't going to name those parameters when the statement is sent to the server.

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