c# - 将数组从 C# 传递到 Oracle 过程

Question

我正在尝试将一个数组传递给 oracle 过程。我搜索了一下，首先我创建了一个名为“dizi”的类型(就像这里的 enter link description here )。所以它适用于 oracle 开发人员。问题是;我无法将我的 C# 数组作为参数传递给过程。那么如何将我的数组传递给我的程序呢？

这是我的代码(当我执行时，oracle 错误说:并非所有变量都绑定(bind))

public void InsertQuestion(List<string> area_list)
{
    quest_areas = area_list.ToArray();
    command.Connection = connect;
    connect.Open();

    var arry = command.Parameters.Add("Areas", OracleDbType.Varchar2);
    arry.Direction = ParameterDirection.Input;
    arry.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    arry.Value = quest_areas;


    command.CommandText ="TESTPROCEDURE(:Areas)";
    command.CommandType = CommandType.StoredProcedure;
    command.ExecuteNonQuery();
    connect.Close();
}

Answer 1

1. 定义一个数组类型和一个过程:

   CREATE or replace PACKAGE Testpackage AS 
     TYPE Areas_t is table of VARCHAR(100) index by BINARY_INTEGER;
     PROCEDURE TESTPROCEDURE(Areas IN Areas_t);       
   END Testpackage; 
   

2. C#例程:

   public void InsertQuestion(IEnumerable<string> area_list)
   {
       var connect = new OracleConnection("YOUR CONNECTION STRING");
   
       var command = new OracleCommand("BEGIN Testpackage.Testprocedure(:Areas); END;", connect);
   
       connect.Open();
   
       var arry = command.Parameters.Add("Areas", OracleDbType.Varchar2);
   
       arry.Direction = ParameterDirection.Input;
       arry.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
       arry.Value = area_list.ToArray();
       arry.Size = area_list.Count();
       arry.ArrayBindSize = area_list.Select(_ => _.Length).ToArray();
       arry.ArrayBindStatus = Enumerable.Repeat(OracleParameterStatus.Success, area_list.Count()).ToArray();
   
       command.ExecuteNonQuery();
   
       connect.Close();
   }