SQL gets datatypes wrong using OPENQUERY to oracle

  • g.britton

    SSChampion

    Points: 13689

    SQL Server version 2008 R2 Enterprise 64bit

    ORACLE version 11g Enterprise Edition Release 11.2.0.3.0 64bit

    SQL Server linked server to Oracle using Oracle ODBC driver.

    I built a SQL Server view that uses OPENQUERY to query a table/view on an Oracle database. It looks like this:

    USE tempdb

    GO

    CREATE VIEW test

    AS

    SELECT *

    FROM openquery(hpsa, 'select * from truth.accounts')

    GO

    sp_help test -- see what we get

    Then, using the Oracle client SQLPLUS, I ran:

    SQL> describe truth.accounts

    I was quite surprised to see that Oracle tells a different (very different, in some cases) than SQl Server. e.g.

    Oracle:

    Name Null? Oracle type

    ---------- -------- -----------

    ACCT_ID NOT NULL NUMBER

    ACCT_NAME NOT NULL VARCHAR2(100 CHAR)

    CREATED_DT DATE

    SQL Server:

    Column_name Type Computed Length Nullable

    ACCT_ID nvarchar no 768 yes

    DISPLAY_NAME nvarchar no 100 no

    CREATED_DT datetime2 no 7 yes

    so, there are at least three big differences. SQL thinks that columns with type "Number" are actually NVARCHAR(768). VARCHAR2 columns are returned as NVARCHAR (at least the length is OK). DATEs are change to datetime2(7).

    I'd like to understand this better.

    In my view, I am changing the return columns to match what Oracle reports (via SQLPLUS) as the "true" datatypes.

    Is this the best approach? If not, what is a better approach? It seems I just can't believe or accept what SQL Server reports.

    Gerald Britton, MCSE-DP, MVPToronto PASS Chapter[/url]

  • Jo Pattyn

    SSC-Dedicated

    Points: 31376

    "Number" are actually NVARCHAR(768)

    ->SQLServer does that when there is no precision and scale specified in Oracle

    VARCHAR2 columns are returned as NVARCHAR (at least the length is OK)

    ->Depends on the Oracle database characterset which can be unicode like AL32UTF8 (even for varchar)

    DATEs are change to datetime2(7)

    ->Because SQL Server DATETIME starts at January 1, 1753

    You could use TO_NUMBER, CONVERT ... in your OPENQUERY to get the dateformat closer to what SQL Server understands

  • sgmunson

    SSC Guru

    Points: 110449

    The basic problem here is assuming that just having an ODBC driver is enough to accurately convert rather different data types seamlessly between Oracle and SQL Server. The differences are too large for such a simplistic solution, and as the connectivity passes thru ODBC, which only supports a subset of the data types of either SQL Server or Oracle, imperfections are going to happen. There really can't be a one-size fits all solution to the problem, either. Dates are the biggest piece of grief, as numeric data can at least be CAST or CONVERTed to the right type. FYI...

    Steve
    ‌(aka sgmunson)
    ‌:) 🙂 🙂
    Health & Nutrition

  • nancyagnes30

    Newbie

    Points: 1

    Hi,

    I'm create database inside creating a table.Now table data are deleted but i want that data's.How can i retrieve the data's.Can you clarify my doubts..?!

  • roydevid

    Newbie

    Points: 3

    Being new to the blogging world I feel like there is still so much to learn. Your tips helped to clarify a few things for me as well as giving.

    https://www.bangaloretrainingacademy.com/Data-Analytics-Course-in-Bangalore.html

  • roydevid

    Newbie

    Points: 3

    Really it was an awesome article,very interesting to read.You have provided an nice article,Thanks for sharing.

    http://www.foreignlanguageschool.in/best-italian-language-training-institute-school.html

    Attachments:
    You must be logged in to view attached files.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply