ODP.Net-使用自定义类型参数调用存储过程会抛出ORA-06550/PLS-00306

问题描述

我正在尝试调用一个存储过程，该存储过程采用SYS_REFCURSOR输出参数和具有自定义类型的输入参数.现在，当我尝试从应用程序中调用该过程时，出现以下错误:

I'm trying to call a stored procedure that takes a SYS_REFCURSOR output parameter and an input parameter with a custom type. Right now when I try to call the procedure from my application I get this error:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_TEST_01'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

这是PL/SQL脚本，用于创建自定义类型和我要调用的存储过程:

This is the PL/SQL script that creates the custom type and the stored procedure I'm trying to call:

CREATE OR REPLACE TYPE t_string_list AS TABLE OF VARCHAR2(4000);
/

CREATE OR REPLACE PACKAGE TEST_PACKAGE_01
AS
    PROCEDURE SP_TEST_01(in_list IN t_string_list, out_cursor OUT SYS_REFCURSOR);
END TEST_PACKAGE_01;
/

CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE_01
AS
    PROCEDURE SP_TEST_01(in_list IN t_string_list, out_cursor OUT SYS_REFCURSOR)
    IS
    BEGIN
        OPEN out_cursor FOR
            SELECT st.*
            FROM TABLE(in_list) t
            JOIN SOME_TABLE st ON st.SOME_COLUMN = t.COLUMN_VALUE;
    END SP_TEST_01;
END TEST_PACKAGE_01;
/

在C#方面，我已经弄乱了各种方法和迭代，但这是到目前为止我要提出的:

I've messed around with a variety of approaches and iterations on the C# side of things, but this is what I've come up with so far:

using (var context = new SomeDbContext())
{
    using (var conn = new OracleConnection(context.Database.Connection.ConnectionString))
    {
        conn.Open();
        var cmd = conn.CreateCommand();
        cmd.CommandText = "TEST_PACKAGE_01.SP_TEST_01";
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.ArrayBindCount = values.Count; // values is a List<string>

        cmd.Parameters.Add(new OracleParameter
        {
            OracleDbType = OracleDbType.Varchar2,
            Direction = ParameterDirection.Input,
            CollectionType = OracleCollectionType.PLSQLAssociativeArray,
            Value = values.ToArray(),
            Size = values.Count
        });

        cmd.Parameters.Add(new OracleParameter()
        {
            OracleDbType = OracleDbType.RefCursor,
            Direction = ParameterDirection.Output
        });

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
              // do stuff here
            }
        }
    }
}

推荐答案

我能够重现该错误，并且发现传递数组参数的[main]问题在于t_string_list的类型声明中.您需要通过添加INDEX BY BINARY_INTEGER使其成为索引数组，而不是关联数组.为了做到这一点，您必须将类型定义移至程序包中，因为在程序包外部似乎不支持该子句.

I was able to reproduce the error and I discovered that the [main] problem with passing the array parameter is in the type declaration of t_string_list. You need to make it an indexed array rather than an associative one by adding INDEX BY BINARY_INTEGER. In order to do that, you have to move the type definition into the package because that clause doesn't seem to be supported outside of a package.

CREATE OR REPLACE PACKAGE TEST_PACKAGE_01
AS
    TYPE t_string_list IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

    PROCEDURE SP_TEST_01(in_list IN t_string_list, out_cursor OUT SYS_REFCURSOR);
END TEST_PACKAGE_01;

这至少可以传递参数.但是，执行完该过程后，在使用TABLE()运算符将列表作为表格查询时，又遇到了另一个错误.

This at least gets the parameter passed. Once executing the procedure, however, I got another error in querying the list as a table using the TABLE() operator.

解决方案此处说，将列表分配给临时变量会导致其正常工作.为什么?我猜是甲骨文.但是，实际上，它可能会延迟加载，并将其分配给临时变量会导致它完全读取参数数据.不过，这只是一个可行的理论，将欢迎一个更好的解决方案(带有解释).

The solution here says that assigning the list to a temporary variable somehow causes it to work. Why? Oracle, I guess. In all seriousness, though, it may be delay-loaded and assigning it to a temporary causes it to fully read the parameter data. That's just a working theory, though, and a better solution (with explanation) would be welcomed.

不管该解决方法为何有用，它都能起作用:

Regardless of why that workaround helped, this worked:

CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE_01
AS
    PROCEDURE SP_TEST_01(in_list IN t_string_list, out_cursor OUT SYS_REFCURSOR)
    IS
        temp_list t_string_list := in_list;
    BEGIN
        OPEN out_cursor FOR
            SELECT t.COLUMN_VALUE
            FROM TABLE(temp_list) t;
            -- took out the join for testing
    END SP_TEST_01;
END TEST_PACKAGE_01;

在C#端，cmd.ArrayBindCount显然不是广告.分配它时，我遇到了一个令人讨厌的错误:

On the C# side, cmd.ArrayBindCount isn't what's advertised, apparently. I got a nasty error when I assigned it:

因此，在深入研究类型和过程定义之前，我已经摆脱了这一点，这使我陷入了上面报告的错误中.因此，就参数本身而言，您所拥有的是正确的.

So I got rid of that before digging into the type and procedure definitions and that got me to the error you reported above. So as far as the parameteter itself, what you had was right.

cmd.Parameters.Add(new OracleParameter()
{
    OracleDbType = OracleDbType.Varchar2,
    Direction = ParameterDirection.Input,
    CollectionType = OracleCollectionType.PLSQLAssociativeArray,
    Value = values.ToArray()
});

Count属性是可选的，但是如果您为其分配的值小于要传递的元素数，则它将仅传递您指定的值.最好不要分配它.

The Count property is optional but if you assign it a value less than the number of elements you want to pass, it will only pass what you specify. Best to leave it unassigned.

但是，对于一个 output 数组，我想您需要设置Count属性，以告诉它输出和中元素的最大数量ArrayBindSize指定每个元素的最大大小.

For an output array, however, I'm guessing you would need to set the Count property to tell it the maximum number of elements on output and ArrayBindSize to specify the maximum size of each element.

像在我的过程的偶数简化版本中一样，只需将数组元素选择到游标中，就可以在while循环中的reader[0]中观察到数组的每个元素.

Simply selecting the array elements into the cursor, as in my even-simpler version of your procedure, I was able to observe each element of the array in reader[0] within the while loop.

这篇关于ODP.Net-使用自定义类型参数调用存储过程会抛出ORA-06550/PLS-00306的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！