oracle C# 2010，ODP.net，调用存储过程传递数组

C# 2010, ODP.net, call stored procedure passing array

提问by user2316634

I have a PL/SQL stored procedure that takes 4 inputs. One of those inputs is an associative array (Oracle Type: Table of VARCHAR2(1) index by PLS_INTEGER).

我有一个接受 4 个输入的 PL/SQL 存储过程。这些输入之一是关联数组（Oracle 类型：PLS_INTEGER 的 VARCHAR2(1) 索引表）。

I want to have a C# program that calls this stored procedure with the proper inputs including the associative array.

我想要一个 C# 程序，它使用正确的输入（包括关联数组）调用此存储过程。

I am using ODP.net 11.2 with Visual C# 2010 Express and Oracle 11gR2.

我将 ODP.net 11.2 与 Visual C# 2010 Express 和 Oracle 11gR2 一起使用。

I cannot find any good examples of how to pass an array to a pl/sql procedure from C#. Can anybody give me an example? Following Oracle Documentation exactly gives me error saying Wrong number or type of arguments.

我找不到任何关于如何将数组从 C# 传递给 pl/sql 过程的好例子。任何人都可以给我一个例子吗？遵循 Oracle 文档确实给了我错误的错误数量或类型的参数。

My C# Code:

我的 C# 代码：

        OracleCommand cmd = new OracleCommand("begin sdg_test.sdg_test2(:1); end;", conn);

        OracleParameter Param1 = cmd.Parameters.Add("1", OracleDbType.Varchar2);

        Param1.Direction = ParameterDirection.Input;

        Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

        Param1.Value = new string[22] { "Y", "Y", "N", "Y", "N", "Y", "Y", "Y", "Y", "Y", "N", "Y", "N", "Y", "Y", "Y", "Y", "Y", "N", "Y", "N", "Y" };

        Param1.Size = 22;
        Param1.ArrayBindSize = new int[22] { 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 };

        cmd.ExecuteNonQuery();

        conn.Close();
        conn.Dispose();

All my procedure does is log a message. I am just trying to get this concept to work.

我的程序所做的只是记录一条消息。我只是想让这个概念发挥作用。

回答by evgenyl

You can start from (simpler way):

您可以从（更简单的方式）开始：

List<int> idList = yourObjectList;
List<int> nameList = yourObjectList;

using (OracleConnection oraconn = new OracleConnection())
{
    oraconn.ConnectionString = "Your_Connection_string";

    using (OracleCommand oracmd = new OracleCommand())
    {
        oracmd.Connection = oraconn;

        oracmd.CommandType = CommandType.StoredProcedure;
        oracmd.CommandText = "Your_Procedura_name";
        oraconn.Open();

        // To use ArrayBinding, you need to set ArrayBindCount   
        oracmd.BindByName = true;
        oracmd.ArrayBindCount = idList.Count;

        // Instead of single values, we pass arrays of values as parameters   
        oracmd.Parameters.Add("ids", OracleDbType.Int32, oyourObjectList.ToArray(), ParameterDirection.Input);
        oracmd.Parameters.Add("names", OracleDbType.Varchar2, oyourObjectList.ToArray(), ParameterDirection.Input);

        oracmd.ExecuteNonQuery();
        oraconn.Close();
    }
}

Then, add package / procedure in db:

然后，在 db 中添加包/过程：

PROCEDURE Your_Procedure_name(
      name IN VARCHAR2,
      id IN NUMBER
      )    IS     
BEGIN

    INSERT INTO your_table VALUES( id, name);
END Your_Procedure_name;

Another option is:

另一种选择是：

using (OracleConnection oraconn = new OracleConnection())
{
    oraconn.ConnectionString = "Your_Connection_string";

    using (OracleCommand cmd = new OracleCommand())
    {

        cmd.Connection = oraconn;

        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "Your_Procedure_name";
        oraconn.Open();


        OracleParameter idParam = new OracleParameter("i_idList", OracleDbType.Int32, ParameterDirection.Input);
        idParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        idParam.Value = idList.ToArray();
        idParam.Size = idList.Count;

        OracleParameter nameParam = new OracleParameter("i_nameList", OracleDbType.Varchar2, ParameterDirection.Input);
        nameParam.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        nameParam.Value = nameList.ToArray();
        nameParam.Size = nameList.Count;

        // You need this param for output
        cmd.Parameters.Add("ret", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
        cmd.Parameters.Add(idParam);
        cmd.Parameters.Add(nameParam);

        conn.Open();

        //If you need to read results ...
        using (OracleDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            {
                ...
            }
        }
        conn.Close();
    }
}

But it is more complicated, since you need to define new types for the stored procedure, like

但它更复杂，因为您需要为存储过程定义新类型，例如

TYPE integer_list IS TABLE OF Your_table.id_column%TYPE INDEX BY BINARY_INTEGER;
// same for names

create a schema-level type like

创建一个模式级别的类型，如

create or replace TYPE T_ID_TABLE is table of number; 

And then use them in the Stored Procedure, like

然后在存储过程中使用它们，比如

PROCEDURE Your_Procedure_name(
      v_ret IN OUT SYS_REFCURSOR,
      i_idList integer_list,
      i_nameList string_list)
  IS  
  begin
    -- Store passed object id list to array
 idList T_ID_TABLE := T_ID_TABLE(); 
  ...
  begin

    -- Store passed object id list to array
    idList.Extend(i_idList.Count);
    FOR i in i_idList.first..i_idList.last loop
     idList(i) := i_idList(i);
    END LOOP;    

    ...
END Your_Procedure_name;