May 15, 2015 at 7:20 am
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_nameType ComputedLength Nullable
ACCT_ID nvarcharno 768 yes
DISPLAY_NAMEnvarcharno 100 no
CREATED_DTdatetime2no 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, Pluralsight courses
May 15, 2015 at 9:56 am
"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
July 7, 2015 at 9:56 am
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 25, 2015 at 4:24 am
March 23, 2020 at 4:53 am
This was removed by the editor as SPAM
March 23, 2020 at 4:54 am
This was removed by the editor as SPAM
May 20, 2020 at 11:00 am
This was removed by the editor as SPAM
June 10, 2020 at 11:36 am
This was removed by the editor as SPAM
August 12, 2020 at 8:45 am
This was removed by the editor as SPAM
March 6, 2021 at 6:45 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply