2

I'm having an issue executing an Oracle query via .NET. I'm quite new to calling Oracle commands from .Net, (i'm coming from a mySQL background) so any help is much appreciated!

I first build a query string...

declare 
pDate date; 
pErrorCode binary_integer; 
pErrorInfo varchar2(500); 
begin 
date := to_date(:Date, 'YYYY-MM-DD'); 
AnUpdateRoutine(:VehicleID, :ContractID, :DriverID, :pDate, :pDriverContribution, pErrorCode, pErrorInfo); 
end;

I then add my parameters...

cmd2.Parameters.Add(":VehicleID", OracleDbType.Int32).Value = CInt(HiddenField.Get("ID"))
cmd2.Parameters.Add(":ContractID", OracleDbType.Int32).Value = CInt(HiddenField.Get("ContractID"))
cmd2.Parameters.Add(":DriverID", OracleDbType.Int32).Value = CInt(cbCurrentDriver.Value)
cmd2.Parameters.Add(":pDate", OracleDbType.Date).Value = Date.Now()
cmd2.Parameters.Add(":pDriverContribution", OracleDbType.NVarchar2).Value = DBNull.Value
cmd2.Parameters.Add("pErrorCode", OracleDbType.NVarchar2).Value = 0
cmd2.Parameters.Add("pErrorInfo", OracleDbType.NVarchar2).Value = 0
cmd2.ExecuteNonQuery()

On the ExecuteNonQuery() call I get an exception... ORA-01036: illegal variable name/number

The definition for the procedure

procedure AnUpdateRoutine(pVehicleID in T_FM_VEHICLE.F_VEH_ID%type,
                              pContractID in T_FM_CONTRACT.F_CON_ID%type,
                              pDriverID in T_FM_DRIVER.F_DVR_ID%type,
                              pDateTimeOn in date,
                              pDriverContribution in T_FM_DRIVER_CHG.F_DCH_DVR_CONTRIBUTION%type,
                              pErrorCode out binary_integer,
                              pErrorInfo out TErrorInfo);

pVehicleID, pContractID, pDriverID and pDateTimeOn are mandatory.

Michael B
  • 109
  • 2
  • 13
  • You have 6 bind variables in anonymous block and you set 7 parameters in your .Net code. Now, why do you need anonymous block altogether? Just use `ExecuteNonQuery` to call `AnUpdateRoutine` directly. Another thing is - your last parameter is custom type but you trying to fill `varchar2` with it – T.S. Jan 13 '15 at 22:39
  • You will probably need to write class with `OracleCustomTypeMappingAttribute` attribute to create a type that will map to `TErrorInfo` – T.S. Jan 13 '15 at 22:50

1 Answers1

0

I could be wrong, because it's been a while since I had Oracle inflicted upon me, but...

pErrorCode should be :pErrorCode

Same issue with :pErrorInfo?

Martin Milan
  • 6,346
  • 2
  • 32
  • 44
  • Thanks, but when I added the colon before pErrorCode and pErrorInfo it gives me the error: "ORA-01008: not all variables bound". I omitted the colon originally because they are OUT parameters, do you know if that is the correct thing to do? – Michael B Jan 13 '15 at 17:29
  • Note sure mate- it's been a while... It doesn't ring a bell though - and it doesn't seem logical either that the direction of the param should have an impact on naming... – Martin Milan Jan 15 '15 at 11:10