Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Array binding parameter of type PLSQLAssociativeArray

286773Jan 7 2004 — edited Jan 9 2004
Hi,
I'm having problem array binding parameter of type PLSQLAssociativeArray to a call to plsql stored proc from C#. Following is the details. Any help really appreciated:

Stored proc code:
CREATE OR REPLACE PACKAGE BODY l_save_8xx_bulk
IS
PROCEDURE p_test(
in_codes IN varchar_list_array,
in_desc IN VARCHAR2
)
IS
BEGIN
FORALL i in in_codes.FIRST..in_codes.LAST
INSERT INTO test_tab2
(code_col)
VALUES
(in_codes(i));
END p_test;
END l_save_8xx_bulk;

Table desc:
SQL> desc test_tab2
Name Null? Type
--------- -------- ------
CODE_COL VARCHAR2(2048)

C# code:
===========
public void TestBulk() {
String[][] pCodes = new String[3][];
pCodes[0] = new String[3] {"A","B","C"};
pCodes[1] = new String[3] {"D","E","F"};
pCodes[2] = new String[3] {"G","H","I"};

String[] pDescs = new String[3] {"Desc 1","Desc 2","Desc 3"};


using (OracleConnection dbc = new OracleConnection(prodConnectDesc))
{
dbc.Open();
OracleTransaction tx = dbc.BeginTransaction();
OracleCommand cmd = new OracleCommand("l_save_8xx_bulk.p_test",dbc);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ArrayBindCount = 3;

OracleParameter param = cmd.Parameters.Add("in_codes",OracleDbType.Varchar2);
param.Direction = ParameterDirection.Input;
param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param.Value = pCodes;
OracleParameter paramDesc = cmd.Parameters.Add("in_desc",OracleDbType.Varchar2);
paramDesc.Direction = ParameterDirection.Input;
paramDesc.Value = pDescs;

cmd.ExecuteNonQuery();
tx.Commit();
}
}

When I don't use the arraybind (i.e. comment cmd.ArrayBindCount = 3;) and pass single dimension array to the first parameter then it works fine. But as as I plugin the array binding code in I get the following Oracle error:
Oracle.DataAccess.Client.OracleException : ORA-01485: compile bind length different from execute bind length

Thanks for your help in advance ,
Thanks,
Ayan
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 6 2004
Added on Jan 7 2004
1 comment
411 views