dyarosh
asked on
ORA-01036: illegal variable name/number error
I am getting a "ORA-01036: illegal variable name/number" error when executing my select statement. All my research indicates that the error is due to a missing parameter. This query only has one input parameter and one output parameter. I added checks to the error message to display the value of the paramter and it shows a valid value.
For this testing, the table is empty so it shouldn't return anything. However, I am not expecting an exception to be thrown. Does this mean if the record is not found, the ORA-01036 error will be returned? Is there a way to trap that error so I can display the message, "Division not found."?
For this testing, the table is empty so it shouldn't return anything. However, I am not expecting an exception to be thrown. Does this mean if the record is not found, the ORA-01036 error will be returned? Is there a way to trap that error so I can display the message, "Division not found."?
public Int32 FindDivisionByName(OracleConnection connectionObj, String division)
{
// Looks for the specified division name in the Division Table and returns the DivisionID if found and the ActiveStatusFlag is Y
// Otherwise, returns 0;
Int32 retVal = 0;
ErrMsg = "";
// Check that the database has been opened
if (connectionObj == null || connectionObj.State != ConnectionState.Open)
{
ErrMsg = "<br />Database is not opened.";
return 0;
}
// Create Command object
OracleCommand command = connectionObj.CreateCommand();
command.Connection = connectionObj;
try
{
// Insert Division information and return DivisionID
command.CommandText =
"SELECT DivisionID FROM EMP_OWNER.EMP_DIVISION " +
"WHERE Division = :Division AND ActiveStatusFlag = 'Y'";
// Remove any previously set Parameters
command.Parameters.Clear();
command.Parameters.Add("Division", OracleDbType.Varchar2, 30, ParameterDirection.Input).Value = division;
command.Parameters.Add("retPK", OracleDbType.Int32, ParameterDirection.Output);
retVal = (Int32)command.ExecuteScalar();
}
catch (Exception ex)
{
ErrMsg = "ERROR Searching Division Data: " + ex.Message + "<br />Division =" + division + "<br />Parameter: " + command.Parameters["Division"].Value;
retVal = 0;
}
finally
{
command.Dispose();
}
return retVal;
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER