ORA-00936 Missing Expression in SSIS 2005

I’ve been doing quite a bit of SSIS 2005 lately and I’ve come across a weird problem that almost had the best of me. It’s no secret that I absolutely *hate* Oracle and this problem I just had didn’t make it any better.

What I had was a quite long and complex pl/sql query (over 250 lines of code!) that was running smoothly in sql developer, the Oracle OLE DB provider in SSIS had absolutely no problems with the code when previewing data or mapping the column metadata. But when I saved the package and ran it I got the error message saying “ORA-000938: missing expression”. Sooo…what to do? Obiously there was some kind of syntax error but I have a query that runs perfecty in SQL Developer, it validates without a hitch in SSIS and I can even preview the data in the OLE DB source object in SSIS. Quite amazing to be honest!

So, after 7 hours (!! I kid you not!) of debugging this query I had a colleague step in to my office for a few minutes. And then he dropped the bomb: “Did you remove all the comments in your query??”. I just stared back at him in disbelief but at that point I was ready to do just about anything so I tried it and; Voila. The query ran through. After a bit more debugging I learned that it was the “–” (double dash) it didn’t like and that the /* */ works just fine and I think it has something to do with th driver actually removing all the carriage returns in the statement so anything after the first comment is also commented out. Go Oracle!

In conclusion:
Don’t ever comment your PL/SQL queries in ssis using “–” (double dash), it simply will not work!


10 thoughts on “ORA-00936 Missing Expression in SSIS 2005

  1. Thank you I just had the same problem and your post pointed directly to the issue! Saved me hours

  2. Haha…ur post gave me a good laugh…..saved my time, solved my problem and thaught me something new. Thank you.

  3. OMG. What’s worse was that the double dashes were working for me and then I added one more comment that was somehow different (maybe no preceding the double dash?) and this started. /*…*/ did the trick. Oracle: the secret salesman for hard liquor.

    • ummm, the word after “no preceding” was “tab”. I put it in angle brackets and the blog code ate my words…or word.


    Saved me hours. Yuck.

    I was also getting “invalid identifier” at one point for the same reason.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s