如何调用Oracle存储过程返回参考游标 [英] How to call Oracle stored procedure which returns ref cursor

问题描述

我试图调用Oracle存储过程返回REF光标，我需要生成树状视图从返回的数据。我在这个新的，我有两个问题。

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.

第一个问题是，我不能够调用该程序。我得到这个错误：错号码或类型的参数在调用'OBJECT_HIERARCHY'。

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

和我的第二个问题是，我不知道我要怎么获取数据时，此过程将返回一个REF CURSOR价值？还有更多的则5000条记录在该表中，我没有得到这些数据，而是参考光标值。有人可以解释我如何可以得到参考游标值数据。我与甲骨文没有经验

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.

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

能有人帮我，向我解释为什么我的代码返回此错误：错号码或类型的呼叫'OBJECT_HIERARCHY论据

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

推荐答案

如果你要提供 OUT ，你需要提供 nParentId 以及因为.NET是不会当语句来命名这些参数，发送到服务器

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;

这篇关于如何调用Oracle存储过程返回参考游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！