C# 如何调用返回引用游标的 Oracle 存储过程

How to call Oracle stored procedure which returns ref cursor

提问by user2718165

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.

我正在尝试调用返回引用游标的 Oracle 存储过程，我需要从返回的数据生成树视图。我是新手，我有两个问题。

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

第一个问题是我无法调用该程序。我收到此错误：“调用 '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.

我的第二个问题是我不明白当这个过程返回一个引用游标值时我将如何获取这些数据？该表中有 5000 多条记录，我没有得到这些数据，而是一个引用游标值。有人可以解释一下如何使用引用游标值获取该数据。我没有使用 Oracle 的经验。

This is the procedure definition in oracle:

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

有人可以帮助我并向我解释为什么我的代码返回此错误：“调用'OBJECT_HIERARCHY'的参数数量或类型错误”

采纳答案by Mike Perrenoud

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

如果要提供OUT，则还需要提供nParentId，因为在将语句发送到服务器时 .NET 不会命名这些参数。

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

回答by Ankit Jain

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