Reading microseconds from Sql server into oracle 10g

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Working with Oracle » Reading microseconds from Sql server into...

18 posts, Page 2 of 2

««1 2

Reading microseconds from Sql server into oracle 10g

Rate Topic

Display Mode

Topic Options

Author

Message

caramelkaps

Posted Tuesday, April 10, 2012 2:43 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 2:38 PM
Points: 8, Visits: 13

Thanks...the example u provided works in oracle.

But when i try to exceute this query to get values from sql server table it fails and gives an error date format not recognized.

Query :
select to_char(source_id,'yyyy-mm-dd hh24:mi:ss:ff6') from xyz@dblink_test;--this fails to execute

Post #1281152

Lowell

Posted Tuesday, April 10, 2012 3:01 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649

caramelkaps (4/10/2012)

Paul mentioned the datatype issue for timestamp vs date, and i will again:
what is the datatype in oracle for the column source_id

if source_id is not timestamp, you cannot use the format mask featuring ff6, because that level of preceision requires the timestamp datatype , not date; maybe you can convert date to timestamp before you try to TO_CHAR it?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1281165

caramelkaps

Posted Tuesday, April 10, 2012 3:16 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 2:38 PM
Points: 8, Visits: 13

currently im not trying to insert into Oracle.In oracle for insertion I have the column as Timestamp(9).

Currently Im just trying to select from Sql server via Oracle. and I am unable to retrieve the milliseconds and see that in oracle.

source_id is a date column in sql server.

Post #1281181

Lowell

Posted Tuesday, April 10, 2012 3:25 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649

caramelkaps (4/10/2012)

if the column source_id is a date column in SQL server,
SELECT CONVERT(VARCHAR(35),source_id,121) FROM YOURTABLE
will give a result like
"2012-04-10 17:23:19.360"

and no need to fiddle with Oracle...is that what you want?
or are you converting the value from SQL to Oracle?
show us the command you are running (linked server?)

i'm getting a little lost now.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1281188

caramelkaps

Posted Wednesday, April 11, 2012 8:40 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 2:38 PM
Points: 8, Visits: 13

When I run this command
SELECT CONVERT(VARCHAR(35),source_id,121) FROM test@dblink_test

I get the following error:ORA-00936: missing expression....hence I am not even able to issue this command..

I am running through Toad.I have a dblink created in Oracle 10g which connects to remote third party SQL Server 2008.The dblink uses HS ODBC connection which uses SQL SERVER native client driver version 2009...Im not sure if conversion over ODBC could be an issue.

I tried convert , CAST , To timestamp....

select cast(source_id as timestamp) from test@dblink_test---gives me an output :1/1/1753 12:00:00.000000 AM....the milliseconds just default to 0000000s.

Thanks.

Post #1281599

Lowell

Posted Wednesday, April 11, 2012 8:56 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:24 PM
Points: 11,605, Visits: 27,649

that was my point.
I thought you said you said sourceid exists on SQL Server., and assumed you were doing the work from SQL server, with linked servers to Oracle.

What confused me was test@dblink_test I think you are saying that is a dblink TO SQL Server?
so your commands are all originating on the Oracle side, not SQL Server, correct?
What Table does source_id exist in?

i would be lazy, and on SQL Server,create a view that formats the the source_id to CONVERT(VARCHAR(35),source_id,121)

this is actually an Oracle question, and dblink behavior, and not really a SQL question, which is causing all the confusion...you ar egetting SQL-oriented answers from us.

see this post, where they specifically say you need to cast it to a char to get timestamps:
https://forums.oracle.com/forums/thread.jspa?threadID=2359176

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1281621

PaulB-TheOneAndOnly

Posted Wednesday, April 11, 2012 9:19 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 7:03 AM
Points: 2,979, Visits: 4,389

Lowell (4/11/2012)this is actually an Oracle question, and dblink behavior

Oracle DBLink connections behave like a normal client connection meaning that the server on the remote side of the DBLink perceives the connection as a client - nothing more, nothing else.

My guess is that poster might be mixing sql syntax sometimes hitting the remote SQL Server either with PL/SQL or Oracle SQL syntax which wouldn't fly.

I think that the solution pointed by Lowell, creating a view on SQL Server side casting the column as varchar() then do whatever you want with it on Oracle side.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Post #1281655

caramelkaps

Posted Wednesday, April 11, 2012 10:42 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 2:38 PM
Points: 8, Visits: 13

Thanks everbody for all the insight.

I have posted my issue in oracle forum as well and am yet to receive any reply.

Unfortunately we have no control over the third party sql server DB.
So might not be able to request them to either change all the columns to varchar or create additional views.

Will update here on the progress.

Thanks.

Post #1281751

« Prev Topic | Next Topic »

18 posts, Page 2 of 2

««1 2

Permissions