I had to "migrate" a program that worked beween two Oracle Databases via DBLink to a program that would between oracle and SQL Server.
The connection between Oracle and the SQL Server was provided by Oracle Gateway for ODBC (not the Oracle Gateway for SQL Server).
For the developer point of view the connection it's a simple DB Link and the developer without making a query would not know that in the "other side" it's a SQL Server. Good thing? No!
1. the call to table columns must use double quotes (") to ensure that the right capitalization is used
2. To Oracle columns of some types are not available (like ntext)
3. decimal and numeric columns values are truncated to int :-(
4. subqueries with a mix of SQL Server tables and Oracle Tables are not possible
5. using || to concatenate strings is not possible (in SQL Server that is and arithmetic addition)
6. oracle updating a float,decimal or numeric column in a SQL Server table with a not integer value was not possible
7. some transaction error would occour after some updates in sql server tables and then trying to update some oracle table
8. trying to use a varchar2 in a query comparing (or given the value) to a sql server collumn of the type nvarchar would give conversion error.
1. use " (double quotes)
2. make a view with a cast in the SQL Server (or a SUBSTRING like function)
3. make a view with a cast to float in the SQL Server
4. rewrite the code (to a cycle for with another query inside for instance)
5. if possible contatenate ouside the query (to a variable)
6. I wrote the value as and integer (multipied by multiple of 10) to another table in the SQL Server and in that table I had a trigger that would take the value received and the multiplied valor and divide the value and put it in the table (as a non-integer )...its very strange to be able to read floats correctly but not inserting or updating
7. make a commit after changing rows in sql server
8. declare a variable of the column type of the sql server table. use that variable in the query.
Anyone was another way to solve these problems using this gateway (Oracle Gateway for ODBC)? Specially the updates with non-integer values?