3
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_APPLICABLE_ADMNEXP Number,
p_DATA_NOT_APPLICABLE_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_opnenr_exp_hdr
(org_id,
year,
data_not_applicable_admexp,
data_not_applicable_opnenr,
valdn_excepn_msg)
values
(p_org_id,
p_year,
p_DATA_NOT_APPLICABLE_ADMNEXP,
p_DATA_NOT_APPLICABLE_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.VarChar, 22)).Value = clsLHSurveyCommon.gOrgid '"11568" '
Cmd.Parameters(0).Direction = ParameterDirection.Input
Cmd.Parameters.Add(New OracleParameter("p_YEAR", OracleType.VarChar, 4)).Value = clsLHSurveyCommon.gPlanYear '"2009" '
Cmd.Parameters(1).Direction = ParameterDirection.Input
Cmd.Parameters.Add(New OracleParameter("p_DATA_NOT_APPLICABLE_ADMNEXP", OracleType.Int16, 1)).Value = Convert.ToInt16(chkDataNotApplicableAdmnExp.Checke d)
Cmd.Parameters(2).Direction = ParameterDirection.Input
Cmd.Parameters.Add(New OracleParameter("p_DATA_NOT_APPLICABLE_OPENENRL", OracleType.Int16, 1)).Value = Convert.ToInt16(chkDataNotApplicableOpenEnrl.Check ed)
Cmd.Parameters(3).Direction = ParameterDirection.Input
Cmd.Parameters.Add(New OracleParameter("P_EXCEPTION_MESSAGE", OracleType.VarChar, 500)).Value = IIf(Not (String.IsNullOrEmpty(txtExceptionMessage.Text)), txtExceptionMessage.Text, System.DBNull.Value)
Cmd.Parameters(4).Direction = ParameterDirection.Input
Cmd.Parameters.Add(New OracleParameter("p_RETVAL", OracleType.Float, 4)).Direction = ParameterDirection.Output
Cmd.ExecuteNonQuery()
************************************************** ***
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_APPLICABLE_ADMEXP" NUMBER(1,0),
"VALDN_EXCEPN_MSG" VARCHAR2(500),
"DATA_NOT_APPLICABLE_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#
