ORA-06550, PLS-00306; Error inserting data to Oracle procedure_问答_开发者

I have tried to troubleshoot this problem but to no avail. I get error while inserting a big chuck of data to Oracle package containing procedure 'INSCRAPP'. The error message is,

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

The C# code is big but I am providing the parameters I am passing,

oraCmd.Parameters.Clear();

            OracleParameter param_fName_in = new OracleParameter("fName_in", OracleType.VarChar);
            param_fName_in.Value = FirstName;
            param_fName_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_fName_in);

            OracleParameter param_mi_in = new OracleParameter("mi_in", OracleType.VarChar);
            param_mi_in.Value = MiddleInitial;
            param_mi_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_mi_in);

            OracleParameter param_lName_in = new OracleParameter("lName_in", OracleType.VarChar);
            param_lName_in.Value = LastName;
            param_lName_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_lName_in);

            OracleParameter param_addr1_in = new OracleParameter("addr1_in", OracleType.VarChar);
            param_addr1_in.Value = HousingAddress1;
            param_addr1_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_addr1_in);

            OracleParameter param_addr2_in = new OracleParameter("addr2_in", OracleType.VarChar);
            param_addr2_in.Value = HousingAddress2;
            param_addr2_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_addr2_in);

            OracleParameter param_city_in = new OracleParameter("city_in", OracleType.VarChar);
            param_city_in.Value = HousingCity;
            param_city_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_city_in);

            OracleParameter param_st_cd_in = new OracleParameter("st_cd_in", OracleType.VarChar);
            param_st_cd_in.Value = HousingStateCode;
            param_st_cd_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_st_cd_in);

            OracleParameter param_zip_cd_in = new OracleParameter("zip_cd_in", OracleType.VarChar);
            param_zip_cd_in.Value = HousingZipCode;
            param_zip_cd_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_zip_cd_in);

            OracleParameter param_home_phone_in = new OracleParameter("home_phone_in", OracleType.VarChar);
            param_home_phone_in.Value = HomePhone;
            param_home_phone_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_home_phone_in);

            OracleParameter param_work_phone_in = new OracleParameter("work_phone_in", OracleType.VarChar);
            param_work_phone_in.Value = WorkPhone;
            param_work_phone_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_work_phone_in);

            OracleParameter param_ext_in = new OracleParameter("ext_in", OracleType.VarChar);
            param_ext_in.Value = WorkExtension;
            param_ext_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_ext_in);

            OracleParameter param_email_Addr_in = new OracleParameter("email_Addr_in", OracleType.VarChar);
            param_email_Addr_in.Value = EmailAddress;
            param_email_Addr_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_email_Addr_in);

            OracleParameter param_ssn_in = new OracleParameter("ssn_in", OracleType.VarChar);
            param_ssn_in.Value = SocialSecurityNumber;
            param_ssn_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_ssn_in);

            OracleParameter param_dob_in = new OracleParameter("dob_in", OracleType.VarChar);
            param_dob_in.Value = DateOfBirth;
            param_dob_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_dob_in);

            OracleParameter param_dl_number_in = new OracleParameter("dl_number_in", OracleType.VarChar);
            param_dl_number_in.Value = DriverLicenseNumber;
            param_dl_number_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_dl_number_in);

            OracleParameter param_dl_st_cd_in = new OracleParameter("dl_st_cd_in", OracleType.VarChar);
            param_dl_st_cd_in.Value = DriverLicenseStateCode;
            param_dl_st_cd_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_dl_st_cd_in);

            OracleParameter param_poe_in = new OracleParameter("poe_in", OracleType.VarChar);
            param_poe_in.Value = PlaceOfEmployment;
            param_poe_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_poe_in);

            OracleParameter param_start_date_in = new OracleParameter("start_date_in", OracleType.VarChar);
            param_start_date_in.Value = EmploymentStartDate;
            param_start_date_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_start_date_in);

            OracleParameter param_income_in = new OracleParameter("income_in", OracleType.VarChar);
            param_income_in.Value = TotalAnnualIncome;
            param_income_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_income_in);

            OracleParameter param_occupation_in = new OracleParameter("occupation_in", OracleType.VarChar);
            param_occupation_in.Value = Occupation;
            param_occupation_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_occupation_in);

            OracleParameter param_rent_own_in = new OracleParameter("rent_own_in", OracleType.VarChar);
            param_rent_own_in.Value = HousingStatus;
            param_rent_own_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_rent_own_in);

            OracleParameter param_store_cd_in = new OracleParameter("store_cd_in", OracleType.VarChar);
            param_store_cd_in.Value = ShoppingStoreCode;
            param_store_cd_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_store_cd_in);

            OracleParameter param_sales_assoc_name_in = new OracleParameter("sales_assoc_name_in", OracleType.VarChar);
            param_sales_assoc_name_in.Value = SaleRepresentativeName;
            param_sales_assoc_name_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_sales_assoc_name_in);

            OracleParameter param_co_fName_in = new OracleParameter("co_fName_in", OracleType.VarChar);
            param_co_fName_in.Value = CoApp_FirstName;
            param_co_fName_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_fName_in);

            OracleParameter param_co_mi_in = new OracleParameter("co_mi_in", OracleType.VarChar);
            param_co_mi_in.Value = CoApp_MiddleInitial;
            param_co_mi_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_mi_in);

            OracleParameter param_co_lName_in = new OracleParameter("co_lName_in", OracleType.VarChar);
            param_co_lName_in.Value = CoApp_LastName;
            param_co_lName_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_lName_in);

            OracleParameter param_co_addr1_in = new OracleParameter("co_addr1_in", OracleType.VarChar);
            param_co_addr1_in.Value = CoApp_HousingAddress1;
            param_co_addr1_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_addr1_in);

            OracleParameter param_co_addr2_in = new OracleParameter("co_addr2_in", OracleType.VarChar);
            param_co_addr2_in.Value = CoApp_HousingAddress2;
            param_co_addr2_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_addr2_in);

            OracleParameter param_co_city_in = new OracleParameter("co_city_in", OracleType.VarChar);
            param_co_city_in.Value = CoApp_HousingCity;
            param_co_city_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_city_in);

            OracleParameter param_co_st_cd_in = new OracleParameter("co_st_cd_in", OracleType.VarChar);
            param_co_st_cd_in.Value = CoApp_HousingStateCode;
            param_co_st_cd_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_st_cd_in);

            OracleParameter param_co_zip_cd_in = new OracleParameter("co_zip_cd_in", OracleType.VarChar);
            param_co_zip_cd_in.Value = CoApp_HousingZipCode;
            param_co_zip_cd_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_zip_cd_in);

            OracleParameter param_co_home_phone_in = new OracleParameter("co_home_phone_in", OracleType.VarChar);
            param_co_home_phone_in.Value = CoApp_HomePhone;
            param_co_home_phone_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_home_phone_in);

            OracleParameter param_co_work_phone_in = new OracleParameter("co_work_phone_in", OracleType.VarChar);
            param_co_work_phone_in.Value = CoApp_WorkPhone;
            param_co_work_phone_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_work_phone_in);

            OracleParameter param_co_ext_in = new OracleParameter("co_ext_in", OracleType.VarChar);
            param_co_ext_in.Value = CoApp_WorkExtension;
            param_co_ext_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_ext_in);

            OracleParameter param_co_email_Addr_in = new OracleParameter("co_email_Addr_in", OracleType.VarChar);
            param_co_email_Addr_in.Value = CoApp_EmailAddress;
            param_co_email_Addr_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_email_Addr_in);

            OracleParameter param_co_ssn_in = new OracleParameter("co_ssn_in", OracleType.VarChar);
            param_co_ssn_in.Value = CoApp_SocialSecurityNumber;
            param_co_ssn_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_ssn_in);

            OracleParameter param_co_dob_in = new OracleParameter("co_dob_in", OracleType.VarChar);
            param_co_dob_in.Value = CoApp_DateOfBirth;
            param_co_dob_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_dob_in);

            OracleParameter param_co_dl_number_in = new OracleParameter("co_dl_number_in", OracleType.VarChar);
            param_co_dl_number_in.Value = CoApp_DriverLicenseNumber;
            param_co_dl_number_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_dl_number_in);

            OracleParameter param_co_dl_st_cd_in = new OracleParameter("co_dl_st_cd_in", OracleType.VarChar);
            param_co_dl_st_cd_in.Value = CoApp_DriverLicenseStateCode;
            param_co_dl_st_cd_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_dl_st_cd_in);

            OracleParameter param_co_poe_in = new OracleParameter("co_poe_in", OracleType.VarChar);
            param_co_poe_in.Value = CoApp_PlaceOfEmployment;
            param_co_poe_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_poe_in);

            OracleParameter param_co_start_date_in = new OracleParameter("co_start_date_in", OracleType.VarChar);
            param_co_start_date_in.Value = CoApp_EmploymentStartDate;
            param_co_start_date_in.Direction开发者_如何学运维 = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_start_date_in);

            OracleParameter param_co_income_in = new OracleParameter("co_income_in", OracleType.VarChar);
            param_co_income_in.Value = CoApp_TotalAnnualIncome;
            param_co_income_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_income_in);

            OracleParameter param_co_occupation_in = new OracleParameter("co_occupation_in", OracleType.VarChar);
            param_co_occupation_in.Value = CoApp_TotalAnnualIncome;
            param_co_occupation_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_occupation_in);

            OracleParameter param_co_rent_own_in = new OracleParameter("co_rent_own_in", OracleType.VarChar);
            param_co_rent_own_in.Value = CoApp_HousingStatus;
            param_co_rent_own_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_co_rent_own_in);

            OracleParameter param_ip_address_in = new OracleParameter("ip_address_in", OracleType.VarChar);
            param_ip_address_in.Value = IPAddress;
            param_ip_address_in.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_ip_address_in);

            OracleParameter param_REQ_FINANCED_AMT_IN = new OracleParameter("REQ_FINANCED_AMT_IN", OracleType.VarChar);
            param_REQ_FINANCED_AMT_IN.Value = RequestedFinanceAmount;
            param_REQ_FINANCED_AMT_IN.Direction = ParameterDirection.Input;
            oraCmd.Parameters.Add(param_REQ_FINANCED_AMT_IN);

            OracleParameter param_o_cust_cd = new OracleParameter("o_cust_cd", OracleType.VarChar, 10);
            param_o_cust_cd.Direction = ParameterDirection.Output;
            oraCmd.Parameters.Add(param_o_cust_cd);

            OracleParameter param_o_person_nr = new OracleParameter("o_person_nr", OracleType.Number);
            param_o_person_nr.Direction = ParameterDirection.Output;
            oraCmd.Parameters.Add(param_o_person_nr);

