OracleParameter question

[Ron Jacobs]

I am having trouble using an oracle parameter in a simple
sql statement. I am able to get this sort of thing to
work in SQL Server with no problem...


Here is my code for oracle:

OracleConnection conn = new
OracleConnection(strConnection);
conn.Open();
string strCommand = "Insert into RonTable
(STRCOL) VALUES (STRCOL)";
string strValue = "Here is the value from
TestParameterCommands";
OracleCommand insCmd = new OracleCommand
(strCommand,conn);

OracleParameter op = new OracleParameter
("STRCOL",OracleType.VarChar,100);
op.Value = strValue;
insCmd.Parameters.Add(op);
int iRows = insCmd.ExecuteNonQuery();


When the command is executed, I get an exception whose
message reads: "ORA-01036: illegal variable name/number"

I have tried various names for the parameter to no avail.

Any Ideas??

I should note that I have no problem calling a stored
procedure on Oracle, just a Parameterized sql statement!

Thanks in advance,

Ron

 

[Hussein Abuthuraya[MSFT]]

Ron,

Thanks for reporting this problem. I reproduced the same error using your code.

The solution to this problem is very simple, change:

string strCommand = "Insert into RonTable (STRCOL) VALUES (STRCOL)";

to

string strCommand = "Insert into RonTable (STRCOL) VALUES STRCOL)";

Notice the colon before the parameter name.

I have no explanation why this is needed. I'll try to get more info and let you know.


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 

[Jeff Fellers]

Ron,

This is most likely due to a bug in the .Net Managed Provider for
Oracle. Here is a quote from a Microsoft Support person who replied to
me when I had a similar problem:

"The error you are seeing is due to a recently discovered bug in the 1.1
Framework's Oracle Managed Provider; unfortunately, we do not have a fix
at this time. If you require this functionality and do not wish to
recode and avoid using the OracleParameter object, the easiest
resolution would be to run your application with the 1.0 Framework."

Hope this helps,

Jeff

 

[Hussein Abuthuraya[MSFT]]

Hi Ron,

The use of ":" before a parameter name is documented in the OracleClient documentation. Here is a link to the MSDN documentation:

http://msdn.microsoft.com/library/d...leclientoraclecommandclassparameterstopic.asp

Looks like this how oracle defines parameters.

I hope this helps!


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

 

[Hussein Abuthuraya[MSFT]]

I'm sure the other person refers to something else. The ":" usage is documented in the MSDN documentation:

http://msdn.microsoft.com/library/d...leclientoraclecommandclassparameterstopic.asp

If this is in the Docs then it can't be a bug.


Thanks,
Hussein Abuthuraya
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

Are you secure? For information about the Microsoft Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.