Thursday, July 12, 2007

Oracle Invalid Character Error, ColdFusion and a semicolon

I faced and solved a real puzzle at work today. Hopefully the following will help someone. First, let's layout the scenario:
  • I have ColdFusion MX running locally on my PC.
  • I have a version of a web site running there that uses a DSN to a development Oracle database.
  • Running the application on the local server, everything works fine.
  • I moved all the code to the actual "development" server. This server is hosted internally by our company.
  • I run through the same steps on this server as my local server.
  • I get an Oracle "Invalid Character" error. The web page displays the SQL that it was trying to execute.
  • When I look at the SQL, it looks ok to me. There is nothing like a missing comma, or a single tick mark in between any other tick marks, or any such thing.
  • I paste the SQL into TOAD and run it. Argggh! It works fine!

To Summarize:

  • I'm doing the same thing on two ColdFusion MX web servers.
  • Both are running the same ColdFusion code.
  • Both are accessing the same Oracle database instance.
  • Both are running the same SQL.
  • On one environment it works, on the other it fails.
  • Run the SQL that fails on the one web server in TOAD and it works fine.

The Issue and Solution:
The SQL was not multiple statements run between a BEGIN and END statement. It was just a single SQL statement. But it ended with a semicolon!

When running several SQL statements at one time, you put a BEGIN at the front and an END at the end and you seperate things by semicolons. The semicolons are needed in this case. But when running one statement alone, it is not. And though however my PC was set up allowed the semicolon to be there, on the other server it did not!

To fix the problem I removed the semicolon at the end of the SQL. Now it runs on the main dev server as well as my localhost web server running on my PC. This was abit tricky!

Labels: , ,


Post a Comment

Subscribe to Post Comments [Atom]

<< Home