Link to home
Create AccountLog in
Avatar of dyarosh
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."?

        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;
        }

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dyarosh
dyarosh

ASKER

I had copied the code from an Insert Statement and modified it for my select and never removed the output parameter.  Can't tell you how long I stared at the code without seeing it.  Thanks.