Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

"ORA-00936: missing expression" w/64bit R2 & OraOLEDB.Oracle provider Expand / Collapse
Author
Message
Posted Tuesday, January 25, 2011 2:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:21 AM
Points: 6, Visits: 148
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.
Post #1053539
Posted Thursday, January 27, 2011 1:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:45 PM
Points: 24, Visits: 152
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.
Post #1054411
Posted Friday, January 28, 2011 8:48 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1055366
Posted Wednesday, March 16, 2011 5:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 25, 2011 4:05 AM
Points: 20, Visits: 12
That was a pretty useful reply to the post!!..

Tech Blog
Post #1078855
Posted Thursday, January 17, 2013 5:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 2:07 PM
Points: 2, 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?
Post #1408364
Posted Friday, January 18, 2013 12:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 5, 2013 2:07 PM
Points: 2, 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.
Post #1409061
Posted Friday, January 18, 2013 1:41 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
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.
Post #1409089
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse