0

I have an Oracle database containing my data and an SQL Server database getting the data from Oracle through DBLink. Problem is - all numbers from the Oracle tables are accepted at the SQL Server as nvarchar. As a result, when i try to filter the query in the SQL Server with some_number_field = 0 i get: "Conversion failed when converting the nvarchar value '3.141' to data type int." This also happens if i try to select "some_number_field * 1" or similar expressions. Any idea ?

user_0
  • 3,173
  • 20
  • 33
Eldad Meir
  • 1
  • 3
  • Is SQL Server getting data? So I suppose it is linked server. – user_0 Feb 19 '15 at 11:24
  • sorry, I did not understand. what so you mean by "it is linked server" ? I have a dblink in the SQL Server retrieving data from oracle but the formats go wrong... – Eldad Meir Feb 19 '15 at 14:01
  • I also so that when the column type in Oracle is 'number(3,0)' it is ok, the problem is only when the oracle column type is just 'number' – Eldad Meir Feb 19 '15 at 14:04

1 Answers1

1

Today I ran into the same kind of problem. It seems that Oracle field with datatype NUMBER are shown as nvarchar where querying through a linked server. However, NUMBER(x,y) not.

E.g. colB is the NUMBER field from an Oracle View (or table) Try this:

SELECT colA, CAST(colB AS DECIMAL(23,2)) colB
FROM OPENQUERY(LINKED_SERVER_NAME, 'select * from myView')

Note: the DECIMAL(xx,y) values depends of course on your data. Also, remember, if your NUMBER column is a repetitive fraction (eg. 33.33333333 etc), you need to place a round() on the oracle side otherwise the CAST will throw an error.

Rob van Meeuwen
  • 109
  • 1
  • 4