pass array to stored procedure

Question

I have to pass arrays and strings to stored procedure and 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 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 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

)

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 type:

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

and I got this error in Visual Studio when one of the array is empty:

OracleParameter.Value is invalid

and I got this error when I pass all the three array:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'REQUESTED_PAYMENT'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'REQUESTED_PAYMENT'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'REQUESTED_PAYMENT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

May be useful http://stackoverflow.com/questions/2885575/passing-an-array-of-data-as-an-input-parameter-to-an-oracle-procedure — Irshad, Jan 21 '16 at 07:21
and this http://stackoverflow.com/questions/831188/how-to-create-a-stored-procedure-in-oracle-which-accepts-array-of-parameters — Irshad, Jan 21 '16 at 07:21

score 2 · Accepted Answer · edited May 23 '17 at 12:04

2

Your stored procedure should not be expecting table type instead it should be expecting associative array. You can convert this to your desired type with in your stored procedure. See this link more details

edited May 23 '17 at 12:04

Community

1
1

answered Jan 21 '16 at 07:23

Martin

644
5
11

but I set the collection type for the parameter to PLSQLAssociativeArray – Non_k Jan 21 '16 at 08:38
Thanks for pointing it out. I have updated my answer. The issue was the incorrect type used in stored procedure. :) – Martin Jan 21 '16 at 11:29

pass array to stored procedure

1 Answers1

Linked