将数组从c#传递给Oracle过程

本文关键字：Oracle 过程 数组 | 更新日期: 2023-09-27 18:15:22

我试图将数组传递给oracle过程。我搜索了一下，首先我创建了一个名为"dizi"的类型(在这里输入链接描述)。所以它在oracle开发人员中工作。问题是;我不能将c#数组作为参数传递给过程。那么我如何将我的数组传递给我的过程呢?

这是我的代码(当我执行时，oracle错误说:Not all variables bound)

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();
}

将数组从c#传递给Oracle过程

  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();
}
相关文章：