CODEWITHSUNDEEP
c#oracle-databasestored-procedures
शेखर
शेखर

Reputation: 17614

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

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        
)

Upvotes: 2

Views: 5385

Answers (3)

sjmarsh
sjmarsh

Reputation: 281

I had a similar issue but I needed to change my connection string to include

providerName="Oracle.ManagedDataAccess.Client"

instead of

providerName="Oracle.DataAccess.Client"

Upvotes: 0

Jeremy Thompson
Jeremy Thompson

Reputation: 65702

I was getting this error and it was a complete red herring.

enter image description here

Oracle client 11.2 and 9.2 were both installed. x64 bit server with a x86 application.

The problem was resolved by uninstalling 11.2 and changing a connection string to use msdaora.1 instead of OraOLEDB.Oracle.1.

Alex Keh - Product Manager-Oracle explains why this works: https://community.oracle.com/thread/2491412

Hope this saves someone else a few locks of hair and days of frustration.

Upvotes: 2

Codo
Codo

Reputation: 78985

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.

Upvotes: 4

Related Questions