SQL Server version 2008 R2 Enterprise 64bit
ORACLE version 11g Enterprise Edition Release 188.8.131.52.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:
CREATE VIEW test
FROM openquery(hpsa, 'select * from truth.accounts')
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.
Name Null? Oracle type
---------- -------- -----------
ACCT_ID NOT NULL NUMBER
ACCT_NAME NOT NULL VARCHAR2(100 CHAR)
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]