Posts Tagged ‘oracle’

ORA-01036- The Comprehensive Guide for Frustrated ASP.Net People

If you’re running into the ambiguous, unhelpful “ORA-01036: illegal variable name/number” error when trying to do an update or an insert in ASP.Net while using a SqlDataSource, here’s some advice that may prevent you from pulling out all of your hair, swearing off technology forever, moving to rural Wyoming and living off the land until the end of your days…

  • Make sure you have the ProviderName specified in your SqlDataSource tag.  It should be System.Data.OracleClient (unless you’re using the Oracle.Whatever.Whatever provider)
  • Make sure you’re using :MyParameterName instead of @MyParameterName.
  • Get rid of OldValuesParameterFormatString in your SqlDataSource tag.
  • Each and every input where you have specified Bind(“FIELD_NAME”) MUST be represented in your UpdateParameters (and/or InsertParameters).  To debug this problem, what I recommend is that you change ALL of your Bind()s to Eval()s and pare down your SQL statements and update/insert parameters to just one or two things…then change the Evals to Binds as you add parameters to your SQL statement.  Every time you get a successful update/insert, add another parameter or two until you’ve found the problem.  More than likely you’ve got an extra input lurking somewhere that isn’t represented in your SQL query and/or parameters.

ORA-01036: illegal variable name/number parameter in ASP.Net app

My Oracle chops are way outdated, and, being a fan of business objects and/or a genuine Data Access Layer, I’m not used to working with typed datasets in asp.net.  So when I got the above error I was pretty stumped, and I started using some obscene anti-Oracle phrases to name a few test variables to figure out wtf was going on.

Eventually I figured out that it’s not a good idea to just enter parameters into a Select method via the Properties window, even though Visual Studio will cheerfully let you do that.  If you don’t have the parameter referenced in the actual SQL of the select method, you’re gonna get the above message when you try using it.  So…

In your Select method’s SQL, be sure to add “WHERE ID = :ID” and Visual Studio should pick up on that and make a parameter for you.  Too bad it doesn’t work the other way around.

When I was trying to find info on this error I found lots of talk about update parameters and other causes, but didn’t see anything related to this particular circumstance.

Seriously, Oracle…?

OK, so it looks like Oracle databases don’t have a quick, easy, non-triggery way to do an autoincrement the same way MS SQL, MySQL, postgres, and every other database on the planet have.  You have to create a “sequence” and then create a trigger to run use that sequence whenever data is inserted into the table.

Seriously?  Wow.

One page I came across in my search for a non-shitheaded way to do this describes this as being a more flexible way than having an auto-increment.  Go to that page and check out the code for MSSQL’s way vs. Oracle’s way.   One line of code versus about 20, plus you have to do it in 3 separate steps.  Using that page’s definition of “more flexible”, I guess a gnawed piece of bone is more flexible than a backhoe.

I imagine Oracle DBAs and developers probably have some quick scripts to let them add incremented keys more easily.  I’ll have to dig around and see.  Considering how much effort Oracle puts into their products as far as scalability and performance, I’m really surprised to see this kind of glaring omission.