The parameters in Oracle Procedure are,

PROCEDURE insCrApp (fName_in            IN VARCHAR2,
                    mi_in               IN VARCHAR2,
                    lName_in            IN VARCHAR2,
                    addr1_in            IN VARCHAR2,
                    addr2_in            IN VARCHAR2,
                    city_in             IN VARCHAR2,
                    st_cd_in            IN VARCHAR2,
                    zip_cd_in           IN VARCHAR2,
                    home_phone_in       IN VARCHAR2,
                    work_phone_in       IN VARCHAR2,
                    ext_in              IN VARCHAR2,
                    email_Addr_in       IN VARCHAR2,
                    ssn_in              IN VARCHAR2,
                    dob_in              IN VARCHAR2,
                    dl_number_in        IN VARCHAR2,
                    dl_st_cd_in         IN VARCHAR2,
                    poe_in              IN VARCHAR2,
                    start_date_in       IN VARCHAR2,
                    income_in           IN VARCHAR2,
                    occupation_in       IN VARCHAR2,
                    rent_own_in         IN VARCHAR2,
                    store_cd_in         IN VARCHAR2,
                    sales_assoc_name_in IN VARCHAR2,
                    co_fName_in         IN VARCHAR2,
                    co_mi_in            IN VARCHAR2,
                    co_lName_in         IN VARCHAR2,
                    co_addr1_in         IN VARCHAR2,
                    co_addr2_in         IN VARCHAR2,
                    co_city_in          IN VARCHAR2,
                    co_st_cd_in         IN VARCHAR2,
                    co_zip_cd_in        IN VARCHAR2,
                    co_home_phone_in    IN VARCHAR2,
                    co_work_phone_in    IN VARCHAR2,
                    co_ext_in           IN VARCHAR2,
                    co_email_Addr_in    IN VARCHAR2,
                    co_ssn_in           IN VARCHAR2,
                    co_dob_in           IN VARCHAR2,
                    co_dl_number_in     IN VARCHAR2,
                    co_dl_st_cd_in      IN VARCHAR2,
                    co_poe_in           IN VARCHAR2,
                    co_start_date_in    IN VARCHAR2,
                    co_income_in        IN VARCHAR2,
                    co_occupation_in    IN VARCHAR2,
                    co_rent_own_in      IN VARCHAR2,
                    ip_address_in       IN VARCHAR2,
                    REQ_FINANCED_AMT_IN IN VARCHAR2,
                    o_cust_cd          OUT VARCHAR2,
                    o_person_nr        OUT NUMBER)

Please help as I couldn't figure out what the problem is as it seems like I am not setting the datatype correct in my C# code though I expect OracleType.VarChar would map fine to VARCHAR2 in Oracle.

Thanking in anticipation.

The following parameters are being repeated. Looks like the copy-paste got messed up .

OracleParameter param_fName_in = new OracleParameter("fName_in", OracleType.VarChar);
OracleParameter param_mi_in = new OracleParameter("mi_in", OracleType.VarChar);
OracleParameter param_lName_in = new OracleParameter("lName_in", OracleType.VarChar);
OracleParameter param_addr1_in = new OracleParameter("addr1_in", OracleType.VarChar);
OracleParameter param_addr2_in = new OracleParameter("addr2_in", OracleType.VarChar);
OracleParameter param_city_in = new OracleParameter("city_in", OracleType.VarChar);

As the parameters appear to match, with the possible query @Rajesh raised about the size being specified for o_cust_id, two possibilities occur; (1) are you sure that you're hitting that right package - that there isn't a different version with different parameters in another schema that you might be picking up instead? (2) you've focused on the parameters, but are you sure the procedure call is right? Might be helpful to post that code too.

Have you tried with a reduced case - a procedure with one parameter - to see that it works as you expect with OracleType.VarChar/VARCHAR2?