Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORA-01036: illegal variable name/number error

Posted on 2013-05-17
3
Medium Priority
?
1,833 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1200 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 800 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the 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

704 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