SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reading microseconds from Sql server into oracle 10g


Reading microseconds from Sql server into oracle 10g

Author
Message
caramelkaps
caramelkaps
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27928 Visits: 39921
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

caramelkaps
caramelkaps
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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.
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27928 Visits: 39921
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

caramelkaps
caramelkaps
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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.
Lowell
Lowell
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27928 Visits: 39921
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5325 Visits: 4639
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.
caramelkaps
caramelkaps
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

Group: General Forum Members
Points: 26 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.
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