将数组传递给存储过程

我必须将数组和字符串传递给存储过程并返回数据表

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 and 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 with searched result
                DataSet ds = new DataSet();
                objAdapter.Fill(ds,"ReportData");

                return ds.Tables["ReportData"];
            }
            finally
            {
                if (objAdapter != null)
                {
                    objAdapter.Dispose();
                    objAdapter = null;
                }

                if (objComm != null)
                {
                    objComm.Dispose();
                    objComm = null;
                }

            }
}

PL/sql 端：

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

)

is

BEGIN 
    open cur_report_data for
    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,

            from
                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

            where
               (p_Organization_Id is Null or org.organization_CODE = p_Organization_Id)
               AND (p_Id is Null OR bm.BATCH_NUMBER = p_Id)
               AND (p_Value_date is null or bm.Debit_VALUE_DATE between startofday(p_Value_date) and endofday(p_Value_date))
               AND (ct.GROUP_CODE = 4)
               AND (BD.DETAIL_SEQUENCE_ID = BP.DETAIL_ID)
               AND (p_empID is Null or bd.BENEFICIARY_organization_ID in (Select column_value FROM TABLE (p_empID)))
               AND (p_account is null or bp.CREDIT_ACCOUNT in (Select column_value FROM TABLE (p_account)))
               AND (p_refrence_number is null or bp.REFERENCE_NUMBER in (Select column_value FROM TABLE (p_account)))

;    

    end Requested_Payment ;

创建类型：

CREATE OR REPLACE TYPE ORGPAYROLL.NVARCHAR_ARRAY
AS TABLE OF VARCHAR2(100)

当数组之一为空时，我在 Visual Studio 中收到此错误：

OracleParameter.Value 无效

当我传递所有三个数组时，我收到此错误：

ORA-06550: 第 1 行，第 7 列：
PLS-00306：调用“REQUESTED_PAYMENT”时参数的数量或类型错误
ORA-06550: 第 1 行，第 7 列：
PLS-00306：调用“REQUESTED_PAYMENT”时参数的数量或类型错误
ORA-06550: 第 1 行，第 7 列：
PLS-00306：调用“REQUESTED_PAYMENT”时参数的数量或类型错误
ORA-06550: 第 1 行，第 7 列：
PL/SQL：语句被忽略

您的存储过程不应期望表类型，而应该期望associative array。您可以在存储过程中将其转换为您想要的类型。看这个链接 https://stackoverflow.com/questions/29605713/calling-an-oracle-procedure-with-a-pl-sql-collection-type-parameter-via-net更多细节