3 Replies Latest reply: Oct 3, 2012 9:21 PM by Jerry Ireland RSS

    Can Apex 4.1.1 see non-Oracle tables across dblinks using gateway?

    tx103108
      OS: Windows7 64-bit
      APEX: APEX 4.1.1
      OracleXE: 11gR2
      Oracle ODBC Gateway: 11gR2
      Remote DB: Sybase Anywhere 10
      =====================

      When trying to develop an application (e.g. interactive report), APEX does not see tables from remote database even after public synoym is created in local OracleXE schema that current workspace points to?
      I can with no issues -- via APEX SQL Workshop -- run SQL that accesses the remote database tables through the dblink.
      Any suggestions?
        • 1. Re: Can Apex 4.1.1 see non-Oracle tables across dblinks using gateway?
          Sven W.
          user601798 wrote:

          When trying to develop an application (e.g. interactive report), APEX does not see tables from remote database even after public synoym is created in local OracleXE schema that current workspace points to?
          I can with no issues -- via APEX SQL Workshop -- run SQL that accesses the remote database tables through the dblink.
          Any suggestions?
          Create a view on top of the synonym.

          Apex can work with the views. It does not work well with synonyms. But keep the synonym, since that makes your view indepentent of the name of the DB link.

          So the typical recommened structure would be:

          <pre>
          remote database table
          -> access via db link
          -> synonym on remote table
          -> View on Synonym
          -> Apex uses View (Wizards etc.)
          </pre>

          If using a non oracle DB you might come across problem with certain concepts like ROWIDs. Obviously cyou can't build a form depending on a rowid, when working with a non oracle database. It should work with a normal primary/unique key however.

          Edited by: Sven W. on Aug 15, 2012 12:46 PM
          • 2. Re: Can Apex 4.1.1 see non-Oracle tables across dblinks using gateway?
            tx103108
            Tried:

            create or replace public synonym sy_my_table for other.remote_table@remote_db;
            create or replace view my_table as select * from sy_my_table;
            grant all on my_table to my_user;

            Still can not see my_table in APEX wizards.

            select * from my_table;
            select * from sy_my_table;

            both work fine from APEX SQL Workshop.

            Please advise and thanks for your time.
            • 3. Re: Can Apex 4.1.1 see non-Oracle tables across dblinks using gateway?
              Jerry Ireland
              Sorry this is not a solution to your problem but I was wondering how do you setup Oracle EX with odbc gateway?