Exception - ORA-01036: illegal variable name/number

Author:Mudassar Khan

Server Error in '/' Application.

ORA-01036: illegal variable name/number

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

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

Cause

This error occurs when the parameters are not properly passed to the SQLDatasource or Command object when connecting to Oracle Database using System.Data.OracleClient.

Issue

The solution to this problem is to correctly pass the parameters Consider the following Query

SELECT * FROM Customers WHERE Cname = :pCname

As you can see above the parameter in Oracle has a prefix : (colon) and not @ like SQL Server Query

When using parameters in query they must start with colon and when adding them they should be added without colon

SelectCommand.Parameters.Add("pCname", OracleType.VarChar, 80).Value="Smith";

Same applies to SQLDatasource

SelectCommand="SELECT * FROM Customers WHERE Cname = :pCname"

<asp:Parameter Name = "pCname"/>

</SelectParameters>

The links below will give some additional information

http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracleparametercollection.aspx