3 Replies Latest reply: Dec 17, 2012 10:55 AM by evrm RSS

    APEX with Sql Server

      I need to build an APEX application reading some data (not all) from a Sql Server Database.
      I thought to use heterogeneous services (DG4ODBC) via ODBC.
      Does someone have some experience/suggestion about that ?
      Does someone know problems/caveats in such configuration ?

      Edited by: tullio0106 on Dec 17, 2012 5:52 PM
        • 1. Re: APEX with Sql Server
          Hi Tullio,

          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.

          • 2. Re: APEX with Sql Server

            I think your question relates and belongs to Heterogeneous Connectivity forum
            Heterogeneous Connectivity

            My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
            Twitter: http://www.twitter.com/jariolai
            • 3. Re: APEX with Sql Server
              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.