ORA-01036: illegal variable name/number

Question

Hi all
Im trying to sort out this issue. I'm using Oracle database and C#, The error I got System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/number. I don't know how I can solve it. I tried to remove the @ in the parameters or even replace them like p.Course_id, then ?p.course, p_course_id but nothing changed.
Please help sort out this issue. Thank you

My code

command.Parameters.Add("course_Id", OracleType.Int).Value = course_Id;
command.Parameters.Add("question", OracleType.VarChar).Value = question;
command.Parameters.Add("posterName", OracleType.VarChar).Value = posterName;
command.Parameters.Add("blogdate", OracleType.DateTime).Value = blog_date;

Vulpes · Answer

As you're using the System.Data.OracleClient rather than ODP.Net the enumeration is called OracleType (rather than OracleDbType) and is in the System.Data.OracleClient namespace which you're already importing. Check this link: http://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracletype(v=vs.100).aspx

Vulpes · Answer

I hadn't noticed that before but yes SqlDbType is Sql Server specific so I think you should change to the equivalent OracleDbType enumeration values which are listed here: http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleDbTypeEnumerationType.htm When I was looking for that I came across this link : http://docs.oracle.com/cd/B19306_01/win.102/b14307/extenBoth.htm You'll notice that :1 is used here for the only parameter. So I wonder whether you should use a colon after all but number the parameters rather than using their names?

Alex · Answer

yes I did changed it thank you. Although wasn't only the problem bcz after I changed it still got the same error. I had to remove the @ replace with . Thank you for your help.

Alex · Answer

I think the issue is to do with the sqlDbType needs to change to OracleDbType thats why even when I changed the @ to colon it doesn't change. Now I have another issue because I couldnt find this reference OracleDbType and can't use it now I'm searching on how I can add it manually if possible. I really appreciate your help.Thank you very much

Alex · Answer

yes I did try it. I think I should change sqlDbType to oracleDbType because I'm using Oracle but I could not find the reference in my visual studio. Do you think this can be the issue?

Vulpes · Answer

Hmm it looks like the colon is only used in SELECT queries and the like. I've been looking for examples of calling stored procedures which take parameters and they all seem to not use any marker at all. Check this thread for example: http://forums.codeguru.com/showthread.php?475415-How-to-call-Oracle-stored-procedure-using-C That would therefore suggest you simply use: command.Parameters.Add( course_Id  SqlDbType.Int).Value = course_Id; // etc. However I think you said you'd tried that and it didn't work. If so I'd wait for other answers from folks who have more experience with Oracle than me.

Alex · Answer

Thank you for your help. I did change my code as you did but I'm still receiving same error and even anew error. System.Data.OracleClient.OracleException: ORA-06550: line 1 column 31: PLS-00103: Encountered the symbol : when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe ORA-06550: line 1 column 55: PLS-00103: Encountered the symbol : when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql ORA-06550: line 1 column 80: PLS-00103: Encountered the symbol : when expecting one of the following: ( - + case mod new not null

Vulpes · Answer

AFAIK Oracle SQL parameters need to be preceded by a colon rather than an @ sign. So I'd try: command.Parameters.Add(:course_Id SqlDbType.Int).Value = course_Id; command.Parameters.Add(:question SqlDbType.VarChar).Value = question; command.Parameters.Add(:posterName SqlDbType.VarChar).Value = posterName; command.Parameters.Add(:blogdate SqlDbType.DateTime).Value = blog_date;