Solved

ORA-01036: illegal variable name/number error

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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…
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 how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

813 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

15 Experts available now in Live!

Get 1:1 Help Now