Friday, July 25, 2008

Troubleshooting the "ORA-01036: illegal variable name/number" Error When Calling an Oracle Stored Procedure from .NET

Yesterday I had written some code in C# to call a stored procedure in an Oracle database, and when I began testing this code today, I was surprised to get the following error whenever the code would attempt to execute the OracleCommand:

ORA-01036: illegal variable name/number

I was scratching my head. Why wasn't this working? I've done this before!

From previous experience, I knew that the names of the procedure parameters must match exactly when you add them to the OracleCommand object. In other words, if your stored procedure is expecting two parameters, named email and phonenumber, your code should look something like this:

cmd.Parameters.Add("email", OracleType.VarChar);
cmd.Parameters.Add("phonenumber", OracleType.VarChar);
cmd.Parameters["email"].Value = psEmail;
cmd.Parameters["phonenumber"].Value = psPhone;

I checked the parameter names and I was already using the correct ones. What else was I missing? Then it dawned on me: when I instantiated the OracleCommand object, I forgot to specify that it was a stored procedure. When I added the following line of code, everything worked as intended:

cmd.CommandType = System.Data.CommandType.StoredProcedure;

I hope this helps anyone else who was going through the same frustration as I was! :)

6 comments:

Hank said...

Thank you!!! Two hours of ripping my beard and rebooting the oracle server and client... DOH! Thanks!

Anonymous said...

Thanks!!. My issue was a misspelling on one of the parameters name.

in my WebService (notice the spaces after "P_status

OracleParameter P_STATUS = CredUtility.CreateParam("P_STATUS ", OracleType.VarChar, ParameterDirection.Input, Status);

in the stored procedure: the parameter name was "P_STATUS"

Anonymous said...

Thank you, Alan. I was invoking a complex stored procedure with all sorts of parameters and forgot to set the Command Type! You saved my hours!

Anonymous said...

+1 or more for this...
Lost a few hours there :(
Thanks a lot

Player said...

It is really helped me.I found space behind my passing param in my .net code. Thanks for the post owner and Anonymous.

gvantsa grigolia said...

(y)

Post a Comment