When I started working with Oracle Bind Variables with Asp.net I encountered these 2 exceptions very frequently. I note down some my observations so that we can avoid these exceptions.
1) Try removing Semicolons at the end from query statements, some time its habitual to keep “;” at the end.
2) Verify, You are assigning values to the Bind Variables.
3) Verify, if you are passing parameters to the stored Procedures, they are exactly same as per requirements (order, type etc.).
4) Verify, if you are using select, then selected columns are actually in the table.
5) Verify, if your are defining the data type of the bind variable then check if the defined data type and actual column type mapping are correct.
6) If you’re using Microsoft – OracleHelper file to connect then not to mentioning the data type for bind variable is some times useful.
7) Some exception occurred due to the length of the data we are passing to parameter.
For Example – In past I come thought one experience in particular, I was creating Query dynamically depending upon the user input, generally I was passing the data in string format when generating the query that means query was treated as single string we passing to Oracle, But for longer Inputs system started showing me exceptions
“ORA-01036: illegal variable name/number” later found that Oracle limit query size to 4k, means if we are having the column of type CLOB/BLOB in DB and we are passing passing the parameter with data type as string with largest data in that string the query which is getting created eventually is more than 4k and we get the exception. In this case we need to use Bind Variables with no Oracle Data Type mentioned.
8) Remove the “:” from the prefix of the Bind Variable which is getting passed parameter of the stored procedure… it is not required when we are calling SP from Asp.net.