tags:

views:

49

answers:

3
+1  Q: 

ORA-06550, PLS-00306; Error inserting data to Oracle procedure.

Hi all,

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.

A: 

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);
Rajesh  2010-09-28 19:55:18
Are you sure they are repeated? Probably you are talking about the same parameters for co-applicant. All the parameters starting with "coApp_" are for co-applicant alongwith the primary applicant. For example, fName_in is the primary applicant First Name and coapp_fName_in is for co-applicant. Similar goes for the Oracle parameter object param_fName_in is for primary and param_co_fName_in is for co-applicant, so nothing is repeated. It matches perfectly with the parameters in oracle procedure but I still get the error.
Ashar Syed  2010-09-28 20:01:37
Sorry, Wrong analysis. As, you said, it does match the parameters in the Oracle procedure. One question though. Why are you specifying the precision for the output parameter "OracleParameter param_o_cust_cd = new OracleParameter("o_cust_cd", OracleType.VarChar, 10);" ?
Rajesh  2010-09-28 20:15:28
Well, you are right, but if I do not do it , it will error saying, the size is not specified for the output parameter o_cust_cd.
Ashar Syed  2010-09-28 20:21:53
A: 

Any further ideas or a possible solution? I still am struggling with this problem which I hope would be a simple only to solve.

Ashar Syed  2010-09-28 21:04:12
A: 

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?

Alex Poole  2010-09-28 21:54:46

related questions

Displaying Flash content in a C# WinForms application
How to get the value of built, encoded ViewState?
Unhandled Exception Handler in .NET 1.1
How do I connect to a database and loop over a recordset in C#?
How do I most elegantly express left join with aggregate SQL as LINQ query
Get a new object instance from a Type in C#
.NET Testing Framework Advice
Automatically update version number
What is the difference between an int and an Integer in Java/C#?
How to write to Web.Config in Medium Trust ?
WinForms ComboBox data binding gotcha
How do you sort a C# dictionary by value?
Adding Scripting functionality to .NET applications
Floating Point Number parsing: Is there a Catch All algorithm?
How do I print an HTML document from a web service?
Decoding T-SQL CAST in C#/VB.net
Anatomy of a "Memory Leak"
How do I get a distinct, ordered list of names from a DataTable using Linq
Reliable Timer in a Console Application
How do I fill a DataSet or a DataTable from a LINQ query resultset ?
What's the difference between Math.Floor() and Math.Truncate() in .NET?
How do I calculate relative time?
How do I calculate someone's age in C#?
Are there any conversion tools for porting Visual J# code to C#?
When setting a form's opacity should I use a decimal or double?