PLS-00306 Wrong number of types of arguments in call to 'Stored Procedure' {Pls help}

[kaushal30]

I am getting this error when I call a stored procedure from my C# code. It is a simple stored procedure with six params that inserts data :

PROCEDURE LHD_SUR_ADMNEXP _HDR_INS (
p_ORG_ID Varchar,
p_YEAR Varchar,
p_DATA_NOT_APPL ICABLE_ADMNEXP Number,
p_DATA_NOT_APPL ICABLE_OPENENR Number,
P_VALDN_EXCEPN_ MSG varchar,
p_RETVAL OUT NUMBER
)
IS

REQUIRED EXCEPTION;
BEGIN
If (p_ORG_ID IS NULL) OR Length(p_ORG_ID ) = 0 Then
Raise REQUIRED;
End If;

If (p_YEAR IS NULL) OR Length(p_YEAR) = 0 Then
Raise REQUIRED;
End If;

insert into lhd_sur_admn_op nenr_exp_hdr
(org_id,
year,
data_not_applic able_admexp,
data_not_applic able_opnenr,
valdn_excepn_ms g)
values
(p_org_id,
p_year,
p_DATA_NOT_APPL ICABLE_ADMNEXP,
p_DATA_NOT_APPL ICABLE_OPENENR,
p_valdn_excepn_ msg
);

p_retval := 1;

EXCEPTION
WHEN REQUIRED THEN --params missing
p_RETVAL := -999;
WHEN NO_DATA_FOUND THEN --data not found
p_RETVAL := -100;

END LHD_SUR_ADMNEXP _HDR_INS;
*************** *************** *************** *********
And here is the C# code
Cmd.Parameters. Clear() 'Initialize Parameters collection
Cmd.Parameters. Add(New OracleParameter ("p_ORG_ID", OracleType.VarC har, 22)).Value = clsLHSurveyComm on.gOrgid '"11568" '
Cmd.Parameters( 0).Direction = ParameterDirect ion.Input

Cmd.Parameters. Add(New OracleParameter ("p_YEAR", OracleType.VarC har, 4)).Value = clsLHSurveyComm on.gPlanYear '"2009" '
Cmd.Parameters( 1).Direction = ParameterDirect ion.Input

Cmd.Parameters. Add(New OracleParameter ("p_DATA_NOT_AP PLICABLE_ADMNEX P", OracleType.Int1 6, 1)).Value = Convert.ToInt16 (chkDataNotAppl icableAdmnExp.C hecked)
Cmd.Parameters( 2).Direction = ParameterDirect ion.Input

Cmd.Parameters. Add(New OracleParameter ("p_DATA_NOT_AP PLICABLE_OPENEN RL", OracleType.Int1 6, 1)).Value = Convert.ToInt16 (chkDataNotAppl icableOpenEnrl. Checked)
Cmd.Parameters( 3).Direction = ParameterDirect ion.Input

Cmd.Parameters. Add(New OracleParameter ("P_EXCEPTION_M ESSAGE", OracleType.VarC har, 500)).Value = IIf(Not (String.IsNullO rEmpty(txtExcep tionMessage.Tex t)), txtExceptionMes sage.Text, System.DBNull.V alue)
Cmd.Parameters( 4).Direction = ParameterDirect ion.Input

Cmd.Parameters. Add(New OracleParameter ("p_RETVAL", OracleType.Floa t, 4)).Direction = ParameterDirect ion.Output

Cmd.ExecuteNonQ uery()

*************** *************** *************** ********
I am not sure if the column types of the destination table matter, but here is that info as well.
"ORG_ID" VARCHAR2(22) NOT NULL ENABLE,
"YEAR" VARCHAR2(4) NOT NULL ENABLE,
"DATA_NOT_APPLI CABLE_ADMEXP" NUMBER(1,0),
"VALDN_EXCEPN_M SG" VARCHAR2(500),
"DATA_NOT_APPLI CABLE_OPNENR" NUMBER(1,0),

Any help would be really appreciated, as I have spent several hours on this error and cannot seem to figure it out.

This question has also been asked on this website before, but it does not have any solution to it.
http://bytes.com/topic/c-sharp/answers/260973-problem-when-accessing-oracle-procedure-varchar2#

[Plater]

Cmd is an oracle command object with the command type set to stored procedure?

[kaushal30]

Yes, ... Command is a System.Data.Ora cleClient.Oracl eCommand object, and the type is set to Stored procedure.

[Plater]

I am not sure what to suggest, I have heard of this trouble before.

You could try making a new stored procedure that only takes one parameter and see if you can get that working, then keeping adding parameters to the storedproc untill you can no longer make it work in code?

[kaushal30]

Plater, Your suggestion was brilliant. Thanks so much.

It helped me narrow down the problem to two parameters that were the cause of the issue. You would not believe, it was the name of those two parameters (3rd and 4th parameters are not named exactly as in the signature of the Stored Procedure). Oracle 10i is definitely throwing a wrong description of the error, partly to blame.

The second conclusion is also that Oracle.DataAcce ss.Client is not sensitive to parameter names. Initially we were using this version and I was not getting these errors and saving successfully to the DB. Only after changing to System.Data.Ora cleClient, this issue surfaced. Thanks again.

[Sourabh4u]

Cmd is an oracle command object with the command type set to stored procedure?

I also had same problem. I have tried following code to create parameter to command object. Its working.. i guess there are few incompatibility between .net & oracle. Oracle sometime cant recognize the parameters..


OleDbParameter param = new OleDbParameter( );
param.Parameter Name = "TPSG_ID1";
param.Value = Int32.Parse(trv Mnu.SelectedNod e.Value);
param.Direction = ParameterDirect ion.Input;
param.OleDbType = OleDbType.Integ er;
DBCmd.Parameter s.Add(param);