c# - pass array to stored procedure -

i have pass arrays , strings stored procedure , return data table

c# side:

public datatable fetchrequested(string [] empid, string [] account, string [] refno, string orgid, string id, datetime valuedate) {             string connetionstring = null;             oracleconnection con;             oracledataadapter objadapter = null;             oraclecommand objcomm = new oraclecommand();              connetionstring = @"data source= payment_devlope; user id=orgpayment;password=orgpayment";             con = new oracleconnection (connetionstring);              try             {                 con.open();                 objcomm.connection = con;                 objcomm.commandtype = commandtype.storedprocedure;                 objcomm.commandtext = "pkg_reports.requested_payment";                  // add , set procedure parameters                 ////////////////////////////////////////////////////////////////////                      objcomm.parameters.add("p_empid", oracledbtype.nvarchar2, 100);                     objcomm.parameters["p_empid"].collectiontype = oraclecollectiontype.plsqlassociativearray;                      if (empid.length != 0)                         objcomm.parameters["p_empid"].value = empid;                  ////////////////////////////////////////////////////////////////////                      objcomm.parameters.add("p_account", oracledbtype.nvarchar2, 100);                     objcomm.parameters["p_account"].collectiontype = oraclecollectiontype.plsqlassociativearray;                      if (account.length != 0)                         objcomm.parameters["p_account"].value = account;                  ////////////////////////////////////////////////////////////////////                  objcomm.parameters.add("p_refrence_number", oracledbtype.nvarchar2, 100);                 objcomm.parameters["p_refrence_number"].collectiontype = oraclecollectiontype.plsqlassociativearray;                  if (refno.length != 0)                     objcomm.parameters["p_refrence_number"].value = refno;                  ////////////////////////////////////////////////////////////////////                  objcomm.parameters.add("p_organization_id", oracledbtype.varchar2);                 objcomm.parameters["p_organization_id"].direction = parameterdirection.input;                  if (!string.isnullorempty(orgid))                 {                     objcomm.parameters["p_organization_id"].value = orgid;                 }                 ////////////////////////////////////////////////////////////////////                  objcomm.parameters.add("p_id", oracledbtype.varchar2);                 objcomm.parameters["p_id"].direction = parameterdirection.input;                  if (!string.isnullorempty(id))                 {                     objcomm.parameters["p_id"].value = id;                 }                 ////////////////////////////////////////////////////////////////////                  objcomm.parameters.add("p_value_date", oracledbtype.date);                 objcomm.parameters["p_value_date"].direction = parameterdirection.input;                  if (valuedate == null)                 {                     objcomm.parameters["p_value_date"].value =valuedate;                 }                  ////////////////////////////////////////////////////////////////////                 objcomm.parameters.add("cur_report_data", oracledbtype.refcursor);                 objcomm.parameters["cur_report_data"].direction = parameterdirection.output;                 ////////////////////////////////////////////////////////////////////                  objadapter = new oracledataadapter();                 objadapter.selectcommand = objcomm;                  // filling dataset searched result                 dataset ds = new dataset();                 objadapter.fill(ds,"reportdata");                  return ds.tables["reportdata"];             }                         {                 if (objadapter != null)                 {                     objadapter.dispose();                     objadapter = null;                 }                  if (objcomm != null)                 {                     objcomm.dispose();                     objcomm = null;                 }              } } 

pl/sql side:

procedure requested_payment (      p_empid             in nvarchar_array := null ,     p_account           in nvarchar_array := null,     p_refrence_number   in nvarchar_array := null,     p_organization_id   in nvarchar2      := null,     p_id      in nvarchar2      := null,     p_value_date        in date           := null,     cur_report_data     out data_table  )   begin      open cur_report_data     select  /*+ index(bp,b_payment_pk) */             org.organization_code org_id,             bm.id,             bd.beneficiary_organization_id employee_id,              bd.beneficiary_name employee_name,             bp.reference_number reference_no,             bp.credit_account acc_no,             ct.english_description reason,                              organization org inner join b_master bm                  on bm.organization_code = org.organization_code             inner join b_detail bd                  on bd.master_sequence_id = bm.master_sequence_id             inner join b_payment bp                 on bp.master_sequence_id = bm.master_sequence_id             inner join code_table ct                  on ct.code = bp.payment_status                             (p_organization_id null or org.organization_code = p_organization_id)                , (p_id null or bm.batch_number = p_id)                , (p_value_date null or bm.debit_value_date between startofday(p_value_date) , endofday(p_value_date))                , (ct.group_code = 4)                , (bd.detail_sequence_id = bp.detail_id)                , (p_empid null or bd.beneficiary_organization_id in (select column_value table (p_empid)))                , (p_account null or bp.credit_account in (select column_value table (p_account)))                , (p_refrence_number null or bp.reference_number in (select column_value table (p_account)))  ;          end requested_payment ; 

create type:

create or replace type orgpayroll.nvarchar_array table of varchar2(100) 

and got error in visual studio when 1 of array empty:

oracleparameter.value invalid

and got error when pass 3 array:

ora-06550: line 1, column 7:
pls-00306: wrong number or types of arguments in call 'requested_payment'
ora-06550: line 1, column 7:
pls-00306: wrong number or types of arguments in call 'requested_payment'
ora-06550: line 1, column 7:
pls-00306: wrong number or types of arguments in call 'requested_payment'
ora-06550: line 1, column 7:
pl/sql: statement ignored

your stored procedure should not expecting table type instead should expecting associative array. can convert desired type in stored procedure. see this link more details