将参数与 Oracle 包一起使用。 ODP.NET

Question

我有一个返回记录集的 Oracle 函数。 我向 Oracle 函数引入了参数，这导致前端代码变得混乱。

这是我的前端代码。

 OracleCommand od = oc.CreateCommand();
            od.CommandType = System.Data.CommandType.Text;
            od.CommandText = " select * from table(pkg_fetchPOInfo.getPORowsTable(:1,:2))";
            //od.CommandText = "pkg_fetchPOInfo.getPORowsTable";
            //od.CommandType = System.Data.CommandType.TableDirect;

            OracleParameter op1 = new OracleParameter();
            op1.ParameterName = "1";
            op1.OracleDbType = OracleDbType.Varchar2;
            op1.Direction = System.Data.ParameterDirection.Input;
            op1.Size = 6;
            op1.Value = strPONumber;
            od.Parameters.Add(op1);

            OracleParameter op2 = new OracleParameter();
            op2.ParameterName = "2";
            op2.OracleDbType = OracleDbType.Varchar2;
            op2.Direction = System.Data.ParameterDirection.Input;
            op2.Size = 3;
            op2.Value = "US";
            od.Parameters.Add(op2);

如果我在前端 SQLPLUS 中执行查询，我会得到一个记录集。 如果我从包和前端代码中删除参数，该代码就可以工作。

从表中选择 *(pkg_fetchPOInfo.getPORowsTable('1007446','US')); --在 SQLPLUS 中工作。

从表中选择 *(pkg_fetchPOInfo.getPORowsTable()); ——在两个地方都有效。

我是否错误地分配了参数？

Answer 1

包定义：

CREATE OR REPLACE 
PACKAGE TESTP AS
    function TESTPIPE(nr in number, nr2 in number) return varchartabletype pipelined;
END TESTP;

CREATE OR REPLACE
PACKAGE BODY TESTP AS

    function TESTPIPE(nr in number, nr2 in number) return varchartabletype pipelined AS
        CURSOR TESTPIPE_cur
        IS
            SELECT (level + 1) datam
            FROM dual
            connect by level < nr;
        vtt varchartabletype ;

    BEGIN
            OPEN TESTPIPE_cur;

            LOOP
                FETCH testpipe_cur
                BULK COLLECT INTO vtt LIMIT nr2;

                FOR indx IN 1 .. vtt.COUNT
                LOOP
                    Pipe Row ( vtt( indx ) )  ;
                END LOOP;

                EXIT WHEN testpipe_cur%NOTFOUND;
            END LOOP;

    END TESTPIPE;

END TESTP;

.NET 代码：

public static void pipeTest()
{
    String conString = GetConnectionString();
    OracleConnection _conn = new OracleConnection(conString);
    _conn.Open();
    OracleCommand oCmd = new OracleCommand();
    oCmd.CommandText = "begin open :crs for Select * from table(testp.testpipe(:nr,:nr2)); end;";

    oCmd.CommandType = CommandType.Text ;
    oCmd.Connection = _conn;

    OracleParameter crs = new OracleParameter();
    crs.OracleDbType = OracleDbType.RefCursor;
    crs.Direction = ParameterDirection.Output;
    crs.ParameterName = "crs";
    oCmd.Parameters.Add(crs);

    OracleParameter nr = new OracleParameter();
    nr.OracleDbType = OracleDbType.Int64;
    nr.Direction = ParameterDirection.Input ;
    nr.ParameterName = "nr";
    nr.Value = 25;
    oCmd.Parameters.Add(nr);

    OracleParameter nr2 = new OracleParameter();
    nr2.OracleDbType = OracleDbType.Int64;
    nr2.Direction = ParameterDirection.Input;
    nr2.ParameterName = "nr2";
    nr2.Value = 10;
    oCmd.Parameters.Add(nr2);

    using (OracleDataReader MyReader = oCmd.ExecuteReader())
    {
        int ColumnCount = MyReader.FieldCount;
        // get the data and add the row
        while (MyReader.Read())
        {
            String s = MyReader.GetOracleValue(0).ToString();
            Console.WriteLine(string.Format("i={0}", s));
        }
    }
    Console.ReadLine();
}