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

问题描述

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

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

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

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;

这些是表字段定义

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屋！