Hi all,
I'm trying to get my head around what's going wrong when I'm executing the below select statement in my asp.net MVC project.
public static Vessel GetVesselDetails(string vesselId, string outbound, string callnumber)
{
var vessel = new Vessel {Id = vesselId};
using (var connection = new OracleConnection(_connectionString))
{
const string query = @"SELECT * FROM OPS_REPORT.SHIP_VOYAGE WHERE SHIP_ID= :1 AND SHIP_VOYAGE.OUT_VOY_NBR= :2 AND SHIP_VOYAGE.OUT_CALL_NBR = :3";
var command = new OracleCommand(query) { Connection = connection };
command.Parameters.Add("1", OracleDbType.NVarchar2, 5).Value = vesselId;
command.Parameters.Add("2", OracleDbType.NVarchar2, 5).Value = outbound;
command.Parameters.Add("3", OracleDbType.NVarchar2, 5).Value = callnumber;
try
{
connection.Open();
OracleDataReader dr = command.ExecuteReader();
while (dr.Read())
{
vessel.Name = dr["NAME"].ToString();
vessel.LineOperator = dr["LINE_ID"].ToString();
vessel.Service = dr["SERVICE_ID"].ToString();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
if (connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
return vessel;
}
The parameters passed trigger the below error, however they're doing this not consistently.
ORA-01460: unimplemented or unreasonable conversion
For example when I run the above with the following parameters, it runs without any issues.
string vesselId = "DES";
string outbound = "16C1";
string callnumber = "1";
However the parameters change to the below, it returns the ORA-01460 exception...
string vesselId = "CGVG";
string outbound = "078E";
string callnumber = "1";
Both select statements work perfectly when I execute them with the above values directly via Toad onto the database view. So that puzzles me even more.
I'm sure I'm overlooking something simple, or handling the parameter declaration in a way that's not recommended. Any pointers towards the right way are much appreciated.
What I have tried:
I have tried changing the way I set the parameters after browsing some information in regards to the ORA-01460 error message.
command.Parameters.Add(new OracleParameter("1", vesselId));
command.Parameters.Add(new OracleParameter("2", outbound));
command.Parameters.Add(new OracleParameter("3", callnumber));
What happens than is that I get the error message when sending in both sets of parameters I described in the Problem summary above.
I checked the Datatypes on the tables as well, these are:
SHIP_ID -> VARCHAR2 (4 Byte)
OUT_VOY_NBR -> VARCHAR2 (5 Byte)
OUT_CALL_NBR -> VARCHAR2 (1 Byte)
All of them are not Nullable.