将Array作为参数传递给Oracle存储过程(从c#到批量插入)

本文关键字：插入 存储过程 Array 参数传递 Oracle | 更新日期: 2023-09-27 18:11:45

我正在尝试将数组作为参数发送给Oracle存储过程，以便处理批量插入。

type Licensingentity_id is table of odilic_admin.licensingentity.licensingentity_id%type index by pls_integer;
type Nationalprovidernumber is table of odilic_admin.licensingentity.nationalprovidernumber%type index by pls_integer;
type Home_state_province_id is table of odilic_admin.licensingentity.home_state_province_id%type index by pls_integer;
procedure HomeStateLookup_bulk_insert(i_entityId    in Licensingentity_id,
                                      i_npn         in Nationalprovidernumber,
                                      i_homeStateId in Home_state_province_id)     is
    v_caller varchar2(60) := 'System_Scheduler';
begin
    FORALL i IN 1 .. i_entityId.count
    insert into home_state_lookup_stg
      (licensingentity_id,
       npn,
       home_state_province_id,
       isprocessed,
       inserted_by,
       inserted_date,
       updated_by,
       updated_date)
    values
      (i_entityId(i),
       i_npn(i),
       i_homeStateId(i),
       0,
       v_caller,
       sysdate,
       v_caller,
       sysdate);
end HomeStateLookup_bulk_insert;
这里是c#代码 
 NiprConnectionString = ConfigurationManager.ConnectionStrings["ODI.NIPR.DB.Reader"].ConnectionString;
        OracleConnection cnn = new OracleConnection(NiprConnectionString);
        cnn.Open();
        OracleCommand cmd = cnn.CreateCommand();
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = NaicStateLookupRepositoryProcedures.HOME_STATE_BULK_INSERT;
        cmd.BindByName = true;
        cmd.ArrayBindCount = entities.Count;
        var i_entityId = new OracleParameter();
        var i_npn = new OracleParameter();
        var i_homeStateId = new OracleParameter();
        i_entityId.OracleDbType = OracleDbType.Int32;
        i_npn.OracleDbType = OracleDbType.Varchar2;
        i_homeStateId.OracleDbType = OracleDbType.Int32;
        i_entityId.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        i_npn.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        i_homeStateId.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
        i_entityId.Value = entities.Select(c => c.Key).ToArray();
        i_npn.Value = entities.Select(c => c.Value.Item1).ToArray();
        i_homeStateId.Value = entities.Select(c => c.Value.Item2).ToArray();
        i_entityId.Size = entities.Count;
        i_npn.Size = entities.Count;
        i_homeStateId.Size = entities.Count;
        cmd.Parameters.Add(i_entityId);
        //cmd.Parameters[0].Value = i_entityId;
        cmd.Parameters.Add(i_npn);
        //cmd.Parameters[1].Value = i_npn;
        cmd.Parameters.Add(i_homeStateId);
        //cmd.Parameters[2].Value = i_homeStateId;
        int result = cmd.ExecuteNonQuery();

但是得到一个异常-

ORA-06550:第1行，第52列:PLS-00103:遇到符号">"

() - + case mod new not null

将Array作为参数传递给Oracle存储过程(从c#到批量插入)

我不能保证这是答案，我没有您参考的表来为自己测试，但乍一看，我注意到您设置:

cmd.BindByName = true;

因此，我认为你需要声明你的参数名:

var i_entityId = new OracleParameter("i_entityId");
var i_npn = new OracleParameter("i_npn");
var i_homeStateId = new OracleParameter("i_homeStateId");

我从来没有把数组作为参数传递给过程，但是如果你要用普通的插入来做这件事，它看起来像这样:

string sql = "insert into foo values (:boo, :bar, :baz)";
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.Add(new OracleParameter("boo", OracleDbType.Varchar2));
cmd.Parameters.Add(new OracleParameter("bar", OracleDbType.Date));
cmd.Parameters.Add(new OracleParameter("baz", OracleDbType.Varchar2));
cmd.Parameters[0].Value = booArray;
cmd.Parameters[1].Value = barArray;
cmd.Parameters[2].Value = bazArray;
cmd.ArrayBindCount = booArray.Length;
cmd.ExecuteNonQuery();
相关文章：