SQLServerCentral / Discuss Content Posted by Haidong Ji / Article Discussions / Article Discussions by Author / Oracle and SQL Server Data Interoperability Issues - Part 1 / Latest Posts

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

arpit.decompiler — Wed, 05 May 2010 10:06:18 GMT

You proposed a work-around for using select openquery but not when using insert openquery. I'd appreciate if you could suggest a work around when inserting into a timestamp column from SQL server using Oracle Linked Server. Here is my syntax:insert OPENQUERY(OracleLinkedServer, 'SELECT [Other Columns], TimeStampColumn FROM OracleSchema.OracleTable') select @data, CONVERT(datetime2(7),'2007-05-02T19:58:47.1234567')TimeStampColumn in Oracle has a datatype of TIMESTAMP(6). I tried all the following to insert timestamps in Oracle but I get the same error in all cases:Error: OLE DB provider "MSDAORA" for linked server "" returned message "Oracle error occurred, but error message could not be retrievedfrom Oracle.".OLE DB provider "MSDAORA" for linked server "" returned message "Data type is not supported.".Msg 7321, Level 16, State 2, Line 5An error occurred while preparing the query "" for execution against OLE DB provider "MSDAORA" for linked server "".

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

gdavidson-740471 — Fri, 29 Jan 2010 04:22:14 GMT

My second post showed how I got around the problem - it offends my aesthetic sensibilities but that's TSQL for you.

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

pearsonrken — Thu, 28 Jan 2010 12:19:22 GMT

Did you ever resolve this issue?Ken Pearson

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

gdavidson-740471 — Tue, 10 Feb 2009 12:00:14 GMT

Got it - using some of the hints earlier in the thread:[font="Courier New"]ALTER PROCEDURE SR_numinput @SR_number VARCHAR(20), @customer VARCHAR(50) OUTPUT, @project VARCHAR(20) OUTPUTASBEGINdeclare @query NVARCHAR(500);declare @parm NVARCHAR(500);set @parm = N'@cust VARCHAR(50) OUTPUT, @proj VARCHAR(50) OUTPUT';set @query = 'select @cust = NAME, @proj = PROJ_NUM FROM openquery(AtlasTest, ''Select P.NAME,P.PROJ_NUM from SXXX.S_SRV_REQ S, SXXX.S_PROJ P where p.row_id=s.proj_id and s.sr_num='''''+@SR_Number + ''''' '')';select @query;execute sp_executesql @query, @parm, @Cust = @customer OUTPUT, @Proj = @project OUTPUT; select @customer as oCustomer, @project as oProject;END[/font]Build up the select string with the input values included. Then use sp_executesql with defining the OUTPUT variables required, and map them to the procedure/trigger variables in the call.Kind of ugly, but there it is.

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

gdavidson-740471 — Tue, 10 Feb 2009 10:15:31 GMT

I'm running into the same problems with OPENQUERY. Not being able to provide it with a string argument containing a dynamically created query is just brain-dead as far as I can see. I, too would like to see the rationale for this.Building up the entire SQL server SQL statement is fine if you simply want to display the output of a query but I need to be able to set variables in a stored procedure or trigger with values abstracted from an Oracle database for further use, but variables set inside an sp_executesql call are localised. ThusALTER PROCEDURE SR_numinput @SR_num VARCHAR(20), @cust Varchar(50) OUTPUT, @proj varchar(20) OUTPUTASBEGINdeclare @query NVARCHAR(500);set @query = 'select @cust = NAME, @proj = PROJ_NUM FROM openquery(AtlasTest, ''Select P.NAME,P.PROJ_NUM from SXXX.S_SRV_REQ S, SXXX.S_PROJ P where p.row_id=s.proj_id and s.sr_num='''''+@SR_Num + ''''' '')';--select @query;execute sp_executesql @query; select @cust as oCustomer, @proj as oProject;ENDputs out a msg states that @cust must be declared when the statement is executed, and @cust declared in the procedure itself does not get set.Any workarounds gratefully received - ThanksGiles

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

JJ B — Fri, 06 Jun 2008 09:15:25 GMT

What a timely article for me. I recently found out that I might have to do some queries/reports off of another agency's Oracle database. I've only worked with MS database products and Sybase. I don't know anything about Oracle. Any advice or "gotchas" people have on doing queries off of Oracle would be appreciated. Thanks,- JJ

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

SuperDBA-207096 — Fri, 06 Jun 2008 07:52:42 GMT

Excellent article. I've found it's a crapshoot getting things to work w/ oracle reliably from SQL server.....

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

Craig A. Silvis — Fri, 06 Jun 2008 06:30:12 GMT

Your article referred to timestamps but I've had the same problem with date fields in Oracle and had to do the same thing you suggested and convert them to character data to make it work.

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

Christian Buettner-167247 — Fri, 06 Jun 2008 05:21:43 GMT

Good Article and Good Topic.I also stumbeled accross the oracle timestamp issue and used TO_CHAR as well to get around it.Btw, here is some information regarding the deprecated timestamp datatype and its replacement "rowversion" in SQL Server 2008.http://msdn.microsoft.com/en-us/library/ms182776(SQL.100).aspx

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

Anipaul — Fri, 06 Jun 2008 04:08:35 GMT

Good article. The problems were well discussed.

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

debrucer1 — Mon, 17 Mar 2008 18:50:28 GMT

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 1The 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!

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

kkam — Fri, 16 Jun 2006 01:53:00 GMT

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!

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

Ian Yates — Thu, 15 Jun 2006 23:33:00 GMT

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.

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

kkam — Thu, 15 Jun 2006 00:32:00 GMT

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

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

Ian Yates — Wed, 14 Jun 2006 21:24:00 GMT

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.

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

kkam — Tue, 13 Jun 2006 20:44:00 GMT

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.

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

win — Tue, 13 Jun 2006 19:34:00 GMT

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

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

Ian Yates — Sat, 10 Jun 2006 00:12:00 GMT

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?

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

kkam — Fri, 09 Jun 2006 14:01:00 GMT

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.

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

ewilson10 — Fri, 09 Jun 2006 11:30:00 GMT

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 @UID char(9), @Query varchar(5000)Set @UID = '123456789'Set @Query = 'SELECT *FROM OPENQUERY(LINKED_SERVER,''SELECT * FROM TABLE_NAME WHERE UID = ''''' + @EID + ''''''')'exec(@Query)

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

kkam — Fri, 09 Jun 2006 10:30:00 GMT

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

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

Amadou Diarra — Tue, 24 May 2005 14:31:00 GMT

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

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

Haidong Ji — Mon, 19 Apr 2004 09:55:00 GMT

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

RE: Oracle and SQL Server Data Interoperability Issues - Part 1

salim — Mon, 19 Apr 2004 01:16:00 GMT

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

Oracle and SQL Server Data Interoperability Issues - Part 1

Haidong Ji — Sat, 10 Apr 2004 19:40:00 GMT

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