CODEWITHSUNDEEP
sql-serveroracle-databasetype-conversionsqldatatypesopenquery
Joe Platano
Joe Platano

Reputation: 614

SQL Server : Openquery to Oracle: Data type for number/numeric

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).

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?

An example I create an table in 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

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

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).

Upvotes: 0

Views: 4523

Answers (2)

Basketman
Basketman

Reputation: 1

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

Upvotes: 0

Lucian
Lucian

Reputation: 4001

You will have to explicitly CAST to the desired precision:

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')

Upvotes: 2

Related Questions