RE: Oracle Computed column datatype

Hi SP,

With my further research, this seems to be a known issue.

According to Oracle documentation, on Oracle, columns defined as NUMBER are
mapped to OLEDB type DBTYPE_VARNUMERIC and not to DBTYPE_NUMERIC as columns
defined as NUMBER(p,s) are. SQL Server converts OLEDB type
DBTYPE_VARNUMERIC to Nvarchar

It occurs on both Microsoft OLEDB Provider for Oracle and Oracle OLEDB
Provider for Oracle.

Here are the workarounds:

1- compute the sum in the remote query and not in the portion of the select
statement that is executed on SQL Server:

2- change the table schema on Oracle and specify a precision and scale:

On Oracle:
create table t_38(col1 number(38,1), col2 (38,1))
insert into t_38 values(10.1, 9.9)
On SQL Server:
select col1 + col2 from oracle..SCOTT.T_38

3- Use the OLEDB provider for ODBC and then an ODBC DSN to access Oracle.
Thus you can compute the sum outside the remote query.

4- convert or cast the columns in SQL Server:

select convert(numeric(38,1), col1) + convert(numeric(38,1), col2) from
oracle..SCOTT.T_NUMBER

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

.

Relevant Pages

  • Re: My list D2007 annoyances/todos I would like to see resolved in D2008
    ... OnCalcFields event of the TADODataSet seems to lose track when concatenating ... The problem only shows up when using Oracle (OLEDB ... D2007 will mess up my project files uses ...
    (borland.public.delphi.non-technical)
  • RE: Oracle Computed column datatype
    ... about number columns that MS Oracle driver has problems with. ... Case 2) Using linked server on SQL Server DB. ... SQL Server converts OLEDB type ... > Thus you can compute the sum outside the remote query. ...
    (microsoft.public.data.ado)
  • Re: OLEDBCommand vs. SQLCommand
    ... You do not *have* to use OleDb for Oracle. ... SQL compliant - assuming that SQL is a general specification, ... Both of the above are considered to be SQL statements and both do the ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Multi Database application
    ... With those databases you can choise for OLEDB ... Although tailoring on Oracle for Oracle, SQL for SQL server and OleDb for ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Can someone please tell me...(maybe Frans Bouma)
    ... Why no vendor independent way to get all tables, views, & stored ... The OleDb info is in the registry and the Oracle info is ... everything it could to have tons of clients shipped with Windows. ...
    (microsoft.public.dotnet.framework.adonet)