I am writing a simple MVC Application using ODP.NET. I am trying to call a Pl/Sql proc that inserts a record.
Here is the simple Pl/Sql:
procedure spAddCountry(pGisRecid in country.GISRECID%type,
pCountryCode in country.COUNTRYCODE%type,
pCountryName in country.COUNTRYNAME%type,
pCurrencyCode in country.CURRENCYCODE%type,
pEUTerritory in country.EUTERRITORY%type,
pFatCAStatus in country.FATCASTATUS%type,
pFATF in country.FATF%type,
pFSCountryCode in country.COUNTRYCODE%type,
pInsertedBy in country.INSERTEDBY%type,
pInsertedOn in country.INSERTEDON%type,
pLanguages in country.LANGUAGES%type,
pNCCT in country.NCCT%type) is
PRAGMA AUTONOMOUS_TRANSACTION;
begin
INSERT INTO COUNTRY (GISRECID, COUNTRYCODE, COUNTRYNAME, CURRENCYCODE,
EUTERRITORY, FATCASTATUS, FATF, FSCOUNTRYCODE, INSERTEDBY,
INSERTEDON, LANGUAGES, NCCT)
VALUES(pGISRECID, pCOUNTRYCODE, pCOUNTRYNAME, pCURRENCYCODE,
pEUTERRITORY, pFATCASTATUS, pFATF, pFSCOUNTRYCODE, pINSERTEDBY,
pINSERTEDON, pLANGUAGES, pNCCT);
Commit;
end;
I am having difficulty passing the date parameter, pInsertedOn, to the Stored Proc. I have verified that the web form retrieves the form data successfully and calls the AddCountry method below, which in turns calls the stored proc, spAddCountry, after populating all of the parms.
Here is a snippet of the MVC C# code.
I get the following exception: "ORA-01840 input value not long enough for date format".
public void AddCountry(Country aCountry) //because the country object field names match the form field names they automatically get bound!!
{
string oradb = "Data Source=XYZ;User Id=XYZ;Password=xyz;";
OracleConnection conn = new OracleConnection(oradb);
OracleCommand cmd = conn.CreateCommand();
cmd.CommandText = "tstpack.spAddCountry";
cmd.CommandType = CommandType.StoredProcedure;
...
OracleParameter paramInsertedBy = new OracleParameter();
paramInsertedBy.ParameterName = "pInsertedBy";
paramInsertedBy.Value = aCountry.InsertedBy;
cmd.Parameters.Add(paramInsertedBy);
// CultureInfo ci = new CultureInfo("en-US");
OracleParameter paramInsertedOn = new OracleParameter();
paramInsertedOn.ParameterName = "pInsertedOn";
// paramInsertedOn.Value = DateTime.Now; //just testing to see if it's WebForm issue
// paramInsertedOn.Value = Convert.ToDateTime(DateTime.Now.ToString(), ci); //flail!
paramInsertedOn.Value = aCountry.InsertedOn;
cmd.Parameters.Add(paramInsertedOn);
...
conn.Open();
cmd.ExecuteNonQuery(); //CRASH! ORA-01840
conn.Close();
}
Just to verify that the flow of the program is working, I tried removing the date parm "pInsertedOn" from the pl/sql and from the parm list above, and everything worked fine. I know I am going off of the rails with the date. Can someone tell me how to pass a date to Oracle from an MVC WebForm? Is there some sort of type cast needed? I would really appreciate an example too.
Thanks so much!
ps, I did try changing the parm type to Varchar2 in the Pl/Sql and doing some conversions myself in the Pl/Sql, the automatic MVC binder was getting in my way, forcing the property of paramInsertedOn.OracleType to DateTime. I tried forcing it to Varchar2, but no luck there either...
