Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Oracle and SQL Server Data Interoperability Issues - Part 1 Expand / Collapse
Author
Message
Posted Saturday, April 10, 2004 7:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
Points: 55, Visits: 60
Comments posted to this topic are about the content posted at http://www.sqlservercentral.c


Post #110674
Posted Monday, April 19, 2004 1:16 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 25, 2011 4:46 AM
Points: 409, 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..


Post #111822
Posted Monday, April 19, 2004 9:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 18, 2013 11:03 AM
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.


Post #111949
Posted Tuesday, May 24, 2005 2:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2008 12:57 PM
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 

Post #184950
Posted Friday, June 9, 2006 10:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 5:57 PM
Points: 16, Visits: 257

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




Post #286345
Posted Friday, June 9, 2006 11:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, March 23, 2009 9:44 AM
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
Post #286361
Posted Friday, June 9, 2006 2:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 5:57 PM
Points: 16, Visits: 257

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. 




Post #286425
Posted Saturday, June 10, 2006 12:12 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444

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?




Post #286471
Posted Tuesday, June 13, 2006 7:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 15, 2013 8:56 AM
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




Post #287214
Posted Tuesday, June 13, 2006 8:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 5:57 PM
Points: 16, Visits: 257

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.




Post #287218
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse