January 25, 2011 at 2:26 pm
I have been working on migrating three of our old server to a new single instance. The new instance is 2008R2 and I am using the 64bit OraOLEDB.Oracle provider.
I have a couple of queries that are used in a DataFlow Task as the source(oracle). Both of these queries will run against the 32bit MS Oracle provider. Using the 64b Oracle provider, I have gotten both to parse, and provide results with the 'Preview'. Both will run in TOAD. Neither is successful when executing the package/task. Both fail with "ORA-00936: missing expression". Has anyone else suffered anything similar. Resolution?
I have found that the OraOLEDB.Oracle provider is very particular in what it will accept. Queries that ran fine with the 32b MS Oracle provider must be massaged (or optimized 😉 ) to work.
January 27, 2011 at 1:31 am
Try wrapping your Oracle statement in BEGIN END. This will pass it off to the PL/SQL parser. I have had success doing this with a variety of strange issues like this.
January 28, 2011 at 8:48 am
knight.randy (1/27/2011)
Try wrapping your Oracle statement in BEGIN END. This will pass it off to the PL/SQL parser. I have had success doing this with a variety of strange issues like this.
Agreed - this may solve the issue.
Suggestion converts original SQL statement into a PL/SQL block - remember, on the Oracle world SQL and PL/SQL are not the same, different syntax, different set of instructions, different rules.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 17, 2013 at 5:41 am
I'm having the EXACT same problem. The query parses and previews fine in BIDS, but when I run/debug the package, I get the "missing expression" error. Wrapping the SQL with BEGIN END just changes my error to "No column information was returned by the SQL command", followed by "Command was not prepared". Any other ideas?
January 18, 2013 at 12:30 pm
Found the problem! Single line comments ( -- my comment) in the SQL work fine for parsing and previewing, but evidently the 64bit provider strips out CRLF's which makes for bad SQL. Multi-line comments ( /* my comment */ ) are fine.
January 18, 2013 at 1:41 pm
Thank you for the feedback Swarren - other people will certainly benefit from it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply