Stored procedure call gives an error PLS-00306 ORA-06550

Question

I have a stored procedure in Oracle which has some parameters and I am calling it from C# (http handler) and sometime getting an exception .

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

I know that the meaning of wrong number or type. It means I am not providing values in all the parameters of the procedure call.

My question is this I have used the string.trim() function in every parameter value as follows

So it should give the error or object reference or I am wrong somewhere.

var prm = new OracleParameter[8];
prm[0] = Command.Parameters.Add(new OracleParameter("IP_ACTION", OracleType.VarChar, 20));
prm[0].Value = pobjCustomerProps.Action.Trim();
prm[1] = Command.Parameters.Add(new OracleParameter("IOP_CUSTOMER_UID", OracleType.VarChar, 30));
prm[1].Direction = ParameterDirection.InputOutput;
prm[1].Value = pobjCustomerProps.CustomerID.Trim();

prm[2] = Command.Parameters.Add(new OracleParameter("IP_MOBILE_NO", OracleType.VarChar, 50));
prm[2].Value = objEncryption.Encrypt(pobjCustomerProps.MobileNo.Trim());
prm[3] = Command.Parameters.Add(new OracleParameter("IP_CUSTOMER_NAME", OracleType.VarChar, 200));
prm[3].Value = pobjCustomerProps.CustomerName==""? "": objEncryption.Encrypt(pobjCustomerProps.CustomerName.Trim());

 prm[4] = Command.Parameters.Add(new OracleParameter("IP_EMAILID", OracleType.VarChar, 500));
 prm[4].Value = pobjCustomerProps.EmailID == "" ? "" : objEncryption.Encrypt(pobjCustomerProps.EmailID.Trim());

 prm[5] = Command.Parameters.Add(new OracleParameter("IP_MODEL_CODE", OracleType.VarChar, 10));
 prm[5].Value = pobjCustomerProps.ModelCode.Trim();

 prm[6] = Command.Parameters.Add(new OracleParameter("IP_USER_UID", OracleType.VarChar, 10));
 prm[6].Value = pobjCustomerProps.UserUID.Trim();

var lobjparamErrorCode = new OracleParameter("OP_ERROR_CODE", OracleType.Number) { Direction = ParameterDirection.Output };
prm[7] = Command.Parameters.Add(lobjparamErrorCode);

and here is my sp definition

 PROCEDURE PROC_ADD_DETAILS_V1
(
    IP_ACTION IN VARCHAR2,
    IOP_CUSTOMER_UID IN OUT DIGICALL.CUSTOMER_DTL.CUSTOMER_UID%TYPE,
    IP_MOBILE_NO IN DIGICALL.CUSTOMER_DTL.MOBILE_NO%TYPE,
    IP_CUSTOMER_NAME IN DIGICALL.CUSTOMER_DTL.CUSTOMER_NAME%TYPE,
    IP_EMAILID IN DIGICALL.CUSTOMER_DTL.EMAIL_ID%TYPE,
    IP_MODEL_CODE IN  DIGICALL.CUSTOMER_DTL.MODEL_CODE%TYPE,
    IP_USER_UID IN DIGICALL.CUSTOMER_DTL.USER_UID%TYPE,
    OP_ERROR_CODE OUT NUMBER        
)

I have edited my question with sq definition. Inside this i am calling different sp on basis of some condition. — शेखर

Codo Codo · Accepted Answer · 2012-08-30T11:17:49

There are several things that can go wrong:

You can have a wrong number of parameters. Seems to be okay in your case.
You can have a wrong type of parameter (regarding in/out, regarding data type). Input/output seems to be okay in your case. The data type cannot be derived from your description because the stored procedure definition refers to some other object.
You can provide the arguments in the wrong order (and thus have a wrong type). Note that by default parameters are bound by position even though you have given a name to each parameter. It partly depends on whether you use ODP.NET or the System.Data.OracleClient classes. It doesn't seem to be the problem in your case.
It could be that your CommandText doesn't match the parameters. It's missing in your question so we can't tell.

String.trim() certainly isn't the problem.

Stored procedure call gives an error PLS-00306 ORA-06550

3 Answers