从C＃传递数组参数甲骨文合集类型的表 [英] Passing Array parameter from C# to Oracle collection type of table

问题描述

我在PLSQL规范申报表收集和记录。

 键入loan_recov_rec是记录（v_prncpl_ed_cd数（3），v_prncpl_recov数（7），v_int_ed_cd数（3）
     v_int_recov，编号（5））;  类型loan_recov_tbl是BINARY_INTEGER loan_recov_rec指数表;
 

  
  

程序pr_final_settlement（v_loan_recov_tbl在loan_recov_tbl）;

所以，我通过从C＃页Array参数

  cmd.ArrayBindCount = v_loan_recov_tbl.Length;
                Oracle.DataAccess.Client.OracleParameter P_loan_recov =新Oracle.DataAccess.Client.OracleParameter（v_loan_recov_tbl，Oracle.DataAccess.Client.OracleDbType.Int32）;
P_loan_recov.Direction = ParameterDirection.Input;P_loan_recov.CollectionType = Oracle.DataAccess.Client.OracleCollectionType.PLSQLAssociativeArray;
P_loan_recov.Value = v_loan_recov_tbl;
cmd.Parameters.Add（P_loan_recov）;
 

使用上述code它不工作，首先得到的

错误无法投类型'System.Int32'的对象类型的System.Array。 v_loan_recov_tbl是一个数组参数。
听，我使用的数据类型为 OracleDbType.Int32 是对还是我对decler其他数据type.I程序中声明输入参数如表

解决方案

我看到两个问题。首先你是一个PL / SQL定义的记录类型，而不是一个SQL定义的对象类型的工作。 ODAC希望SQL定义的对象类型。其次，假设你拥有所有正确的SQL定义的对象类型和C＃自定义类型定义，有C＃code的一个很好的例子，在你的机器上的ODP.NET软件堆栈阵列结合的工作：％ODAC_HOME％\\ ODP .NET \\样本\\ 4 \\ AssocArray。

I have declare a collection Table and record in PLSQL Specification.

         type loan_recov_rec is  record(v_prncpl_ed_cd number(3),v_prncpl_recov number   (7),v_int_ed_cd number(3),
     v_int_recov,number(5));



  type loan_recov_tbl is table of loan_recov_rec index by binary_integer;

procedure pr_final_settlement(v_loan_recov_tbl in loan_recov_tbl);

So, I pass the Array parameter from C# page

 cmd.ArrayBindCount = v_loan_recov_tbl.Length;
                Oracle.DataAccess.Client.OracleParameter P_loan_recov = new Oracle.DataAccess.Client.OracleParameter("v_loan_recov_tbl", Oracle.DataAccess.Client.OracleDbType.Int32);
P_loan_recov.Direction = ParameterDirection.Input;

P_loan_recov.CollectionType = Oracle.DataAccess.Client.OracleCollectionType.PLSQLAssociativeArray;
P_loan_recov.Value = v_loan_recov_tbl;
cmd.Parameters.Add(P_loan_recov);

using above code it not works it get's error Unable to cast object of type 'System.Int32' to type 'System.Array'. v_loan_recov_tbl is an Array Parameter. Hear,I am using datatype as OracleDbType.Int32 is it right or i have to decler other data type.I declare Input parameter in Procedure as Table

解决方案

I see two issues. First you are working with a PL/SQL-defined record type and not an SQL-defined object type. ODAC wants SQL-defined object types. Second, assuming you had all the proper SQL-defined object types and C# custom types defined, there's an excellent example of c# code to work with array binding in the ODP.NET software stack on your machine: %ODAC_HOME%\odp.net\samples\4\AssocArray.

这篇关于从C＃传递数组参数甲骨文合集类型的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！