Passing Array parameter from C# to Oracle collection type of table

Question

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

Sportball · Accepted Answer · 2013-04-03 22:37:02Z

1

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.

answered Apr 3, 2013 at 22:37

Sportball

9538 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing Array parameter from C# to Oracle collection type of table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related