4 Replies Latest reply: Apr 27, 2013 2:58 PM by 1005913 RSS

    APEX and DG4ODBC connection between 11gR2 and SQLServer

    807562
      Hi,
      I am having trouble establishing a heterogeneous connection between my Oracle 11gR2 database which is running an ApEx application and SQL Server (2005 I believe). Actually, the trouble is with establishing the DB and keeping the ApEx app alive on the same listener.

      Here is what I have done:
      1) Created and tested a System DSN on the oracle server to the SQL Server - the DSN connection test is successful
      2) Edited tnsnames.ora to include the following where WONDERWARE is the name of the DSN:

      WONDERWARE =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
      (CONNECT_DATA = (SID=WONDERWARE))
      (HS=OK)
      )

      3) Created an initwonderware.ora file in the "hs/admin" directory with the following:

      HS_FDS_CONNECT_INFO = WONDERWARE
      HS_FDS_TRACE_LEVEL = OFF

      4) Updated listener.ora so the contents are the following:

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
      )

      SID_LIST_LISTENER =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = WONDERWARE)
      (ORACLE_HOME = E:\oracle\product\11.2.0\db_1)
      (PROGRAM = dg4odbc)
      )
      )

      5) Create a public database link called "wonderwaretest" and the restart the listener.

      I am able to successfully query the SQL Server in SQL Plus with:

      select * from dual@wonderwaretest; (

      I get a return of (not really sure what this means but it's not an error):

      D
      _

      X

      Unfortunately, when I do all of this, I can no longer get to the ApEx application. My thought was that the DG4ODBC connection was interfering with the ApEx app on port 1521, so I even tried creating a second listener called LISTENERWW on port 1522 through the net config assistant, changed tnsnames.ora to be:

      WONDERWARE =
      (DESCRIPTION =
      (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      )
      (CONNECT_DATA = (SID=WONDERWARE))
      (HS=OK)
      )

      and listener.ora to be:


      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
      )

      LISTENERWW =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))
      )
      )

      SID_LIST_LISTENERWW =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = WONDERWARE)
      (ORACLE_HOME = E:\oracle\product\11.2.0\db_1)
      (PROGRAM = dg4odbc)
      )
      )

      And I get the same result. I can hit the SQL Server, but I can't bring up my ApEx app...

      Any suggestions?
      Thanks!
      -Ian