none
System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number

    Question

  • Is anyone  familiar with this error?

    System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number

    I get it when calling oracle stored procedures

     Here is a sample of my code:

    m_oracleCommand[0].CommandType = System.Data.CommandType.StoredProcedure;

    for (int i = 0; i < this.DbParamsCollection.Count; i++)
    {
        p = (FocusDbParameter)this.DbParamsCollectionIdea;
        m_oracleCommand[0].Parameters.Add(":" + p.ParameterName, p.Value).Direction = System.Data.ParameterDirection.Input;
        m_oracleCommand[0].ParametersIdea.Size = p.Value.ToString().Length;

    }
    m_oracleCommand[0].CommandText = "FOCUS_RPT.FOCUS_RPT_B3";
    m_oracleCommand[0].Connection = m_arrOracleConn[0];

    Thanks,

     

    Doug

     

    Thursday, August 10, 2006 9:30 PM

Answers

  • Doug,

    Try leaving off the ":" before the parameter name.  This prefix is needed for in-line parameters, but not stored procedure parameters.

    I hope this information proves helpful.

    Thursday, August 17, 2006 4:56 PM
    Moderator

All replies

  • Doug,

    Try leaving off the ":" before the parameter name.  This prefix is needed for in-line parameters, but not stored procedure parameters.

    I hope this information proves helpful.

    Thursday, August 17, 2006 4:56 PM
    Moderator
  • This kind of problem i encountered 4 to 5 times and everytime i found a different problem. Most of the time the underlying problem is with paramaters.

    Check following when you get this error.

    1. parameter name which you are setting and which you are passing in the insert, update and delete commands.
    2. Values : If you try to pass string  to a number datatype.
    3. Null Values. Null values has to be handled seperately for Delete and update
        Example : (ColumnName = :pColumn_Name OR :pColumn_Name IS NULL AND ColumnName IS NULL);
    4. Datatypes of hose columns in Oracle table with the Datatable columns
    5. Check spaces in the parameter name.

    I hope above tips may help you in resolving the problem.

    • Proposed as answer by msudheer Tuesday, December 07, 2010 10:47 PM
    Thursday, March 15, 2007 3:13 PM
  • Looks like you have to clear your parameters right before creating the first one.

    You have to also clear your parameter right after any execution.

    user this command and see if it works:

    cmd.Parameters.Clear()

    good luck.

     

    Tuesday, August 14, 2007 11:52 PM
  • I was having a similar problem.  The system required that both MS SQL Server and Oracle was to be supported.  For MS SQL Server the prefix '@' was used but in Oracle this causes a parameter mismatch.  By removing the '@' prefix the code executed successfully in Oracle. 

     

    Happy New Year 2008.

    Thursday, January 03, 2008 3:16 PM
  • I had similar problem today ...

    I was trying to make a simple code to see how connection to Oracle works ...

    this is how my code looked like :

    OracleCommand insCom = new OracleCommand("INSERT INTO pomoctechniczna VALUES(1,1,:Cat,:Sub,"
                    + ":Opis,1)" , DataBaseCon);

                insCom.Parameters.Add(":Cat", OracleType.Number,11);
                insCom.Parameters[":Cat"].Value = Convert.ToDecimal(categoryList.SelectedItem.Value);

                insCom.Parameters.Add(":Sub", OracleType.Number, 11);
                insCom.Parameters[":Sub"].Value = Convert.ToDecimal(subjectList.SelectedItem.Value);
               
                insCom.Parameters.Add(":Opis", OracleType.VarChar, 50);
                insCom.Parameters[":Opis"].Value = descriptionTextBox.Text;

    I had this "
    ORA-01036: illegal variable name/number "

    and this is how it looks like now :

    OracleCommand insCom = new OracleCommand("BEGIN INSERT INTO pomoctechniczna VALUES(1,1,:Cat,:Sub,"
                    + ":Opis,1); END;" , DataBaseCon);


    And now it works ... Anonymous block must be used ( BEGIN ... END ) ... the prefix really doesn't matter :)


    • Proposed as answer by Wrociu Wednesday, March 18, 2009 11:51 PM
    Wednesday, March 18, 2009 11:45 PM
  • Thanks much for this, putting the BEGIN...END around the command text fixed my problems.
    Monday, November 30, 2009 11:49 PM
  • Looks like you have to clear your parameters right before creating the first one.

    You have to also clear your parameter right after any execution.

    user this command and see if it works:

    cmd.Parameters.Clear()

    good luck.

     

    tanx men you just save my life. it's works fine for in-line parameters. =)
    Friday, May 21, 2010 4:27 PM
  • Thanks... I was beating my head...i had a mistake of space in the parameetr..!!
    Tuesday, December 07, 2010 10:48 PM
  • I found a new tip to check whenever I get this error, so I publish all the tips as @msudheer published before, plus the mine one ;)

    1. parameter name which you are setting and which you are passing in the insert, update and delete commands.

    2. Values : If you try to pass string  to a number datatype.

    3. Null Values. Null values has to be handled seperately for Delete and update
        Example : (ColumnName = :pColumn_Name OR :pColumn_Name IS NULL AND ColumnName IS NULL);

    4. Datatypes of hose columns in Oracle table with the Datatable columns

    5. Check spaces in the parameter name.

    6. Check if the OracleCommand has the CommandType property set to CommandType.StoredProcedure

     


    Tuesday, May 17, 2011 1:59 PM
  • This was my issue, I was doing this:

     

     like upper(':username') 
    
    However, the quotes are not necessary (doh!), this worked instead:

     like upper(:username) 
    


    Friday, June 17, 2011 6:25 PM