This content has been marked as final. Show 3 replies
when using heterogeneous services it is wise to create Oracle views to get the data from the SQL Servers.
Because SQL Servers allows spaces and mixed case in column and table names you have to use double quotes for the names in the Oracle Views.
Some problems might arise if SQL Server has column or table names which are longer than 30 characters.
Sometimes I experienced problems with converting the date formats in SQL Server to Oracle dates.
If you have access to the SQL Servers you could make special views that shorten the names and format the dates etc. for Oracle to access.
I think your question relates and belongs to Heterogeneous Connectivity forum
My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
I've done that and it works reasonably well.
Of course, there are potential performance issues since, at a minimum, the data has to make an extra network trip to move from the SQL Server database to the Oracle database and then to the user. And there are relatively "standard" issues with heterogeneous database links-- SQL Server allows, for example, columns longer than 30 characters or column names that are not valid Oracle identifiers, which can force you to use the DBMS_HS_PASSTHROUGH package to interact with the SQL Server database. These aren't APEX-specific issues, they are general issues with applications that use Heterogeneous Services.
From an APEX perspective, you'd generally want to create synonyms for any objects in the SQL Server database that you want to interact with. That generally makes it easier to use APEX to automatically build processes, forms, etc.