Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Oracle and SQL Server Data Interoperability Issues - Part 1


Oracle and SQL Server Data Interoperability Issues - Part 1

Author
Message
Haidong Ji
Haidong Ji
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 60
Comments posted to this topic are about the content posted at http://www.sqlservercentral.c



salim
salim
SSC-Addicted
SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)SSC-Addicted (411 reputation)

Group: General Forum Members
Points: 411 Visits: 17
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
Haidong Ji
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 60
You are correct Salim. Yes, timestamp is introduced into Oracle since Oracle 9i. Maybe I should have mentioned that in my article.



Amadou Diarra
Amadou Diarra
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 8

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
kkam
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 343

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
ewilson10
SSC-Enthusiastic
SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)SSC-Enthusiastic (179 reputation)

Group: General Forum Members
Points: 179 Visits: 22
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)



Everett Wilson
ewilson10@yahoo.com
kkam
kkam
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 343

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
Ian Yates
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 445

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
win
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 176

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
kkam
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 343

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.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search