Oracle 10g --> Linked Server Using Ora OLE DB --> SQL SERVER 2k

• bjh1977

  Right there with Babe

  Points: 740

  More actions

  January 16, 2008 at 4:57 am

  #182801

  Hi,

  Not sure how much of response this will illicit, seeing as this is the only topic here, but here goes...

  I have a view in an Oracle 10g database called WIP_WORK_ORDERS. Here it is:

  CREATE OR REPLACE VIEW WIP_WORK_ORDERS AS

  select

  a.wo_no,

  c.customer_no,

  c.agreement_id,

  a.reference_no,

  UPPER(a.address1) as ADDRESS1,

  a.reg_date as LOGGED_DATE,

  c.real_f_date as COMPLETION_DATE,

  TO_NUMBER(b.TOTAL_COST) as TOTAL_COST,

  from active_separate a

  left join WORK_ORDER_COST b on a.wo_no = b.wo_no

  left join WORK_ORDER c on a.wo_no = c.wo_no;

  I've left out the WHERE clause because I don't want to confuse the issue.

  I also have a SQL Server 2k database, dbMYDB, and have added the Oracle database as a linked server (OracleDB) using the Oracle OLE DB Provider (Oracle 10g Release 2 ODAC).

  I then run the code below:

  SELECT * INTO WIP_WORK_ORDERS FROM OPENQUERY(OracleDB, 'SELECT * FROM schema.WIP_WORK_ORDERS')

  My question is - why, when this code is executed, do I end up with a table where the TOTAL_COST column is an NVARCHAR, instead of a FLOAT, MONEY or other numerical data type?

  I've wrapped the TOTAL_COST column in a "TO_NUMBER" function in the Oracle view just to make sure, but I still end up with an NVARCHAR. Is this something to do with the driver?

• Jo Pattyn

  SSC-Dedicated

  Points: 32347

  More actions

  January 16, 2008 at 11:15 am

  #768842

  Hello,

  You have to specify the format mask of the to_number function.

  See

  http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm

  At second thought, name the columns. Select column1,column... FROM OPENQUERY (... SELECT ... column1,....) in case the columnorder would change.