如何将字符串数组从C#传递到Oracle存储过程 [英] How to pass an array of strings from C# to an Oracle stored procedure

问题描述

我有以下代码将字符串表传递给名为spTest的Oracle存储过程:

I have the following code to pass a table of strings to the Oracle stored procedure called spTest:

using (OracleConnection oracleConnection = new OracleConnection(connectionString))
{
    oracleConnection.Open();
    OracleCommand oracleCommand = new OracleCommand();

    oracleCommand.Parameters.Add(new OracleParameter
    {
        ParameterName = "eventids",
        Direction = ParameterDirection.Input,
        CollectionType = OracleCollectionType.PLSQLAssociativeArray,
        Value = new string[] { "Test1", "Test2" },
        Size = 2,
        UdtTypeName = "T_STRING_TAB"
    });

    oracleCommand.Connection = oracleConnection;
    oracleCommand.CommandText = "spTest";
    oracleCommand.CommandType = CommandType.StoredProcedure;

    using (OracleDataReader oracleDataReader = oracleCommand.ExecuteReader())
    {
        while (oracleDataReader.Read())
        {
            int fieldCount = oracleDataReader.FieldCount;
        }
    }               
}

我已经在Oracle中定义了类型和存储过程，如下所示:

I have defined the type and stored procedure as follows in Oracle:

create type T_STRING_TAB is table of varchar2(260) index

create or replace procedure spTest(eventids in T_STRING_TAB)
as
starteventid integer;
begin
starteventid := 1000000;
end;

运行代码时，出现以下错误:

When I run the code I get the following error:

Oracle.ManagedDataAccess.Client.OracleException
HResult=0x80004005
Message=ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SPTEST'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Source=Oracle Data Provider for .NET, Managed Driver

我正在使用Oracle.ManagedDataAccess.dll版本号2.0.18.3.

I am using Oracle.ManagedDataAccess.dll version number 2.0.18.3.

有人知道我在做什么错吗?

Does anyone know what I am doing wrong?

谢谢 伊恩

推荐答案

我已经在Oracle中定义了类型和存储过程，如下所示:

I have defined the type and stored procedure as follows in Oracle:

create type T_STRING_TAB is table of varchar2(260) index

这在语法上是无效的，因为它在末尾有一个额外的INDEX关键字，但是如果您解决了该问题，则:

This is syntactically invalid as it has an extra INDEX keyword at the end but if you fix that:

create type T_STRING_TAB is table of varchar2(260);

这是在SQL范围内定义的收集数据类型. C#当前不支持传递非关联数组.

That is a collection data type defined in the SQL scope. C# does not currently support passing a non-associative array.

您的C#代码期望在PL/SQL范围内定义一个关联数组:

Your C# code is expecting an associative array defined in the PL/SQL scope:

CollectionType = OracleCollectionType.PLSQLAssociativeArray,

要创建PL/SQL关联数组，您需要在程序包中进行处理，并包含一个INDEX BY <numeric data type>句:

To make a PL/SQL associative array you need to do it inside of a package and to include an INDEX BY <numeric data type> clasue:

CREATE PACKAGE package_name AS
  TYPE STRING_MAP IS TABLE OF VARCHAR2(260) INDEX BY BINARY_INTEGER;
END;
/

然后您的过程可以是:

create or replace procedure spTest(
  eventids in PACKAGE_NAME.STRING_MAP
)
as
  starteventid integer;
begin
  starteventid := 1000000;
end;
/

这篇关于如何将字符串数组从C#传递到Oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！