Solved

ORA-01036: illegal variable name/number error

Posted on 2013-05-17
3
1,761 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

947 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now