Oracle and SQL Server Data Interoperability Issues - Part 1

Question

Oracle and SQL Server Data Interoperability Issues - Part 1

Haidong Ji

SSCrazy

Points: 2453
More actions
April 10, 2004 at 7:40 pm

#161359

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

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

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

salim SSChasing Mays Points: 659 More actions · Answer 1

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

Haidong Ji SSCrazy Points: 2453 More actions · Answer 2

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

Amadou Diarra Valued Member Points: 54 More actions · Answer 3

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

kkam Say Hey Kid Points: 661 More actions · Answer 4

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

ewilson10 SSCarpal Tunnel Points: 4255 More actions · Answer 5

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),

@Queryvarchar(5000)

Set @UID = '123456789'

Set @Query = 'SELECT *

FROM OPENQUERY(LINKED_SERVER,''SELECT *

FROM TABLE_NAME

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

exec(@Query)

Everett Wilson
ewilson10@yahoo.com

kkam Say Hey Kid Points: 661 More actions · Answer 6

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.

Ian Yates SSCoach Points: 19738 More actions · Answer 7

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?

win SSCommitted Points: 1569 More actions · Answer 8

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

kkam Say Hey Kid Points: 661 More actions · Answer 9

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.

Ian Yates SSCoach Points: 19738 More actions · Answer 10

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.

kkam Say Hey Kid Points: 661 More actions · Answer 11

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

Ian Yates SSCoach Points: 19738 More actions · Answer 12

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.

kkam Say Hey Kid Points: 661 More actions · Answer 13

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!

debrucer1 SSCertifiable Points: 5366 More actions · Answer 14

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:

SELECT [QUEUE]

FROM [EMREP]..[SYSMAN].[AQ$MGMT_LOADER_QTABLE]

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)

Thanks!

Thank-you,
David Russell
Any Cloud, Any Database, Oracle since 1982