SQL Server：Openquery to Oracle：数字/数字的数据类型

On a SQL Server 2012 SP2 I am using OPENQUERY to Oracle 9/10/11. In general I dealt with integers, now we have some OPENQUERY's which address floating numbers. The values are stored in columns with the data type number(). In SQL Servre the significant digits will be truncated. In SQL Server this columns is handled as NUMERIC(38,0).

在SQL Server 2012 SP2上,我使用OPENQUERY到Oracle 9/10/11。一般来说,我处理的是整数,现在我们有一些OPENQUERY来处理浮点数。值存储在数据类型为number()的列中。在SQL Servre中,有效数字将被截断。在SQL Server中,此列的处理方式为NUMERIC(38,0)。

Can I change this data type handling in the settings of the database or the linked server? Or is the best practice to avoid this misinterpretations by generally convert numbers into chars before transfer from an Oracle to SQL Server?

我可以在数据库或链接服务器的设置中更改此数据类型处理吗?或者最好的做法是在从Oracle转移到SQL Server之前通常将数字转换为字符来避免这种误解?

An example I create an table in Oracle:

我在Oracle中创建一个表的示例:

CREATE TABLE oratab 
(
      num_col1 number , num_col2 number(10, 5) ,
      flo_col1 FLOAT , flo_col2 FLOAT(5)
);

INSERT INTO oratab ( num_col1, num_col2, flo_col1, flo_col2)
VALUES (3.1415, 3.1415, 3.1415, 3.1415);

SELECT 
     num_col1, num_col2, num_col3, flo_col1, flo_col2 
FROM oratab;

As expected the result is:

正如所料,结果是:

NUM_COL1 | NUM_COL2 | FLO_COL1 | FLO_COL2
------------------------------------------
 3.1415  |  3.1415  |  3.1415  |   3.142 

When I make a select on this table from a SQL Server

当我从SQL Server中选择此表时

SELECT 
    num_col1, num_col2, flo_col1, flo_col2 
FROM 
    OPENQUERY (ORADB, 'SELECT num_col1, num_col2, flo_col1, flo_col2 FROM oratab')

I get the following result:

我得到以下结果:

NUM_COL1 | NUM_COL2 | FLO_COL1 | FLO_COL2
------------------------------------------
    3    | 3,14150  |  3.1415  |   3.142 

When I convert the numeric data to char I will get the expected result

当我将数字数据转换为char时,我将得到预期的结果

SELECT num_col1 
FROM OPENQUERY (ORADB, 'SELECT to_char(num_col1) FROM oratab')

When I run a select into from this OPENQUERY, SQL Server creates the column num_col1 with the data type NUMERIC(38,0).

当我从此OPENQUERY运行select into时,SQL Server会创建数据类型为NUMERIC(38,0)的列num_col1。

2 个解决方案

#1

You will have to explicitly CAST to the desired precision:

您必须显式CAST到所需的精度:

SELECT 
    num_col1,
    cast(num_col2 as number(10,5)) as num_col2,
    flo_col1, 
    cast(flo_col2 as float(5)) as flo_col2
FROM OPENQUERY (ORADB, 'SELECT num_col1, num_col2, flo_col1, flo_col2 FROM oratab')

#2

SELECT *
FROM OPENQUERY (ORADB, 'SELECT to_char(num_col1), num_col2, flo_col1, flo_col2 FROM oratab')