Oracle and SQL Server Data Interoperability Issues - Part 1

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.c

  • Not all versions of oracle support timestamps/datetime with .999999 fractions of a second . Only Oracle 10G and may be 9i ..The older versions do not even have fractions of a second..

  • You are correct Salim. Yes, timestamp is introduced into Oracle since Oracle 9i. Maybe I should have mentioned that in my article.

  • 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.


  • There's the strategy of building the string to pass through. If there's a better way I'd sure love to know. Counting tick marks gets old.

    DECLARE @UIDchar(9),


    Set @UID = '123456789'

    Set @Query = 'SELECT *



    WHERE UID = ''''' + @EID + ''''''')'


    Everett Wilson

  • 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).



  • 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?


  • kkam, you are correct.  Hmmm - I thought I had done so before but obviously not... Perhaps I was using a UDF of some sort as the source.

    In any case, you could still use a #temp table rather than a ##temp table.  The scope is limited to the DB connection rather than the current SQL block but it's still not global.

  • That would probably work.  However, I am not sure what happens if the SP were called again before the first call could finish executing.  This SP is called from a web page and my concern is that before the contents of the table could be cleared after we are done processing the request, the SP could be called again.

    Is that a valid concern or am I off in my understanding of SP execution within SQL server?

    Thanks again

  • The temporary tables prefixed with a single # are scoped within a single database connection.  Thus if you have 10 hits on your website then you might have 10 concurrent database connections (being simple here - no pooling or other voodoo ).  Each connection can have its own temp table.  Thus the use of temp tables within the stored proc should not cause any problem.

    A global temporary table (prefix ##) IS shared - much like a standard fixed table in the database.

  • Thanks!  That narrows down the wires that were crossed for me.  I won't have a chance to test it for at least a few days but I'll keep you posted.

    Thanks again!

  • Nice article and with SQL Server and Oracle, it comes back into focus every couple of years. I am experiencing a problem today that is supposed to be with a date/timestamp; but, there isn't one in my query. This isn't an oddball case, it is happening regularly with a linked server from SS 2005 to Oracle10g.

    The query is as follows:



    The error message is as follows:

    Msg 7354, Level 16, State 1, Line 1

    The OLE DB provider "OraOLEDB.Oracle" for linked server "EMREP" supplied invalid metadata for column "DELAY_TIMESTAMP". The data type is not supported.

    Anybody have a clue?

    (I will post in forum tomorrow)


    David Russell
    Any Cloud, Any Database, Oracle since 1982

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply