I have a linked server set up to an Oracle Database. I have created views to tables in that database.
Initially I used the 4 part version to connect to the table but when I started getting the 7356 error for inconsistent metadata, I changed to using the OPENQUERY method.
I have several stored procedures that return recordsets using joins between my SQL tables and the Oracle tables. My problem is that I am not getting all records returned with the OPENQUERY view. So far, in my testing, I am only getting one record returned. If I go back to the 4 part method and create a view with that and use that view in my join I get the correct number of records returned. (If I don't get the 7356 error on the view.)
The field in the join in the Oracle DB is defined as nvarchar(25) while the field in the join in the SQL table is defined as varchar(25). I use rtrim in the join to avoid data entry issues with spaces. This is an example select statement:
select * from OraclePartsTable left join SQLQuoteLineTable
(I only want to return part data for parts in the selected quote)
With OPENQUERY view I get 1 record, with 4 part view I get all the correct records back.
I either need another work around for the 7356 error issue or I need a way to join Oracle to SQL tables that returns the correct result set. I cannot do anything on the Oracle server except read the data. We are interfacing our application to an ERP package that uses Oracle.