Answered by:
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
Answers
All replies
-
-
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 7, 2010 10:47 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.
-
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
-
-
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.
-
-
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
-