ORA-01036: illegal variable name/number

Alex

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;

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Alex12y ago
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.
Vulpes12y ago
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
Alex12y ago
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
Vulpes12y ago
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?
Alex12y ago
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?
Vulpes12y ago
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.
Alex12y ago
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
Vulpes12y ago
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;