Solved

ORA-01036: illegal variable name/number error

Posted on 2013-05-17
3
1,771 Views
Last Modified: 2013-05-17
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

0
Comment
Question by:dyarosh
3 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 39174527
Comment out the following line and try it again:
command.Parameters.Add("retPK", OracleDbType.Int32, ParameterDirection.Output);
0
 
LVL 10

Assisted Solution

by:eguilherme
eguilherme earned 200 total points
ID: 39174532
Hmm is it just me or you are not referencing/setting the output parameter at all in your commandtext?
0
 

Author Closing Comment

by:dyarosh
ID: 39174613
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.
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question