This discussion is archived
3 Replies Latest reply: Oct 3, 2012 7:21 PM by 560613 RSS

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

tx103108 Newbie
Currently Being Moderated
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. Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?
    560613 Oracle ACE
    Currently Being Moderated
    Sorry this is not a solution to your problem but I was wondering how do you setup Oracle EX with odbc gateway?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points