SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


"ORA-00936: missing expression" w/64bit R2 & OraOLEDB.Oracle provider


"ORA-00936: missing expression" w/64bit R2 & OraOLEDB.Oracle provider

Author
Message
_Louie_
_Louie_
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 180
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.
Randy Knight
Randy Knight
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 155
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.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14755 Visits: 4639
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.
alexsmth114
alexsmth114
SSC Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 12
That was a pretty useful reply to the post!!..

Tech Blog
swarren 48095
swarren 48095
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 18
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?
swarren 48095
swarren 48095
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 18
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.
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14755 Visits: 4639
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search