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.DbParamsCollection;
    m_oracleCommand[0].Parameters.Add(":" + p.ParameterName, p.Value).Direction = System.Data.ParameterDirection.Input;
    m_oracleCommand[0].Parameters.Size = p.Value.ToString().Length;

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

Thanks,

 

Doug

 

Answer 1

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.

Answer 2

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.

Answer 3

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.

 

Answer 4

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.

Answer 5

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 :)

Answer 6

Thanks much for this, putting the BEGIN...END around the command text fixed my problems.

Answer 7

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. =)

Answer 8

Thanks... I was beating my head...i had a mistake of space in the parameetr..!!

Answer 9

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

 

Answer 10

This was my issue, I was doing this:

 

 like upper(':username') 

However, the quotes are not necessary (doh!), this worked instead:

 like upper(:username)