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 ««12

Reading microseconds from Sql server into oracle 10g Expand / Collapse
Author
Message
Posted Tuesday, April 10, 2012 2:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 10, 2012 3:01 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 12,880, Visits: 31,807
caramelkaps (4/10/2012)
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


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
Posted Tuesday, April 10, 2012 3:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Tuesday, April 10, 2012 3:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 12,880, Visits: 31,807
caramelkaps (4/10/2012)
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.



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
Posted Wednesday, April 11, 2012 8:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, April 11, 2012 8:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 12,880, Visits: 31,807
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
Posted Wednesday, April 11, 2012 9:19 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,068, Visits: 4,639
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
Posted Wednesday, April 11, 2012 10:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase ««12

Permissions Expand / Collapse