IN MS SQL the timestamp datatype is used  as a mechanism for version-stamping table rows. Do we have the same mechanism in ORACLE? Thanks 

Nice article, right to the point.

I have a question (since this is "Part 1") regarding Part 2.  Do you know of any way to pass in variables through the OpenQuery statement?  There are many times that you want to filter the returnset on the Oracle side.

Thanks

Thanks for the suggestion.  We have tried that and although it works, the limitations of Dynamic SQL don't fit well with the rest of our needs. 

The return from our query needs to be inserted into a local Table variable.  Dynamic SQL can only access this table if I declare it as a standing table or a global temporary table (##)...neither option will work for this solution.  We chose to use a Table variable so each time the SP executes it maintains the table within its own scope. 

You could use sp_executesql which at least cleans things up a bit with the quote counting..

Also, you can insert into a table variable from an exec statement - so the dynamic SQL is not out of the question for you.  It is rather annoying that openQuery doesn't take variables as inputs - anyone know why it's designed that way?  Is it to allow SQL Server Optimiser to know about the statement it will be sending?

There's another subtle difference about varchars.  The maximum field width is 4000 as you mentioned, but the declaration of a varchar variable in PL/SQL can be as large as 32767 (32Kb - 1).

Cheers,

Win

Follow-up question for Ian Yates' post.

I have been operating under the belief that you cannot "insert into a table variable from an exec statement."  If that is possible I believe that would solve our problems.  Could you give an example of the syntax?

Thanks.